ROLLUP e CUBE

Statistiche riepilogative e funzioni finestra in PostgreSQL

Michel Semaan

Data Scientist

Totali a livello di gruppo

Medaglie cinesi e russe alle Olimpiadi estive 2008 per categoria di medaglia

| 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    |

Il vecchio metodo

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;

Entra in scena 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 è una sotto-clausola di GROUP BY che aggiunge righe extra per aggregazioni a livello di gruppo
GROUP BY Country, ROLLUP(Medal) conterà tutti i totali a livello di Country e Medal, poi solo i totali a livello Country, riempiendo Medal con null per queste righe

ROLLUP - Query

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 è gerarchico: de-aggrega da sinistra a destra
- ROLLUP(Country, Medal) include i totali a livello Country
- ROLLUP(Medal, Country) include i totali a livello Medal
- Entrambi includono il totale complessivo

ROLLUP - Risultato

| 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    |

I totali a livello di gruppo contengono null; la riga con tutti null è il totale complessivo
Nota che non include i totali a livello Medal, perché è ROLLUP(Country, Medal) e non ROLLUP(Medal, Country)

Entra in scena 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 è un ROLLUP non gerarchico
Genera tutte le possibili aggregazioni a livello di gruppo
- CUBE(Country, Medal) conta i totali per Country, per Medal e il totale complessivo

CUBE - Risultato

| 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    |

Nota che sono inclusi i totali a livello Medal

ROLLUP vs CUBE

Fonte

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

Usa ROLLUP con dati gerarchici (es. parti di data) quando non vuoi tutte le aggregazioni possibili
Usa CUBE quando vuoi tutte le aggregazioni possibili

ROLLUP(Year, Quarter)

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

CUBE(Year, Quarter)

Righe sopra + le seguenti

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

Passiamo alla pratica !

Statistiche riepilogative e funzioni finestra in PostgreSQL