Bonjour à tous.

Alors j'annonce, ça va être long. Bon, j'ai un problème que je rencontre assez souvent dans mon domaine (le décisionnel), qu'on contourne toujours un peu comme on peut et pour lequel j'aimerai bien pouvoir poser des Best Practices, voire écrire un article: agréger correctement des indicateurs dans un modèle entête - détail.

L'idéal serait que les solutions fonctionnent à partir de 9i.

Imaginons le modèle suivant :
Vendeur (id, nom, salaire)
Vente (id, id_vendeur, client, ristourne)
Vente_Detail (id, id_vente, objet, montant)

on a donc un modèle
Vendeur (1.1) - (1.n) Vente (1.1) - (1.n) Vente_Detail
Soit, pour (1) vendeur (1 à plusieurs) ventes et pour (1) vente (1 à plusieurs) lignes de détails.

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
nom	salaire	client	ristourne	objet	montant
BOB	1000	M.A	10		truc1	100
BOB	1000	M.A	10		truc2	200
BOB	1000	M.B	20		truc1	100
BOB	1000	M.B	20		truc2	200
TED	2000	M.C	100		truc1	100
TED	2000	M.C	100		truc2	200
TED	2000	M.D	200		truc1	100
TED	2000	M.D	200		truc2	200
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
WITH
VENDEUR AS (
select 0 "id", 'BOB' "nom", 1000 "salaire" from DUAL
UNION
select 1 "id", 'TED' "nom", 2000 "salaire" from DUAL
)
,VENTE AS (
select 0 "id", 0 "id_vendeur", 'M.A' "client", 10 "ristourne" from DUAL
UNION
select 1 "id", 0 "id_vendeur", 'M.B' "client", 20 "ristourne" from DUAL
UNION
select 2 "id", 1 "id_vendeur", 'M.C' "client", 100 "ristourne" from DUAL
UNION
select 3 "id", 1 "id_vendeur", 'M.D' "client", 200 "ristourne" from DUAL
)
,VENTE_DETAIL AS (
select 0 "id", 0 "id_vente", 'truc1' "objet", 100 "montant" from DUAL
UNION
select 1 "id", 0 "id_vente", 'truc2' "objet", 200 "montant" from DUAL
UNION
select 2 "id", 1 "id_vente", 'truc1' "objet", 100 "montant" from DUAL
UNION
select 3 "id", 1 "id_vente", 'truc2' "objet", 200 "montant" from DUAL
UNION
select 4 "id", 2 "id_vente", 'truc1' "objet", 100 "montant" from DUAL
UNION
select 5 "id", 2 "id_vente", 'truc2' "objet", 200 "montant" from DUAL
UNION
select 6 "id", 3 "id_vente", 'truc1' "objet", 100 "montant" from DUAL
UNION
select 7 "id", 3 "id_vente", 'truc2' "objet", 200 "montant" from DUAL
)
select
VENDEUR."nom",
VENDEUR."salaire",
VENTE."client",
VENTE."ristourne",
VENTE_DETAIL."objet",
VENTE_DETAIL."montant"
from
VENDEUR
inner join VENTE on (VENDEUR."id" = VENTE."id_vendeur")
inner join VENTE_DETAIL on (VENTE."id" = VENTE_DETAIL."id_vente")
Les 3 indicateurs à agréger sont:
Salaire du vendeur (on va dire qu'on travaille sur une période définie, un mois par exemple)
Ristourne accordée au client dans une vente
Montant de l'objet vendu

A partir de ces données à plat, essayons agréger les résultats en supprimant 1 à 1 les dimensions, pour aller vers le résultat le plus agrégé:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
Total SALAIRE	Total RISTOURNE	Total MONTANT
3000		330		1200
Pour le premier niveau d'agrégation supprimons le nom de l'objet vendu, pour agréger les résultats par vendeur et client (ou vente, car pour l'exemple 1 client = 1 vente):

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
nom	salaire	client	ristourne	SUM(VENTE_DETAIL."MONTANT")
BOB	1000	M.A	10		300
BOB	1000	M.B	20		300
TED	2000	M.D	200		300
TED	2000	M.C	100		300
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
WITH
VENDEUR AS (
select 0 "id", 'BOB' "nom", 1000 "salaire" from DUAL
UNION
select 1 "id", 'TED' "nom", 2000 "salaire" from DUAL
)
,VENTE AS (
select 0 "id", 0 "id_vendeur", 'M.A' "client", 10 "ristourne" from DUAL
UNION
select 1 "id", 0 "id_vendeur", 'M.B' "client", 20 "ristourne" from DUAL
UNION
select 2 "id", 1 "id_vendeur", 'M.C' "client", 100 "ristourne" from DUAL
UNION
select 3 "id", 1 "id_vendeur", 'M.D' "client", 200 "ristourne" from DUAL
)
,VENTE_DETAIL AS (
select 0 "id", 0 "id_vente", 'truc1' "objet", 100 "montant" from DUAL
UNION
select 1 "id", 0 "id_vente", 'truc2' "objet", 200 "montant" from DUAL
UNION
select 2 "id", 1 "id_vente", 'truc1' "objet", 100 "montant" from DUAL
UNION
select 3 "id", 1 "id_vente", 'truc2' "objet", 200 "montant" from DUAL
UNION
select 4 "id", 2 "id_vente", 'truc1' "objet", 100 "montant" from DUAL
UNION
select 5 "id", 2 "id_vente", 'truc2' "objet", 200 "montant" from DUAL
UNION
select 6 "id", 3 "id_vente", 'truc1' "objet", 100 "montant" from DUAL
UNION
select 7 "id", 3 "id_vente", 'truc2' "objet", 200 "montant" from DUAL
)
select
VENDEUR."nom",
VENDEUR."salaire",
VENTE."client",
VENTE."ristourne",
sum(VENTE_DETAIL."montant")
from
VENDEUR
inner join VENTE on (VENDEUR."id" = VENTE."id_vendeur")
inner join VENTE_DETAIL on (VENTE."id" = VENTE_DETAIL."id_vente")
group by
VENDEUR."nom",
VENDEUR."salaire",
VENTE."client",
VENTE."ristourne"
Il n'y a pas de piège, un SUM classique dans un GROUP BY.


Pour le second niveau d'agrégation supprimons le nom du client (et donc la dimension Vente), pour agréger les résultats par vendeur :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
nom	salaire	SUM(VENTE."RISTOURNE")	SUM(VENTE_DETAIL."MONTANT")
BOB	1000	60			600
TED	2000	600			600
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
WITH
VENDEUR AS (
select 0 "id", 'BOB' "nom", 1000 "salaire" from DUAL
UNION
select 1 "id", 'TED' "nom", 2000 "salaire" from DUAL
)
,VENTE AS (
select 0 "id", 0 "id_vendeur", 'M.A' "client", 10 "ristourne" from DUAL
UNION
select 1 "id", 0 "id_vendeur", 'M.B' "client", 20 "ristourne" from DUAL
UNION
select 2 "id", 1 "id_vendeur", 'M.C' "client", 100 "ristourne" from DUAL
UNION
select 3 "id", 1 "id_vendeur", 'M.D' "client", 200 "ristourne" from DUAL
)
,VENTE_DETAIL AS (
select 0 "id", 0 "id_vente", 'truc1' "objet", 100 "montant" from DUAL
UNION
select 1 "id", 0 "id_vente", 'truc2' "objet", 200 "montant" from DUAL
UNION
select 2 "id", 1 "id_vente", 'truc1' "objet", 100 "montant" from DUAL
UNION
select 3 "id", 1 "id_vente", 'truc2' "objet", 200 "montant" from DUAL
UNION
select 4 "id", 2 "id_vente", 'truc1' "objet", 100 "montant" from DUAL
UNION
select 5 "id", 2 "id_vente", 'truc2' "objet", 200 "montant" from DUAL
UNION
select 6 "id", 3 "id_vente", 'truc1' "objet", 100 "montant" from DUAL
UNION
select 7 "id", 3 "id_vente", 'truc2' "objet", 200 "montant" from DUAL
)
select
VENDEUR."nom",
VENDEUR."salaire",
sum(VENTE."ristourne"),
sum(VENTE_DETAIL."montant")
from
VENDEUR
inner join VENTE on (VENDEUR."id" = VENTE."id_vendeur")
inner join VENTE_DETAIL on (VENTE."id" = VENTE_DETAIL."id_vente")
group by
VENDEUR."nom",
VENDEUR."salaire"
Là on voit bien qu'il y a un souci : si les salaires et la somme des montants sont corrects, les ristournes ont été multipliées par 2 (60 au lieu de 30 et 600 au lieu de 300), la faute à la jointure entre Vente et Vente_Detail.
Une première solution pourrait être de pré-agréger chaque indicateur au niveau qui va bien puis de joindre les résultats.
Une autre, qui permet de conserver l'enchaînement des tables et donc qui m'arrange plus, est d'utiliser les partitions, mais cela oblige à faire une sur-requête et d'utiliser un distinct au lieu d'un GROUP BY :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
nom	salaire	SUM("RISTOURNE")OVER(PARTITIONBY"NOM")	SUM("MONTANT")OVER(PARTITIONBY"NOM")
TED	2000	300					600
BOB	1000	30					600
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
WITH
VENDEUR AS (
select 0 "id", 'BOB' "nom", 1000 "salaire" from DUAL
UNION
select 1 "id", 'TED' "nom", 2000 "salaire" from DUAL
)
,VENTE AS (
select 0 "id", 0 "id_vendeur", 'M.A' "client", 10 "ristourne" from DUAL
UNION
select 1 "id", 0 "id_vendeur", 'M.B' "client", 20 "ristourne" from DUAL
UNION
select 2 "id", 1 "id_vendeur", 'M.C' "client", 100 "ristourne" from DUAL
UNION
select 3 "id", 1 "id_vendeur", 'M.D' "client", 200 "ristourne" from DUAL
)
,VENTE_DETAIL AS (
select 0 "id", 0 "id_vente", 'truc1' "objet", 100 "montant" from DUAL
UNION
select 1 "id", 0 "id_vente", 'truc2' "objet", 200 "montant" from DUAL
UNION
select 2 "id", 1 "id_vente", 'truc1' "objet", 100 "montant" from DUAL
UNION
select 3 "id", 1 "id_vente", 'truc2' "objet", 200 "montant" from DUAL
UNION
select 4 "id", 2 "id_vente", 'truc1' "objet", 100 "montant" from DUAL
UNION
select 5 "id", 2 "id_vente", 'truc2' "objet", 200 "montant" from DUAL
UNION
select 6 "id", 3 "id_vente", 'truc1' "objet", 100 "montant" from DUAL
UNION
select 7 "id", 3 "id_vente", 'truc2' "objet", 200 "montant" from DUAL
)
select
distinct
"nom",
min("salaire") over (partition  by "nom") "salaire",
sum("ristourne") over (partition  by "nom"),
sum("montant") over (partition  by "nom")
from
(
select
distinct
VENDEUR."nom",
min(VENDEUR."salaire") over (partition  by VENDEUR."nom") "salaire",
min(VENTE."ristourne") over (partition  by VENTE."id") "ristourne",
sum(VENTE_DETAIL."montant") over (partition  by VENTE."id") "montant"
from
VENDEUR
inner join VENTE on (VENDEUR."id" = VENTE."id_vendeur")
inner join VENTE_DETAIL on (VENTE."id" = VENTE_DETAIL."id_vente")
)

Le même problème se posera pour le dernier niveau d'agrégation : les salaires vont être multipliés par le nombre de ventes (voire par le nombre de lignes de détail des ventes, selon la requête). Il faut donc utiliser la même méthode pour obtenir une agrégation correcte :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
SUM("SALAIRE")	SUM("RISTOURNE")	SUM("MONTANT")
3000		330			1200
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
52
53
54
55
56
57
58
WITH
VENDEUR AS (
select 0 "id", 'BOB' "nom", 1000 "salaire" from DUAL
UNION 
select 1 "id", 'TED' "nom", 2000 "salaire" from DUAL
)
,VENTE AS (
select 0 "id", 0 "id_vendeur", 'M.A' "client", 10 "ristourne" from DUAL
UNION
select 1 "id", 0 "id_vendeur", 'M.B' "client", 20 "ristourne" from DUAL
UNION
select 2 "id", 1 "id_vendeur", 'M.C' "client", 100 "ristourne" from DUAL
UNION
select 3 "id", 1 "id_vendeur", 'M.D' "client", 200 "ristourne" from DUAL
)
,VENTE_DETAIL AS (
select 0 "id", 0 "id_vente", 'truc1' "objet", 100 "montant" from DUAL
UNION
select 1 "id", 0 "id_vente", 'truc2' "objet", 200 "montant" from DUAL
UNION
select 2 "id", 1 "id_vente", 'truc1' "objet", 100 "montant" from DUAL
UNION
select 3 "id", 1 "id_vente", 'truc2' "objet", 200 "montant" from DUAL
UNION
select 4 "id", 2 "id_vente", 'truc1' "objet", 100 "montant" from DUAL
UNION
select 5 "id", 2 "id_vente", 'truc2' "objet", 200 "montant" from DUAL
UNION
select 6 "id", 3 "id_vente", 'truc1' "objet", 100 "montant" from DUAL
UNION
select 7 "id", 3 "id_vente", 'truc2' "objet", 200 "montant" from DUAL
)
select
sum("salaire"),
sum("ristourne"),
sum("montant")
from
(
select
distinct
"nom",
min("salaire") over (partition  by "nom") "salaire",
sum("ristourne") over (partition  by "nom") "ristourne",
sum("montant") over (partition  by "nom") "montant"
from
(
select
distinct
VENDEUR."nom",
min(VENDEUR."salaire") over (partition  by VENDEUR."nom") "salaire",
min(VENTE."ristourne") over (partition  by VENTE."id") "ristourne",
sum(VENTE_DETAIL."montant") over (partition  by VENTE."id") "montant"
from
VENDEUR
inner join VENTE on (VENDEUR."id" = VENTE."id_vendeur")
inner join VENTE_DETAIL on (VENTE."id" = VENTE_DETAIL."id_vente")
)
)
D'où ma question : comment géreriez-vous cette problématique ? Toutes les solutions sont acceptées, même une remise en question du modèle, pourquoi pas (par exemple faire remonter au niveau d'agrégation au dessus la somme du niveau d'en dessous) ou des fonctions très avancées.
L'important, dans ma vision, étant d'obtenir un code SQL dont le coeur, cad l'organisation des tables et les jointures, ne changent pas selon le niveau d'agrégation, donc pas de pré-agrégation. Mais ça n'empêche pas d'évoquer la solution.

Merci