Les agrégats recapitulatifs
Un calcul d'agrégats porte toujours sur le regroupement indiqué par la clause« GROUP BY », mais il est parfois nécessaire d'effectuer un regroupement plus large afin de connaître d'autres valeurs.
La norme SQL 1999 introduit des nouvelles façons de réaliser des regroupements de données a l’aide des extensions à la clause « GROUP BY » il s'agit des deux fonctions de groupage « ROLLUP » et « CUBE » et une expression plus générale « GROUPING SETS ».
Groupage ROLLUP
La fonction « ROLLUP » permet de générer des sous-totaux pour les attributs spécifiés, plus une ligne supplémentaire représentant le total global.
SQL> SELECT FONCTION, SUM(SALAIRE) FROM EMPLOYES GROUP BY ROLLUP(FONCTION);
FONCTION SUM(SALAIRE)
------------------------------ ------------
Assistante commerciale 2000
Chef des ventes 8000
Représentant(e) 16561
Vice-Prèsident 10000
36561
Dans l’exemple, vous pouvez observer la fonction « ROLLUP » permettant de calculer le total pour la somme des salaires des employés.
SQL> SELECT FONCTION, PAYS, SUM(SALAIRE) FROM EMPLOYES
2 GROUP BY ROLLUP(FONCTION, PAYS);
FONCTION PAYS SUM(SALAIRE)
------------------------------ ------------ ------------
Président 150000
Président 150000
Vice-Président 243000
Vice-Président 243000
Chef des ventes 83000
Chef des ventes 83000
Représentant(e) Suède 31300
Représentant(e) Brésil 23100
Représentant(e) Canada 35500
Représentant(e) France 32200
Représentant(e) Italie 29000
Représentant(e) Suisse 36700
Représentant(e) Espagne 36700
Représentant(e) Irlande 36400
Représentant(e) Mexique 28900
Représentant(e) Norvège 35300
Représentant(e) Pologne 32400
Représentant(e) Autriche 25600
Représentant(e) Belgique 27000
Représentant(e) Danemark 27500
Représentant(e) Finlande 29800
Représentant(e) Portugal 31600
Représentant(e) Allemagne 51200
Représentant(e) Argentine 38900
Représentant(e) Venezuela 37800
Représentant(e) États-Unis 23100
Représentant(e) Royaume-Uni 42900
Représentant(e) 692900
Assistante commerciale 16540
Assistante commerciale 16540
1185440
Dans l’exemple précèdent, vous pouvez remarquer que le groupe est formé par les deux critères précisés dans la clause « GROUP BY » ; les résultats de la fonction « SUM » sont retournés aux niveaux suivants :
- par le pays PAYS et la fonction FONCTION;
- sous-totaux par la fonction de l’employé FONCTION;
- total global.
SQL> SELECT CODE_CLIENT, ANNEE, TRIMESTRE, SUM(PORT) PORT FROM COMMANDES
2 WHERE CODE_CLIENT IN ('ALFKI','ANTON')
3 GROUP BY ROLLUP( CODE_CLIENT, ANNEE, TRIMESTRE ) ;
CODE_ ANNEE TRIMESTRE PORT
----- ---------- ---------- ----------
ALFKI 2010 1 2444,3
ALFKI 2010 2 2028,9
ALFKI 2010 3 1261,7
ALFKI 2010 4 1370,9
ALFKI 2010 7105,8
ALFKI 2011 1 1738,6
ALFKI 2011 2 1772,7
ALFKI 2011 3511,3
ALFKI 10617,1
ANTON 2010 1 877,6
ANTON 2010 2 2111,1
ANTON 2010 3 1650,4
ANTON 2010 4 2328,8
ANTON 2010 6967,9
ANTON 2011 1 2248,1
ANTON 2011 2 1526,1
ANTON 2011 3774,2
ANTON 10742,1
21359,2
Dans l’exemple précèdent, vous pouvez remarquer la fonction « ROLLUP » permettant d’obtenir les valeurs intermédiaires des regroupements pour toutes les combinaisons existantes et le total global.
Il est possible d’utiliser la fonction « ROLLUP » uniquement sur une partie des champs précisés dans la clause « GROUP BY ». Ainsi les regroupements sont effectués uniquement sur les champs passés comme arguments à la fonction « ROLLUP ».
SQL> SELECT NO_EMPLOYE,CODE_CLIENT,ANNEE,TRIMESTRE,SUM(PORT) PORT
2 FROM COMMANDES
3 WHERE CODE_CLIENT = 'ALFKI' AND NO_EMPLOYE IN (40,59)
4 GROUP BY NO_EMPLOYE, CODE_CLIENT, ROLLUP( ANNEE, TRIMESTRE );
NO_EMPLOYE CODE_ ANNEE TRIMESTRE PORT
---------- ----- ---------- ---------- ----------
40 ALFKI 2010 1 314,4
40 ALFKI 2010 2 302,6
40 ALFKI 2010 3 201,4
40 ALFKI 2010 4 228,2
40 ALFKI 2010 1046,6
40 ALFKI 2011 1 79,1
40 ALFKI 2011 2 590,4
40 ALFKI 2011 669,5
40 ALFKI 1716,1
59 ALFKI 2010 1 268,3
59 ALFKI 2010 2 189,2
59 ALFKI 2010 3 168,7
59 ALFKI 2010 4 64,6
59 ALFKI 2010 690,8
59 ALFKI 2011 1 436
59 ALFKI 2011 2 224,7
59 ALFKI 2011 660,7
59 ALFKI 1351,5
Dans l’exemple suivant vous pouvez voir la requête qui affiche le client, l’année, le trimestre et le mois mais les regroupent sont calculés uniqument pour le client et l’année.
SQL> SELECT CODE_CLIENT, ANNEE, TRIMESTRE, MOIS, SUM(PORT) PORT
2 FROM COMMANDES
3 WHERE CODE_CLIENT BETWEEN 'ALFKI' AND 'ANATR'
4 GROUP BY CODE_CLIENT, ROLLUP( ANNEE,(TRIMESTRE,MOIS )) ;
CODE_ ANNEE TRIMESTRE MOIS PORT
----- ---------- ---------- ---------- ----------
ALFKI 2010 1 1 926,7
ALFKI 2010 1 2 793
ALFKI 2010 1 3 724,6
ALFKI 2010 2 4 549,7
ALFKI 2010 2 5 900,7
ALFKI 2010 2 6 578,5
ALFKI 2010 3 7 687,5
ALFKI 2010 3 8 238,1
ALFKI 2010 3 9 336,1
ALFKI 2010 4 10 748,9
ALFKI 2010 4 11 622
ALFKI 2010 7105,8
ALFKI 2011 1 2 1287,9
ALFKI 2011 1 3 450,7
ALFKI 2011 2 4 977,8
ALFKI 2011 2 5 64,4
ALFKI 2011 2 6 730,5
ALFKI 2011 3511,3
ALFKI 10617,1
ANATR 2010 1 1 637
ANATR 2010 1 2 703,7
ANATR 2010 1 3 427,6
ANATR 2010 2 4 471,4
ANATR 2010 2 5 329,3
ANATR 2010 2 6 336,3
ANATR 2010 3 7 634,5
ANATR 2010 3 8 623,7
ANATR 2010 3 9 370,5
ANATR 2010 4 10 179,4
ANATR 2010 4 11 373,2
ANATR 2010 4 12 1034
ANATR 2010 6120,6
ANATR 2011 1 1 186,3
ANATR 2011 1 2 930,7
ANATR 2011 1 3 942,2
ANATR 2011 2 4 911,6
ANATR 2011 2 5 988,4
ANATR 2011 2 6 573,3
ANATR 2011 4532,5
ANATR 10653,1
Groupage CUBE
La fonction « CUBE » va plus loin que « ROLLUP » ; elle permet de générer des sous-totaux pour toute combinaison d'attributs possibles parmi les argument de la fonctions, des totaux par attribut, et un total global.
SQL> SELECT CODE_CLIENT, ANNEE, TRIMESTRE, SUM(PORT) PORT FROM COMMANDES
2 WHERE CODE_CLIENT = 'ALFKI'
3 GROUP BY ROLLUP( CODE_CLIENT, ANNEE, TRIMESTRE ) ;
CODE_ ANNEE TRIMESTRE PORT
----- ---------- ---------- ----------
ALFKI 2010 1 2444,3
ALFKI 2010 2 2028,9
ALFKI 2010 3 1261,7
ALFKI 2010 4 1370,9
ALFKI 2010 7105,8
ALFKI 2011 1 1738,6
ALFKI 2011 2 1772,7
ALFKI 2011 3511,3
ALFKI 10617,1
10617,1
SQL> SELECT CODE_CLIENT, ANNEE, TRIMESTRE, SUM(PORT) PORT FROM COMMANDES
2 WHERE CODE_CLIENT = 'ALFKI'
3 GROUP BY CUBE( CODE_CLIENT, ANNEE, TRIMESTRE ) ;
CODE_ ANNEE TRIMESTRE PORT
----- ---------- ---------- ----------
10617,1
1 4182,9
2 3801,6
3 1261,7
4 1370,9
2010 7105,8
2010 1 2444,3
2010 2 2028,9
2010 3 1261,7
2010 4 1370,9
2011 3511,3
2011 1 1738,6
2011 2 1772,7
ALFKI 10617,1
ALFKI 1 4182,9
ALFKI 2 3801,6
ALFKI 3 1261,7
ALFKI 4 1370,9
ALFKI 2010 7105,8
ALFKI 2010 1 2444,3
ALFKI 2010 2 2028,9
ALFKI 2010 3 1261,7
ALFKI 2010 4 1370,9
ALFKI 2011 3511,3
ALFKI 2011 1 1738,6
ALFKI 2011 2 1772,7
Dans l’exemple précèdent, vous pouvez observer la comparaison entre la fonction « CUBE » et la fonction « ROLLUP ». Le groupe est formé par les trois critères le client, l’année et le mois précisés dans la clause « GROUP BY » ; les résultats de la fonction « SUM » sont retournés dans le cas de la fonction « CUBE » aux niveaux suivants :
- total global
- sous-totaux par Trimestre
- sous-totaux par Année
- sous-totaux par Trimestre pour l’Année
- sous-totaux par Client
- sous-totaux par Trimestre pour le Client
- sous-totaux par Année pour le Client
- sous-totaux par Trimestre pour l’Année et par Client
GROUPING SETS
Dans une clause « GROUP BY » avec la fonction « GROUPING SETS », on peut spécifier le jeu de groupes que l'on désire créer. Ceci permet des spécifications précises au travers de dimensions multiples sans devoir calculer le « CUBE » en entier.
La fonction « GROUPING SETS » permet de définir plusieurs groupes dans la même requête.
SQL> SELECT CODE_CLIENT, ANNEE, MOIS, SUM(PORT) PORT FROM COMMANDES
2 WHERE CODE_CLIENT BETWEEN 'ALFKI' AND 'BLONP'
3 GROUP BY GROUPING SETS( CODE_CLIENT, ANNEE, MOIS)
4 ORDER BY CODE_CLIENT, ANNEE, MOIS;
CODE_ ANNEE MOIS PORT
----- ---------- ---------- ----------
ALFKI 10617,1
ANATR 10653,1
ANTON 10742,1
AROUT 12944,5
BERGS 11969,2
BLAUS 9724,5
BLONP 10413,4
2010 44890,1
2011 32173,8
1 5979,3
2 10228
3 9514,7
4 10183,1
5 9474
6 8575,9
7 3654,5
8 3296,1
9 2642,4
10 5282,2
11 4086
12 4147,7
Comme on peut le constater dans cet exemple, les résultats ne sont que ceux des groupages, le premier sur Client, le deuxième sur Année, le troisième sur le Mois et le quatrième sur l'ensemble global. En fait, cette écriture synthétique correspond à la concaténation des résultats des trois requêtes suivantes :
SELECT CODE_CLIENT "Client", SUM(PORT) "Port"
FROM COMMANDES GROUP BY CODE_CLIENT
UNION
SELECT ANNEE "Année", SUM(PORT) "Port"
FROM COMMANDES GROUP BY ANNEE
UNION
SELECT MOIS "Mois", SUM(PORT) "Port"
FROM COMMANDES GROUP BY MOIS;
L’exemple suivant montre l’utilisation des arguments composés à l’aide des parenthèses. Chaque parenthèse est un argument pour la fonction « GRUPING SETS » et la troisième parenthèse constitue le cumul total pour l’ensemble des enregistrements.
SQL> SELECT CODE_CLIENT, ANNEE, TRIMESTRE, SUM(PORT) PORT FROM COMMANDES
2 WHERE CODE_CLIENT BETWEEN 'ALFKI' AND 'AROUT'
3 GROUP BY GROUPING SETS( (CODE_CLIENT,ANNEE), (ANNEE,TRIMESTRE), ())
4 ORDER BY CODE_CLIENT, ANNEE, TRIMESTRE;
CODE_ ANNEE TRIMESTRE PORT
----- ---------- ---------- ----------
ALFKI 2010 7105,8
ALFKI 2011 3511,3
ANATR 2010 6120,6
ANATR 2011 4532,5
ANTON 2010 6967,9
ANTON 2011 3774,2
AROUT 2010 7440,5
AROUT 2011 5504
2010 1 6873,9
2010 2 7011,6
2010 3 6232,7
2010 4 7516,6
2011 1 8045,8
2011 2 9276,2
44956,8
SQL> SELECT CODE_CLIENT,ANNEE,TRIMESTRE,MOIS,SUM(PORT) PORT FROM COMMANDES
2 WHERE CODE_CLIENT BETWEEN 'ALFKI' AND 'ANTON'
3 GROUP BY GROUPING SETS(ROLLUP(CODE_CLIENT,ANNEE),
4 ROLLUP(ANNEE,TRIMESTRE,MOIS))
5 ORDER BY CODE_CLIENT, ANNEE, TRIMESTRE, MOIS;
CODE_ ANNEE TRIMESTRE MOIS PORT
----- ---------- ---------- ---------- ----------
ALFKI 2010 7105,8
ALFKI 2011 3511,3
ALFKI 10617,1
ANATR 2010 6120,6
ANATR 2011 4532,5
ANATR 10653,1
ANTON 2010 6967,9
ANTON 2011 3774,2
ANTON 10742,1
2010 1 1 1563,7
2010 1 2 1816,2
2010 1 3 1710,3
2010 1 5090,2
2010 2 4 1471,5
2010 2 5 2232,6
2010 2 6 1572,9
2010 2 5277
2010 3 7 1419,7
2010 3 8 1358,9
2010 3 9 1762,2
2010 3 4540,8
2010 4 10 1786
2010 4 11 1845
2010 4 12 1655,3
2010 4 5286,3
2010 20194,3
2011 1 1 872,4
2011 1 2 3657,3
2011 1 3 1516,2
2011 1 6045,9
2011 2 4 1889,4
2011 2 5 1694,4
2011 2 6 2188,3
2011 2 5772,1
2011 11818
32012,3
32012,3