ROLLUP i CUBE

PostgreSQL – statystyki podsumowujące i funkcje okna

Michel Semaan

Data Scientist

Sumy na poziomie grup

Medale Chin i Rosji na Letnich Igrzyskach Olimpijskich 2008 według rodzaju medalu

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

Stare podejście

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;

Wprowadzenie do 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 to podklauzula GROUP BY dodająca wiersze z agregacjami grupowymi
GROUP BY Country, ROLLUP(Medal) zlicza sumy dla Country i Medal, a następnie tylko sumy dla Country, wypełniając Medal wartościami null

ROLLUP – zapytanie

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 jest hierarchiczny – dezagreguje od lewej do prawej kolumny
- ROLLUP(Country, Medal) uwzględnia sumy na poziomie Country
- ROLLUP(Medal, Country) uwzględnia sumy na poziomie Medal
- Oba uwzględniają grand total

ROLLUP – wynik

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

Sumy grupowe zawierają null; wiersz z samymi null to grand total
Wynik nie zawiera sum dla Medal, ponieważ użyto ROLLUP(Country, Medal), a nie ROLLUP(Medal, Country)

Wprowadzenie do 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 to niehierarchiczny odpowiednik ROLLUP
Generuje wszystkie możliwe agregacje na poziomie grup
- CUBE(Country, Medal) zlicza sumy dla Country, Medal i grand total

CUBE – wynik

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

Wynik zawiera sumy na poziomie Medal

ROLLUP kontra CUBE

Source

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

Użyj ROLLUP dla danych hierarchicznych (np. składowe daty), gdy nie są potrzebne wszystkie agregacje
Użyj CUBE, gdy potrzebne są wszystkie możliwe agregacje grupowe

ROLLUP(Year, Quarter)

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

CUBE(Year, Quarter)

Powyższe wiersze + następujące

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

Czas na ćwiczenia!

PostgreSQL – statystyki podsumowujące i funkcje okna