SQL ROLLUP, CUBE et GROUPING SETS

Leçon 4.5 · Temps de lecture : ~9 min · Exercices pratiques ↓

SQL ROLLUP, CUBE et GROUPING SETS étendent GROUP BY : en plus des groupes habituels, ils renvoient dans le même résultat des lignes de sous-totaux et un total général. Dans cette leçon, vous apprendrez en quoi ces trois constructions diffèrent, comment utiliser WITH ROLLUP dans MySQL et comment la fonction GROUPING() distingue les lignes de total des vraies valeurs NULL.

Un rapport typique affiche des lignes de détail, des sous-totaux et un total général : le chiffre d'affaires par mois, un total pour chaque année et le total de toute la période. Avec un GROUP BY simple, il faudrait exécuter une requête par niveau et coller les résultats. ROLLUP, CUBE et GROUPING SETS renvoient tous les niveaux en une seule requête.

Comment fonctionnent les niveaux de regroupement

Un niveau de regroupement est l'ensemble des colonnes selon lesquelles les lignes sont regroupées. Pour deux colonnes, il existe quatre niveaux possibles :

Dans une ligne de sous-total, les colonnes « regroupées » contiennent NULL.

WITH ROLLUP dans MySQL et MariaDB

MySQL et MariaDB prennent en charge ROLLUP avec leur propre syntaxe : WITH ROLLUP après la liste du GROUP BY.

SELECT YEAR(payment_date) AS pay_year,
       MONTH(payment_date) AS pay_month,
       SUM(amount) AS revenue
FROM payment
GROUP BY YEAR(payment_date), MONTH(payment_date) WITH ROLLUP;

Résultat : le chiffre d'affaires de chaque mois (de mai à août 2005 et février 2006), une ligne de sous-total pour chaque année où pay_month est NULL, et une ligne de total général où les deux colonnes sont NULL.

L'ordre des colonnes dans GROUP BY définit la hiérarchie : des sous-totaux sont ajoutés pour chaque valeur de la première colonne, et la dernière colonne est « regroupée » en premier.

Distinguer les lignes de total avec GROUPING()

Dans une ligne de total, les colonnes regroupées valent NULL. Mais les données réelles peuvent aussi contenir NULL, et l'on ne peut alors plus distinguer un sous-total d'un groupe de lignes sans valeur. La fonction GROUPING(column) résout ce problème : elle renvoie 1 si la colonne a été regroupée dans cette ligne, et 0 si la valeur provient des données.

GROUPING() est pris en charge par MySQL 8.0+, PostgreSQL, SQL Server et Oracle. MariaDB ne la prend pas en charge ; on ne peut y tester que NULL.

-- MySQL 8.0+
SELECT CASE
           WHEN GROUPING(YEAR(payment_date)) = 1 THEN 'Grand total'
           WHEN GROUPING(MONTH(payment_date)) = 1 THEN 'Year total'
           ELSE 'Month'
       END AS row_type,
       YEAR(payment_date) AS pay_year,
       MONTH(payment_date) AS pay_month,
       SUM(amount) AS revenue
FROM payment
GROUP BY YEAR(payment_date), MONTH(payment_date) WITH ROLLUP;

Résultat : les mêmes huit lignes, chacune étiquetée Month, Year total ou Grand total.

ROLLUP, CUBE et GROUPING SETS standard

PostgreSQL, SQL Server et Oracle prennent en charge la syntaxe standard des trois constructions. Les exemples ci-dessous s'exécutent sur la base AdventureWorks dans SQL Server. Les produits sont regroupés par année de début de vente et par couleur ; certains produits n'ont pas de couleur, donc Color contient de vraies valeurs NULL.

ROLLUP

SELECT YEAR(SellStartDate) AS start_year,
       Color,
       COUNT(*) AS products,
       GROUPING(Color) AS is_year_total
FROM Product
GROUP BY ROLLUP (YEAR(SellStartDate), Color)
ORDER BY GROUPING(YEAR(SellStartDate)), start_year, GROUPING(Color), Color;

Résultat : le nombre de produits pour chaque année et couleur, un sous-total pour chaque année (is_year_total = 1) et le total général en dernier. Pour 2006, il y a deux lignes avec Color = NULL : celle avec is_year_total = 0 est un groupe de 18 produits sans couleur, et celle avec is_year_total = 1 est le sous-total de l'année.

CUBE

CUBE ajoute des totaux pour chaque combinaison de colonnes, y compris des totaux par couleur toutes années confondues :

SELECT YEAR(SellStartDate) AS start_year,
       Color,
       COUNT(*) AS products
FROM Product
GROUP BY CUBE (YEAR(SellStartDate), Color)
ORDER BY GROUPING(YEAR(SellStartDate)), start_year, GROUPING(Color), Color;

Résultat : tout ce que renvoie ROLLUP, plus un total pour chaque couleur toutes années confondues.

Avec n colonnes, CUBE produit 2ⁿ niveaux de regroupement, donc le résultat grossit vite. Utilisez-le pour un petit nombre de dimensions.

GROUPING SETS

GROUPING SETS ne renvoie que les niveaux listés. Par exemple, des totaux par année et des totaux par couleur, sans lignes de détail :

SELECT YEAR(SellStartDate) AS start_year,
       Color,
       COUNT(*) AS products
FROM Product
GROUP BY GROUPING SETS ((YEAR(SellStartDate)), (Color), ())
ORDER BY GROUPING(YEAR(SellStartDate)), start_year, Color;

Résultat : une ligne par année, une ligne par couleur et le total général.

ROLLUP et CUBE sont des raccourcis : ROLLUP (a, b) équivaut à GROUPING SETS ((a, b), (a), ()).

Prise en charge selon les SGBD

Lorsqu'une construction n'est pas prise en charge, combinez plusieurs requêtes GROUP BY avec UNION ALL, que vous verrez dans la leçon 5.10.

Quand utiliser quoi

• ROLLUP pour les hiérarchies : année → mois, région → ville, catégorie → produit.
• CUBE pour analyser toutes les coupes de quelques dimensions indépendantes.
• GROUPING SETS quand vous n'avez besoin que de certains niveaux, par exemple deux ventilations indépendantes dans un seul résultat.

Ajoutez toujours ORDER BY pour que les lignes de total apparaissent à un endroit prévisible, et étiquetez-les avec GROUPING() et CASE.

Points clés :

• ROLLUP, CUBE et GROUPING SETS ajoutent des lignes de sous-totaux et de total général au résultat de GROUP BY.
• ROLLUP (a, b) donne (a, b), (a), () ; CUBE (a, b) ajoute aussi (b).
• GROUPING SETS renvoie exactement les niveaux listés.
• GROUPING(column) = 1 signale une colonne regroupée et distingue les lignes de total des vraies valeurs NULL.
• MySQL et MariaDB ne prennent en charge que WITH ROLLUP ; MariaDB n'a pas GROUPING().

Questions fréquentes

Quelle est la différence entre ROLLUP et CUBE ?

ROLLUP crée des sous-totaux hiérarchiques de droite à gauche : pour (year, month), il ajoute des totaux par année et un total général. CUBE crée des sous-totaux pour toutes les combinaisons, il ajoute donc aussi des totaux par mois toutes années confondues.

Comment ajouter une ligne de total à une requête GROUP BY ?

Dans MySQL et MariaDB, ajoutez WITH ROLLUP après les colonnes du GROUP BY. Dans PostgreSQL, SQL Server et Oracle, utilisez GROUP BY ROLLUP (columns), ou GROUP BY GROUPING SETS ((columns), ()) pour obtenir seulement le détail et un total général.

Pourquoi y a-t-il des valeurs NULL dans les résultats de ROLLUP ?

Dans les lignes de sous-total et de total, les colonnes regroupées n'ont pas de valeur unique, elles valent donc NULL. Utilisez GROUPING(column), qui renvoie 1 pour ces lignes, afin de les distinguer des vraies valeurs NULL des données.