ROLLUP dan CUBE

Ringkasan Statistik dan Window Functions di PostgreSQL

Michel Semaan

Data Scientist

Total tingkat grup

Medali Tiongkok dan Rusia di Olimpiade Musim Panas 2008 per kelas medali

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

Cara lama

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;

Memahami 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 adalah subklausa GROUP BY yang menambah baris ekstra untuk agregasi tingkat grup
GROUP BY Country, ROLLUP(Medal) akan menghitung semua total tingkat Country dan Medal, lalu hanya total tingkat Country dan mengisi Medal dengan null untuk baris-baris ini

ROLLUP - Kueri

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 bersifat hierarkis, memecah agregasi dari kolom paling kiri ke paling kanan
- ROLLUP(Country, Medal) menyertakan total tingkat Country
- ROLLUP(Medal, Country) menyertakan total tingkat Medal
- Keduanya menyertakan total keseluruhan

ROLLUP - Hasil

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

Total tingkat grup berisi null; baris dengan semua null adalah total keseluruhan
Perhatikan tidak ada total tingkat Medal, karena ini ROLLUP(Country, Medal), bukan ROLLUP(Medal, Country)

Memahami 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 adalah ROLLUP non-hierarkis
Menghasilkan semua agregasi tingkat grup yang mungkin
- CUBE(Country, Medal) menghitung total tingkat Country, tingkat Medal, dan total keseluruhan

CUBE - Hasil

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

Perhatikan bahwa total tingkat Medal disertakan

ROLLUP vs CUBE

Sumber

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

Gunakan ROLLUP untuk data hierarkis (mis. bagian tanggal) dan saat tidak perlu semua agregasi tingkat grup
Gunakan CUBE saat Anda ingin semua agregasi tingkat grup

ROLLUP(Year, Quarter)

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

CUBE(Year, Quarter)

Baris di atas + berikut ini

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

Ayo berlatih!

Ringkasan Statistik dan Window Functions di PostgreSQL