ROLLUP och CUBE

PostgreSQL – Sammanfattande statistik och fönsterfunktioner

Michel Semaan

Data Scientist

Totaler per grupp

Kinesiska och ryska medaljer i 2008 års sommar-OS per medaljklass

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

Det gamla sättet

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;

Introduktion till 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 är en subklausul till GROUP BY som lägger till rader för aggregeringar på gruppnivå
GROUP BY Country, ROLLUP(Medal) räknar totaler för alla kombinationer av Country och Medal, och sedan enbart totaler på Country-nivå – för dessa rader fylls Medal med null

ROLLUP – fråga

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 är hierarkisk och aggregerar från den vänstraste kolumnen till den högraste
- ROLLUP(Country, Medal) inkluderar totaler på Country-nivå
- ROLLUP(Medal, Country) inkluderar totaler på Medal-nivå
- Båda inkluderar totalsummor

ROLLUP – resultat

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

Totaler på gruppnivå innehåller null-värden; raden med enbart null är totalsumman
Observera att totaler på Medal-nivå inte ingår, eftersom det är ROLLUP(Country, Medal) och inte ROLLUP(Medal, Country)

Introduktion till 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 är en icke-hierarkisk variant av ROLLUP
Den genererar alla möjliga aggregeringar på gruppnivå
- CUBE(Country, Medal) räknar totaler på Country-nivå, Medal-nivå och totalsummor

CUBE – resultat

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

Observera att totaler på Medal-nivå ingår

ROLLUP vs CUBE

Source

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

Använd ROLLUP för hierarkiska data (t.ex. datumdelar) när du inte behöver alla möjliga aggregeringar på gruppnivå
Använd CUBE när du vill ha alla möjliga aggregeringar på gruppnivå

ROLLUP(Year, Quarter)

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

CUBE(Year, Quarter)

Raderna ovan plus följande

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

Nu kör vi en övning!

PostgreSQL – Sammanfattande statistik och fönsterfunktioner