SQLのGROUP BY句の処理を理解する

はじめに

SQLのGROUP BY句の解説記事は多くありますが、本エントリでは他の記事とは解説方法を変え、処理の段階を追って説明します。これにより、これまでGROUP BY句のふるまいにモヤモヤを感じていた方が少しでもスッキリできることを目指します。
また、CASE式との組み合わせにも触れます。

本エントリは『プログラマのためのSQL 第4版』を参考にしています。

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

作者:ジョー・セルコ,Joe Celko
発売日: 2013/05/24
メディア: 大型本

SQLの実行順序

SQLの実行順序は以下の通り決まっています。
FROM→WHERE→GROUP BY→HAVING→SELECT

この後の説明を読み進めるときはこの順番を意識してみてください。

検証環境・データ

PostgreSQL 10.3, Oracle Database 12cR2 で検証を行いました。検証に用いたSQLは他のRDBでもそのまま、もしくは小さい修正で動作すると思われます。

以下のStudents表を利用します。

stud_nbr	stud_name	sex_code	stud_age
1	Smith	1	16
2	Smyth	2	17
3	Smoot	2	16
4	Adams	2	17
5	Jones	1	16
6	Celko	1	17
7	Vennor	2	16
8	Murray	1	18

CREATE TABLE Students
(stud_nbr INTEGER NOT NULL PRIMARY KEY,
 stud_name CHAR(50) NOT NULL,
 sex_code INTEGER NOT NULL
     CHECK (sex_code IN (0, 1, 2)),
 stud_age INTEGER NOT NULL);

INSERT INTO Students VALUES (1, 'Smith', 1, 16);
INSERT INTO Students VALUES (2, 'Smyth', 2, 17);
INSERT INTO Students VALUES (3, 'Smoot', 2, 16);
INSERT INTO Students VALUES (4, 'Adams', 2, 17);
INSERT INTO Students VALUES (5, 'Jones', 1, 16);
INSERT INTO Students VALUES (6, 'Celko', 1, 17);
INSERT INTO Students VALUES (7, 'Vennor', 2, 16);
INSERT INTO Students VALUES (8, 'Murray', 1, 18);

グループ化

GROUP BY句はFROM句とWHERE句の結果を入力にとり、GROUP BY句に記述された列のリスト（集約キー）を基準に同じ値を持つ行を同じグループに振り分けます。その結果テーブルは集約テーブルと呼ばれます。

グループにまとめたことで、そこから得られるものはグループの性質となります。グループに含まれる、ある特定の列の特性（もしくは値）を表すわけではありません。これが、GROUP BY句があるとき、SELECT句に集約キーと集約関数（および定数）だけを含めることができるというルールの理由となります。このことは後の節で詳しく説明します。

GROUP BY句を含むSQLとその実行結果を提示します。

SELECT sex_code, stud_age, COUNT(*)
  FROM Students
 GROUP BY sex_code, stud_age
 ORDER BY sex_code, stud_age;

sex_code	stud_age	count
1	16	2
1	17	1
1	18	1
2	16	2
2	17	2

Students表とこのSQLでGROUP BY句及びSELECT句がどのように処理されたかを確認します。
今回のSQLにはWHERE句がないため、Students表の全行がグループ処理の対象となります。集約テーブルのイメージを示します。

group_key {sex_code, stud_age}	group_values {stud_nbr, stud_name, sex_code, stud_age}
{1, 16}	{1, Smith, 1, 16} {5, Jones, 1, 16}
{1, 17}	{6, Celko, 1,17}
{1, 18}	{8, Murray, 1, 18}
{2, 16}	{3, Smoot, 2, 16} {7, Vennor, 2, 16}
{2, 17}	{2, Smyth, 2, 17} {4, Adams, 2, 17}

集約キーが同じ値である行毎にグループとしてまとまりました。また、この時点ではグループ化前の情報（Students表の各行の値）が保持されています。

SELECT句には集約キーと集約関数を含めることができます。集約キーは集約テーブルのgroup_keyに相当します。集約関数の演算は集約テーブルのgroup_valuesに対して行われます。COUNT(*)でグループ内のデータの個数をカウントし、実行結果を得ることができます。

グループの性質

GROUP BY句に存在しない列名をSELECT句に指定するとエラーになります。

以下のSQLの場合、GROUP BY句に"stud_name"がないのにSELECT句で指定したため、エラーとなります。

SELECT sex_code, stud_age, stud_name
  FROM Students
 GROUP BY sex_code, stud_age
 ORDER BY sex_code, stud_age;

GROUP BY句を指定したということは、そこで指定した集約キーに基づいてグループ化し、そのグループの性質を得たいということを意味しています。"stud_name"はグループ内の情報であり、グループとしての性質（情報）とは異なります。

グループとグループ内の情報が異なることを説明する例として、地域と都道府県を挙げることができます。地域は単数もしくは複数の都道府県を含んだグループです。地域は都道府県を含むものであり、同列とはいえません。このような同列でないものを同時にSELECTすることはできません。

SELECT area, pref  // 地域(area)と都道府県(pref)はレベルが異なる
  FROM Japan
 GROUP BY area;  // 地域でGROUP BYすることで都道府県はグループ化された

ではなぜGROUP BY句で指定した列はSELECT句に指定できるかというと、それがグループの性質の一つだからです。このグループはどんなグループですか？と質問されたとしたら、GROUP BY句で指定した列はそのグループを表す一つの性質として説明できます。

例えば、性別と年齢でグループ化すれば、一つ一つのグループは性別及び年齢が同じ集合となります。つまり、性別や年齢はそのグループの性質の一部といえます。

集約キーの一部だけをSELECTする

集約キーの一部だけをSELECT句に指定した場合を確認します。

SELECT sex_code, COUNT(*)
  FROM Students
 GROUP BY sex_code, stud_age
 ORDER BY sex_code, stud_age;

sex_code	count
1	2
1	1
1	1
2	2
2	2

SELECT句に集約キーの全てを指定した結果から、削除した集約キーの列(stud_age)が省かれた結果となりました。
GROUP BY句の指定が同じなので、集約テーブルの内容も同じです。同じ集約テーブルからSELECT句で異なる列を指定しているだけなので、結果は出力列の違いのみとなります。

グループ化前の情報を参照する

集約テーブルにもグループ化前の情報を保持しています。これが参照可能なことを集約関数とともにCASE式を使ったSQLで確認します。

SELECT stud_age,
       SUM(CASE WHEN sex_code = 1 THEN 1 ELSE 0 END) AS males,
       SUM(CASE WHEN sex_code = 2 THEN 1 ELSE 0 END) AS females
  FROM Students
 GROUP BY stud_age
 ORDER BY stud_age;

まず、グループ処理を行った結果を確認します。

group_key {stud_age}	group_values {stud_nbr, stud_name, sex_code, stud_age}
{16}	{1, Smith, 1, 16} {3, Smoot, 2, 16} {5, Jones, 1, 16} {7, Vennor, 2, 16}
{17}	{2, Smyth, 2, 17} {4, Adams, 2, 17} {6, Celko, 1,17}
{18}	{8, Murray, 1, 18}

SUM集約関数の引数にCASE式が使われており、その条件式ではsex_code列を参照しています。集約テーブルのgroup_valuesにはグループ化前の値が保持されており、その中にはsex_code列もあります。よって、この値を参照して条件の判定を行うことができます。

最終的には以下の結果が得られます。

stud_age	males	females
16	2	2
17	1	2
18	1	0

SELECT句の記述にGROUP BY句で指定してない列(sex_code)が現れたことに違和感があるかもしれません。ですが、今回のSQLのSUM集約関数とCASE式で表しているのは「グループ内のsex_code列の値（性別）に応じたカウントを取得する」ということであり、最終的な結果はグループの性質といえます。よって、GROUP化後のSELECTの結果としては妥当といえます。

まとめ

GROUP BY句はグループの性質を得るために使います。

GROUP BY句を読み解く際は内部的に集約テーブルが作られていることをイメージすることで理解しやすくなります。

集約テーブルはグループ化以前の情報も保持していますが、集約テーブルから得る結果はグループの性質である必要があります。このため、結果を得る過程でグループ化以前の情報を参照することはできますが、最終的にはグループに関する性質とする必要があります。

参考

SQLをより深く、全般的に知るためには『プログラマのためのSQL 第4版』を参照ください。