SQLの GROUP BY 句には列名だけでなく式も記述することができる

はじめに

「達人に学ぶSQL徹底指南書」（以下、達人SQL）の「1-1 CASE式のススメ」には GROUP BY 句に CASE 式の含まれるSQLが登場します。

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

一般的に GROUP BY 句には列名を指定すると説明されており、このSQLは乖離しているように思えます。なぜこれが実現できるのかを説明します。

解説には「プログラマのためのSQL 第4版」（以下、プログラマSQL）も参照します。

プログラマのためのSQL 第4版

検証環境・データ

PostgreSQL 10.3, Oracle Database 12cR2 で検証を行いました。
達人SQLを参考に検証用のテーブルおよびデータを作成します。

pref_name （県名）	population （人口）
徳島	100
香川	200
愛媛	150
高知	200
福岡	300
佐賀	100
長崎	200
東京	400
群馬	50

CREATE TABLE PopTbl
(pref_name CHAR(10) NOT NULL PRIMARY KEY,
 population INTEGER NOT NULL);

INSERT INTO PopTbl VALUES ('徳島', 100);
INSERT INTO PopTbl VALUES ('香川', 200);
INSERT INTO PopTbl VALUES ('愛媛', 150);
INSERT INTO PopTbl VALUES ('高知', 200);
INSERT INTO PopTbl VALUES ('福岡', 300);
INSERT INTO PopTbl VALUES ('佐賀', 100);
INSERT INTO PopTbl VALUES ('長崎', 200);
INSERT INTO PopTbl VALUES ('東京', 400);
INSERT INTO PopTbl VALUES ('群馬', 50);

解説

達人SQLに記載のSQLとその実行結果を提示します。

SELECT CASE pref_name
         WHEN '徳島' THEN '四国'
         WHEN '香川' THEN '四国'
         WHEN '愛媛' THEN '四国'
         WHEN '高知' THEN '四国'
         WHEN '福岡' THEN '九州'
         WHEN '佐賀' THEN '九州'
         WHEN '長崎' THEN '九州'
       ELSE 'その他' END AS district,
       SUM(population)
  FROM PopTbl
 GROUP BY CASE pref_name
         WHEN '徳島' THEN '四国'
         WHEN '香川' THEN '四国'
         WHEN '愛媛' THEN '四国'
         WHEN '高知' THEN '四国'
         WHEN '福岡' THEN '九州'
         WHEN '佐賀' THEN '九州'
         WHEN '長崎' THEN '九州'
       ELSE 'その他' END;

district	sum
四国	650
九州	600
その他	450

このSQLのように GROUP BY 句に CASE 式を含めてもエラーにならない理由を直感的に理解するには、PopTbl テーブルに CASE 式の結果が district 列として追加され、その district 列でグループ化したとイメージするとわかりやすいです。

PopTbl テーブルに district 列を追加したイメージを示します。

pref_name （県名）	population （人口）	district （地区）
徳島	100	四国
香川	200	四国
愛媛	150	四国
高知	200	四国
福岡	300	九州
佐賀	100	九州
長崎	200	九州
東京	400	その他
群馬	50	その他

達人SQL「1-1 CASE式のススメ」のまとめでは、CASE式が 1 + 1 や a / b と同じ式の仲間であるとしたうえで、以下の説明をしています。

式であるがゆえに、CASE 式は実行時には評価されて一つの値に定まりますし（だから集約関数の中に書ける）、式だから、SELECT 句にも GROUP BY 句にも WHERE 句にも ORDER BY 句にも書くことができます。ひらたく言って、CASE 式は列名や定数を書ける場所には常に書くことができます。

また、プログラマSQL「28.4 計算列によるグルーピング」には以下の記述があります。

SQL-99では計算列によるグループ化が可能になった。

つまり、CASE 式が式として扱われ、それを仮想的な計算列（列の値は式の結果）とみなし、計算列を集約キーとしてその値でグループ化したと考えることができます。また、CASE 式（の計算列）は集約キーであるため、SELECT 句に含めることができます。

GROUP BY 句に列名ではなく式を記述できることを以下の例で確認できます。

（例1）

SELECT MOD(population, 100), COUNT(*)
  FROM PopTbl
 GROUP BY MOD(population, 100);

mod	count
0	7
50	2

（例2）

SELECT COUNT(pref_name), SUM(population)
  FROM PopTbl
 GROUP BY 5-0;

count	sum
9	1700

例2のSQLには GROUP BY 句に列名すら表れていませんが、実行可能です。"5"という値でPopTblをグループ化、つまりPopTbl全体をグループとし、そのグループを集約関数で処理しています。

まとめ

多くの記事や本の説明では GROUP BY 句に列名を指定できるとしていますが、実際には式も記述することができます。

CASE式が式の仲間である、との説明は達人SQLのまとめに「細かい話」として記載されています。ですが、本来は章の冒頭にもってきてもよいぐらい重要なポイントといえます。