NULLを含まない集合を探す
COUNT関数の使用法には、COUNT(*)とCOUNT(列名)の2通りがあります。両者の違いは2つあります。ひとつはパフォーマンスの違い、もうひとつは、COUNT(*)がNULLを数えるのに対し、COUNT(列名)は他の集約関数と同様、NULLを除外して集計するという結果の違いです。後者を言い換えると、COUNT(*)が全行を数えるのに対し、COUNT(列名)はそうではない、ということです。
両者の結果の違いは、NULLしか含まない極端なテーブルにSQL文を実行してみると明らかになります。
| 列1（col_1） |
--NULLを含む列に適用した場合、COUNT(*)とCOUNT(列名)の結果は異なる SELECT COUNT(*), COUNT(col_1) FROM NullTbl;
count(*) count(col_1)
-------- --------------
3 0
この相違は、もちろんコーディングの際に注意が必要な点ですが、うまく使うと興味深い応用が可能です。例えば、学生のレポート提出日を記録する次のようなテーブルを考えます。
| 学生ID（student_id） | 学部（dpt） | 提出日（sbmt_date） |
| 100 | 理学部 | 2005/10/10 |
| 101 | 理学部 | 2005/09/22 |
| 102 | 文学部 | |
| 103 | 文学部 | 2005/09/10 |
| 200 | 文学部 | 2005/09/22 |
| 201 | 工学部 | |
| 202 | 経済学部 | 2005/09/25 |
学生がレポートを提出すると、提出日に日付が入ります。未提出の間はNULLです。このテーブルから、所属する全ての学生が提出済みの学部（理、経済学部）を求めます。単純に「WHERE sbmt_date IS NOT NULL」という条件で選択すると、不要な文学部まで含まれてしまい、うまくいきません（文学部は102番が未提出）。考え方としては、まず学部をキーにGROUP BY句を使って下図のような部分集合を作ります。
こうして作られた4つの部分集合のうち、欲しいのはS1とS4です。では、この2つの集合が共有していて、他の集合が持っていない性質は何でしょう？ それは「COUNT(*)とCOUNT(sbmt_date)が一致する」という性質です。これはS2とS3がNULLを含むために起こる現象です。したがって答えはこうなります。
--提出日にNULLを含まない学部を選択する SELECT dpt FROM Students GROUP BY dpt HAVING COUNT(*) = COUNT(sbmt_date);
dpt -------- 理学部 経済学部
さらにここからCASE式を併用することで、より幅広い条件を記述することができます。例えば、「学生番号が100番代の学生が2人以上受講した学部」（文、理学部）という条件や「全員が9月中に提出済みの学部」（経済学部）なども簡単に書けます。
--全員が9月中に提出済みの学部を選択する SELECT dpt FROM Students GROUP BY dpt HAVING COUNT(*) = SUM(CASE WHEN sbmt_date BETWEEN '2005/09/01' AND '2005/09/30' THEN 1 ELSE 0 END);
dpt -------- 経済部
このように、HAVING句は集合の性質を調べる道具として使えます。特に集約関数やCASE式と組み合わせたときの記述力は強力無比です。
関係除算でバスケット解析
次に、全国展開しているディスカウントチェーンの商品マスタ（Items）および店舗ごとの在庫状況を示すテーブル（ShopItems）を考えます。関連エンティティでよく見かけるテーブル構成です。
今度の問題は、「Items」テーブルのすべての商品をそろえている店舗を選択することです。すなわち、求める結果は仙台店と東京店です。大阪店はビールを置いていないので対象外です。この問題の実務における代表例は、データマイニングの技術である「バスケット解析」ですが、形を変えてさまざまな業務に現れます。例えば医療分野で同時に複数の薬を併用している患者を探す場合や、社員の技術データベースからUNIXとOracleの両方に通じているプログラマを探し出す場合、等々。
「ShopItems」テーブルのように、ひとつの実体（ここでは店舗）についての情報が複数行に分散して存在する場合、WHERE句で単純にORやINで条件を指定しても正しい結果が得られません。WHERE句で指定する条件は、あくまで1行について適用されるからです。
--ビールと紙オムツと自転車をすべて置いている店舗を検索する：間違ったSQL SELECT DISTINCT shop FROM ShopItems WHERE item IN (SELECT item FROM Items);
shop -------- 仙台 東京 大阪
このIN述語の条件は、結局のところ、「ビールまたは紙オムツまたは自転車を置いている店舗」を指定するに過ぎませんから、どれか1つでも置いていれば結果に含まれてしまいます。ではこういうとき、複数行にまたがった条件 ―― すなわち集合に対する条件 ―― を設定するにはどうすればよいのでしょう？ もうお分かりですね、HAVING句を使います。次のように書きます。
--ビールと紙オムツと自転車をすべて置いている店舗を検索する：正しい SQL SELECT SI.shop FROM ShopItems SI, Items I WHERE SI.item = I.item GROUP BY SI.shop HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM Items);
shop -------- 仙台 東京
HAVING句のサブクエリ「(SELECT COUNT(item) FROM Items)」は、定数3を返します。したがって、商品マスタと店舗在庫テーブルを結合した結果が3行になる店舗が選択されます。ビールを置いていない大阪店は2行になるので失格、（仙台店、カーテン）の行は結合で除外されるので仙台店は合格、東京店も無論合格です。
なおここで「HAVING COUNT(SI.item) = COUNT(I.item)」とするのは間違いなので注意してください。この条件だと仙台、東京、大阪の全店舗が選択されてしまいます。これは、結合の影響を受けてCOUNT(I.item)の値がもとの「Items」テーブルの行数ではなくなっているからです。次の結果を見れば一目瞭然です。
-- COUNT(I.item)はもはや3とは限らない SELECT SI.shop, COUNT(SI.item), COUNT(I.item) FROM ShopItems SI, Items I WHERE SI.item = I.item GROUP BY SI.shop
shop COUNT(SI.item) COUNT(I.item) -------- --------------- -------------- 仙台 3 3 東京 3 3 大阪 2 2
これで要件を満たすSQL文ができました。では次に、商品マスタにない「カーテン」を置いている仙台店も除外して、東京店のみを選択するという変更を考えましょう。いわば「厳密な関係除算（exact relational division）」、つまり、過不足なく割り切れる店舗のみを選択するということです（これに対し、今見たような除算は「剰余を持った除算（division with a remainder）」と呼ばれます）。これは次のように外部結合を使います。
--厳密な関係除算：外部結合とCOUNT関数の利用 SELECT SI.shop FROM ShopItems AS SI LEFT OUTER JOIN Items AS I ON SI.item=I.item GROUP BY SI.shop HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM Items) --条件1 AND COUNT(I.item) = (SELECT COUNT(item) FROM Items); --条件2
shop -------- 東京
「ShopItems」テーブルをマスタとして外部結合すると、「Items」テーブルに存在しないカーテンとテレビは、NULLとしてI.item列に現れます。ここまでくれば、先のレポート提出の例題と同じでCOUNT関数のトリックが使えます。条件1によってCOUNT(SI.item) = 4の仙台店が除外され、条件2によってCOUNT(I.item) = 2の大阪店（NULLはカウントされない！）が除外されます。
普通、外部結合というと、商品マスタである「Items」テーブルを主に結合する場合が多いものですが、ここではその主従をあえて逆転させているところが面白い発想です。
関係除算は、関係代数の中で最も知名度の低いものです。といっても、実務での利用機会が少ないわけではなく、章の冒頭で例を挙げたように、様々な局面で（しばしば名前を知らないまま）利用されています。しかも、コッドが最初に定義した8つの基本的な演算の中に含まれているという「由緒正しい」演算でもあります。
それが何で冷遇されているかというと、関係除算の定義が複数あるというのが大きな理由です。今回紹介した2種類の除算だけでなく、デイトが
EXISTS述語を使って定義した「剰余を持った除算」も、今回見た除算とは微妙に違う動作をします。デイトの除算は「Items」テーブルが空だった場合に全店舗を返しますが、COUNT関数を使う今回の除算は結果が空になります。関係除算の標準化が遅れていて、未だに専用の演算子が存在しない背景には、こういう厄介な事情もあります。おわりに
HAVING句は、ともするとあまり出番のない脇役と思われがちです。「何だかオマケみたいな句」と軽視されていることも少なくありません。しかし本稿で見てきたように、HAVING句もまた集合指向言語の強力な武器のひとつです。そしてその真価は、CASE式や自己結合といった他の武器と組み合わせたときに発揮されます。
それでは、今回のポイントをまとめましょう。
- テーブルはファイルではない。行も順序を持たない。そのためSQLではソートを記述しない。
- SQLは手続き型言語ではないので、ループ、分岐、代入を行わない。
- 代わりにSQLは、求める集合にたどりつくまで次々に集合を作る。SQLで考えるときは四角と矢印を描くのではなく、円を描くのがコツ。
GROUP BY句は部分集合を作る。WHERE句が集合の要素の性質を調べる道具であるのに対し、HAVING句は集合自身の性質を調べる道具である。
いかがでしょう。集合指向言語の本領、少し実感していただけたでしょうか。
参考資料
- 『C. J. Dateの データベース実践講義』 C. J. デイト 著、オライリージャパン、2006年2月
EXISTS述語を使った関係除算について説明があります。除算については「あまり詳細に触れたくない」という、正直なコメントが印象的。 - 『プログラマのためのSQL 第2版』 J. セルコ 著、ピアソンエデュケーション、2001年4月
第23章「SQLでの統計」では
HAVING句が大活躍。 - 『Joe Celko's Analytics And Olap in SQL』 J. セルコ 著、Morgan Kaufmann Pub、2006年7月
OLAPのツールとしてのSQLに焦点を当てた面白い視点の本。バスケット解析の方法として関係除算が詳しく紹介されています。
