あなたは肥り過ぎ？ 痩せ過ぎ？ ～カットとパーティション～

これまでの2問では，GROUP BYの「集約」という側面を強調してその機能を調べてきました。ですが，冒頭でも少し触れたようにGROUP BYには，集約以外にも，もう1つ重要な機能があります。それが，「カット」という機能です。これは要するに，母集合である元のテーブルを小さな部分集合に切り分けることです。だからGROUP BYというのは，実はこれ1つの中に，

1. カット
2. 集約

という2つの操作が組み込まれた演算なのです注5。1つの句の中に2つの演算が組み込まれているというのもGROUP BYに対する理解を阻む一因になっているのですが，まあそれはいま言っても始まりません。今度は，この「カット」の機能に焦点を当ててみましょう。サンプルに，表5のような個人の身長などの情報を保持するテーブルを使います。

あなたは，上司からこのテーブルを使って簡単な集計作業を依頼されたとします。まずは小手調べ。名簿のインデックスを作るために，名前の頭文字のアルファベットごとに何人がテーブルに存在するかを集計しましょう。

これはつまり，Persons集合を図6のようなS1～S4の部分集合に切り分けて，それぞれの要素数を調べる，ということです。

集合の要素数を調べる関数は，もちろんCOUNT。あとは，頭文字をGROUP BYのキーに指定すれば，カット完了です。SQLはリスト7です。

SELECT SUBSTRING(name, 1, 1) AS label,
         COUNT(*)
  FROM Persons
 GROUP BY SUBSTRING(name, 1, 1);

label       COUNT(*)
------      ---------
A           2
B           3
C           1
D           3

こういうGROUP BY句でカットして作られた1つひとつの部分集合は，数学的には「類（partition）」と呼ばれます。同じ母集合からでも，類の作り方は切り分け方によってさまざまあります。たとえば，年齢によって，子供（20歳未満），成人（20～69歳），老人（70歳以上）に分けるなら，図8のようにカットされます。

当然，GROUP BYのキーもこの3つの区分に対応する形になります。これは，CASE式を使ってリスト8のように表現します（実行結果は図9）。

SELECT CASE WHEN age < 20 THEN '子供'
            WHEN age BETWEEN 21 AND 69 THEN '成人'
            WHEN age > 70 THEN '老人'
            ELSE NULL END AS age_class,
       COUNT(*)
  FROM Persons
 GROUP BY CASE WHEN age < 20 THEN '子供'
               WHEN age BETWEEN 21 AND 69 THEN '成人'
               WHEN age > 70 THEN '老人'
               ELSE NULL END;

age_class      COUNT(*)
----------     ---------
子供              1
成人              6
老人              2

カットしたい区分を，GROUP BY句とSELECT句の両方に書いてやるのがポイントです。PostgreSQLとMySQLでは，SELECT句で付けた「age_class」という別名を使って，「GROUP BY age_class」という簡潔な書き方も許しているのですが，標準違反なので勧めません。

さて，それでは最後の問題。もっと複雑な基準でPersons集合をカットしてみましょう。これが解けたら，GROUP BY句に対するみなさんの理解は十分であることを保証しましょう。

注5）

GROUP BYから集約の機能を取り去って，カットの機能だけを残したのがPARTITION BYです。この2つの句を比較すると，興味深いことが見えてきます。『指南書』の「2-5 GROUP BYとPARTITION BY」を参照。