帰ってきたHAVING句

集合指向言語としてのSQL：その5

集合にきめ細かな条件を設定する

　最後に、CASE式で特性関数を作る方法を、もう少し練習しておきましょう。これを使いこなせるようになると、どんな複雑な条件でも記述できます（大げさじゃなく、本当に）。

　次のような、生徒のテスト結果を保持するテーブルを例にとります。

TestResults

student_id（学生ID）	class（クラス）	sex（性別）	score（得点）
001	A	男	100
002	A	女	100
003	A	女	49
004	A	男	30
005	B	女	100
006	B	男	92
007	B	男	80
008	B	男	80
009	B	女	10
010	C	男	92
011	C	男	80
012	C	女	21
013	D	女	100
014	D	女	0
015	D	女	0

　このサンプルを使って、今から出す問題を解いてみてください。今回は、筆者の方からベン図は見せません。皆さん、自分で円を描いてみてください。

　ではまずは軽く。

第1問

　クラスの75%以上の生徒が80点以上のクラスを選択せよ。

　クラスの総人数は、COUNT(*)で分かります。80点以上をとった生徒の数は、特性関数でカウントできます。従って答えは、

  SELECT class
    FROM TestResults
GROUP BY class
  HAVING COUNT(*) * 0.75
         <= SUM(CASE WHEN score >= 80
                     THEN 1
                     ELSE 0 END) ;

結果

class
-------
    B

　どうでしょう、簡単でしたか？ では次です。

第2問

　50点以上を取った生徒のうち、男子の数が女子の数より多いクラスを選択せよ。

　今度はどちらの条件も特性関数で記述します。

  SELECT class
    FROM TestResults
GROUP BY class
  HAVING SUM(CASE WHEN score >= 50 AND sex = '男'
                  THEN 1
                  ELSE 0 END)
       > SUM(CASE WHEN score >= 50 AND sex = '女'
                  THEN 1
                  ELSE 0 END) ;

結果

class
-------
    B
    C

　それでは、最後の問題です。この問題には少しトリッキーなところがあるのですが、分かるでしょうか？

第3問

　女子の平均点が、男子の平均点より高いクラスを選択せよ。

　今までの流れからいって、次のようなクエリを考えた人も多いのではないでしょうか。

-- 男子と女子の平均点を比較するクエリ
-- 　その1：空集合に対するAVGを0で返す
  SELECT class
    FROM TestResults
GROUP BY class
  HAVING AVG(CASE WHEN sex = '男'
                  THEN score
                  ELSE 0 END)
       < AVG(CASE WHEN sex = '女'
                  THEN score
                  ELSE 0 END) ;

結果

class
-------
    A
    D

　Aクラスが選択されることには、特に疑問点はありません。男子の平均は (100 + 30) / 2 = 65、女子の平均は(100 + 49) / 2 = 74.5 ですから、確かに女子の方が平均点の高いクラスです。問題は、Dクラスです。

　上のサンプルを見てもらえば分かるように、Dクラスには女子しかいません。上の回答では、男子の方のCASE式で「ELSE 0」を指定しているため、男子の平均は0点という扱いになります。そのため、女子の平均が約33.3点のDクラスにおいても、「0 < 33.3」という比較が成立して選択されるのです。

　この場合は、これでもいいかもしれません。でも、仮に013番の生徒がたまたま0点だったらどうなったでしょう。その場合、女子の平均点も0になり、Dクラスは選択されません。

　しかし同じ「0」という数ではあっても、両者の意味は大違いです。女子の方はれっきとした平均点数ですが、男子はそもそも平均の計算ができないのを、0という数でむりやり代用しただけのことです。本当は、空集合に対する平均は「未定義」でなければなりません。ゼロ除算の結果が未定義なのと同じことです。

　標準SQLでは、空集合にAVG関数を適用した場合は、NULLを返すこととされています（未定義をNULLで代用するこの仕様にも問題はあるのですが、ここでは深入りしません。詳しくは「3値論理とNULL」を参照）。この点を修正したクエリが次のものです。

-- 男子と女子の平均点を比較するクエリ
-- 　その2：空集合に対する平均をNULLで返す
  SELECT class
    FROM TestResults
GROUP BY class
  HAVING AVG(CASE WHEN sex = '男'
                  THEN score
                  ELSE NULL END)
       < AVG(CASE WHEN sex = '女'
                  THEN score
                  ELSE NULL END) ;

　今度は、Dクラスの男子の平均はNULLです。従って、Dクラスは女子の平均点によらず常に選択対象外とされます。こちらの方が、通常のAVG関数の動作とも一致します。

　集合の性質に着目するということは、裏返して言えば、個々の要素の特性を無視する、ということです。今回の例題でも、私たちが考えたのは、あくまでクラスが集団として持つ特徴や傾向性であって、誰か何点を取ったかという生徒の個人情報は詮索しません。

　構成員の匿名性を保ったまま集団の傾向を把握するこの考え方は、統計学の方法論とぴたり一致します。近年、リレーショナル・データベースを用いた統計分析が（特にアメリカで）盛んになってきているのも、SQLの集合指向性と統計の親和性を考えれば、不思議なことではありません。統計分析のツールとしてのデータベースという使い方は、今後、日本でも広まっていくことでしょう。

おわりに

　さて、2回にわたってHAVING句の応用方法を見てきました。HAVING句（とGROUP BY）の使い方にも、大分慣れてもらえたでしょうか。

　HAVING句を使うときのポイントをひとことで言うならば、何をもって集合と見なすかに着目せよ、ということです。これまでの例題では、実に多種多様な実体を「集合」として捉えてきました。数列や学校のクラス、チームのように、最初から複数のモノの集まりとしてイメージしやすいものもあれば、店舗や生産拠点のように、それ自身を原子的な要素と考えてもおかしくないものまで「集合」に見立ててきました。

　これが意味することは、SQLにおいて何を集合とみなすかの基準は、それが現実世界でどういうレベルの存在かということとは一切関係ない、ということです。基準はただ、それがテーブルでどのように表現されているかということだけです。ある実体が、時には要素になり、時には集合になります。

　実体一つにつき一行が割り当てられていれば、その実体は集合の要素として扱われています。だから、条件を設定するときも迷うことなくWHERE句を使えばかまいません。一方、一つにつき複数行が割り当てられていれば、それは集合として扱われている証拠です。そうなったら、HAVING句の出番です。

　最後に、集合の性質を調べるための代表的な条件をまとめておきます。この条件は、HAVING句またはSELECT句のCASE式で使うことができます。リファレンスとして活用してください。

集合の性質を調べるための条件の使い方一覧

#	条件式	用途
1	COUNT (DISTINCT col) = COUNT (col)	col の値が一意である
2	COUNT(*) = COUNT(col)	col に NULL が存在しない
3	COUNT(*) = MAX(col)	colは歯抜けのない連番（開始値は1）
4	COUNT(*) = MAX(col) - MIN(col) + 1	colは歯抜けのない連番（開始値は任意の整数）
5	MIN(col) = MAX(col)	col が一つだけの値を持つか、または NULL である
6	MIN(col) * MAX(col) > 0	すべての col_x の符号が同じである
7	MIN(col) * MAX(col) < 0	最大値の符号が正で最小値の符号が負
8	MIN(ABS(col)) = 0	col は少なくとも一つのゼロを含む
9	MIN(col - 定数) = - MAX(col - 定数)	col の最大値と最小値が指定した定数から同じ幅の距離にある

　1、4、5番は、今回使ったトリックです。2番と3番は、前回の「HAVING句の力」で使ったのでおなじみでしょう（4番は、3番の一般化です）。また、こうした簡潔な条件以外だけでなく、CASE式で特性関数を表現すれば、どんなに複雑で一般的な条件でも記述できることは、繰り返すまでもありません。

　それでは、今回の要点です。

1. SQLで検索条件を設定するときは、検索対象となる実体が集合なのか集合の要素なのかを見極めることが基本。
2. 実体一つにつき一行が対応している　→　要素なのでWHERE句を使う。
3. 実体一つにつき複数行が対応している　→　集合なのでHAVING句を使う。
4. HAVING句では集約関数（特に極値関数）を駆使することで、多様な条件を集合に対して設定できる。
5. CASE式で特性関数を表現すれば、どんな複雑な条件でも設定できる。
6. HAVING句は凄い。

参考資料

1. 『SQLパズル 第2版』 Joe Celko 著、ミック 訳、翔泳社、2007年11月
数列の欠番チェックについては、「第57問　GAPS―VERSION ONE」を参照。セルコは最小の欠番を求める際、NOT INを使っていますが、今回はNULL対策とパフォーマンス・チューニングのためにNOT EXISTSで置き換えました。
2. DBAzine.com 『Thinking in Aggregates』 J.セルコ 著、2005年9月
HAVING句を使ってSQLの集合指向という概念を理解させようというコンセプトの名エッセイ。筆者も大きな影響を受けました。「おわりに」に載せた条件のリファレンスは、このサイトのものを筆者が少し改変したものです。また、冒頭に引用したセルコの言葉もこのエッセイから。
3. 『GROUP BYとPARTITION BY』 ミック 著、2007年6月
SQLのGROUP BYとPARTITION BYの理論的背景である類と類別の概念について、筆者がまとめたテキスト。SQLの後ろには、いつも集合論や群論が控えているのです。
4. 物理のかぎしっぽ 『類別』 2006年4月
物理学に関連する諸分野をやさしく解説する「物理のかぎしっぽ」プロジェクト有志による、類と類別の概念についての解説。数式を極力つかわない、大変分かりやすい解説です。