ROLLUP en CUBE

PostgreSQL Samenvattingsstatistieken en vensterfuncties

Michel Semaan

Data Scientist

Totalen per groep

Chinese en Russische medailles op de Zomerspelen 2008 per medailleklasse

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

De oude manier

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;

Maak kennis met 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 is een GROUP BY-subclausule die extra rijen voor groepsaggregaties toevoegt
GROUP BY Country, ROLLUP(Medal) telt eerst alle totalen op Country- en Medal-niveau, telt daarna alleen Country-totalen en vult Medal met null voor die rijen

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 is hiërarchisch en de-aggregeert van de meest linkse naar de meest rechtse kolom
- ROLLUP(Country, Medal) bevat totalen op Country-niveau
- ROLLUP(Medal, Country) bevat totalen op Medal-niveau
- Beide bevatten een totaaltelling

ROLLUP - Resultaat

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

Groeps-totalen bevatten null; de rij met overal null is het totaaltotaal
Let op: er zijn geen totalen op Medal-niveau, omdat we ROLLUP(Country, Medal) gebruiken en niet ROLLUP(Medal, Country)

Maak kennis met 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 is een niet-hiërarchische variant van ROLLUP
Het genereert alle mogelijke groepsaggregaties
- CUBE(Country, Medal) telt per Country, per Medal en de totaaltelling

CUBE - Resultaat

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

Let op: totalen op Medal-niveau zijn inbegrepen

ROLLUP vs CUBE

Bron

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

Gebruik ROLLUP bij hiërarchische data (bijv. datumdelen) als je niet alle mogelijke groepsaggregaties wilt
Gebruik CUBE als je alle mogelijke groepsaggregaties wilt

ROLLUP(Year, Quarter)

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

CUBE(Year, Quarter)

Bovenstaande rijen + het volgende

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

Laten we oefenen!

PostgreSQL Samenvattingsstatistieken en vensterfuncties