Discussion :

[jerjerrod]

Bonjour à tous,

J'ai un souci sur une requête analytique :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
select distinct client
, id_aaaamm
, sum(ca) over (partition by client,id_aaaamm) CA_M
, sum(ca) over (partition by client order by id_aaaamm range between 100 preceding and 100 preceding) CA_M1
from table_vente v
order by client, id_aaaamm

Voici une extraction du résultat sur un client :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
CLIENT       ID_AAAAMM       CA_M	     CA_M1
Client1       200601	        4,39	
Client1       200603	        49,53	
Client1       200605	        33,03	
Client1       200608	        97,41	
Client1       200701	        -30,94       4,39
Client1       200705	        91,81	     33,03

Je calcule la somme du CA pour le mois de la ligne, et le CA pour le même mois mais de l'année précédente.

Ce que j'aimerais faire, c'est d'ajouter à ce résultat les lignes qui n'ont pas de CA sur le mois de la ligne, mais qui en a pour le mois de l'année précédente. Ce qui donnerait ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
CLIENT       ID_AAAAMM       CA_M	     CA_M1
Client1       200601	        4,39	
Client1       200603	        49,53	
Client1       200605	        33,03	
Client1       200608	        97,41	
Client1       200701	        -30,94       4,39
Client1       200703	                     49,53
Client1       200705	        91,81	     33,03
Client1       200608	                     97,41

Je ne vois pas comment faire ca avec les fonctions analytiques.
J'ai de plus essayé de faire un outer join avec une table de mois (quitte à afficher tous les mois, même ceux qui ont "null" sur les 2 colonnes CA, même si ca ne m'arrange pas en terme de volumétrie), mais ca ne marche pas.

Pour info, je suis sous oracle 10g.

Merci d'avance pour toute idée !

Adrien

[dragon74]

J'ai peut-être un début de réponse pour toi mais je ne suis pas sûr que c'est le plus propre. J'ai créé une table contenant 2 colonnes. La première contient le mois au format aaaamm et la deuxième le mois équivalent pour l'année précédante:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
create table months
(
id_aaaamm   number,
prev_id_aaaamm    number
)

Ensuite, j'exécute ce query:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
SELECT v.client
, m.id_aaaamm
, sum(decode(v.id_aaaamm, m.id_aaaamm, v.ca))  CA_M
, sum(decode(v.id_aaaamm, m.prev_id_aaaamm, v.ca)) CA_M1
FROM table_vente v, months m
where 
    (v.id_aaaamm = m.id_aaaamm or 
    v.id_aaaamm = m.prev_id_aaaamm
    )
group by v.client, m.id_aaaamm

[jerjerrod]

Merci pour ta réponse, c'est vrai que ca pourrait bien marcher comme ca.

Mais j'ai deux petit soucis concernant cette solution dans mon cas :
- J'aurai bien aimé éviter de passer par une table intermédiaire

- J'ai simplifié l'exemple en ne mettant que le CA du mois courant et du mois de l'année précédente, mais au final il y aura le cumul des 12 derniers mois, le cumul des 12 derniers mois de l'année d'avant, le cumul de janvier jusqu'au mois, etc...
Ca compliquerai complètement les choses.

N'y a-t-il vraiment pas de solution en utilisant les fonctions analytiques ?

Merci !

[dragon74]

Malheureusement, si tu ne veux employer que ta table source, je ne vois pas comment Oracle pourrait te renvoyer les lignes où il n'y a pas de CA sur le mois car elles n'existent pas dans la table

[jerjerrod]

Ok, c'est bien ce qui me semblait.

Merci beaucoup pour tes réponses.

A+

[salim11]

Salut,

Essaie cette solution

Script :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
SELECT CLIENT,ANNEE,CA_M,CA_M1
FROM (SELECT  CLIENT,MAX(SUBSTR(ID_AAAAMM,1,4))OVER(PARTITION BY CLIENT) MAX,
			  SUBSTR(ID_AAAAMM,1,4)||SUBSTR(ID_AAAAMM,5,2)  ANNEE ,
			  DECODE(CA ,NULL,NULL,SUM(CA)OVER(PARTITION BY CLIENT,ID_AAAAMM))CA_M
			, SUM(CA) OVER (PARTITION BY CLIENT ORDER BY TO_NUMBER(ID_AAAAMM) 
			                RANGE BETWEEN 100 PRECEDING AND 100 PRECEDING) CA_M1
	 FROM TABLE_VENTE 
	 UNION ALL
	 SELECT  DISTINCT CLIENT, MAX(SUBSTR(ID_AAAAMM,1,4))OVER(PARTITION BY CLIENT) MX,
			          SUBSTR(ID_AAAAMM,1,4)+1||SUBSTR(ID_AAAAMM,5,2) ANNEE,NULL		CA ,
					   SUM(CA)OVER(PARTITION BY CLIENT,ID_AAAAMM) CA_M1
	 FROM TABLE_VENTE V
	 WHERE     NOT EXISTS  (SELECT 1  
							FROM TABLE_VENTE T
							WHERE T.CLIENT=V.CLIENT 
							AND TO_NUMBER(SUBSTR(V.ID_AAAAMM,1,4))=TO_NUMBER(SUBSTR(T.ID_AAAAMM,1,4))-1
							AND SUBSTR(V.ID_AAAAMM,5,2)= SUBSTR(T.ID_AAAAMM,5,2)))
WHERE SUBSTR(ANNEE,1,4)<=MAX
ORDER BY 1,3

Démo

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
39
40
41
42
43
44
45
46
47
48
49
50
51
 
  SQL> SELECT CLIENT , ID_AAAAMM,CA   
  2  FROM TABLE_VENTE 
  3  ORDER BY 1,2;
 
CLIENT     ID_AAA         CA
---------- ------ ----------
Client1    200601       4.39
Client1    200603      49.83
Client1    200605      33.03
Client1    200608      97.41
Client1    200701      30.94
Client1    200705      91.81
Client1    200801        100
 
7 rows selected.
 
SQL> SELECT CLIENT,ANNEE,CA_M,CA_M1
  2  FROM (SELECT  CLIENT,MAX(SUBSTR(ID_AAAAMM,1,4))OVER(PARTITION BY CLIENT) MAX,
  3       SUBSTR(ID_AAAAMM,1,4)||SUBSTR(ID_AAAAMM,5,2)  ANNEE ,
  4       DECODE(CA ,NULL,NULL,SUM(CA)OVER(PARTITION BY CLIENT,ID_AAAAMM))CA_M
  5     , SUM(CA) OVER (PARTITION BY CLIENT ORDER BY TO_NUMBER(ID_AAAAMM) 
  6                     RANGE BETWEEN 100 PRECEDING AND 100 PRECEDING) CA_M1
  7    FROM TABLE_VENTE 
  8    UNION ALL
  9    SELECT  DISTINCT CLIENT, MAX(SUBSTR(ID_AAAAMM,1,4))OVER(PARTITION BY CLIENT) MX,
 10               SUBSTR(ID_AAAAMM,1,4)+1||SUBSTR(ID_AAAAMM,5,2) ANNEE,NULL  CA ,
 11          SUM(CA)OVER(PARTITION BY CLIENT,ID_AAAAMM) CA_M1
 12    FROM TABLE_VENTE V
 13    WHERE     NOT EXISTS  (SELECT 1  
 14         FROM TABLE_VENTE T
 15         WHERE T.CLIENT=V.CLIENT 
 16         AND TO_NUMBER(SUBSTR(V.ID_AAAAMM,1,4))=TO_NUMBER(SUBSTR(T.ID_AAAAMM,1,4))-1
 17         AND SUBSTR(V.ID_AAAAMM,5,2)= SUBSTR(T.ID_AAAAMM,5,2)))
 18  WHERE SUBSTR(ANNEE,1,4)<=MAX
 19  ORDER BY 1,2;
 
CLIENT     ANNEE                                      CA_M                                          CA_M1
---------- ------------------------------------------ ---------------------------------------- -----
Client1    200601                                     4.39
Client1    200603                                     49.83
Client1    200605                                     33.03
Client1    200608                                     97.41
Client1    200701                                     30.94                                          4.39
Client1    200703                                                                                   49.83
Client1    200705                                     91.81                                         33.03
Client1    200708                                                                                   97.41
Client1    200801                                     100                                           30.94
Client1    200805                                                                                   91.81
 
10 rows selected.