SQLアタマアカデミー

第8回 SQLにおける論理演算~なぜ真理を隠すのか~ (3)HAVING句で論理演算を行おう

この記事を読むのに必要な時間:およそ 3 分

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)
会計フラグ*製造フラグ
100111
200100
300010
400010
500111

先ほど,0/1のビット演算は,真理値の論理演算と同等であると述べたのを覚えているでしょうか。この解答は,まさにその特性を利用したものです。会計フラグと製造フラグを乗算しているのは,論理演算の「AND」に相当します。

もしみなさんがお使いのRDBが,真理値をサポートしていれば,ここで0/1の代わりにtrue/false(およびAND/OR)を使うこともできますし,そのほうがぱっと見て論理演算であることがわかりやすいのですが,現在,それが可能なのはPostgreSQLのみです。したがって,汎用的な解答としては,上記のようにビット演算を行うものとなります。いずれはどの実装でも真理値が正式にサポートされることを期待しましょう。

注3)
裏を返すと,テーブル構造を変えれば,WHERE句で2つ以上のスキルを指定するクエリを書くことも可能になります。どのようにテーブルを変えればよいか,そのときのメリット/デメリットは何か,考えてみてください。

著者プロフィール

ミック

SI企業に勤務するDBエンジニア。主にデータウェアハウス業務に従事している。自身のサイト「リレーショナル・データベースの世界」でデータベースとSQLについての技術情報を公開している。『Web+DB Press』で「SQLアタマアカデミー」を連載中。

著書:『達人に学ぶ SQL徹底指南書』(翔泳社、2008)訳書:J.セルコ『SQLパズル 第2版』(翔泳社、2007)

SQLアタマアカデミー:サポートページ

コメント

コメントの記入