ROLLUP et CUBE

Statistiques récapitulatives et fonctions de fenêtrage PostgreSQL

Michel Semaan

Data Scientist

Totaux au niveau du groupe

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

| 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 méthode

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;

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 GROUP BY qui inclut des lignes supplémentaires pour les agrégations au niveau du groupe
GROUP BY Country, ROLLUP(Medal) comptera tous les totaux au niveau Country et Medal, puis ne comptera que les totaux au niveau Country et remplira Medal avec les valeurs nullpour 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, désagrégé de la colonne la plus à gauche vers la colonne la plus à droite
- ROLLUP(Country, Medal) inclut les totaux au niveau Country
- ROLLUP(Medal, Country) inclut les totaux au niveau Medal
- Les deux comprennent 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 au niveau du groupe contiennent des valeurs nulls ; la ligne contenant toutes les valeurs null correspond au total général
Notez que les totaux au niveau Medal n'ont pas été inclus, car il s'agit de ROLLUP(Country, Medal) et non de ROLLUP(Medal, Country)

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) génère des agrégations pour chaque Country, pour chaque type de Medal, ainsi qu’un 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    |

Veuillez noter que les totaux de Medal sont inclus

ROLLUP vs CUBE

Source

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

Utilisez ROLLUP si les données sont hiérarchiques (ex. : date) et si vous ne voulez pas toutes les agrégations au niveau groupe
Utilisez CUBE si vous voulez toutes les agrégations au niveau groupe

ROLLUP(Year, Quarter)

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

CUBE(Year, Quarter)

Lignes au-dessus + les suivantes

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

Passons à la pratique !

Statistiques récapitulatives et fonctions de fenêtrage PostgreSQL