Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Use RANK OVER PARTITION function in Hive 0.11


Copy link to this message
-
Re: Use RANK OVER PARTITION function in Hive 0.11
You can see my query below :

SELECT
            mag.co_magasin,
            dem.id_produit                                  as
id_produit_orig,
            pnvente.dt_debut_commercial                     as
dt_debut_commercial,
            COALESCE(pnvente.id_produit,dem.id_produit)     as id_produit,
            RANK() OVER (PARTITION BY mag.co_magasin, dem.id_produit ORDER
BY pnvente.dt_debut_commercial DESC,
COALESCE(pnvente.id_produit,dem.id_produit) DESC) as rang

FROM default.demarque_mag_jour dem
          LEFT OUTER JOIN default.produit_norm pn
          ON  pn.co_societe = dem.co_societe
          AND pn.id_produit = dem.id_produit
          LEFT OUTER JOIN default.produit_norm pnvente
          ON  pnvente.co_societe = pn.co_societe
          AND pnvente.co_produit_rfu = pn.co_produit_lip
          INNER JOIN default.kpi_magasin mag
          ON mag.id_magasin = dem.id_magasin

GROUP BY
            mag.co_magasin,
            dem.id_produit,
            pnvente.dt_debut_commercial,
            COALESCE(pnvente.id_produit,dem.id_produit);
2013/7/16 Richa Sharma <[EMAIL PROTECTED]>

> Can you share query with just RANK().
>
> Richa
>
>
> On Tue, Jul 16, 2013 at 6:08 PM, Jérôme Verdier <
> [EMAIL PROTECTED]> wrote:
>
>> Hi Richa,
>>
>> I tried to execute the rank function alone, but the result is the same
>>
>> Thanks
>>
>>
>> 2013/7/16 Richa Sharma <[EMAIL PROTECTED]>
>>
>>> Hi Jerome
>>>
>>>
>>> I think the problem is you are trying to use MIN, SUM and RANK function
>>> in a single query.
>>>
>>> Try to get the rank first in a query and on top of it apply these
>>> aggregate functions
>>>
>>> Richa
>>>
>>>
>>>
>>>
>>> On Tue, Jul 16, 2013 at 2:15 PM, Jérôme Verdier <
>>> [EMAIL PROTECTED]> wrote:
>>>
>>>> Hi,
>>>>
>>>> I have a problem while using RANK OVER PARTITION function with Hive.
>>>>
>>>> Hive is in version 0.11 and, as we can see here :
>>>> https://cwiki.apache.org/Hive/languagemanual-windowingandanalytics.html,
>>>> we can now use these functions in Hive.
>>>>
>>>> But, when i use it, i encountered this error :
>>>>
>>>> FAILED: SemanticException Failed to breakup Windowing invocations into
>>>> Groups. At least 1 group must only depend on input columns. Also check for
>>>> circular dependencies.
>>>> Underlying error:
>>>> org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException: One or more
>>>> arguments are expected.
>>>>
>>>> Here is my script :
>>>>
>>>> SELECT
>>>>             mag.co_magasin,
>>>>             dem.id_produit                                  as
>>>> id_produit_orig,
>>>>             pnvente.dt_debut_commercial                     as
>>>> dt_debut_commercial,
>>>>             COALESCE(pnvente.id_produit,dem.id_produit)     as
>>>> id_produit,
>>>>             min(
>>>>               CASE WHEN dem.co_validation IS NULL THEN 0 ELSE 1 END
>>>>             )                                               as
>>>> flg_demarque_valide,
>>>>             sum(CASE WHEN dem.co_validation IS NULL THEN 0 ELSE
>>>> CAST(dem.mt_revient_ope AS INT) END)
>>>>                                                             as
>>>> me_dem_con_prx_cs,
>>>>             0                                               as
>>>> me_dem_inc_prx_cs,
>>>>             0                                               as
>>>> me_dem_prov_stk_cs,
>>>>             sum(CASE WHEN dem.co_validation IS NULL THEN 0 ELSE
>>>> CAST(dem.qt_demarque AS INT) END)
>>>>                                                             as
>>>> qt_dem_con,
>>>>             0                                               as
>>>> qt_dem_inc,
>>>>             0                                               as
>>>> qt_dem_prov_stk, -- !!!!!!!! VIRGULE
>>>>             RANK() OVER (PARTITION BY mag.co_magasin, dem.id_produit
>>>> ORDER BY pnvente.dt_debut_commercial DESC,
>>>> COALESCE(pnvente.id_produit,dem.id_produit) DESC) as rang
>>>>           from default.calendrier cal
>>>>           INNER JOIN default.demarque_mag_jour dem
*Jérôme VERDIER*
06.72.19.17.31
[EMAIL PROTECTED]
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB