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レベルの合計を含む
- いずれも総計を含む

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の行が総計を表す
ROLLUP(Country, Medal)であるため、Medalレベルの合計は含まれない点に注目

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とCUBEの比較

Source

| 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 集計統計とウィンドウ関数