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

これまでの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句に対するみなさんの理解は十分であることを保証しましょう。

BMIによるカット

健康診断などで、BMIという体重の指標を見たことがあると思います。身長をt（メートル⁠）⁠、体重をw（キログラム）とすると、以下の式で求められます。

• BMI = w / t²

ここで、身長はセンチではなくメートルであることに注意してください。これによって求められた数値に基づいて、日本では18.5未満を「やせている⁠」⁠、18.5以上25未満を標準、25以上を肥満としています。この基準に基づいて、Personsテーブルの人々の体重を分類して、各階級の人数を求めます。ちなみに、各人のBMIは表6のとおり。

すると、カットのイメージは図10のようになります。

まずBMIを計算しましょう。これは「weight / POWER(height / 100, 2)」という式で簡単に求められます。こうして求められたBMIをCASE式で3つの階級に振り分ければ、カットする基準が作れます。あとは、これをGROUP BY句とSELECT句に書けばできあがり（リスト9。実行結果は図11⁠）⁠。

SELECT CASE WHEN weight / POWER(height /100, 2) < 18.5     THEN 'やせている'
            WHEN 18.5 <= weight / POWER(height /100, 2) 
                   AND weight / POWER(height /100, 2) < 25 THEN '標準'
            WHEN 25 <= weight / POWER(height /100, 2)      THEN '肥満'
            ELSE NULL END AS bmi,
       COUNT(*)
  FROM Persons
 GROUP BY CASE WHEN weight / POWER(height /100, 2) < 18.5     THEN 'やせている'
               WHEN 18.5 <= weight / POWER(height /100, 2) 
                      AND weight / POWER(height /100, 2) < 25 THEN '標準'
               WHEN 25 <= weight / POWER(height /100, 2)      THEN '肥満'
               ELSE NULL END;

BMI                COUNT(*)
------------       ----------
やせている            2
標準                  4
肥満                  3

GROUP BY句が「SQLの本領」である、という言葉の意味が、少しわかっていただけたのではないでしょうか。GROUP BY句には列名を書くものだと思い込んでいる人にとっては、こんな複雑な基準によるパーティションカットが可能であると知ることは、一種の感動をもたらします（かつて私もそうでした⁠）⁠。

年齢階級別のパーティションカット

それでは最後に、発展的な話をして章を締めくくりましょう。先ほど私は、注5で「GROUP BYから集約機能を取り去って、カットの機能だけ残したのがPARTITION BYだ」と述べました。実際、その1点を除けば、GROUP BYとPARTITION BYに機能的な差はありません。

ということは、です。PARTITION BY句にも、やはり単純な列名だけでなく、CASE式や計算式を利用した複雑な基準を記述できてもおかしくないはずです。そして事実、それは可能なのです。

たとえば、さっきの年齢階級別のパーティションカットを使いましょう。これをPARTITION BY句に記述して、同一年齢階級内で年齢の上下によって順位をつけるクエリは、リスト10のようになります（実行結果は図12⁠）⁠。

SELECT name,
       age,
       CASE WHEN age < 20 THEN '子供'
            WHEN age BETWEEN 21 AND 69 THEN '成人'
            WHEN age > 70 THEN '老人'
            ELSE NULL END AS age_class,
       RANK() OVER(PARTITION BY  CASE WHEN age < 20 THEN '子供'             
                                      WHEN age BETWEEN 21 AND 69 THEN '成人'
                                      WHEN age > 70 THEN '老人'             
                                      ELSE NULL END                        　→PARTITION BY句に式を指定している
                   ORDER BY age) AS age_rank_in_class
  FROM Persons
 ORDER BY age_class, age_rank_in_class;

name       age    age_class  age_rank_in_class
--------  ----- --------  -----------------
Darwin    12    子供        1
―――――――――――――――――――――――――――――――――――――
Adela     21    成人        1
Dawson    25    成人        2
Anderson  30    成人        3
Donald    30    成人        3
Bill      39    成人        5
Becky     54    成人        6
―――――――――――――――――――――――――――――――――――――
Bates     87    老人        1
Chris     90    老人        2

結果に横棒を引いたのは、パーティション（類）の区切りを明確にするためです。最後尾のage_rank_in_classが各パーティション内部での年齢の順位を示す列です。PARTITION BYはGROUP BYと違って集約を伴わないため、もとのPersonsテーブルの行がすべてそのまま「ヒラ」で出てくることに注目してください。

GROUP BYが式を引数にとれる以上PARTITION BYもまた同様であるということは、論理的には何のためらいもなく得られる結論ではありますが、実際にクエリを目にしてみると「こんなことが可能なのか…」という感慨にとらわれるのではないでしょうか。