一意集合と多重集合
「SQLで集合演算」でも述べましたが、リレーショナル・データベースで扱われる集合は、重複値（だぶり）を認める多重集合です。反対に、通常の集合論で扱われる集合は重複を認めません。こちらの集合を「一意集合」と呼んでおきます（これは筆者の造語なので、正式な術語ではありません）。
データの入出力を繰り返すテーブルでは、データに重複が発生することがあります。テーブル定義に一意制約を付けて、事前にその芽を摘めるなら安全ですが、業務要件として、重複が生じること自体はありうる、というケースもあります。
例えば、次のように生産拠点ごとの資材ストックを管理するテーブルを見ましょう。
| center（拠点） | receive_date（搬入日） | material（資材） |
| 東京 | 2007-4-01 | 錫 |
| 東京 | 2007-4-12 | 亜鉛 |
| 東京 | 2007-5-17 | アルミニウム |
| 東京 | 2007-5-20 | 亜鉛 |
| 大阪 | 2007-4-20 | 銅 |
| 大阪 | 2007-4-22 | ニッケル |
| 大阪 | 2007-4-29 | 鉛 |
| 名古屋 | 2007-3-15 | チタン |
| 名古屋 | 2007-4-01 | 炭素鋼 |
| 名古屋 | 2007-4-24 | 炭素鋼 |
| 名古屋 | 2007-5-02 | マグネシウム |
| 名古屋 | 2007-5-10 | チタン |
| 福岡 | 2007-5-10 | 亜鉛 |
| 福岡 | 2007-5-28 | 錫 |
資材は、一日一回、拠点へ搬入されます。各拠点は、資材を使ってさまざまな製品を生産しますが、中には当初の計画通りに消費されず、資材がだぶついてしまうことがあります。こういう場合は、拠点同士で余った資材の調整を行うために、「だぶり」の存在する拠点を調べる必要があります。
やりたいことは「拠点」の性質を調べることですが、見ての通り、このテーブルは一拠点につき一行という構成にはなっていません。複数行に分散しています。すなわち、「拠点」という実体は、このテーブルにおいて要素ではなく集合として存在している、ということです。こういう場合の定石は、GROUP BYで部分集合に切り分けることでした。次のようにカットできます。
欲しいのは、亜鉛がだぶついている東京拠点と、チタンおよび炭素鋼がだぶついている名古屋拠点。では、この二つの集合が満たし、他の集合が満たさない条件とは？
それはまさに「重複を排除して数えた要素数が、排除せずに数えた要素数とは異なる」という条件にほかなりません。要素に重複がなければ、DISTINCTオプションつきでCOUNTしても結果が変わることはありえないからです。
-- 資材のだぶっている拠点を選択する SELECT center FROM Materials GROUP BY center HAVING COUNT(material) <> COUNT(DISTINCT material);
center ------ 東京 名古屋
これだけでは、どんな資材が余っているのかまでは分かりませんが、WHERE句で具体的な資材をパラメータとして渡して、特定の資材の余っている拠点を選択することも可能です。また、前の問題と同様、SELECT句へ条件を移せば、拠点ごとにダブりの有無を一覧表示できます。練習問題として、やってみてください。
さて、前回から大分練習してきましたが、GROUP BYで元のテーブルを部分集合に切り分ける考え方も、大分慣れたでしょうか？ ここでちょっと種明かし的な話をすると、今まで単純に「部分集合」という言い方をしてきましたが、実はGROUP BYの作る部分集合は、数学で類（partition）という名前で呼ばれています。集合論や群論で使われる重要な概念で、ある集合を何らかの基準に従って過不足なく切り分けたときの部分集合をこう呼びます。また、このような分割操作を類別と言います。SQLのGROUP BYは、この類別という操作を実装したものです（類と類別についての詳細は、参考資料に挙げた資料を参照してください）。
欠番を探せ：発展版
以前、「HAVING句の力」で、数列の欠番チェックを行う次のようなクエリを紹介しました。
-- 結果が返れば歯抜けあり SELECT '歯抜けあり' AS gap FROM SeqTbl HAVING COUNT(*) <> MAX(seq);
このクエリは、数列の開始値が1であるという前提のもとで考えられています。大抵の場合、それで問題ないと思いますが、もう少し条件をゆるくして、下限がどんな値であるかは特に問わず、とにかく数列が連続しているか否かだけ調べたい、というケースを考えましょう。つまり、次の4パターンのうち、(3)についても「連続している」と見なし、(4)のケースを「歯抜けあり」と見なすケースです。前回のクエリだと、開始値が1という前提のため、(3)の場合も「歯抜けあり」と見なされてしまいます。
テーブル全体を一つの集合と見なし、COUNT(*)で集合の要素数を把握する、という基本的な考え方は変わりません。この4パターンであれば、いずれのテーブルもCOUNT(*) = 5 となります。そして、もし数列の下限値と上限値の間に欠番がないと仮定すると、その間に含まれる数の個数は、
上限値 - 下限値 + 1
になるはずです。従って、次のような比較条件を書いてやればいいわけです。
-- 結果が返れば歯抜けあり：数列の連続性のみ調べる SELECT '歯抜けあり' AS gap FROM SeqTbl HAVING COUNT(*) <> MAX(seq) - MIN(seq) + 1 ;
このクエリは、(1)と(3)のケースを「連続」と見なします。また、欠番の有無にかかわらず、必ず一行の結果を返したいなら、例によって、SELECT句へ条件を移しましょう。
-- 欠番があってもなくても一行返す SELECT CASE WHEN COUNT(*) = 0 THEN 'テーブルが空です' WHEN COUNT(*) <> MAX(seq) - MIN(seq) + 1 THEN '歯抜けあり' ELSE '連続' END AS gap FROM SeqTbl;
このクエリでは、ちょっと工夫を加えて、テーブルが空の場合だけ例外扱いとして「テーブルが空です」という結果を返すようにしています。（HAVING句のクエリでは、テーブルが空の場合も「連続」と見なします。）このように詳細な分岐を表現できることが、CASE式の魅力です。
それではついでに、欠番の最小値を求めるクエリも、開始値が1でないケースへ対応させましょう。前回のシンプルなクエリだと、(4)のケースでも、愚直に最初の「欠番」である5を返します。1と2はそもそもテーブルに存在しないので、次の数の存在チェックも行いようがないからです。このように、そもそも1がテーブルに存在しない場合は1を返すという分岐を追加したのが、次のクエリです。
-- 歯抜けの最小値を探す：テーブルに1がない場合は、1を返す SELECT CASE WHEN COUNT(*) = 0 OR MIN(seq) > 1 -- 下限が1でない場合→1を返す THEN 1 ELSE (SELECT MIN(seq +1) -- 下限が1の場合→最小の欠番を返す FROM SeqTbl S1 WHERE NOT EXISTS (SELECT * FROM SeqTbl S2 WHERE S2.seq = S1.seq + 1)) END FROM SeqTbl;
前回使ったクエリをスカラ・サブクエリとして、そのまま豪快にCASE式の戻り値にしています。「COUNT(*) = 0 」の条件は、テーブルが空だった場合を考慮したものです。また、NOT INをNOT EXISTSに変えていますが、これはNULL対策とちょっとしたパフォーマンス・チューニングのためです。特にseq列にインデックスがある場合は、NOT EXISTSを使うことでかなり改善されます。このクエリは、次のような結果を返します。
- ケース（1）： 6（欠番がないので、最大値5の次の数）
- ケース（2）： 3（最小の欠番）
- ケース（3）： 1（テーブルに1がないため）
- ケース（4）： 1（テーブルに1がないため）
手続き型言語では、分岐はIF文やCASE文といった「文」の単位で行います。しかし、SQLではすべての分岐を「式（関数）」の単位で行う ―― その点で、Lispのcondやcaseに似ている ―― という特徴が際立つクエリです。
なお、OracleはCASE式にスカラ・サブクエリを組み込んだときの動作に難があるため、このクエリは動きません。PostgreSQLおよびMySQLでは問題なく動作します。
