ROLLUP et CUBE

Fonctions de synthèse et de fenêtre dans PostgreSQL

Michel Semaan

Data Scientist

Totaux par groupe

Médailles chinoises et russes aux Jeux olympiques d'été 2008 par catégorie

| Country | Medal  | Awards |
|---------|--------|--------|
| CHN     | Bronze | 57     |
| CHN     | Gold   | 74     |
| CHN     | Silver | 53     |
| CHN     | Total  | 184    |
| RUS     | Bronze | 56     |
| RUS     | Gold   | 43     |
| RUS     | Silver | 44     |
| RUS     | Total  | 143    |

À l'ancienne

SELECT
  Country, Medal, COUNT(*) AS Awards
FROM Summer_Medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY Country, Medal
ORDER BY Country ASC, Medal ASC

UNION ALL

SELECT
  Country, 'Total', COUNT(*) AS Awards
FROM Summer_Medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY Country, 2
ORDER BY Country ASC;

Voici ROLLUP

SELECT
  Country, Medal, COUNT(*) AS Awards
FROM Summer_Medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY Country, ROLLUP(Medal)
ORDER BY Country ASC, Medal ASC;

ROLLUP est une sous-clause de GROUP BY qui ajoute des lignes pour les agrégations au niveau du groupe
GROUP BY Country, ROLLUP(Medal) compte tous les totaux au niveau Country et Medal, puis ne compte que les totaux Country et remplit Medal avec des null pour ces lignes

ROLLUP - Requête

SELECT
  Country, Medal, COUNT(*) AS Awards
FROM summer_medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY ROLLUP(Country, Medal)
ORDER BY Country ASC, Medal ASC;

ROLLUP est hiérarchique : il désagrège de la colonne la plus à gauche vers la plus à droite
- ROLLUP(Country, Medal) inclut les totaux au niveau Country
- ROLLUP(Medal, Country) inclut les totaux au niveau Medal
- Les deux incluent les totaux généraux

ROLLUP - Résultat

| Country | Medal  | Awards |
|---------|--------|--------|
| CHN     | Bronze | 57     |
| CHN     | Gold   | 74     |
| CHN     | Silver | 53     |
| CHN     | null   | 184    |
| RUS     | Bronze | 56     |
| RUS     | Gold   | 43     |
| RUS     | Silver | 44     |
| RUS     | null   | 143    |
| null    | null   | 327    |

Les totaux par groupe contiennent des null ; la ligne avec tous les null est le total général
Remarquez l'absence de totaux au niveau Medal, car c'est ROLLUP(Country, Medal) et non ROLLUP(Medal, Country)

Voici CUBE

SELECT
  Country, Medal, COUNT(*) AS Awards
FROM summer_medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY CUBE(Country, Medal)
ORDER BY Country ASC, Medal ASC;

CUBE est un ROLLUP non hiérarchique
Il génère toutes les agrégations possibles au niveau du groupe
- CUBE(Country, Medal) compte les totaux au niveau Country, au niveau Medal et le total général

CUBE - Résultat

| Country | Medal  | Awards |
|---------|--------|--------|
| CHN     | Bronze | 57     |
| CHN     | Gold   | 74     |
| CHN     | Silver | 53     |
| CHN     | null   | 184    |
| RUS     | Bronze | 56     |
| RUS     | Gold   | 43     |
| RUS     | Silver | 44     |
| RUS     | null   | 143    |
| null    | Bronze | 113    |
| null    | Gold   | 117    |
| null    | Silver | 97     |
| null    | null   | 327    |

Remarquez que les totaux au niveau Medal sont inclus

ROLLUP vs CUBE

Source

| Year | Quarter | Sales |
|------|---------|-------|
| 2008 | Q1      | 12    |
| 2008 | Q2      | 15    |
| 2009 | Q1      | 21    |
| 2009 | Q2      | 27    |

Utilisez ROLLUP pour des données hiérarchiques (p. ex., composantes de date) lorsque vous ne voulez pas toutes les agrégations possibles
Utilisez CUBE lorsque vous voulez toutes les agrégations possibles

ROLLUP(Year, Quarter)

| Year | Quarter | Sales |
|------|---------|-------|
| 2008 | null    | 27    |
| 2009 | null    | 48    |
| null | null    | 75    |

CUBE(Year, Quarter)

Lignes ci-dessus + les suivantes

| Year | Quarter | Sales |
|------|---------|-------|
| null | Q1      | 33    |
| null | Q2      | 42    |

Passons à la pratique !

Fonctions de synthèse et de fenêtre dans PostgreSQL