Qu'est-ce que la clause SQL GROUPING SETS, et comment l'utiliser ?

Les GROUPING SETS sont des groupes, ou ensembles, de colonnes permettant de regrouper des lignes. Au lieu d'écrire plusieurs requêtes et de combiner les résultats avec un UNION, vous pouvez simplement utiliser les GROUPING SETS.

GROUPING SETS en SQL peut être considéré comme une extension de la clause GROUP BY. Elle vous permet de définir plusieurs ensembles de regroupement dans la même requête.

Examinons sa syntaxe et comment elle peut être équivalente à une GROUP BY avec plusieurs clauses UNION ALL.

Syntaxe des GROUPING SETS SQL

La syntaxe générale de GROUPING SETS est la suivante :

SELECT
    aggregate_function(column_1)
    column_2,
    column_3,
FROM
    table_name
GROUP BY
    GROUPING SETS (
        (column_2, column_3),
        (column_2),
        (column_3),
        ()
);

Vous pouvez voir comment nous regroupons par les différents ensembles.

Cette syntaxe est équivalente à la requête plus longue suivante qui utilise GROUP BY avec UNION ALL pour combiner les résultats :

SELECT SUM(column_1), column_2, column_3
FROM table_name
GROUP BY
    column_2,
    column_3

UNION ALL

SELECT SUM(column_1), column_2, NULL
FROM table_name
GROUP BY column_2

UNION ALL

SELECT SUM(column_1), NULL, column_3
FROM table_name
GROUP BY column_3

UNION ALL

SELECT SUM(column_1), NULL, NULL
FROM table_name

Si vous utilisez GROUP BY comme cela, vous avez besoin de plusieurs clauses UNION ALL pour combiner les données provenant de différentes sources. UNION ALL exige également que tous les ensembles de résultats aient le même nombre de colonnes avec des types de données compatibles, vous devez donc ajuster les requêtes en ajoutant une valeur NULL là où c'est nécessaire.

Même si la requête fonctionne comme prévu, elle présente deux problèmes principaux :

• Elle est longue et pas très facile à gérer.
• Elle peut entraîner un problème de performances, car SQL doit analyser la table des ventes à chaque fois.

La clause GROUPING SETS résout ces problèmes. Mais quel est son impact sur le résultat par rapport à une clause traditionnelle GROUP BY? Il est temps de regarder un exemple !

Exemple de GROUPING SETS SQL

Nous avons besoin d'un échantillon de données. Créons une table appelée payments qui contient tous les paiements que notre entreprise a reçus en janvier, février et mars pour les quatre dernières années, de 2018 à 2021. Le magasin exact où le paiement a eu lieu est indiqué par la colonne store_id.

Pour créer cette table, exécutez la requête suivante :

CREATE TABLE payment (payment_amount decimal(8,2), payment_date date, store_id int);

INSERT INTO payment
VALUES
(1200.99, '2018-01-18', 1),
(189.23, '2018-02-15', 1),
(33.43, '2018-03-03', 3),
(7382.10, '2019-01-11', 2),
(382.92, '2019-02-18', 1),
(322.34, '2019-03-29', 2),
(2929.14, '2020-01-03', 2),
(499.02, '2020-02-19', 3),
(994.11, '2020-03-14', 1),
(394.93, '2021-01-22', 2),
(3332.23, '2021-02-23', 3),
(9499.49, '2021-03-10', 3),
(3002.43, '2018-02-25', 2),
(100.99, '2019-03-07', 1),
(211.65, '2020-02-02', 1),
(500.73, '2021-01-06', 3);

Vous pouvez visualiser les données à l'aide de cette simple clause SELECT:

SELECT * FROM payment ORDER BY payment_date;

L'exécution de cette requête donne le résultat suivant :

Vous pouvez voir qu'il y a plusieurs entrées pour certains magasins. Imaginez que nous préparions un rapport et que nous voulions voir un total pour chaque magasin. La fonction d'agrégation SUM() peut nous y aider. Nous utiliserons également la clause GROUP BY pour regrouper nos résultats par année et par magasin.

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment
GROUP BY YEAR(payment_date), store_id
ORDER BY YEAR(payment_date), store_id;

Les résultats sont agrégés par chaque combinaison unique d'année et de magasin.

Cependant, nous ne pouvons pas voir les paiements totaux par année : les paiements totaux pour 2018, 2019, 2020 ou 2021. Nous ne pouvons pas non plus voir les totaux par magasin, ce qui serait une mesure utile à avoir. L'utilisation de GROUPING SETS nous permet de voir ces totaux.

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment_new
GROUP BY GROUPING SETS (YEAR(payment_date), store_id)
ORDER BY YEAR(payment_date), store_id;

Wow, nos résultats ont radicalement changé ! Maintenant, nous ne voyons que les grands totaux pour chaque magasin ainsi que les grands totaux pour chaque année.

Pour les colonnes pour lesquelles les lignes ne sont pas groupées, vous voyez les valeurs NULL.

N'oubliez pas que vous pouvez inclure plusieurs clauses GROUP BY dans votre GROUPING SETS. En appliquant cela à notre requête, nous obtenons le résultat suivant :

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment_new
GROUP BY GROUPING SETS
(
(YEAR(payment_date), store_id),
(YEAR(payment_date)),
(store_id)
)
ORDER BY YEAR(payment_date), store_id;

Avant de terminer ce tutoriel, nous devons mentionner deux autres extensions SQL GROUP BY qui pourraient s'avérer utiles pour votre projet ou scénario particulier : ROLLUP et CUBE. Ces sujets sont couverts de manière très détaillée dans ce parcours d'apprentissage SQL avancé de LearnSQL.fr qui présente les fonctions de fenêtre, les extensions GROUP BY et les requêtes récursives.

Exemple de ROLLUP SQL

Comme pour GROUPING SETS, vous pouvez utiliser l'option ROLLUP dans une seule requête pour générer plusieurs ensembles de regroupement.

ROLLUP suppose une hiérarchie entre les colonnes d'entrée. Par exemple, si les colonnes en entrée sont :

GROUP BY ROLLUP(column_1,column_2)

la hiérarchie pour ceci est column_1 > column_2, et ROLLUP génère les ensembles de regroupement suivants :

(column_1, column_2)
(column_1)
()

ROLLUP génère tous les ensembles de regroupement qui ont un sens dans cette hiérarchie. Il génère une ligne de sous-total chaque fois que la valeur de column_1 change ; c'est la hiérarchie que nous avons fournie. Pour cette raison, nous utilisons souvent ROLLUP pour générer des sous-totaux et des totaux généraux dans les rapports. L'ordre de vos colonnes dans ROLLUP est très important.

Examinons une requête qui utilise ROLLUP:

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment
GROUP BY ROLLUP (YEAR(payment_date), store_id)
ORDER BY YEAR(payment_date), store_id

Le grand total est affiché en haut du résultat :

Le reste du résultat est structuré comme suit. Tout d'abord, le total annuel est affiché :

Il est suivi par les totaux par magasin et par année :

Comme vous pouvez le constater, ROLLUP génère une ligne de sous-total chaque fois que la valeur de Payment Year change, puisque c'est la hiérarchie que nous avons fournie. Cet exemple montre à quel point ROLLUP peut être utile à des fins de reporting.

Exemple de SQL CUBE

Tout comme ROLLUP, CUBE est une extension de la clause GROUP BY. Elle vous permet de générer des sous-totaux pour toutes les combinaisons des colonnes de regroupement spécifiées dans la clause GROUP BY.

La clause CUBE revient à combiner GROUPING SETS et ROLLUP. Elle affiche le résultat détaillé des deux.

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment
GROUP BY CUBE (YEAR(payment_date), store_id)
ORDER BY YEAR(payment_date), store_id

La principale différence de cette sortie par rapport à l'exemple ROLLUP est que le total général pour chaque magasin est également affiché ici.

À l'exception de ces lignes, toutes les lignes de ce résultat sont identiques à celles du résultat de ROLLUP.

Ceci conclut notre comparaison de GROUPING SETS, ROLLUP, et CUBE! Vous trouverez d'autres exemples dans cet article sur le regroupement, le roulement et le cubage des données.

Regroupez efficacement vos données avec les extensions SQL GROUP BY

La maîtrise des extensions SQL GROUP BY demande de la pratique. Des options comme GROUPING SETS, ROLLUP et CUBE vous permettent de manipuler les résultats de vos requêtes de différentes manières. Savoir utiliser efficacement ces extensions réduit la nécessité de formater manuellement vos données avant de les transmettre aux parties prenantes concernées.

Pour approfondir vos connaissances dans ce domaine, vous pouvez suivre ce cours sur les extensions GROUP BY à l'adresse LearnSQL.fr, qui couvre GROUPING SETS, ROLLUP et CUBE.