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 <verdier.jerom...@gmail.com>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 > ON CASE WHEN dem.co_societe = 1 THEN 1 ELSE 2 END = > '${hiveconf:in_co_societe}' -- A modifier > AND dem.dt_jour = cal.dt_jour > 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 > AND pnvente.co_type_motif='05' > INNER JOIN default.kpi_magasin mag > ON mag.co_societe = '${hiveconf:in_co_societe}' > AND mag.id_magasin = dem.id_magasin > WHERE cal.dt_jour = '${hiveconf:in_dt_jour}' > AND NOT (dem.co_validation IS NULL AND cal.dt_jour > > unix_timestamp()-3*60*60*24) -- A verifier > -- JYP 4.4 > AND dem.co_operation_magasin IN ('13','14','32') > GROUP BY > mag.co_magasin, > dem.id_produit, > pnvente.dt_debut_commercial, > COALESCE(pnvente.id_produit,dem.id_produit) > > Thanks. > > -- > *Jérôme VERDIER* > 06.72.19.17.31 > verdier.jerom...@gmail.com > >
