【SQL基礎】GROUPING関数で見分けよう

本項ではGROUPING関数について解説していきます。

超集合行の見分け方

今回は、以下のテーブルを使用してROLLUPの動きを見てみましょう。

このテーブルに対して、GROUP BYで商品と売上日の二つを指定してROLLUPを使用します。

SELECT name, date, sum(price)
FROM sales 
GROUP BY name, date WITH ROLLUP;

集約対象のデータにNULLがなければ超集合行とそれ以外の行を見分けることは容易ですが
今回のように集約対象のデータにNULLが含まれていた場合、どれが超集合行を表すNULLか見分けがつかなくなることがあります。
この問題を解決するために、SQLでは超集合行を表すNULLか判定するGROUPING関数が用意されています。

実際に使ってみましょう。

SELECT name, date, sum(price) , GROUPING(name) AS grp_name, GROUPING(date) AS grp_date
FROM sales GROUP BY name, date
WITH ROLLUP;

このようにGROUPING関数で指定した列のNULLの値が超集合行のNULLだった場合は1を、それ以外の場合には0を返します。
今回の場合、2行あるミカン行の内、2行目の列が超集合行であることがわかりますね。
また、GROUPING関数と別の関数を組み合わせて利用することで超集合行のNULLを別の表記にすることが可能です。
例えばIF関数と組み合わせると以下のようになります。

SELECT IF(GROUPING(name), '合計', name) AS name, IF(GROUPING(date), '小計', date) AS date, sum(price)
FROM sales GROUP BY name, date
WITH ROLLUP;

先ほどは、GROUPING関数には1つの列だけ引数に指定していましたが複数列を指定することも可能です。
この場合の戻り値は、1と0ではなく、GROUPING関数にそれぞれの列を一つだけ指定した場合の結果を結合したビットマスクになります。

SELECT name, date, sum(price) , GROUPING(name, date) AS grp
FROM sales
GROUP BY name, date WITH ROLLUP;

このように、date列のみが1であった場合は、「01」となり1が、nameとdateが両方ともに1の場合は、「11」となり3が
両方ともに0の超集合行以外の行では0が表示されています。

今回の学習ポイント

・GROUPING関数で超集合行かどうか見分けることができる
・GROUPING関数は引数に複数列を渡すことができる

練習問題

問題として使用するデータは「SQL練習問題」のものを使用しています。
以下からダウンロード可能です。
また、ダウンロードしたら以下のようにしてworldcup2014データベースを作成し、インポートしてください。
※worldcup2014.zip解凍してできるworldcup2014.sqlファイルをCドライブ直下に配置した場合。

worldcup2014.zip

[sql] CREATE DATABASE worldcup2014;

use worldcup2014

source C:\worldcup2014.sql
[/sql]

１．ROLLUPを使用して、ゴール情報テーブル(goals_tmp)のデータで、各選手(playser_name)の対戦国(enemy_country)ごとの得点数と全試合の合計得点数を表示してください。
（※ワールドカップ全体の合計得点は表示させない）

答え

[sql] SELECT player_name, enemy_country, count(*)
FROM goals_tmp
GROUP BY player_name, enemy_country WITH ROLLUP
HAVING GROUPING(player_name, enemy_country) <> 3;

— もしくは

SELECT player_name, enemy_country, count(*)
FROM goals_tmp
GROUP BY player_name, enemy_country WITH ROLLUP
HAVING GROUPING(player_name) <> 1;
[/sql]