Plusieurs niveaux de totaux dans une seule requête
Un sous-total est le total d'un niveau intermédiaire : par magasin, par produit, puis pour toute la chaîne. rollup et cube calculent tous ces niveaux dans une seule requête, au lieu d'en écrire une par niveau.
La direction d'une chaîne de papeteries veut un tableau : les produits de chaque magasin, le sous-total du magasin, puis le total général.
À la fin de cette leçon
Remarque
Je peux calculer des totaux à plusieurs niveaux dans une seule requête.
Mots clés
| Mot | Ce que ça veut dire | Exemple |
|---|---|---|
| Sous-total | le total d'un niveau intermédiaire | |
| ROLLUP | ajoute les sous-totaux de droite à gauche, puis le total général | group by rollup (magasin, produit) |
| CUBE | ajoute les totaux de toutes les combinaisons de colonnes | |
| GROUPING | vaut 1 quand une colonne est regroupée dans cette ligne | |
| FILTER | garde, pour un agrégat, seulement les lignes qui réussissent un test | |
| Tableau croisé | un tableau où les valeurs d'une colonne deviennent des colonnes |
Les cellules que les étudiants remplissent restent vides dans cet aperçu.
Un GROUP BY simple ne donne qu'un niveau
group by magasin, produit donne une ligne par magasin et par produit : un seul niveau, le plus fin.
Pour avoir aussi le total de chaque magasin et le total général, on pourrait écrire trois requêtes et les coller. rollup fait tout en une fois.
Exemple : les trois niveaux d'un tableau de ventes
| Niveau | Regroupé par | Exemple de ligne |
|---|---|---|
| Le plus fin | magasin et produit | Lyon, cahier : 55 |
| Intermédiaire | magasin | Lyon, tous les produits : 190 |
| Le plus large | rien du tout | Tous les magasins : 437 |
Les cellules que les étudiants remplissent restent vides dans cet aperçu.
ROLLUP ajoute les sous-totaux et le total général
group by rollup (magasin, produit) calcule trois niveaux : magasin et produit, puis magasin seul, puis rien.
L'ordre des colonnes compte : rollup retire les colonnes de droite à gauche. Une colonne regroupée vaut NULL dans la ligne de son sous-total.
À vous : les sous-totaux avec rollup
-- Le total de chaque magasin et produit, puis de chaque magasin, puis de tout.
select magasin, produit, sum(montant) as total
from ventes
group by rollup (magasin, produit)
order by magasin nulls last, produit nulls last;
-- Inversez l'ordre : rollup (produit, magasin). Quels sous-totaux obtenez-vous ?
Exemple : le résultat de rollup
| magasin | produit | total | Niveau |
|---|---|---|---|
| Lille | cahier | 20 | Fin |
| Lille | sac | 70 | Fin |
| Lille | stylo | 36 | Fin |
| Lille | NULL | 126 | Sous-total de Lille |
| Lyon | cahier | 55 | Fin |
| Lyon | sac | 105 | Fin |
| Lyon | stylo | 30 | Fin |
| Lyon | NULL | 190 | Sous-total de Lyon |
| Nantes | cahier | 38 | Fin |
| Nantes | sac | 35 | Fin |
| Nantes | stylo | 48 | Fin |
| Nantes | NULL | 121 | Sous-total de Nantes |
| NULL | NULL | 437 | Total général |
Les cellules que les étudiants remplissent restent vides dans cet aperçu.
Une erreur fréquente
Attention
Beaucoup pensent qu'un NULL dans un sous-total veut dire que la donnée manque. Ici, NULL veut dire « tous » : la colonne est regroupée. Si la colonne contient aussi de vrais NULL, la fonction grouping fait la différence.
À vous : nommer les lignes de totaux
-- grouping(...) vaut 1 quand la colonne est « tous » dans cette ligne.
select
coalesce(magasin, 'Tous les magasins') as magasin,
coalesce(produit, 'Tous les produits') as produit,
sum(montant) as total,
grouping(magasin) as g_magasin
from ventes
group by rollup (magasin, produit)
order by grouping(magasin), magasin, grouping(produit), produit;
-- Quelle colonne vaut 1 sur la ligne du total général ?
CUBE totalise toutes les combinaisons
rollup suit une hiérarchie : de droite à gauche. cube va plus loin et calcule toutes les combinaisons.
Avec cube (magasin, produit), vous avez aussi le total de chaque produit, tous magasins confondus. Il y a quatre niveaux au lieu de trois.
À vous : les combinaisons avec cube
-- Toutes les combinaisons : aussi le total de chaque produit, tous magasins confondus.
select magasin, produit, sum(montant) as total
from ventes
group by cube (magasin, produit)
order by magasin nulls last, produit nulls last;
-- Combien de lignes de plus que rollup ? Lesquelles ?
FILTER met une condition sur un seul agrégat
sum(montant) filter (where paiement = 'carte') additionne seulement les ventes payées par carte. Les autres colonnes du select ne sont pas touchées.
On obtient plusieurs totaux, chacun avec sa condition, sans écrire une requête par condition.
À vous : plusieurs totaux avec filter
-- Plusieurs totaux dans une seule requête, chacun avec sa condition.
select magasin,
sum(montant) as total,
sum(montant) filter (where paiement = 'carte') as par_carte,
count(*) filter (where paiement = 'especes') as nb_especes
from ventes
group by magasin
order by magasin;
-- Ajoutez count(*) as nb_ventes pour comparer le nombre de ventes de chaque magasin.
FILTER écrit un tableau croisé à la main
Un tableau croisé fait des valeurs d'une colonne des colonnes du résultat : un magasin par colonne, un produit par ligne.
Sans outil spécial, on écrit un sum(...) filter (...) par colonne voulue. C'est long, mais clair.
À vous : un tableau croisé des magasins
-- Un tableau croisé écrit à la main : une colonne par magasin.
select produit,
sum(montant) filter (where magasin = 'Lyon') as lyon,
sum(montant) filter (where magasin = 'Lille') as lille,
sum(montant) filter (where magasin = 'Nantes') as nantes
from ventes
group by produit
order by produit;
-- Ajoutez sum(montant) as total pour une colonne de total en fin de ligne.