Utilisation des fonctions analytiques

**niuniuk36** · 06/07/2016, 12h06

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 :

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

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 :

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
 
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 :

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

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 :

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
 
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 :

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
 
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

**skuatamad** · 06/07/2016, 14h13

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)

**niuniuk36** · 06/07/2016, 15h05

Bonjour,

Merci pour ta réponse.

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.

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 :

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

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

par:

Code :

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

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

**skuatamad** · 06/07/2016, 16h01

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 :

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

1
2
select art, qte, Pourc, sum(Pourc) over (order by Pourc desc) as cumul
  from (la_requete)

**Sunchaser** · 06/07/2016, 17h16

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 ?

**niuniuk36** · 06/07/2016, 17h39

@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"

@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 :

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
 
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

Utilisation des fonctions analytiques [12c]

SQL Oracle

Discussions similaires

Partager

Partager