ROLLUP 與 CUBE

PostgreSQL 統計摘要與視窗函式

Michel Semaan

Data Scientist

群組層級總計

2008 年夏季奧運：中俄各獎牌等級的獎牌數

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

傳統作法

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;

引入 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 是 GROUP BY 的子句，會加入群組層級彙總的額外列
GROUP BY Country, ROLLUP(Medal) 會先計算 Country 與 Medal 層級，再只計算 Country 層級，並在這些列的 Medal 填入 null

ROLLUP 查詢

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 具階層性，會由最左欄到最右欄逐步取消明細
- ROLLUP(Country, Medal) 會包含 Country 層級總計
- ROLLUP(Medal, Country) 會包含 Medal 層級總計
- 兩者都會包含總計（grand total）

ROLLUP 結果

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

群組層級總計會含有 null；全為 null 的那列是總計
注意未包含 Medal 層級總計，因為用的是 ROLLUP(Country, Medal)，不是 ROLLUP(Medal, Country)

引入 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 是非階層版的 ROLLUP
會產生所有可能的群組層級彙總
- CUBE(Country, Medal) 會計算 Country、Medal 層級與總計

CUBE 結果

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

注意這裡包含了 Medal 層級總計

ROLLUP vs CUBE

來源

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

當資料具階層（例如日期拆解）且不需所有組合時，用 ROLLUP
當你需要所有可能的群組層級彙總時，用 CUBE

ROLLUP(Year, Quarter)

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

CUBE(Year, Quarter)

以上各列加上以下內容

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

一起來練習吧！

PostgreSQL 統計摘要與視窗函式