SQL で考える ―― HAVING句の力

(DBAzine (2005/04/20), "Thinking in SQL")

手続き VS 集合

　手続き型言語を長年使ってきた正統派のプログラマが SQL でプログラムを組む際、一番の足かせになるのが、まさにその手続き型の発想であることは、今さら繰り返すまでもありません。SQL の中では手続きを一切書くことができないため、手続き型の発想でコーディングすることがそもそも不向きだからです^[1]。無理にやろうとすると、読むに堪えない長大で複雑な SQL になるか、安易にプロシージャとカーソルに手を出して、慣れ親しんだ手続き型の世界へ逃げ込むことになります。
　かといって、プログラミングにずぶの素人が SQL の学習に有利な地点にいるかといえば、そう単純な話でもありません。SQL が原理とする集合指向という考え方は、やっぱり、普通に高校ぐらいまでで習う数学の中でもあまり重点が置かれていないからです。関数や証明については高度なことまで習いますが、集合の概念についてはベン図書いて終わり、という場合が多いでしょう。かつて、集合論を重視するニュー・マスなんていう教育運動もありましたが、既に廃れて久しいものです（それにニュー・マスも弊害の多い運動でした）。
　要するに、SQL に習熟するには SQL 独自の原理を理解しなければいけない、ということはみんな漠然と自覚しているのですが、具体的にどんな学習カリキュラムが有効なのかはよく分からない、というのが現状です。まだ歴史の浅い言語ということもあって、日本語の良いテキストもありません。私自身も手探り状態で、確固たる方法論はまだありません。
　それでも、私が知っているだけでも、SQL と集合論を結ぶ道は幾つかあります。そのうちの有力な一つが、今回のテーマである HAVING句です。きっと皆さん、HAVING句を使うことはあまりないのではないでしょうか。SELECT、FROM ･･････ ORDER BY の中で一番使用頻度の低い「何だかおまけみたいな句」というのが、HAVING句についての一般的なイメージだと思います。ところが、これがどうして意外に重要な役割を果たすのです。

HAVING句の力

　それでは早速 HAVING句の使い方を解説していきます。以下のような、全ての列が数値型のテーブルがあるとします。

**Foobar**
col1	col2	col3	col4

1	1	1	0
1	1	1	0
1	1	1	0
1	1	2	1
1	1	2	0
1	1	2	0
1	1	3	0
1	1	3	0
1	1	3	0

　何に使うテーブルかさっぱり分かりませんし、主キーもないという困り者ですが、課題の方も輪をかけて奇怪です。いわく

　　　　　col1、col2、col3 の三列でグループ化したときに、col4 がすべて 0 であるような行を一意に取得せよ。

　要するにこの場合だと、下のような2行が欲しい結果となります。

　　　　　(1, 1, 1, 0)
　　　　　(1, 1, 3, 0)

　（1, 1, 2）でグループ化した場合には、col4 が 1 の行が含まれるので対象外となる点に注意してください。この課題に対して、皆さんならどう答えますか？手続き型の発想に従えば、要件を次のように「手続き」に分解して考えることになるでしょう。

まずは col1、col2、col3 でグループ化しよう
次に、col4 の合計が 0 か否かを一行ごとに判定しよう

　うん、グループ化した結果をインライン・ビューで持てばいけそうだ。こうして出来上がるのが、次のようなクエリです。

　　--解答１：手続き型の発想で考えた場合
　　SELECT *
　　　FROM (SELECT col1, col2, col3, SUM(col4)
　　　　　　　　FROM Foobar
　　　　　　　　GROUP BY col1, col2, col3) AS F1
　　 WHERE F1.col4 = 0;

　おお、これはこれで悪くないんじゃないの･･････と思ったのもつかの間、セルコからすぐにダメ出しが飛びます。「まず、この SQL は一つ不注意な暗黙の前提を置いていることが問題だ！つまり、col4 には符号の違う数値が入らないという前提だ。この前提がまずい理由は、次の2行をテーブルに追加してみればすぐに分かる。」

　　　　　(4, 5, 6, 1)
　　　　　(4, 5, 6, -1)

　お分かりでしょう。1 + (-1) は 0。ということで、（4, 5, 6, 0）も結果に紛れ込んでしまうのです。「そしてこの SQL がもっとダメな点。それこそが、手続き型の発想で作られていることだ！これは見た目は SQL に見えるが実は全然 SQL 的じゃない！」いやもう言いたい放題です。
　それでは、ここからどういう風に改善するのか？そこで HAVING句が登場することになります。実は、上の SQL はサブクエリを使う必要などないのです。次のように書きます。

　　--解答２：HAVING句で書き換えた場合
　　SELECT col1, col2, col3, 0 AS col4zero
　　　FROM Foobar
　　GROUP BY col1, col2, col3
　　HAVING SUM(col4) = 0;

　一歩前進！なぜなら、HAVING句は全体のグループ化が終わる前に実行可能なため、インライン・ビューを作るより効率的に実行されるからです。これで少し正解に近づきましたが、まだ例の間違った前提が入り込んだままです。そこで登場するのが、これも集合指向言語の強力な武器である CASE式です。手続き型言語の IF 文や CASE 文は、全部 CASE 式で書き換えることができます。今回は、col4 が 0 か否かを判別することがポイントですから、次のように書きます。

　　--解答２：HAVING句で CASE式を使う。これが本当の正解
　　SELECT col1, col2, col3, 0 AS col4zero
　　　FROM Foobar
　　GROUP BY col1, col2, col3
　　HAVING COUNT(*) = SUM(CASE WHEN col4 = 0
　　　　　　　　　　　　　　　　　　　　　　 THEN 1 ELESE 0 END);

　この HAVING句の SUM 関数は、col4 が 0 の行数を数えています。それがグループ全体の行数と一致したなら、そのグループ内の col4 はすべて 0 であることが保証されるという仕掛けです。集約関数の中で CASE式を使うトリックは、集合指向的な発想のもう一つの重要なキーです^[2]。
　ここまで来て、どうやら要求を満たすクエリが出来上がりました。ここからは、さらに発展的な改作を試みます。集約関数の中で CASE式を利用するトリックは、便利な反面、オプティマイザがあまり良い最適化を行なわないことがあります。そこで、HAVING句の比較を CASE式を使わずに書き換えることを考えて作られたのが、次の解答です。

　　--解答３：HAVING句で極値関数を使う。これも正解。しかも速度向上。
　　SELECT *
　　　FROM Foobar
　　GROUP BY col1, col2, col3
　　HAVING MIN(col4) = MAX(col4)　　　 -- col4 がグループ内で一つの値しか持たないこと
　　　　 AND MIN(col4) = 0;　　　　　　　　-- col4 の最小値が 0 であること

　お分かりでしょうか？ col4 の最大値と最小値が等しいということは、グループ内に値が一つしか存在しない証拠です。それに最小値が 0 （最大値が 0 でもいいのですが）という条件をあわせてやれば、「グループ内の col4 の値はすべて 0 である」と同値な条件が作れるわけです。COUNT(*) や MAX、MIN などの極値関数を使うことの利点は、オプティマイザがインデックスや統計テーブルを利用した高速スキャンを行なってくれることです。
　さて、次に考える拡張は、要求仕様に明言されていない NULL の扱いをどうするかです。もし col4 に NULL が含まれていた場合、それを考慮するか、それとも無視するか？サブクエリを使う解答１の場合、SUM 関数が NULL を無視して集約するので、col4 が NULL の行はテーブルに存在しないのと同じ扱いを受けます。CASE式を使う解答２の場合、NULL は ELSE句にまとめられて 0 に変換されるので、COUNT (*) と SUM 関数は不一致となり、NULL を含むグループは結果から除外されます。つまり、NULL をあたかも一つの値であるかのように扱います。
　解答３の場合、MAX と MIN は NULL を除外して集計するため、解答１の場合と同様、NULL はテーブルに存在しないのと同じ扱いを受けます。ですが、もし col4 に NULL を含むグループを結果から除外したいなら、次のように条件を追加することで実現できます。

　　--解答３改：HAVING句で極値関数を使う。NULL 除外条件を追加。
　　SELECT col1, col2, col3
　　　FROM Foobar
　　GROUP BY col1, col2, col3
　　HAVING MIN(col4) = MAX(col4)　　　　　　　　-- col4 がグループ内で一つの値しか持たないこと
　　　　 AND MIN(col4) = 0;　　　　　　　　　　　　 -- col4 の最小値が 0 であること
　　　　 AND COUNT(*) = COUNT(col4);　　　-- グループ内に NULL を含まないこと

　この最後の条件は、COUNT(*) が NULL を数えるのに対して COUNT(列名) が NULL を数えないという COUNT関数の特性を利用しています^[3]。もし col4 に NULL を含むグループが存在したなら、そのグループについては COUNT(*) > COUNT(col4) になるのです。

まとめ

　本稿では、HAVING句の使い方を例にとって、集合指向的な発想による SQL の作り方を解説してきました。重要なポイントをまとめるなら、次のようになります。

HAVING句は集約されたグループ全体に対して条件を設定できるため、手続きのステップを考える必要がない。それゆえ SQL もシンプルになる。
CASE式や集約関数、極値関数を利用することで柔軟な条件設定が可能になり、拡張性の高い SQL を作成できる。
しかも、COUNT(*) や MAX、MIN はオプティマイザによる最適化を受けやすい。

　どうでしょう。今まで脇役だと思っていた HAVING句が、実は意外な実力者だったことが、少し実感できたでしょうか。それでは、ちょっと一息ついたら、パート２である「集合で考える」へ進みましょう。HAVING句の真価がここで紹介したものに尽きると思ったら、大間違いなのですよ。ふっふっふ。

註
[1] 非手続き型言語としての SQL の特徴については、「そんなあなたが大好きよ（SQL礼賛）」を参照。

[2] CASE式の応用方法の詳細は、「CASE式のススメ」を参照。

[3] この COUNT関数の特性を利用したトリックについては、「SQL For Practitioners」の「COUNT(*) は NULL を数える　その１：HVING句での応用」も参照。

戻る