SQLのGROUP BYは集約ではなく属性の値によって切り分けるという機能を持つ

数ヶ月前に『達人に学ぶSQL徹底指南書第2版初級者で終わりたくないあなたへ』を読みました。GROUP BY について、業務でGROUP BYに関連した不具合があったり、切り分けるという考えの方がイメージしやすかったので、ちょっと触れてみたいと思います。

GROUP BY は「行」から「行の集合」へ階層を上げる
GROUP BY を使用したサンプル
- 集約後の最大値を比較に使用する
- 全ての属性を指定すると元のテーブルに一致する
参照

GROUP BY は「行」から「行の集合」へ階層を上げる

GROUP BYの機能ですが、多くの人がおそらく「指定したカラムの値ごとに集約する」という意味で捉えてると思います。間違ってはないのですが、個人的には「集約する」というよりは「集合を指定した属性(カラム)の値ごとの集合に切り分ける」という「切り分ける」という要素のでイメージした方がより理解しやすいと思います。

本書では、以下のポイントが述べられています。

GROUP BY は、個々の要素を集合的に振り分ける機能を持つのでした。
GROUP BY で集約を行うことによって、SQLが扱う対象は、「行」という0階の存在から、「行の集合」という1階の存在へと変化します。その時点で、行の属性は全て参照不可となります。SQLの世界は、かくも厳しい階層が分け隔てられた階級社会なのです。
達人に学ぶSQL徹底指南書第2版 p318

これは本書の図の通りで、個人が持っている「名前」「年齢」「身長」などの属性でグループ化すると、グループされた集合はそれぞれが平均や合計という統計的属性（行の集合としての情報）しか意味を持たないということです。

例えば、アンケートでも大多数の母集団に対して、複数の項目のアンケートをとった場合に、質問の項目ごとに分類しますよね。アンケートによって得られた1次情報を分類して、2次情報にするわけです。
これと似てて、SQLにおけるGROUP BYはここの要素に対して、属性の値によって集約、つまりグループで切り分けて、行の集合という1階の存在にしているということになります。

この階層の区別は条件句にもそのまま関係します。

この階層の区別は、そのままWHERE句とHAVING句の区別に対応します。WHERE句が行という0階の存在を相手にするのに対し、HAVING句は行の集合という1階の存在を相手にします。

WHERE句=行に対する条件指定
HAVING句=グループに対する条件指定
達人に学ぶSQL徹底指南書第2版 p320
SQL 第2版ゼロからはじめるデータベース操作 p106

ちなみに、集約関数を使うときは、SELECT列に書くことができるのは以下の3つのみです。

常数
集約関数
GROUP BY 句で指定した列名（集約キー）

GROUP BY を使用したサンプル

こういうテーブルで考えてみます。

WITH Teams (member, team, age) AS (
SELECT '小林', 'A', 20 FROM dual UNION ALL
SELECT '佐藤', 'A', 25 FROM dual UNION ALL
SELECT '伊藤', 'A', 28 FROM dual UNION ALL
SELECT '渡辺', 'B', 22 FROM dual UNION ALL
SELECT '田中', 'B', 30 FROM dual UNION ALL
SELECT '加藤', 'B', 35 FROM dual UNION ALL
SELECT '山本', 'C', 33 FROM dual UNION ALL
SELECT '鈴木', 'C', 35 FROM dual UNION ALL
SELECT '中村', 'D', 30 FROM dual UNION ALL
SELECT '高橋', 'D', 40 FROM dual
)

本書に載っていたのは以下のようなSQLです。Oracle では「MAX(T1.age)」を認識してくれなかったので、（理論上は、GROUP BY の後にSELECTが実行されるので、当然集約後の集合がサブクエリ内で実行されるはずで、集約関数MAXが利用できるはずなんですが、、、）さらにサブクエリでチームのMAX年齢を算出しています。

SELECT
     T1.team AS チーム名
    ,MAX(T1.age) AS 年齢
    ,(SELECT MAX(member)
       FROM Teams T2
       WHERE T2.team = T1.team
         -- AND T2.age = MAX(T1.age)
         AND T2.age = (SELECT MAX(T3.age) FROM Teams T3 WHERE T3.team = T1.team)
      ) AS 最年長者
FROM Teams T1
GROUP BY T1.team;

ちなみに、サブクエリのMAX(member) のようにGROUP BYで切り分けた集合に対してレコードが一意に定まるなら、MAX関数で取得するということはよく行います。（集約関数を用いれば、列として取得が可能になるので。）

集約後の最大値を比較に使用する

サブクエリの中にサブクエリは…とちょっと考えてみたのが以下です。考え方としては、MAX年齢の集合を作って、元の集合と INNER JOINすると必要なレコードだけになります。

SELECT *
FROM (
    SELECT team, MAX(age) AS max_ag
    FROM Teams
    GROUP BY team
) T1
JOIN Teams T2
   on T1.team = T2.team
  AND T1.max_age= T2.age
ORDER BY T1.team;

あとは、PARTITION BY を使った方法や、WHEREを使った方法ですね。

-- PARTITION BY を使った方法
SELECT 
    team AS チーム名
    MAX(age) AS 年齢,
    MAX(member) KEEP (DENSE_RANK LAST ORDER BY age) AS 最年長者
FROM Teams
GROUP BY team
ORDER BY team;

-- WHEREを使った方法
SELECT 
    team AS チーム名,
    MAX(age) AS 年齢,
    MAX(member) AS 最年長
FROM Teams
WHERE age = (SELECT MAX(age) FROM Teams T2 WHERE T2.team = Teams.team)
GROUP BY team
ORDER BY team;

全ての属性を指定すると元のテーブルに一致する

ちなみに、「切り分ける」という意味でしっくりくるのが、GROUP BY に全ての属性を指定した場合です。
以下のSQLの場合、順番にteamのA, B, C, Dでテーブルを切り分けて、さらにその中でageの年齢に応じて、さらにmemberで切り分けて、といった感じで最終的に元の個々のレコードと一致します。

-- 全ての属性を追加すると元のテーブルと一致する
SELECT *
FROM Teams
GROUP BY team, age, member
ORDER BY team, age;

だから、あえて、列として取得するためにGROUP BYに加えているSQLも見たことがあります。当然、GROUP BY自体は切り分けるという機能なので、切り分ける前にレコードが1行に特定されていないと結果が変わる（行数が増えるわけですが。）

-- 列に member を取得するために、あえてGROUP BYに追加する。
WITH Teams (member, team, age) AS (
SELECT '小林', 'A', 20 FROM dual UNION ALL
SELECT '渡辺', 'B', 22 FROM dual UNION ALL
SELECT '鈴木', 'C', 35 FROM dual UNION ALL
SELECT '中村', 'D', 30 FROM dual
) SELECT team, member FROM Teams GROUP BY team, member ORDER BY team;

以下のように、teamで集約した場合にその属性内にレコードが2つ以上あって、指定した属性（この場合 member）の値が一意に絞り込まれない場合、当然レコード数が増えます。（業務上でGROUP BY に不要な属性が追加されて、合計行が2重になるという不具合がありました。）

-- team で切り分けた場合よりも、レコード数が増える
WITH Teams (member, team, age) AS (
SELECT '小林', 'A', 20 FROM dual UNION ALL
SELECT '渡辺', 'B', 22 FROM dual UNION ALL
SELECT '鈴木', 'C', 35 FROM dual UNION ALL
SELECT '中村', 'D', 30 FROM dual UNION ALL
SELECT '高橋', 'D', 40 FROM dual
) SELECT team, member FROM Teams GROUP BY team, member ORDER BY team;