ROLLUP et CUBE sont des modificateurs de GROUP BY. Tous deux calculent des sous-totaux. ROLLUP prend une liste ordonnée de colonnes, par exemple (day, month, year), et calcule des sous-totaux à chaque niveau de l’agrégation, puis un total général. CUBE calcule des sous-totaux pour toutes les combinaisons possibles des colonnes spécifiées. GROUPING permet d’identifier quelles lignes renvoyées par ROLLUP ou CUBE sont des super-agrégats, et lesquelles correspondent aux lignes qu’un GROUP BY sans modificateur renverrait.
La fonction GROUPING prend plusieurs colonnes en argument et renvoie un masque de bits.
1 indique qu’une ligne renvoyée par un modificateur ROLLUP ou CUBE de GROUP BY est un sous-total0 indique qu’une ligne renvoyée par ROLLUP ou CUBE n’est pas un sous-total
Par défaut, le modificateur CUBE calcule des sous-totaux pour toutes les combinaisons possibles des colonnes transmises à CUBE. GROUPING SETS vous permet de spécifier les combinaisons exactes à calculer.
L’analyse de données hiérarchiques constitue un bon cas d’utilisation des modificateurs ROLLUP, CUBE et GROUPING SETS. L’exemple ci-dessous s’appuie sur une table contenant des données sur les distributions Linux et leurs versions installées dans deux centres de données. Il peut être utile d’examiner les données par distribution, version et lieu.
Charger des données d’exemple
CREATE TABLE servers ( datacenter VARCHAR(255),
distro VARCHAR(255) NOT NULL,
version VARCHAR(50) NOT NULL,
quantity INT
)
ORDER BY (datacenter, distro, version)
INSERT INTO servers(datacenter, distro, version, quantity)
VALUES ('Schenectady', 'Arch','2022.08.05',50),
('Westport', 'Arch','2022.08.05',40),
('Schenectady','Arch','2021.09.01',30),
('Westport', 'Arch','2021.09.01',20),
('Schenectady','Arch','2020.05.01',10),
('Westport', 'Arch','2020.05.01',5),
('Schenectady','RHEL','9',60),
('Westport','RHEL','9',70),
('Westport','RHEL','7',80),
('Schenectady','RHEL','7',80)
┌─datacenter──┬─distro─┬─version────┬─quantity─┐
│ Schenectady │ Arch │ 2020.05.01 │ 10 │
│ Schenectady │ Arch │ 2021.09.01 │ 30 │
│ Schenectady │ Arch │ 2022.08.05 │ 50 │
│ Schenectady │ RHEL │ 7 │ 80 │
│ Schenectady │ RHEL │ 9 │ 60 │
│ Westport │ Arch │ 2020.05.01 │ 5 │
│ Westport │ Arch │ 2021.09.01 │ 20 │
│ Westport │ Arch │ 2022.08.05 │ 40 │
│ Westport │ RHEL │ 7 │ 80 │
│ Westport │ RHEL │ 9 │ 70 │
└─────────────┴────────┴────────────┴──────────┘
10 rows in set. Elapsed: 0.409 sec.
SELECT
datacenter,
distro,
SUM (quantity) qty
FROM
servers
GROUP BY
datacenter,
distro;
┌─datacenter──┬─distro─┬─qty─┐
│ Schenectady │ RHEL │ 140 │
│ Westport │ Arch │ 65 │
│ Schenectady │ Arch │ 90 │
│ Westport │ RHEL │ 150 │
└─────────────┴────────┴─────┘
4 rows in set. Elapsed: 0.212 sec.
SELECT
datacenter,
SUM (quantity) qty
FROM
servers
GROUP BY
datacenter;
┌─datacenter──┬─qty─┐
│ Westport │ 215 │
│ Schenectady │ 230 │
└─────────────┴─────┘
2 rows in set. Elapsed: 0.277 sec.
SELECT
distro,
SUM (quantity) qty
FROM
servers
GROUP BY
distro;
┌─distro─┬─qty─┐
│ Arch │ 155 │
│ RHEL │ 290 │
└────────┴─────┘
2 rows in set. Elapsed: 0.352 sec.
SELECT
SUM(quantity) qty
FROM
servers;
┌─qty─┐
│ 445 │
└─────┘
1 row in set. Elapsed: 0.244 sec.
Comparaison de plusieurs clauses GROUP BY avec GROUPING SETS
SELECT
datacenter,
distro,
SUM (quantity) qty
FROM
servers
GROUP BY
datacenter,
distro
UNION ALL
SELECT
datacenter,
null,
SUM (quantity) qty
FROM
servers
GROUP BY
datacenter
UNION ALL
SELECT
null,
distro,
SUM (quantity) qty
FROM
servers
GROUP BY
distro
UNION ALL
SELECT
null,
null,
SUM(quantity) qty
FROM
servers;
┌─datacenter─┬─distro─┬─qty─┐
│ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 445 │
└────────────┴────────┴─────┘
┌─datacenter──┬─distro─┬─qty─┐
│ Westport │ ᴺᵁᴸᴸ │ 215 │
│ Schenectady │ ᴺᵁᴸᴸ │ 230 │
└─────────────┴────────┴─────┘
┌─datacenter──┬─distro─┬─qty─┐
│ Schenectady │ RHEL │ 140 │
│ Westport │ Arch │ 65 │
│ Schenectady │ Arch │ 90 │
│ Westport │ RHEL │ 150 │
└─────────────┴────────┴─────┘
┌─datacenter─┬─distro─┬─qty─┐
│ ᴺᵁᴸᴸ │ Arch │ 155 │
│ ᴺᵁᴸᴸ │ RHEL │ 290 │
└────────────┴────────┴─────┘
9 rows in set. Elapsed: 0.527 sec.
SELECT
datacenter,
distro,
SUM (quantity) qty
FROM
servers
GROUP BY
GROUPING SETS(
(datacenter,distro),
(datacenter),
(distro),
()
)
┌─datacenter──┬─distro─┬─qty─┐
│ Schenectady │ RHEL │ 140 │
│ Westport │ Arch │ 65 │
│ Schenectady │ Arch │ 90 │
│ Westport │ RHEL │ 150 │
└─────────────┴────────┴─────┘
┌─datacenter──┬─distro─┬─qty─┐
│ Westport │ │ 215 │
│ Schenectady │ │ 230 │
└─────────────┴────────┴─────┘
┌─datacenter─┬─distro─┬─qty─┐
│ │ │ 445 │
└────────────┴────────┴─────┘
┌─datacenter─┬─distro─┬─qty─┐
│ │ Arch │ 155 │
│ │ RHEL │ 290 │
└────────────┴────────┴─────┘
9 rows in set. Elapsed: 0.427 sec.
Comparaison entre CUBE et GROUPING SETS
CUBE(datacenter,distro,version), définit une hiérarchie qui n’est pas forcément pertinente. Il n’est pas pertinent d’examiner version pour les deux distributions (car Arch et RHEL n’ont ni le même cycle de publication ni les mêmes conventions de nommage des versions). L’exemple de GROUPING SETS qui suit est plus approprié, car il regroupe distro et version dans le même ensemble.
SELECT
datacenter,
distro,
version,
SUM(quantity)
FROM
servers
GROUP BY
CUBE(datacenter,distro,version)
ORDER BY
datacenter,
distro;
┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐
│ │ │ 7 │ 160 │
│ │ │ 2020.05.01 │ 15 │
│ │ │ 2021.09.01 │ 50 │
│ │ │ 2022.08.05 │ 90 │
│ │ │ 9 │ 130 │
│ │ │ │ 445 │
│ │ Arch │ 2021.09.01 │ 50 │
│ │ Arch │ 2022.08.05 │ 90 │
│ │ Arch │ 2020.05.01 │ 15 │
│ │ Arch │ │ 155 │
│ │ RHEL │ 9 │ 130 │
│ │ RHEL │ 7 │ 160 │
│ │ RHEL │ │ 290 │
│ Schenectady │ │ 9 │ 60 │
│ Schenectady │ │ 2021.09.01 │ 30 │
│ Schenectady │ │ 7 │ 80 │
│ Schenectady │ │ 2022.08.05 │ 50 │
│ Schenectady │ │ 2020.05.01 │ 10 │
│ Schenectady │ │ │ 230 │
│ Schenectady │ Arch │ 2022.08.05 │ 50 │
│ Schenectady │ Arch │ 2021.09.01 │ 30 │
│ Schenectady │ Arch │ 2020.05.01 │ 10 │
│ Schenectady │ Arch │ │ 90 │
│ Schenectady │ RHEL │ 7 │ 80 │
│ Schenectady │ RHEL │ 9 │ 60 │
│ Schenectady │ RHEL │ │ 140 │
│ Westport │ │ 9 │ 70 │
│ Westport │ │ 2020.05.01 │ 5 │
│ Westport │ │ 2022.08.05 │ 40 │
│ Westport │ │ 7 │ 80 │
│ Westport │ │ 2021.09.01 │ 20 │
│ Westport │ │ │ 215 │
│ Westport │ Arch │ 2020.05.01 │ 5 │
│ Westport │ Arch │ 2021.09.01 │ 20 │
│ Westport │ Arch │ 2022.08.05 │ 40 │
│ Westport │ Arch │ │ 65 │
│ Westport │ RHEL │ 9 │ 70 │
│ Westport │ RHEL │ 7 │ 80 │
│ Westport │ RHEL │ │ 150 │
└─────────────┴────────┴────────────┴───────────────┘
39 rows in set. Elapsed: 0.355 sec.
La version dans l’exemple ci-dessus peut ne pas avoir de sens lorsqu’elle n’est pas associée à une distribution ; en revanche, si nous suivions la version du noyau, cela pourrait être pertinent, car la version du noyau peut être associée à l’une ou l’autre des distributions. Utiliser GROUPING SETS, comme dans l’exemple suivant, peut donc être un meilleur choix.
SELECT
datacenter,
distro,
version,
SUM(quantity)
FROM servers
GROUP BY
GROUPING SETS (
(datacenter, distro, version),
(datacenter, distro))
┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐
│ Westport │ RHEL │ 9 │ 70 │
│ Schenectady │ Arch │ 2022.08.05 │ 50 │
│ Schenectady │ Arch │ 2021.09.01 │ 30 │
│ Schenectady │ RHEL │ 7 │ 80 │
│ Westport │ Arch │ 2020.05.01 │ 5 │
│ Westport │ RHEL │ 7 │ 80 │
│ Westport │ Arch │ 2021.09.01 │ 20 │
│ Westport │ Arch │ 2022.08.05 │ 40 │
│ Schenectady │ RHEL │ 9 │ 60 │
│ Schenectady │ Arch │ 2020.05.01 │ 10 │
└─────────────┴────────┴────────────┴───────────────┘
┌─datacenter──┬─distro─┬─version─┬─sum(quantity)─┐
│ Schenectady │ RHEL │ │ 140 │
│ Westport │ Arch │ │ 65 │
│ Schenectady │ Arch │ │ 90 │
│ Westport │ RHEL │ │ 150 │
└─────────────┴────────┴─────────┴───────────────┘
14 rows in set. Elapsed: 1.036 sec.
