GROUPINGを理解してSQLマスターになろう

GROUPING演算子

集約キーの「組み合わせ」が異なる結果を計算する

超集合行

集約キー無し、つまりGROUP BY()で集約された行のこと。
超集合行は、集約キーの値が不明なので、デフォルトではNULLが使用される。

GROUPING関数

超集合行では集約列の値がNULLになるが、超集合行のNULLとテーブル上でのNULLを見分ける関数としてGROUPING関数が存在する。
引数に取った列の値が超集合行のため生じたNULLなら1を、それ以外なら0を返す。
CASE式と併用することで、超集合行のNULLを他の値に変換することも可能。

今回の例で用いるテーブル

pop_sex

pref_name	sex	population
東京	1	250
東京	2	250
神奈川	1	200
神奈川	2	100
福岡	1	150
福岡	2	100
埼玉	1	100
埼玉	2	150
長崎	1	20
長崎	2	30
千葉	1	120
千葉	2	130
山形	1	50
山形	2	50

ROLLUP

引数に与えた列ベクトルが V = (colN) だとすると、
以下のN+1個の組み合わせについて集約する。

(), (col1), (col1, col2), ... , (col1, col2, ... , colN)

構文

GROUP BY ROLLUP(<col1, [col2, ...]>)

例

SELECT
    pref_name,
    SUM(population) as sum
FROM
    pop_sex
GROUP BY ROLLUP(pref_name)

これは次の２つの組み合わせについての集約を行っている。

1. GROUP BY()
2. GROUP BY(pref_name)

pref_name	sum
	1700
東京	500
神奈川	300
福岡	250
埼玉	250
長崎	50
千葉	250
山形	100

1番上の行が超集合行であり、集約キーのpref_nameがNULLとなっている。

もしGROUPING関数を使って別の値に変換する場合の例を以下に示す。

SELECT
    CASE WHEN GROUPING(pref_name) = 1 THEN '合計'
    ELSE pref_name
    END AS pref_name,
    SUM(population) as sum
FROM
    pop_sex
GROUP BY ROLLUP(pref_name)

pref_name	sum
合計	1700
東京	500
神奈川	300
福岡	250
埼玉	250
長崎	50
千葉	250
山形	100

CUBE

引数に与えた列ベクトルの全ての組み合わせについて集約する。
集約の組み合わせ数は2^N。

V = (col1, col2, col3)

(), (col1), (col2), (col3), (col1, col2), (col1, col3), (col2, col3), (col1, col2, col3)

構文

GROUP BY CUBE(<column1, [column2, ...]>)

GROUPING SETS

引数に与えた列ベクトルの全てに対して、単独で集約キーとした場合について集約する。
集約の組み合わせ数はN。

V = (col1, col2, col3)

(col1), (col2), (col3)

構文

GROUP BY GROUPING SETS(<column1, [column2, ...]>)