Utilisation des fonctions analytiques

Version imprimable

Bonjour,

Je viens de découvrir les fonctions analytiques sous Oracle et il y quelque chose que je n'arrive pas à faire dans une requête.

J'ai une table avec des articles, des quantité et une période.
Je peux avoir plusieurs lignes par articles avec des quantités différentes , sur une période différentes. (Les périodes sont définies par des numéro)

Dans un premier temps j'ai récupéré la somme des quantités par articles.

Voici la requête et le résultat.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
select artsvstt as Art,sum(qtfsvstt+qgfsvstt)  As Qte
from svstt
where capsvstt in 
    (select numoecap 
    from oecap 
    where etsoecap = '20' and typoecap = 'M' 
    and to_char(to_date(dtdoecap,'YYYYMMDD'),'YYYYMM') >= to_char(ADD_MONTHS(TO_DATE(to_char(sysdate,'YYYYMMDD'), 'YYYYMMDD'), -12), 'YYYYMM')
    and to_char(to_date(dtfoecap,'YYYYMMDD'),'YYYYMM') <= to_char(ADD_MONTHS(TO_DATE(to_char(sysdate,'YYYYMMDD'), 'YYYYMMDD'), -1), 'YYYYMM'))
and tcasvstt = 'M' 
and typsvstt = 'F' 
and artsvstt in ('768672','768682','768683','768696','770627','813196','851678','851679','851682','851771','854670','855010','856112','856312','870632','870633')
group by artsvstt
order by 2 DESC

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
ART	QTE
813196	12438
870632	10618
768696	4447
870633	3896
854670	3349
768672	3299
770627	2621
768682	2511
851678	2079
851679	1302
768683	1184
851682	1060
851771	253
855010	93
856112	31
856312	-1

Grâce au fonction analytique je réussi à récupérer le pourcentage des quantités par article par rapport au total des quantités.

Voici la requête et le résultat.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
select distinct artsvstt as Art,sum(qtfsvstt+qgfsvstt) OVER (partition by artsvstt)  As Qte,
round((sum(qtfsvstt+qgfsvstt) OVER (partition by artsvstt order by artsvstt) / sum(qtfsvstt+qgfsvstt) OVER ()) *100,2) as Pourc
from svstt
where capsvstt in 
    (select numoecap 
    from oecap 
    where etsoecap = '20' and typoecap = 'M' 
    and to_char(to_date(dtdoecap,'YYYYMMDD'),'YYYYMM') >= to_char(ADD_MONTHS(TO_DATE(to_char(sysdate,'YYYYMMDD'), 'YYYYMMDD'), -12), 'YYYYMM')
    and to_char(to_date(dtfoecap,'YYYYMMDD'),'YYYYMM') <= to_char(ADD_MONTHS(TO_DATE(to_char(sysdate,'YYYYMMDD'), 'YYYYMMDD'), -1), 'YYYYMM'))
and tcasvstt = 'M' 
and typsvstt = 'F' 
and artsvstt in ('768672','768682','768683','768696','770627','813196','851678','851679','851682','851771','854670','855010','856112','856312','870632','870633')
order by 3 DESC

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
ART	QTE	POURCENT
813196	12438	25,29
870632	10618	21,59
768696	4447	9,04
870633	3896	7,92
854670	3349	6,81
768672	3299	6,71
770627	2621	5,33
768682	2511	5,11
851678	2079	4,23
851679	1302	2,65
768683	1184	2,41
851682	1060	2,16
851771	253	0,51
855010	93	0,19
856112	31	0,06
856312	-1	0

Pour finir voici mon problème.
Je souhaiterais rajoutais une colonne qui me fasse le cumul de ce pourcentage ligne après ligne.
Pour avoir un résultat comme ceci:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
ART	QTE	POURCENT	CUMUL
813196	12438	25.29	25.29
870632	10618	21.59	46.88
768696	4447	9.04	55.92
870633	3896	7.92	63.84
854670	3349	6.81	70.65
768672	3299	6.71	77.36
770627	2621	5.33	82.69
768682	2511	5.11	87.8
851678	2079	4.23	92.03
851679	1302	2.65	94.68
768683	1184	2.41	97.09
851682	1060	2.16	99.25
851771	253	0.51	99.76
855010	93	0.19	99.95
856112	31	0.06	100.01
856312	-1	0	100.01

Je n'arrive pas à le faire via les fonctions analytiques car de ce que je comprend je ne peux utiliser une fonction analytique dans une fonction analytique. (les imbriqués en quelque sorte)

Est ce que vous sauriez comment je peux faire pour avoir ce cumul des pourcentages?

Merci d'avance

06/07/2016, 14h13
skuatamad

Citation:

Envoyé par niuniuk36

Je n'arrive pas à le faire via les fonctions analytiques car de ce que je comprend je ne peux utiliser une fonction analytique dans une fonction analytique. (les imbriqués en quelque sorte)

Il faut passer par une sous-requête pour le calcul du cumul (SUM analytique avec ORDER BY)
Par compte je suis dubitatif sur l'utilisation du ORDER BY dans le calcul du pourcentage (au niveau du numérateur)
06/07/2016, 15h05
niuniuk36

Bonjour,

Merci pour ta réponse.

Citation:

Il faut passer par une sous-requête pour le calcul du cumul (SUM analytique avec ORDER BY)

Je ne comprend pas ce que tu veux dire.

Si je passe par une sous requête il va quand même falloir que je fasse dans un premier temps le calcul du pourcentage par l'analytique. Pour ensuite sortir le cumul.
Mais pour sortir le cumul je dois me baser sur la colonne du pourcentage qui vient d'un calcul analytique.

Donc au final, faire un calcul analytique par rapport à une colonne venant déjà d'un calcul analytique.
C'est ça que je n'arrive pas à faire.

Citation:

Par compte je suis dubitatif sur l'utilisation du ORDER BY dans le calcul du pourcentage (au niveau du numérateur)

oui effectivement, j'ai supprimé le ORDER BY.
Du coup ça donne ça :

Code:

round((sum(qtfsvstt+qgfsvstt) OVER (partition by artsvstt order by artsvstt) / sum(qtfsvstt+qgfsvstt) OVER ()) *100,2) as Pourc

par:

Code:

round((sum(qtfsvstt+qgfsvstt) OVER (partition by artsvstt) / sum(qtfsvstt+qgfsvstt) OVER ()) *100,2) as Pourc

J'ai découvert ces fonctions analytiques ce matin, du coup j'apprend encore et je patauge encore un peu ;)

Merci d'avance
06/07/2016, 16h01
skuatamad
Il n'est pas possible de faire une fonction analytique de fonction analytique au même niveau, mais en passant par une sous-requête ça devrait passer :
Code:

1 2 select art, qte, Pourc, sum(Pourc) over (order by Pourc desc) as cumul from (la_requete)
06/07/2016, 17h16
Sunchaser

Citation:

Envoyé par niuniuk36

Est ce que vous sauriez comment je peux faire pour avoir ce cumul des pourcentages?

Bonjour,
Pardonnez moi, mais j'ai besoin de poser la question même si cela risque de ne pas vous faire avancer: cela fait un sens de faire un SUM() OVER () sur un pourcentage ? :koi:

@skuatamad

Merci beaucoup, ça fonctionne.
En fait je n'avais pas compris cela au début, mais de le voir écrit ça ma "sauté au yeux" :D

@Sunchaser
En fait cela me permet d'afficher pour nos clients le % du cumul des ventes sur une familles d'articles.
Par exemple si un client veut connaitre les articles d'une famille qui correspondent à 80% des ventes alors je lui donnerais les articles suivants : (par rapport au tableau ci-dessous.)

813196, 870632, 768696, 870633 et 768672
Ces 5 articles correspondent à 80% de nos ventes sur leur famille article.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 ART QTE POURCENT CUMUL 813196 12438 27,14 27,14 870632 10618 23,17 50,31 768696 4447 9,7 60,01 870633 3896 8,5 68,51 768672 3299 7,2 75,71 770627 2621 5,72 81,43 768682 2511 5,48 86,91 851678 2079 4,54 91,45 851679 1302 2,84 94,29 768683 1184 2,58 96,87 851682 1060 2,31 99,18 851771 253 0,55 99,73 855010 93 0,2 99,93 856112 31 0,07 100 856312 -1 0 100
Merci pour votre aide