[9i+][Best Practice] Agrégation d'indicateurs dans un modèle entête - détail

**nuke_y** · 12/02/2010, 10h03

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

**Waldar** · 15/02/2010, 10h39

Je n'ai pas eu le temps de vous répondre vendredi, mais je le fais maintenant.
Effectivement ce que vous décrivez est un problème récurrent.

Pour ma part, je n'hésite pas à pré-agréger les indicateurs au niveau des entêtes si les conditions sont réunies (mise à jour peu fréquentes par clef d'agrégat, delta facilement identifiable).

Celà permet de s'affranchir de ce genre de problématique, avec bien entendu un surcoût à l'alimentation.

Pour votre problème, si je devais tout passer en une requête je calculerai les agrégats intermédiaires, mais évidement si c'est pour de la génération automatique avec un BO-like, c'est plus compliqué :

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 ALL
SELECT 1     , 'TED'      , 2000           FROM DUAL
)
  ,  VENTE AS
(
SELECT 0 "id", 0 "id_vendeur", 'M.A' "client",  10 "ristourne" FROM DUAL UNION ALL
SELECT 1     , 0             , 'M.B'         ,  20             FROM DUAL UNION ALL
SELECT 2     , 1             , 'M.C'         , 100             FROM DUAL UNION ALL
SELECT 3     , 1             , 'M.D'         , 200             FROM DUAL
)
  ,  VENTE_DETAIL AS
(
SELECT 0 "id", 0 "id_vente", 'truc1' "objet", 100 "montant" FROM DUAL UNION ALL
SELECT 1     , 0           , 'truc2'        , 200           FROM DUAL UNION ALL
SELECT 2     , 1           , 'truc1'        , 100           FROM DUAL UNION ALL
SELECT 3     , 1           , 'truc3'        , 200           FROM DUAL UNION ALL
SELECT 4     , 2           , 'truc2'        , 100           FROM DUAL UNION ALL
SELECT 5     , 2           , 'truc3'        , 200           FROM DUAL UNION ALL
SELECT 6     , 3           , 'truc3'        , 100           FROM DUAL UNION ALL
SELECT 7     , 3           , 'truc3'        , 200           FROM DUAL
)
  ,  VENTE_DET_AGG AS
(
  select VE."id_vendeur", sum(VD."montant") as montant_total
    from VENTE_DETAIL VD
         INNER JOIN VENTE VE
           ON VE."id" = VD."id_vente"
group by VE."id_vendeur"
)
  ,  VENTE_AGG AS
(
  select "id_vendeur", sum("ristourne") as ristourne_total
    from VENTE
group by "id_vendeur"
)
select
    sum(VE."salaire")       as s1,
    sum(VA.ristourne_total) as s2,
    sum(VD.montant_total)   as s3
from
    VENDEUR VE
    INNER JOIN VENTE_AGG VA -- ici on pourrait mettre des jointures externes
      ON VA."id_vendeur" = VE."id"
    INNER JOIN VENTE_DET_AGG VD
      ON VD."id_vendeur" = VE."id";

Code :

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

1
2
S1	S2	S3
3000	330	1200

Notez que même si ça ne répond pas complètement à votre problème, Oracle permet un niveau d'imbrication d'agrégat :

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
WITH VENTE AS
(
SELECT 0 "id", 0 "id_vendeur", 'M.A' "client",  10 "ristourne" FROM DUAL UNION ALL
SELECT 1     , 0             , 'M.B'         ,  20             FROM DUAL UNION ALL
SELECT 2     , 1             , 'M.C'         , 100             FROM DUAL UNION ALL
SELECT 3     , 1             , 'M.D'         , 200             FROM DUAL
)
  ,  VENTE_DETAIL AS
(
SELECT 0 "id", 0 "id_vente", 'truc1' "objet", 100 "montant" FROM DUAL UNION ALL
SELECT 1     , 0           , 'truc2'        , 200           FROM DUAL UNION ALL
SELECT 2     , 1           , 'truc1'        , 100           FROM DUAL UNION ALL
SELECT 3     , 1           , 'truc3'        , 200           FROM DUAL UNION ALL
SELECT 4     , 2           , 'truc2'        , 100           FROM DUAL UNION ALL
SELECT 5     , 2           , 'truc3'        , 200           FROM DUAL UNION ALL
SELECT 6     , 3           , 'truc3'        , 100           FROM DUAL UNION ALL
SELECT 7     , 3           , 'truc3'        , 200           FROM DUAL
)
SELECT
    sum("ristourne")    as s2,
    sum(sum("montant")) as s3
FROM
    VENTE
    INNER JOIN VENTE_DETAIL ON VENTE."id"   = VENTE_DETAIL."id_vente"
group by
    VENTE."id",
    VENTE."ristourne";

Code :

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

[9i+][Best Practice] Agrégation d'indicateurs dans un modèle entête - détail

SQL Oracle

Discussions similaires

Partager

Partager