第11回　SQL流集合操作（4）　集合の性質を調べる

集合の性質を調べる

これまでは、主に集合の切り分け方のいろいろなバリエーションを紹介してきました。第2章で見たような入れ子の再帰集合を作る方法から、本章で見たような重なり合わないパーティション単位に切り分ける方法まで、GROUP BY句を使えば思うままに集合を組み替えられます。

ここでは、そうして作られた部分集合について、その性質を調べる方法を取り上げます。私たちが頻繁に条件を記述するWHERE句というのは、言うまでもなく「行」に対する条件を記述する場所です。しかし、「⁠行」ではなく「行の集合」を操作の基本単位とするというSQLの原理に照らして見た場合、当然、集合について条件を設定する機能が用意されていて然るべきです。そのために活躍するのが、今からスポットを当てるHAVING句です。

サンプルに、前節e「あなたは肥り過ぎ？痩せ過ぎ？～カットとパーティション～」で使った年齢階級別に人数を求めるクエリを使いましょう。その基準に従えば、各人は表7のように階級に振り分けられるのでした。

表7　階級の振り分け

name （名前）	age （年齢）	age_class （年齢階級）	Height （身長 cm）	weight （体重 kg）
Anderson	30	成人	188	90
Adela	21	成人	167	55
Bates	87	老人	158	48
Becky	54	成人	187	70
Bill	39	成人	177	120
Chris	90	老人	175	48
Darwin	12	子供	160	55
Dawson	25	成人	182	90
Donald	30	成人	176	53

では、ここからさらに、「⁠その階級の全員のBMIが『標準』か『やせ』のどちらかに分類される」階級だけを選択してください。結果は、「⁠子供」と「老人」の階級だけになります。

まず、「⁠子供」「⁠成人」「⁠老人」の3階級に分割した部分集合を作るまでは、先ほどのクエリと同じです。あとはそこに、「⁠階級」という集合自身の性質を調べる条件を付加するだけです。ここでHAVING句が活躍します（リスト11。実行結果は図13⁠）⁠。

リスト11　集合の性質を調べるためのクエリ

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
  HAVING COUNT(*) = SUM(CASE WHEN weight / POWER(height /100, 2) < 25 THEN 1
                              ELSE 0 END);

図13　リスト11の実行結果


age_class count(*)
--------- ----------
老人         2
子供         1

ここで重要な役割を果たすトリックは、HAVING句の右辺で、CASE式で特性関数（戻り値が0または1の関数）を用いていることです。この特性関数によって、BMIが25未満の人物については「1⁠」⁠、25以上の人物については「0」という、一種のビットフラグを立てています。あとはこのビットフラグを合計した結果が集合の要素数と一致すれば、「⁠全員のビットフラグが1だった」ことが保証されるわけです。

もしもっと直截に「各階級の肥満ではない人物の数」を結果として求めたいなら、リスト12のようにSELECT句でビットフラグの合計を数えてやればよいでしょう（実行結果は図14⁠）⁠。

リスト12　集合の性質を調べるためのクエリ

SELECT CASE WHEN age < 20 THEN '子供'
            WHEN age BETWEEN 21 AND 69 THEN '成人'
            WHEN age > 70 THEN '老人'
            ELSE NULL END AS age_class,
       COUNT(*) AS all_cnt,
       SUM(CASE WHEN weight / POWER(height /100, 2) < 25 THEN 1
                ELSE 0 END) AS not_fat_cnt                       →「⁠肥満」以外の人数を数える式
  FROM Persons
 GROUP BY CASE WHEN age < 20 THEN '子供'
               WHEN age BETWEEN 21 AND 69 THEN '成人'
               WHEN age > 70 THEN '老人'
               ELSE NULL END;

図14　リスト12の実行結果

age_class     all_cnt not_fat_cnt
---------- ---------- -----------
成人                  6           3
老人                  2           2
子供                  1           1

こうすることで、先ほどは条件を満たさないために結果から除外されていた「成人」階級も結果に現れます。こちらのほうが、成人はAnderson、Bill、Dawsonの3人が「肥満」に該当してしまうのだ、という事実も明確に示す結果となっていることがおわかりいただけるでしょう。

おわりに

SQLの集合操作の勘所を学ぶために、GROUP BY（とPARTITION BY）を中心に解説をしてきました。いかがでしょう。少し、SQLの寄って立つ基礎について理解を深められたでしょうか。もう一度、重要なポイントをまとめておきましょう。

SQLの基本的な処理単位は、レコード（行）ではなく「レコードの集合」
GROUP BY句は元のテーブルを小さな部分集合（類）に切り分ける「カット」の機能を持つ
GROUP BY句はさらに、カットした集合単位に行をまとめる「集約」の機能も持つ
GROUP BY句から集約の機能を取り去って、カットだけを残したのがPARTITION BY句
どちらの句も、列名に限らず「式」を引数に取れる柔軟さが強み。これによって、SQLは集合をどんな複雑な基準によってでも切り刻み、料理することが可能となっている

GROUP BYやPARTITION BYは、もちろんそれ単独で見ても、強力で使いでのある武器です。しかし、本章でも見たように、CASE式などのほかの武器と組み合わせたときに、最もその真価を発揮します。そして実は、GROUP BY句と最も相性のよい武器はHAVING句です。最後で少しだけ紹介しましたが、この2つを組み合わせたときの記述力には目を見張るものがあります。これについては、取り上げようとするとかなり紙幅を必要とするので、また機会を改めてお話しすることにしましょう注6。

本章の内容について、さらに踏み込んで学びたい方は、以下の参考文献を参照してください。

『SQLパズル　第2版』 （J.セルコ著、翔泳社、2007）: GROUP BYの応用として興味深いのは、「⁠カット」の機能をうまく利用した「パズル29　最頻値を求める」や入れ子の再帰集合を作って累計を求める「パズル35　在庫調整⁠」⁠。HAVING句については、特性関数を駆使する「パズル11　作業依頼」や集合指向の極致と言うべき芸術的な欠番探索の方法を紹介する「パズル57　欠番探し――バージョン1」など。
『達人に学ぶ　SQL徹底指南書』 （ミック、翔泳社、2008）: 本書ではHAVING句をかなり重点的に取り上げました。「⁠1-4 HAVING句の力」や「1-10 帰ってきたHAVING句」を参照。一方、HAVING句の復権に力を入れるあまり、GROUP BY句についての説明が薄くなってしまったので、今回の特集と併せて読んでいただくと、より一層効果的でしょう。

第11回SQL流集合操作（4）　集合の性質を調べる

集合の性質を調べる

おわりに

連載のおわりに