はじめに
SQLというのは変わった言語です。こういう印象は人によって差があると思いますが、おそらく最初に手続き型言語を学んだ正統派のプログラマやSEほど強くそう感じると思います。
SQLに違和感を感じる理由は、いくつか考えられます。第一に、SQLが「集合指向」という発想に基づいて設計された言語で、この設計方針を持つ言語が少ないことです。そして第二に、それに劣らず大きいのが、最初に学んだ言語のスキーマ（概念の枠組み）が心理的モデルとして固定され、それを通して世界を見るようになるため、異なるスキーマを持つ言語の理解が妨げられることです。
本稿では、HAVING句のさまざまな応用方法を紹介していきますが、その際、手続き型言語とSQLの考え方を比較します。それによって、私たちが手続き型言語で身に付けた無意識の心理的モデルを自覚し、集合指向という発想に感じる違和感を軽減したいと考えています。
今回は『自己結合の使い方』に続くシリーズ第2回ということで、サブタイトルに「その2」とつけました。前回の主眼は、「テーブルを集合という抽象度の高い存在とみなす」という集合指向のとっかかりを説明することでした。今回はさらに踏み込んで「集合単位の操作」という特徴に焦点を当てます。前回の内容を前提とするものではありませんが、自己結合も登場するので、未読の方は前回を先に読まれると理解が増すでしょう。
稼働環境
- Oracle
- SQL Server
- DB2
- PostgreSQL
- MySQL（サブクエリを使うものについてはバージョン4.1以上）
対象読者
HAVING句の基本的な使い方は知っているけど、あまり実務で利用していない方。CASE式、自己結合、サブクエリについての知識があると望ましいです。
データの歯抜けを探す
では、さっそく例を見ながら進めていくことにしましょう。次のような連番を持つテーブルがあるとします。システムで一意に割り振る数値を使う場合などに多く見かけます。
| 連番（seq） | 名前（name） |
| 1 | ディック |
| 2 | アン |
| 3 | ライル |
| 5 | カー |
| 6 | マリー |
| 8 | ベン |
ところが「連番」とあるものの、この数列は連続していません。4と7が抜けています。最初の問題はこのテーブルにデータの歯抜けが存在するか否かを調べることです。このサンプルのように数行なら一目瞭然ですが、100万行を目で確認する勇気のある人はいないでしょう。
仮にこのテーブルがファイルで、手続き型言語を使って調べるなら、次のような手順になります。
- 連番の昇順か降順にソートする。
- ループさせて、1行ずつ次の行と連番を比較する。
この単純な手順の中にも、手続き型言語とファイルシステムの特徴が浮き彫りになっています。それは、ファイルのレコードは順序を持ち、それを扱うために、言語はソートを行うということです。一方、テーブルの行は順序を持ちませんし、SQLもソートの演算子を持っていません（注1）。代わりにSQLは、複数行をひとまとめにして集合として扱います。従って、テーブル全体をひとつの集合とみなすと、解答は次のようになります。
-- 結果が返れば歯抜けあり SELECT '歯抜けあり' AS gap FROM SeqTbl HAVING COUNT(*) <> MAX(seq);
gap ------------- '歯抜けあり'
このクエリの結果が1行返れば「歯抜けあり」、1行も返らなければ「歯抜けなし」です。COUNT(*)で数えた行数と連番の最大値が一致したなら、それは最初から最後まで抜けなくカウントアップできたという証拠だからです。抜けがあれば、「COUNT(*) < MAX(seq)」となり、HAVING句の条件が真になります。わずか3行のエレガントな解答です。
ところで、このSQL文にはGROUP BY句が存在しません。こういう場合、テーブル全体が1行に集約されます。その場合でも、HAVING句は問題なく使えます。昔のSQLでは、HAVING句はGROUP BY句と併用しなければならなかったので、今でも時々そう勘違いされていることがありますが、現在の標準SQLではHAVING句を単独で使えます。ただしその場合、SELECT句で元テーブルの列を参照できなくなるので、サンプルのように定数を指定するか、または「SELECT COUNT(*)」のように集約関数を使う必要があります。
さて、これでこのテーブルに歯抜けが存在することが判明しました。今度は、歯抜けの最小値を求めます。最小ときたらMIN関数。というわけで次のように書きます。
-- 歯抜けの最小値を探す SELECT MIN(seq + 1) AS gap FROM SeqTbl WHERE (seq+ 1) NOT IN ( SELECT seq FROM SeqTbl);
gap ------ 4
これもわずか3行。NOT INを使ったサブクエリは、ある連番について、それより1つ大きい数値がテーブル内に存在するかどうかを調べるものです。すると、（3、ライル）、（6, マリー）、（8, ベン）の行について、次の数が見つからないため、条件が真になります。歯抜けが無い場合は、最大の連番8の次の数である9が得られます。繰り返しになりますが、テーブルはファイルではないので、行に順序がありません（上の「SeqTbl」を昇順で表示しているのは、あくまで見やすくするためです）。それゆえ、こういう行同士の比較を行う際にソートをしません。
ところで、SeqTblにもしNULLが含まれていた場合、このクエリの結果は正しくなりません。なぜ正しくならないかすぐに理由が分からなかった人は、『3値論理とNULL』を参照してください。
ORDER BY句があるじゃないか」と思うかもしれませんが、実はORDER BY句はSQLの演算子ではなく、カーソル定義の一部です。「ORDER BYは、結果を表示する目的には便利だが、それ自体はリレーショナル演算子ではない。」（C. J. デイト 『C. J. Dateのデータベース実践講義』 15ページより）
HAVING句でサブクエリ：最頻値を求める
1984年、アメリカのヴァージニア大学は、その年の修辞コミュニケーション学科の卒業生の平均初任給が55,000ドルだと発表しました。当時は1ドル=240円ぐらいと考えると日本円で約1,320万円です。これだけ聞くと、卒業生の多くがかなりの高給取りだという印象を受けます。しかし、この数字にはトリックがありました。卒業生には、「大学史上最高の選手」と呼ばれたNBAの新星ラルフ・サンプソンが含まれていたのです。つまり、大学が使った卒業生テーブルは、イメージとしては次のような極端な分布のテーブルだったのです。
| 名前（name） | 収入（income） |
| サンプソン | 400,000 |
| マイク | 30,000 |
| ホワイト | 20,000 |
| アーノルド | 20,000 |
| スミス | 20,000 |
| ロレンス | 15,000 |
| ハドソン | 15,000 |
| ケント | 10,000 |
| ベッカー | 10,000 |
| スコット | 10,000 |
このことから分かるように、単純平均は「外れ値（outlier）」に影響を受けやすいという欠点があります。こういうケースでは、集団の傾向をもっと正確に示す指標を使わねばなりません。そのひとつが「最頻値（mode）」です。これは、母集団の中で最も数の多かった値のことです。その意味で「流行値」という呼び名もあります。上の卒業生テーブルだと、20,000と10,000の2つです。これを求める方法を考えてみましょう。
DBMSによっては、最頻値を求める独自の関数を用意しているものもあります。しかし、標準SQLでも簡単に求められます。考え方は、収入が同じ卒業生をひとまとめにする集合を作り、その集合群から要素数が最も多い集合を探すことです。SQLにとって、こういう集合操作はお手の物です。
--最頻値を求めるSQL その1：ALL述語の利用 SELECT income FROM Graduates GROUP BY income HAVING COUNT(*) >= ALL ( SELECT COUNT(*) FROM Graduates GROUP BY income);
income --------- 10,000 20,000
GROUP BYは、もとの集合から部分集合を作る働きをします（前回、ランキングの算出で再帰的な部分集合を作ったことを思い出してください）。したがって、収入（income）をGROUP BYのキーに使うと、次のような5つの部分集合S1～S5が得られます。
この中で最大の要素数を持つのは、要素数が3のS3とS5です。したがって、この2つの集合が選択されます。
また、『3値論理とNULL』でも触れたように、ALL述語は、NULLと空集合のケースに気をつければ極値関数で代用できます。今回は「最も多い」ですから、MAX関数を使います。
--最頻値を求めるSQL その2：極値関数の利用 SELECT income FROM Graduates GROUP BY income HAVING COUNT(*) >= ( SELECT MAX(cnt) FROM ( SELECT COUNT(*) AS cnt FROM Graduates GROUP BY income) );
もし仮に「Graduates」テーブルがファイルで、手続き型言語で最頻値を求めようとするなら、どうなるでしょう。おそらく収入でソートした後、1行ずつループさせてコントロールブレイクを行い、同じ収入の行数が前に数えた収入の行数より大きければ、その収入を変数に代入して保存することになるでしょう。しかし、既に見たように、SQLにおいてはループも代入も現れません。
HAVING句で自己結合：メジアンを求める
平均が信用できない場合、最頻値と並んでよく使われる指標にメジアン（中央値、中位数）があります。これは、母集合のデータを昇順に並べてちょうど中央に来た値です。データの個数が偶数の場合は中央の2つの数の平均をとります。先の「Graduates」テーブルは10行なので、（スミス、20,000）と（ロレンス、15,000）の中間、17,500がメジアンになります。
これをSQLで求めるにはどうすればよいでしょう。ソートして端から数えるという手続き的な方法は、もちろんとりません。集合指向的に考えた場合、ある値がちょうど母集合の中央にくるということはどうすれば分かるでしょうか？
それには、母集合を上位と下位の2つの集合に分割し、しかもその際、2つの集合が真ん中の値を共有するようにしておくことです。そうすれば、共通部分の平均がメジアンになります。イメージとしては、下図のようになります。
このように大小関係に基づいて部分集合を作るとなれば、自己非等値結合の出番です。
--メジアンを求めるSQL：自己非等値結合をHAVING句で使う SELECT AVG(DISTINCT income) FROM (SELECT T1.income FROM Graduates T1, Graduates T2 GROUP BY T1.income --S1の条件 HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END) >= COUNT(*) / 2 --S2の条件 AND SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END) >= COUNT(*) / 2 );
ポイントは、「>= COUNT(*) / 2」の比較条件に「=」を付けることで、S1とS2をスッパリ切り分けるのではなく、わざと共通部分を持つようにすることです。この条件から等号を除いて「> COUNT(*) / 2」とすると、データ数が偶数の場合にS1とS2が共通部分を持たなくなるケースが生じてしまい、メジアンが得られません。
また、データ数が奇数であることが分かっている場合、FROM句のサブクエリは値をひとつしか持たないので、外側のAVG関数は不要です。しかし、偶数の場合を考慮した汎用的なクエリを考えるなら、AVG関数が必要になります。
CASE式、自己結合、そしてHAVING句というSQLの持てる武器を駆使した、見事な解答です。
