Return-Path: X-Original-To: apmail-hive-user-archive@www.apache.org Delivered-To: apmail-hive-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 6E736C32E for ; Wed, 17 Jul 2013 09:38:55 +0000 (UTC) Received: (qmail 26160 invoked by uid 500); 17 Jul 2013 09:38:50 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 26108 invoked by uid 500); 17 Jul 2013 09:38:48 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 26095 invoked by uid 99); 17 Jul 2013 09:38:46 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 17 Jul 2013 09:38:46 +0000 X-ASF-Spam-Status: No, hits=1.7 required=5.0 tests=FREEMAIL_ENVFROM_END_DIGIT,HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of verdier.jerome66@gmail.com designates 209.85.128.44 as permitted sender) Received: from [209.85.128.44] (HELO mail-qe0-f44.google.com) (209.85.128.44) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 17 Jul 2013 09:38:40 +0000 Received: by mail-qe0-f44.google.com with SMTP id 5so1001350qeb.31 for ; Wed, 17 Jul 2013 02:38:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=CAYwAFykh6kjRDqImkiHKvIcwrXmbBLc1DJsZbhQsl0=; b=LzAlr9U0y8sv3E67T+Z6d5BKYC8Yqk+m/BciIAqTUYfXLIMeq/L01AFojzA3D/SRuA ZbJbwpzlhF09rhN0yrqYi/Nk8KArUr88hIGFpqoVt4dL5/Eqa6zdDhBcDJ7JTnmChWz9 c7TsLP4uuwB8fLYObzbSA8DsHdkWKP014dAaxlcRBdXGVpEYA8k9H1uAlb2N0zZBCkqQ xNJEbMh0N16VeKigS5f3gK6s/kzL3yoyuKBQ0pGxGuACHpDpDg0B15RKW6itTzWAS9iQ JgXEU9goE5hEoTYKEn+DXX007Riel1sjJZ7yYyPlBPitZ7y1BSl5s2FmbOL7OZjL60S2 NYRQ== MIME-Version: 1.0 X-Received: by 10.224.137.130 with SMTP id w2mr840311qat.71.1374053899936; Wed, 17 Jul 2013 02:38:19 -0700 (PDT) Received: by 10.49.48.52 with HTTP; Wed, 17 Jul 2013 02:38:19 -0700 (PDT) In-Reply-To: References: Date: Wed, 17 Jul 2013 11:38:19 +0200 Message-ID: Subject: Re: Use RANK OVER PARTITION function in Hive 0.11 From: =?ISO-8859-1?Q?J=E9r=F4me_Verdier?= To: user@hive.apache.org Content-Type: multipart/alternative; boundary=001a11c2c1ae9ba7de04e1b1da92 X-Virus-Checked: Checked by ClamAV on apache.org --001a11c2c1ae9ba7de04e1b1da92 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi Richa, I have tried one query, with what i've understand of Vijay's tips. SELECT code_entite, RANK(mag.me_vente_ht) OVER (PARTITION BY mag.co_societe ORDER BY mag.me_vente_ht) AS rank FROM default.thm_renta_rgrp_produits_n_1 mag; This query is working, it gives me results. You say that maybe i'm hitting the same bug of JIRA HIVE-4663, but query is also failling when i put analytical columns in... 2013/7/17 Richa Sharma > Vijay > > Jerome has already passed column -> mag.co_societe for rank. > > syntax -> RANK() OVER (PARTITION BY mag.co_societe ORDER BY > mag.me_vente_ht) > This will generate a rank for column mag.co_societe based on column value > me_vente_ht > > Jerome, > > Its possible you are also hitting the same bug as I mentioned in my email > before. > > > Richa > > > On Wed, Jul 17, 2013 at 2:31 PM, Vijay wrote: > >> As the error message states: "One ore more arguments are expected," you >> have to pass a column to the rank function. >> >> >> On Wed, Jul 17, 2013 at 1:12 AM, J=E9r=F4me Verdier < >> verdier.jerome66@gmail.com> wrote: >> >>> Hi Richa, >>> >>> I have tried a simple query without joins, etc.... >>> >>> SELECT RANK() OVER (PARTITION BY mag.co_societe ORDER BY >>> mag.me_vente_ht),mag.co_societe, mag.me_vente_ht FROM >>> default.thm_renta_rgrp_produits_n_1 mag; >>> >>> Unfortunately, the error is the same like previously. >>> >>> Error: Query returned non-zero code: 40000, cause: FAILED: >>> SemanticException Failed to breakup Windowing invocations into Groups. = At >>> least 1 group must only depend on input columns. Also check for circula= r >>> dependencies. >>> >>> Underlying error: >>> org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException: One or more >>> arguments are expected. >>> SQLState: 42000 >>> ErrorCode: 40000 >>> >>> >>> >>> >>> 2013/7/17 Richa Sharma >>> >>>> Jerome >>>> >>>> I would recommend that you try Rank function with columns from just on= e >>>> table first. >>>> Once it is established that rank is working fine then add all the join= s. >>>> >>>> I am still on Hive 0.10 so cannot test it myself. >>>> However, I can find a similar issue on following link - so its possibl= e >>>> you are facing issues due to this reported bug. >>>> >>>> https://issues.apache.org/jira/browse/HIVE-4663 >>>> >>>> >>>> Richa >>>> >>>> >>>> On Tue, Jul 16, 2013 at 6:41 PM, J=E9r=F4me Verdier < >>>> verdier.jerome66@gmail.com> wrote: >>>> >>>>> 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 =3D dem.co_societe >>>>> AND pn.id_produit =3D dem.id_produit >>>>> LEFT OUTER JOIN default.produit_norm pnvente >>>>> ON pnvente.co_societe =3D pn.co_societe >>>>> AND pnvente.co_produit_rfu =3D pn.co_produit_lip >>>>> INNER JOIN default.kpi_magasin mag >>>>> ON mag.id_magasin =3D 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 >>>>> >>>>>> Can you share query with just RANK(). >>>>>> >>>>>> Richa >>>>>> >>>>>> >>>>>> On Tue, Jul 16, 2013 at 6:08 PM, J=E9r=F4me Verdier < >>>>>> verdier.jerome66@gmail.com> wrote: >>>>>> >>>>>>> Hi Richa, >>>>>>> >>>>>>> I tried to execute the rank function alone, but the result is the >>>>>>> same >>>>>>> >>>>>>> Thanks >>>>>>> >>>>>>> >>>>>>> 2013/7/16 Richa Sharma >>>>>>> >>>>>>>> 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=E9r=F4me Verdier < >>>>>>>> verdier.jerome66@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-windowingandanalytic= s.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 m= ore >>>>>>>>> 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 E= ND >>>>>>>>> ) 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 =3D 1 THEN 1 ELSE 2 END = =3D >>>>>>>>> '${hiveconf:in_co_societe}' -- A modifier >>>>>>>>> AND dem.dt_jour =3D cal.dt_jour >>>>>>>>> LEFT OUTER JOIN default.produit_norm pn >>>>>>>>> ON pn.co_societe =3D dem.co_societe >>>>>>>>> AND pn.id_produit =3D dem.id_produit >>>>>>>>> LEFT OUTER JOIN default.produit_norm pnvente >>>>>>>>> ON pnvente.co_societe =3D pn.co_societe >>>>>>>>> AND pnvente.co_produit_rfu =3D pn.co_produit_lip >>>>>>>>> AND pnvente.co_type_motif=3D'05' >>>>>>>>> INNER JOIN default.kpi_magasin mag >>>>>>>>> ON mag.co_societe =3D '${hiveconf:in_co_societe}' >>>>>>>>> AND mag.id_magasin =3D dem.id_magasin >>>>>>>>> WHERE cal.dt_jour =3D '${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=E9r=F4me VERDIER* >>>>>>>>> 06.72.19.17.31 >>>>>>>>> verdier.jerome66@gmail.com >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> *J=E9r=F4me VERDIER* >>>>>>> 06.72.19.17.31 >>>>>>> verdier.jerome66@gmail.com >>>>>>> >>>>>>> >>>>>> >>>>> >>>>> >>>>> -- >>>>> *J=E9r=F4me VERDIER* >>>>> 06.72.19.17.31 >>>>> verdier.jerome66@gmail.com >>>>> >>>>> >>>> >>> >>> >>> -- >>> *J=E9r=F4me VERDIER* >>> 06.72.19.17.31 >>> verdier.jerome66@gmail.com >>> >>> >> > --=20 *J=E9r=F4me VERDIER* 06.72.19.17.31 verdier.jerome66@gmail.com --001a11c2c1ae9ba7de04e1b1da92 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Hi Richa,

I have tried one que= ry, with what i've understand of=A0 Vijay's tips.

SELECT code_entite, RANK(mag.me_ven= te_ht) OVER (PARTITION BY mag.co_societe ORDER BY=A0 mag.me_vente_ht) AS ra= nk FROM default.thm_renta_rgrp_produits_n_1 mag;

This query is working, it gives me results.

You say = that maybe i'm hitting the same bug of JIRA HIVE-4663, but query is als= o failling when i put analytical columns in...


2013/7/17 Richa Sharma <mailt= orichasharma@gmail.com>
Vijay

Jerome has already passed column = ->=A0mag.co_societe for rank.=A0

= syntax -> RANK() OVER (PARTITION BY mag.co_societe ORDER BY=A0 mag.me_ve= nte_ht)=A0
This will generate a rank for column mag.co= _societe based on column value=A0me_vente_ht

Jerome,

Its possible you are a= lso hitting the same bug as I mentioned in my email before.


Ric= ha


On Wed, Jul 17, 2013 at 2:31= PM, Vijay <techvd@gmail.com> wrote:
As the error message states: "One ore more arguments = are expected," you have to pass a column to the rank function.


On Wed, Jul 17, 2013 at 1:12 AM, J=E9r=F4me Verdier <<= a href=3D"mailto:verdier.jerome66@gmail.com" target=3D"_blank">verdier.jero= me66@gmail.com> wrote:
Hi Richa,

=
I have tried a simple query without joins, etc....

SELECT RANK() OVER (PARTITION BY mag= .co_societe ORDER BY=A0 mag.me_vente_ht),mag.co_societe, mag.me_vente_ht FR= OM default.thm_renta_rgrp_produits_n_1 mag;

Unfortunately, the error is the same like previously.

Error: Query returned non-zer= o code: 40000, cause: FAILED: SemanticException Failed to breakup Windowing= invocations into Groups. At least 1 group must only depend on input column= s. Also check for circular dependencies.

Underlying error: org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException: = One or more arguments are expected.
SQLState:=A0 42000
ErrorCod= e: 40000




2013/7/17 Richa Sharma <mailtorichasharma@gmail.com>
Jerome

I would recommend that you try R= ank function with columns from just one table first.
Once it is e= stablished that rank is working fine then add all the joins.

I am still on Hive 0.10 so cannot test it myself.=
However, I can find a similar issue on following link - so its p= ossible you are facing issues due to this reported bug.



Richa


On Tue, Jul 16, 2013 at 6:41 PM, J=E9r=F4me Verdier <verdier.jerome66@gmail.com> wrote:
You can see my query below = :

SELECT
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 mag.co_magasin,
=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0 dem.id_produit=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 as id_produit_orig,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 pnvente.dt_debut_commercial=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 as dt_debut_commercial,=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 COALESCE(pnvente.id_produit,dem.id_produ= it)=A0=A0=A0=A0 as id_produit,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 RANK() OVER (PARTITION BY mag.co_magasin, dem.id_produit ORDER BY pnven= te.dt_debut_commercial DESC, COALESCE(pnvente.id_produit,dem.id_produit) DE= SC) as rang
=A0=A0=A0=A0=A0=A0=A0=A0=A0
FROM default.demarque_mag_jour dem
=A0=A0=A0=A0=A0=A0=A0=A0=A0 LEFT OUTER JOIN default.produit_norm pn=A0=A0=A0=A0=A0=A0=A0=A0=A0 ON=A0 pn.co_societe =3D dem.co_societe
=A0= =A0=A0=A0=A0=A0=A0=A0=A0 AND pn.id_produit =3D dem.id_produit
=A0=A0=A0=A0=A0=A0=A0=A0=A0 LEFT OUTER JOIN default.produit_norm pnvente =A0=A0=A0=A0=A0=A0=A0=A0=A0 ON=A0 pnvente.co_societe =3D pn.co_societe
= =A0=A0=A0=A0=A0=A0=A0=A0=A0 AND pnvente.co_produit_rfu =3D pn.co_produit_li= p
=A0=A0=A0=A0=A0=A0=A0=A0=A0 INNER JOIN default.kpi_magasin mag=A0=A0=A0=A0=A0=A0=A0=A0=A0 ON mag.id_magasin =3D dem.id_magasin

=A0=A0=A0=A0=A0=A0=A0=A0=A0
GROUP BY
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 mag.co_magasin,
=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0 dem.id_produit,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 pnvente.dt_debut_commercial,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= COALESCE(pnvente.id_produit,dem.id_produit);


2013/7/16 Richa Sharma <mailtorichash= arma@gmail.com>
Can you share query with just RANK().

Richa


On Tue, Jul 16, 2013 at 6:08 PM, J=E9r=F4me Verdier <<= a href=3D"mailto:verdier.jerome66@gmail.com" target=3D"_blank">verdier.jero= me66@gmail.com> wrote:
Hi Richa,

=
I tried to execute the rank function alone, but the result is the sam= e

Thanks


2013/7/16 Richa Sharma <mailtorichasharma= @gmail.com>
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 a= pply these aggregate functions

Richa


=


On Tue, Jul 16, 2013 at 2:15 PM, J=E9r=F4me Verdier &l= t;verdier.j= erome66@gmail.com> wrote:
Hi= ,

I have a problem while using RANK OVER PARTITION function wi= th Hive.

Hive is in version 0.11 and, as we can see here : https://cwiki.apache.org/Hive/languagemanual-windowingandanalyt= ics.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 depen= d 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
=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0 mag.co_magasin,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 dem.id_produit=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 as= id_produit_orig,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 pnvente.dt_debut_com= mercial=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 as dt_d= ebut_commercial,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 COALESCE(pnvente.id_p= roduit,dem.id_produit)=A0=A0=A0=A0 as id_produit,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 min(
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0 CASE WHEN dem.co_validation IS NULL THEN 0 ELSE 1 END
=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0 )=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0 as flg_demarque_valide,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 sum(CASE WHEN dem.co_validation IS NULL THEN 0 ELSE CAST(dem.mt_revient= _ope AS INT) END)
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0 as me_dem_con_prx_cs,
=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0 0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 as me_dem_inc_prx_cs,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 as me_dem_prov_stk_c= s,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 sum(CASE WHEN dem.co_validation IS NULL T= HEN 0 ELSE CAST(dem.qt_demarque AS INT) END)
=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= as qt_dem_con,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 as qt_dem_inc,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0 as qt_dem_prov_stk, -- !!!!!!!! VIRGULE
=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0 RANK() OVER (PARTITION BY mag.co_magasin, dem.i= d_produit ORDER BY pnvente.dt_debut_commercial DESC, COALESCE(pnvente.id_pr= oduit,dem.id_produit) DESC) as rang
=A0=A0=A0=A0=A0=A0=A0=A0=A0 from default.calendrier cal
=A0=A0=A0=A0=A0= =A0=A0=A0=A0 INNER JOIN default.demarque_mag_jour dem
=A0=A0=A0=A0=A0=A0= =A0=A0=A0 ON=A0 CASE WHEN dem.co_societe =3D 1 THEN 1 ELSE 2 END =3D '$= {hiveconf:in_co_societe}' -- A modifier
=A0=A0=A0=A0=A0=A0=A0=A0=A0 = AND dem.dt_jour=A0=A0=A0 =3D cal.dt_jour
=A0=A0=A0=A0=A0=A0=A0=A0=A0 LEFT OUTER JOIN default.produit_norm pn
=A0= =A0=A0=A0=A0=A0=A0=A0=A0 ON=A0 pn.co_societe =3D dem.co_societe
=A0=A0= =A0=A0=A0=A0=A0=A0=A0 AND pn.id_produit =3D dem.id_produit
=A0=A0=A0=A0= =A0=A0=A0=A0=A0 LEFT OUTER JOIN default.produit_norm pnvente
=A0=A0=A0= =A0=A0=A0=A0=A0=A0 ON=A0 pnvente.co_societe =3D pn.co_societe
=A0=A0=A0=A0=A0=A0=A0=A0=A0 AND pnvente.co_produit_rfu =3D pn.co_produit_li= p
=A0=A0=A0=A0=A0=A0=A0=A0=A0 AND pnvente.co_type_motif=3D'05'=A0=A0=A0=A0=A0=A0=A0=A0=A0 INNER JOIN default.kpi_magasin mag
=A0=A0= =A0=A0=A0=A0=A0=A0=A0 ON=A0 mag.co_societe =3D '${hiveconf:in_co_societ= e}'
=A0=A0=A0=A0=A0=A0=A0=A0=A0 AND mag.id_magasin =3D dem.id_magasin
=A0=A0= =A0=A0=A0=A0=A0=A0=A0 WHERE cal.dt_jour =3D '${hiveconf:in_dt_jour}'= ;
=A0=A0=A0=A0=A0=A0=A0=A0=A0 AND NOT (dem.co_validation IS NULL AND cal= .dt_jour > unix_timestamp()-3*60*60*24) -- A verifier
=A0=A0=A0=A0=A0=A0=A0=A0=A0 -- JYP 4.4
=A0=A0=A0=A0=A0=A0=A0=A0=A0 AND d= em.co_operation_magasin IN ('13','14','32')
=A0= =A0=A0=A0=A0=A0=A0=A0=A0 GROUP BY
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 mag.= co_magasin,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 dem.id_produit,
=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0 pnvente.dt_debut_commercial,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 COALESCE(pnvente.id_produit,dem.id_produi= t)

Thanks.

--
J=E9r=F4me VERDIER
06.72.19.17.31=





--
J=E9r=F4= me VERDIER
06.72.19.17.31
<= br>




--
J=E9r=F4= me VERDIER
06.72.19.17.31
<= br>




--
J=E9r=F4= me VERDIER
06.72.19.17.31
<= br>





--
J=E9r=F4= me VERDIER
06.72.19.17.31
<= br>
--001a11c2c1ae9ba7de04e1b1da92--