ROLLUP și CUBE

Statistici sumare și funcții de fereastră în PostgreSQL

Michel Semaan

Data Scientist

Totaluri la nivel de grup

Medaliile Chinei și Rusiei la Jocurile Olimpice de Vară 2008, pe clase de medalii

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

Metoda veche

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;

Introducere 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 este o subclauză GROUP BY care adaugă rânduri pentru agregări la nivel de grup
GROUP BY Country, ROLLUP(Medal) numără totalurile la nivel de Country și Medal, apoi numără doar totalurile la nivel de Country și completează Medal cu null pentru aceste rânduri

ROLLUP - Interogare

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 este ierarhic, de-agregând de la coloana cea mai din stânga spre dreapta
- ROLLUP(Country, Medal) include totalurile la nivel de Country
- ROLLUP(Medal, Country) include totalurile la nivel de Medal
- Ambele includ totalul general

ROLLUP - Rezultat

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

Totalurile la nivel de grup conțin null; rândul cu toate null este totalul general
Observați că nu sunt incluse totalurile la nivel de Medal, deoarece este ROLLUP(Country, Medal) și nu ROLLUP(Medal, Country)

Introducere 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 este un ROLLUP non-ierarhic
Generează toate agregările posibile la nivel de grup
- CUBE(Country, Medal) numără totalurile la nivel de Country, Medal și totalul general

CUBE - Rezultat

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

Observați că sunt incluse totalurile la nivel de Medal

ROLLUP vs CUBE

Source

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

Utilizați ROLLUP pentru date ierarhice (ex.: componente de dată) când nu doriți toate agregările posibile
Utilizați CUBE când doriți toate agregările posibile la nivel de grup

ROLLUP(Year, Quarter)

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

CUBE(Year, Quarter)

Rândurile de mai sus + următoarele

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

Să exersăm!

Statistici sumare și funcții de fereastră în PostgreSQL