HAVING句で論理演算を行おう
これまで後ろ向きな話が多かったので、気が滅入ってしまったかもしれませんね。本節では、SQLで真理値と論理演算を有効活用するとどんな便利なことができるか、というポジティブな話をします。
SQLの中で論理演算が行われる個所は、大きく3つあります。
① WHERE句
② HAVING句
③ CASE式
まあ要するにAND、OR、NOTの演算子が使える場所、と思っていただいて結構です。このうち、最も頻繁に使われるのは① のWHERE句と② のHAVING句ですが、WHERE句についてはそれほど使い方が難しくないので割愛します。他方、HAVING句での論理演算を使いこなしている人は、プロのDBエンジニアでもそれほど多くありません。
例題の前提
ではさっそく、具体的な例題に即して考えてみましょう。例題は、Joe Celko著『SQLパズル 第2版』( 翔泳社)の「パズル17 人材紹介会社」を使います。まず、図5 のような単純な構造のテーブルを用意します。
図5 CandidateSkills(就業希望者スキルテーブル)
candidate_id (就業希望者ID) skill_code (スキルコード)
100 会計
100 在庫管理
100 製造
200 会計
200 在庫管理
300 製造
400 在庫管理
400 製造
500 会計
500 製造
ある人材紹介会社に登録されている就業希望者と、その仕事のスキルの組み合わせを表現したものです。本当は、スキルコードも「01102」とかコードで表すのがよりリアリティがありますが、わかりやすさのためスキルの名前をそのまま使うことにします。このテーブルによれば、たとえば100番の人は会計、在庫整理、製造という3つの分野の技術を持っており、300番の人は製造のみの技術を持つ、ということになります。
さて、この会社にはいつもひっきりなしにクライアント企業から人材についての問い合わせが入ります。みなさん、この会社の対応オペレータになったつもりで、クライアントからの問い合わせ条件に合致する人材がいるかどうか、検索してみてください。
①クライアントAからの問い合わせ
次のような条件であるとします。
これは簡単ですね。スキルが1つだけであれば、WHERE句で条件を記述するだけでOKです(リスト3 ) 。200番以外の全員が「製造」スキルは持っているため、結果は図6 のように4人になります。
リスト3 「 製造」スキルを持っている人材を抽出するクエリ
SELECT DISTINCT candidate_id
FROM CandidateSkills
WHERE skill_code = '製造';
図6 リスト3の結果
candidate_id
------------
100
300
400
500
②クライアントBからの問い合わせ
次のような条件であるとします。
条件:「会計」と「製造」の両方のスキルを持っている人材
今度は、条件がもっと厳しい問い合わせが来ました。しかしラッキーなことに、100番と500番の人が合致します。
この条件を表現しようとすると、もうWHERE句に記述してもダメです(リスト4 ) 。
リスト4 「 会計」と「製造」の両方のスキルを持っている人材を抽出するクエリ(間違い)
SELECT DISTINCT candidate_id
FROM CandidateSkills
WHERE skill_code = '会計'
AND skill_code = '製造';
このクエリは、結果を1 行も返しません。理由は、WHERE句の条件は同一行に対して適用されるからです。今、ある人物のスキルは、1行につき1つだけ保持する形になっています。したがって、2つ以上のスキルを問い合わせるのに、WHERE句に記述するのは不適切なのです[3] 。
条件が複数行にまたがるとき、それはすなわちHAVING句の出番です。正解はリスト5 のように記述します。実行すると、図7 のように表示されます。
リスト5 「 会計」と「製造」の両方のスキルを持っている人材を抽出するクエリ(正しい)
SELECT candidate_id
FROM CandidateSkills
GROUP BY candidate_id
HAVING MAX(CASE WHEN skill_code = '会計' THEN 1 ELSE 0 END)
* MAX(CASE WHEN skill_code = '製造' THEN 1 ELSE 0 END) = 1;
図7 リスト5の実行結果
candidate_id
------------
100
500
このHAVING句が何をやっているかというと、図8 のように、会計スキルと製造スキルについて、各登録者がスキルを持っているかどうかによって、0/1のビットフラグを立てているのです。
図8 スキルの保有有無によるビットフラグ
candidate_id (就業希望者ID) 会計フラグ MAX(CASE WHEN skill_code = '会計'THEN 1 ELSE 0 END) 製造フラグ MAX(CASE WHEN skill_code = '製造'THEN 1 ELSE 0 END) 会計フラグ*製造フラグ
100 1 1 1
200 1 0 0
300 0 1 0
400 0 1 0
500 1 1 1
先ほど、0/1のビット演算は、真理値の論理演算と同等であると述べたのを覚えているでしょうか。この解答は、まさにその特性を利用したものです。会計フラグと製造フラグを乗算しているのは、論理演算の「AND」に相当します。
もしみなさんがお使いのRDBが、真理値をサポートしていれば、ここで0/1の代わりにtrue/false(およびAND/OR)を使うこともできますし、そのほうがぱっと見て論理演算であることがわかりやすいのですが、現在、それが可能なのはPostgreSQLのみです。したがって、汎用的な解答としては、上記のようにビット演算を行うものとなります。いずれはどの実装でも真理値が正式にサポートされることを期待しましょう。
[3] 裏を返すと、テーブル構造を変えれば、WHERE句で2つ以上のスキルを指定するクエリを書くことも可能になります。どのようにテーブルを変えればよいか、そのときのメリット/デメリットは何か、考えてみてください。
③クライアントC からの問い合わせ
続いて、次のような条件であるとします。
条件:「在庫管理」と「製造」の両方のスキルを持っている、または「会計」のスキルを持っている人材
今度は「または」が条件に入ってきました。論理式で書けば次のようになります。
(「 在庫管理」AND「製造」 )OR「会計」
これも考え方は② のときと同じです。ORは、ビット演算では足し算で代用します(リスト6 ) 。結果は図9 のようになります。
リスト6 「 在庫管理」と「製造」の両方のスキルを持っている、または「会計」のスキルを持っている人材を抽出するクエリ
SELECT candidate_id
FROM CandidateSkills
GROUP BY candidate_id
HAVING ( MAX(CASE WHEN skill_code = '在庫管理' THEN 1 ELSE 0 END)
* MAX(CASE WHEN skill_code = '製造' THEN 1 ELSE 0 END))
+ MAX(CASE WHEN skill_code = '会計' THEN 1 ELSE 0 END ) >= 1;
図9 リスト6の出力結果
candidate_id
------------
100
200
400
500
先ほどと同様、各スキルについて、ビットフラグの一覧表を作ってみましょう(図10 ) 。
図10 スキルの保有有無によるビットフラグ
candidate_id (就業希望者ID) 在庫管理フラグ 製造フラグ 会計フラグ (在庫管理フラグ*製造フラグ)+会計フラグ
100 1 1 1 2
200 1 0 1 1
300 0 1 0 0
400 1 1 0 1
500 0 1 1 1
今回は条件に「OR」が入っているため、ビット演算に足し算が含まれます。すると、100番の人のように、結果が0/1の範囲を超えて、2以上になる場合があります。これはある意味当然の話で、いかにこの演算が真理値の論理演算を代用するものとはいえ、それは私たちユーザが勝手にそう思っているだけで、DBにとってはただの整数の四則演算だからです。そのため、この演算の結果は、「 0か、それ以上か」という形で真偽判定をしなくてはなりません。
整数で真理値を代用した場合の結果判定のルール
そのため、結果判定の際に等号ではなく不等号を使わざるをえないのが気持ち悪いところです(リスト6参照) 。
このように、HAVING句で論理演算の考え方を利用すると、非常に便利で幅広い応用が可能になります。そのため、DBを扱う人には積極的に身につけてもらいたい技術なのですが、その障害になるのが、SQLとRDBへの真理値型のサポートの遅れなのです。
終わりに
今回は、いささか厄介な、しかしSQLとデータベースを扱う上で避けては通れないテーマを取り上げました。頭がこんがらがった方もいるかもしれませんので、最後にまとめましょう。
標準SQLには真理値型も定義されているが、サポートしているDBは少ない
その理由は、SQLの真理値がunknownという第3の値まで持つ独特の体系(3値論理)であるため
NULLとunknownは厳密には異なるのだが、PostgreSQLはこれを混同している
NULLと空文字は厳密には異なるのだが、Oracleはこれを混同している
しかたないので、現在のところ、SQLで論理演算をしたいときは、0/1のビットフラグでビット演算をするしかない
最後に、参考資料を記します。
参考資料
ミック『達人に学ぶSQL徹底指南書』
(翔泳社,2008)
「1-3 3値論理とNULL」では、SQLにおける3値論理演算がどのように行われるか、そしてそれがいかにSQLに混乱をもたらすかという点を詳細に解説しました。本稿と併せて読むとよいでしょう。
また、なぜSQLがそもそも真理値に3つ目の値を持ち込むことになったのか、という歴史的な事情を知りたい人は「2-8 神のいない論理」を参照してください。
ミック「3値論理とNULL 」
そもそもなぜSQLにunknownという第3の真理値が存在するのか、という理由を知りたい方は、こちらの記事を参照。NULLがSQLの演算に含まれた場合の危険性についても解説しています。
明智重蔵「OracleSQL パズル 9-22 」
Joe Celkoの『SQLパズル 第2版』( 翔泳社)の「パズル17人材紹介会社」に対して、HAVING句で真理値演算を行う解答が解説されています。本稿を書くうえで参考にさせていただきました。
COLUMN NULLとゼロの違い
先のコラムではNULLと空文字の違いについて取り上げましたが、ここではNULLとゼロの違いについて、少し注意すべき点をお話しておきましょう。
SQLのNULLには、伝播(でんば) (propagation )という妙な性質があります。これは演算の中にNULLが含まれていた場合、その結果が自動的にNULLになるというものです。例を示します。
3 - NULL = NULL
1 + (2 * NULL) = NULL
4 / NULL = NULL
これだけでも十分に気味が悪い話ですが、もっと凄いのはゼロ除算の場合です。普通、SQL内の演算でゼロ除算が発生するとエラーになります。ところが、NULLをゼロで割ってもエラーにならないのです。
5 / 0 -> Erorr!
NULL / 0 = NULL -> Errorにならず!
このように、普通の値に対して適用できる演算ルールの一切を破壊してしまうという点でも、NULLが値とはまったく異なるものだということがわかります。NULLが演算に含まれた場合、その演算そのものが不成立 という扱いを受けるのです。
ちなみに、NULLが演算を破壊するのは数値の四則演算の場合だけではありません。文字列の結合演算でもやはり同様です。
文字列に空文字を結合しても結果が変化しない
文字列にNULLを結合すると結果がNULLになる
空文字は、文字列におけるゼロのような役割を果たすので、これを任意の文字列に結合しても変化しません。ところがNULLを結合すると、結果がNULLになってしまうのです。
NULLは、すべてを呑み込むブラックホール なのです。