1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
|
WITH historic_1656379283 AS
(
SELECT to_date('01/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,10 AS Nb_Produits,to_date('01/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('02/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,11 AS Nb_Produits,to_date('02/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('03/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,35 AS Nb_Produits,to_date('03/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('04/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,9 AS Nb_Produits,to_date('04/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('05/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,0 AS Nb_Produits,to_date('05/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('06/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,58 AS Nb_Produits,to_date('06/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('07/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,16 AS Nb_Produits,to_date('07/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('08/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,10 AS Nb_Produits,to_date('08/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('01/01/2011','DD/MM/YYYY') AS eventdate,'yoyo' AS Nom_Produit,11 AS Nb_Produits,to_date('01/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('02/01/2011','DD/MM/YYYY') AS eventdate,'yoyo' AS Nom_Produit,12 AS Nb_Produits,to_date('02/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('03/01/2011','DD/MM/YYYY') AS eventdate,'yoyo' AS Nom_Produit,13 AS Nb_Produits,to_date('03/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('04/01/2011','DD/MM/YYYY') AS eventdate,'yoyo' AS Nom_Produit,14 AS Nb_Produits,to_date('04/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('05/01/2011','DD/MM/YYYY') AS eventdate,'yoyo' AS Nom_Produit,15 AS Nb_Produits,to_date('05/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('06/01/2011','DD/MM/YYYY') AS eventdate,'yoyo' AS Nom_Produit,16 AS Nb_Produits,to_date('06/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('07/01/2011','DD/MM/YYYY') AS eventdate,'yoyo' AS Nom_Produit,17 AS Nb_Produits,to_date('07/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('08/01/2011','DD/MM/YYYY') AS eventdate,'yoyo' AS Nom_Produit,18 AS Nb_Produits,to_date('08/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('01/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,10 AS Nb_Produits,to_date('01/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('02/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,11 AS Nb_Produits,to_date('02/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('03/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,35 AS Nb_Produits,to_date('03/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('04/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,9 AS Nb_Produits,to_date('04/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('05/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,0 AS Nb_Produits,to_date('05/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('06/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,58 AS Nb_Produits,to_date('06/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('07/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,16 AS Nb_Produits,to_date('07/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('08/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,10 AS Nb_Produits,to_date('08/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL
)
select DISTINCT
to_char(eventdate, 'yyyy/mm') as MOIS,
Nom_Produit as Nom,
SUM(Nb_Produits) OVER (partition by to_char(eventdate, 'yyyy/mm'),Nom_Produit) as Nb_de_produits_Total,
AVG(Nb_Produits) OVER (partition by to_char(eventdate, 'yyyy/mm'),Nom_Produit) as Moyenne_nb,
MAX(Nb_Produits) OVER (partition by to_char(eventdate, 'yyyy/mm'),Nom_Produit) as Pic_Nb_Produit,
MAX(date_vente) KEEP (DENSE_RANK FIRST ORDER BY Nom_Produit,Nb_Produits DESC) OVER (partition by to_char(eventdate, 'yyyy/mm'),Nom_Produit) as Date_Du_Pic
from historic_1656379283 t1
order by to_char(eventdate, 'yyyy/mm') desc; |
Partager