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

en savoir

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