Fonctions analytiques SQL . . .

Version imprimable

Bonjour,

n'ayant pas d'oracle sous la main, je cherche à vérifier si la requête suivante fonctionne sous Oracle.

Le principe est de calculez pour chaque produit et par années/mois le chiffre d'affaire de la période et le nombre de ventes, ainsi que leurs cumuls depuis l'origine. La table S_COM.T_VENTE_VTE comporte les colonnes :
(VTE_DATE, VTE_PRODUIT, VTE_MONTANT)...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE SCHEMA S_COM;
 
CREATE TABLE S_COM.T_VENTE_VTE
(VTE_DATE    DATE,
 VTE_PRODUIT VARCHAR(32),
 VTE_MONTANT DECIMAL(16,2))
 
INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-01-12', 'Zebulon', 33.21)   
INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-01-13', 'Zebulon', 44.21)   
INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-01-14', 'Zebulon', 22.21)   
INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-02-12', 'Zebulon', 55.21)   
INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-02-15', 'Zebulon', 11.21)   
INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-02-18', 'Zebulon', 88.21)   
INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-03-10', 'Zebulon', 32.21)   
INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-01-10', 'Agormat', 21.21)   
INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-01-12', 'Agormat', 47.21)   
INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-01-14', 'Agormat', 53.21)   
INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-01-15', 'Agormat', 29.21)   
INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-02-12', 'Agormat', 16.21)   
INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-02-15', 'Agormat', 19.21)

Voici la requête théorique à la norme SQL:2003 :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT VTE_PRODUIT,
       EXTRACT(YEAR FROM VTE_DATE)                  AS AN,
       EXTRACT(MONTH FROM VTE_DATE)                 AS MOIS,
       SUM(VTE_MONTANT) OVER(W_SUMCNT)              AS TOTAL_MONTANT, 
       SUM(SUM(VTE_MONTANT)) OVER (W_CUMULS)        AS CUMUL_MONTANT,
       COUNT(ALL VTE_MONTANT) OVER(W_SUMCNT)        AS QUANTITE,
       COUNT(COUNT(ALL VTE_MONTANT)) OVER(W_CUMULS) AS CUMUL_QUANTITE
FROM   S_COM.T_VENTE_VTE
WINDOW W_SUMCNT AS (PARTITION BY VTE_PRODUIT, 
                               EXTRACT(YEAR FROM VTE_DATE), 
                               EXTRACT(MONTH FROM VTE_DATE))
       W_CUMULS AS (ORDER BY VTE_PRODUIT UNBOUNDED PRECEDING)

Voici ce qu'un de mes amis à commencer à pisser :

Code:

1
2
3
4
5
6
7
8
9
10
11
SELECT VTE_PRODUIT,  
       EXTRACT(YEAR FROM VTE_DATE) AS AN , 
       EXTRACT(MONTH FROM VTE_DATE) AS Mois, 
       SUM(VTE_MONTANT) 
          OVER (PARTITION BY VTE_PRODUIT, 
                EXTRACT(YEAR FROM VTE_DATE),
                EXTRACT(MONTH FROM VTE_DATE)) AS TOTAL_MONTANT, 
       COUNT(ALL VTE_MONTANT) 
          OVER (PARTITION BY VTE_PRODUIT ORDER BY 1 ROWS 
                UNBOUNDED PRECEDING) AS QUANTITE 
 FROM   T_VENTE_VTE;

Bref, si vous avez du temps ...

A +

11/02/2008, 19h06
pifor

Oracle est généreux et met à disposition un schéma en version 10g gratuitement (avec quelques dizaines de Mo):
http://www.developpez.net/forums/sho...d.php?t=287843
12/02/2008, 16h12
rouardg

Voici la réponse :

VTE_PRODUIT AN MOIS TOTAL_MONTANT QUANTITE
Agormat 2007 1 150.84 1
Agormat 2007 1 150.84 2
Agormat 2007 1 150.84 3
Agormat 2007 1 150.84 4
Agormat 2007 2 35.42 5
Agormat 2007 2 35.42 6
Zebulon 2007 1 99.63 1
Zebulon 2007 1 99.63 2
Zebulon 2007 1 99.63 3
Zebulon 2007 2 154.63 4
Zebulon 2007 2 154.63 5
Zebulon 2007 2 154.63 6
Zebulon 2007 3 32.21 7

Personnellement, j'aurais fait ceci :
Code:

1 2 3 4 5 6 7 8 9 select vte_produit, an, mois, montant, sum (montant) over (partition by vte_produit order by an, mois) as cumul_montant, quantite, sum (quantite) over (partition by vte_produit order by an, mois) as cumul_quantite from (select vte_produit, extract (year from vte_date) as an, extract (month from vte_date) as mois, count (*) as quantite, sum (vte_montant) as montant from t_vente_vte group by vte_produit, extract (year from vte_date), extract (month from vte_date) ) ;
Le calcul se passe donc en 2 étapes :
1) regroupement classique par période et produit pour calculer les quantités et les montants,
2) la requête précédente sert d'In-line View pour y appliquer les fonctions analytiques de cumul sur les quantités et les montants.