OLAP 操作：GROUPING SETS

以資料驅動的 SQL 決策制定

Irene Ortner

Data Scientist at Applied Statistics

SQL 中的 OLAP 運算子總覽

用於簡化 OLAP 操作的 SQL 延伸語法

GROUP BY CUBE
GROUP BY ROLLUP
GROUP BY GROUPING SETS

資料表 renting_extended

資料表 renting_extended 的前幾列：

| renting_id | country  | genre  | rating |
|------------|----------|--------|--------|
| 2          | Belgium  | Drama  | 10     |
| 32         | Belgium  | Drama  | 10     |
| 203        | Austria  | Drama  | 6      |
| 292        | Austria  | Comedy | 8      |
| 363        | Belgium  | Drama  | 7      |
| .......... | ........ | ...... | ...... |

GROUP BY GROUPING SETS

使用 GROUPING SETS 的查詢範例：

SELECT country, 
       genre, 
       COUNT(*)
FROM rentings_extended
GROUP BY GROUPING SETS ((country, genre), (country), (genre), ());

以括號包住的欄位代表一個彙總層級。
GROUP BY GROUPING SETS 等同於多個 GROUP BY 查詢的 UNION。

GROUPING SETS 與 GROUP BY 查詢

SELECT country, 
       genre, 
       COUNT(*)
FROM renting_extended
GROUP BY GROUPING SETS (country, genre);

針對每個國家與類型的唯一組合計算租借次數。
GROUPING SETS 中的表達式：(country, genre)

SELECT country, 
       genre, 
       COUNT(*)
FROM renting_extended
GROUP BY country, genre;

| country | genre  | count  |
|---------|--------|--------|
| Austria | Comedy | 2      |
| Belgium | Drama  | 15     |
| Austria | Drama  | 4      |
| Belgium | Comedy | 1      |

GROUPING SETS 與 GROUP BY 查詢

SELECT country, COUNT(*)
FROM renting_extended
GROUP BY GROUPING SETS (country);

針對每個國家計算租借次數。
GROUPING SETS 中的表達式：(country)

SELECT country, COUNT(*)
FROM renting_extended
GROUP BY country;

| country | count |
|---------|-------|
| Austria | 16    |
| Belgium | 6     |

GROUPING SETS 與 GROUP BY 查詢

SELECT genre, COUNT(*)
FROM renting_extended
GROUP BY GROUPING SETS (genre);

針對每個類型計算租借次數。
GROUPING SETS 中的表達式：(genre)

SELECT genre, COUNT(*)
FROM renting_extended
GROUP BY genre;

| country | count |
|---------|-------|
| Comedy  | 3     |
| Drama   | 19    |

GROUPING SETS 與 GROUP BY 查詢

SELECT COUNT(*)
FROM renting_extended
GROUP BY GROUPING SETS ();

總彙總：計算所有租借次數。
GROUPING SETS 中的表達式：()

SELECT COUNT(*)
FROM renting_extended;

| count |
|-------|
| 22    |

GROUP BY GROUPING SETS 的寫法

GROUP BY GROUPING SETS (...)

SELECT country, genre, COUNT(*)
FROM renting_extended
GROUP BY GROUPING SETS ((country, genre), (country), (genre), ());

對前述 4 個查詢做 UNION。
在一個查詢裡整合樞紐分析表的所有資訊。
此查詢等同於 GROUP BY CUBE (country, genre)。

使用 GROUPING SETS 的結果

SELECT country, genre, COUNT(*)
FROM renting_extended
GROUP BY GROUPING SETS ((country, genre), (country), (genre), ());

| country | genre  |count|
|---------|--------|-----|
| NULL    | NULL   | 22  |
| Austria | Comedy | 2   |
| Belgium | Drama  | 15  |
| Austria | Drama  | 4   |
| Belgium | Comedy | 1   |
| Belgium | NULL   | 16  |
| Austria | NULL   | 6   |
| NULL    | Comedy | 3   |
| NULL    | Drama  | 19  |

計算租借數與平均評分

只組合特定彙總：
- country 與 genre
- genre

使用租借次數與平均評分做彙總。

SELECT country, 
     genre, 
     COUNT(*), 
     AVG(rating) AS avg_rating
FROM renting_extended
GROUP BY GROUPING SETS ((country, genre), (genre));

計算租借數與平均評分

SELECT country, genre, COUNT(*), AVG(rating) AS avg_rating
FROM renting_extended
GROUP BY GROUPING SETS ((country, genre), (genre));

| country | genre  | count| avg_rating |
|---------|--------|------|------------|
| Austria | Comedy | 2    | 8.00       |
| Belgium | Drama  | 15   | 9.17       |
| Austria | Drama  | 4    | 6.00       |
| Belgium | Comedy | 1    | NULL       |
| NULL    | Comedy | 3    | 8.00       |
| NULL    | Drama  | 19   | 8.38       |

一起來練習吧！

以資料驅動的 SQL 決策制定