Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

帰ってきたHAVING句

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

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2007/07/11 14:00

HAVING句は、SQLが持つ重要な機能の一つです。前回「HAVING句の力」では、その強力な力の一端を紹介しました。今回も引き続き、HAVING句の「集合自身の性質を調べる」という特性を縦横に駆使したテクニックを紹介していきます。

目次

はじめに

SQLのクラスを教えるとき、最大の課題の一つが、生徒たちがそれまでに手続き型言語から身に付けたことを、一度「頭から追い出す(unlearn)」ことだ。私がそのとき採る一つの方法は、処理を「レコード単位」ではなく、集合という観点から考えるよう強調することである。
――――J.セルコ

 SQLの考え方を習得するときに最大の障壁となるのが、私たちの多くが慣れ親しんだ手続き型言語の考え方(ソート、ループ、分岐、代入、等々)です。SQLの本質を理解するには、私たちの心に強固に貼り付いてしまった思考パターンを、一度ベリベリと引き剥がし、更地に戻してやる必要があります。それが、セルコが「unlearn」という言葉に込めたニュアンスです。セルコ自身、Fortranからプログラマとしてのキャリアを開始し、C、Algol、Pascalと手続き型言語を渡り歩いた後にSQLを身に付けた人物だけに、言葉に実感がこもっています。

 とはいえ、言うは易し、行うは何とやら。ずっと昔に身につけ、長く使ってきた安定感のある考え方を手放すことは、そう簡単ではありません。この「unlearn」という作業をスムーズに行い、かつ、SQLの集合指向的な考え方を理解するために一番有効な方法は、「HAVING句の使い方を学ぶこと」です。なぜなら、HAVING句はまさにレコードではなく集合レベルで作用する演算子なので、使いこなすためには、否が応でも集合レベルで考えることを要請されるからです。

 というわけで、「HAVING句の力」第2回です。今回も、張り切って円を描きましょう。皆さん、ペンとノートの準備はいいですか?

稼働環境

  • Oracle
  • SQL Server
  • DB2
  • PostgreSQL
  • MySQL(バージョン4.1以上)

対象読者

 HAVING句の基本的な使い方を知っている方。「HAVING句の力」の続編にあたるので、未読の方は先にこちらを読むと理解が増すでしょう。

各隊、総員点呼!

 突然ですが、あなたは今、消防隊(地球防衛隊でもかまいませんが)の統括を行う責任者に任命されたとします。すると早速、司令部へ出動要請の入電が!

 あなたの仕事は、いま現在出動可能な部隊を検索することです。出動可能な条件は、隊のメンバー全員が「待機」状態にあることです。使うテーブルは、次のようなものです。

Teams
member(隊員)team_id(チームID)status(状態)
ジョー 1待機
ケン 1出動中
ミック 1待機
カレン 2出動中
キース 2休暇
ジャン 3待機
ハート 3待機
ディック3待機
ベス 4待機
アレン 5出動中
ロバート5休暇
ケーガン5待機

 このサンプル・データにおいて、出動可能な隊は3チームと4チームです。4チームはベス一人しかいませんが、全員揃っていることに違いはありません。これを求めるクエリを考えましょう。

 「すべてのメンバーの状態が『待機中』である」という条件は、全称量化文ですから、NOT EXISTSを使って書くことができます。

-- 全称文を述語で表現する
SELECT team_id, member
  FROM Teams T1
 WHERE NOT EXISTS
        (SELECT *
           FROM Teams T2
          WHERE T1.team_id = T2.team_id
            AND status <> '待機' );
結果
team_id   member
-------   ----------
      3   ジャン
      3   ハート
      3   ディック
      4   ベス

 これは、次のような全称量化と存在量化の同値変換を利用しています(「量化って何?」という方は「SQLで数列を扱う」(特にコラム「SQLと量化」)を参照)。

 「すべてのメンバーの状態が待機中である」
=「待機中ではないメンバーが一人も存在しない

 このクエリは、パフォーマンスにすぐれ、具体的なチームメンバーも表示できるため情報量が多いという利点を持ちます。ですが、二重否定を使うため、直観的には少し分かりにくいクエリです。HAVING句を使うと、次のような簡単な書き方ができます。

-- 全称文を集合で表現する:その1
SELECT team_id
  FROM Teams
GROUP BY team_id
HAVING COUNT(*) = SUM(CASE WHEN status = '待機'
                           THEN 1
                           ELSE 0 END);
結果:
team_id
-------
      3
      4

 これは素直な肯定文ですから、読みやすくコードも簡潔です。では、このクエリがどういう動作をしているのか、詳しく見ていきましょう。まず、定石に従ってGROUP BY句で元のTeams集合を、チーム単位の部分集合に分割します。

 欲しい集合は、S3とS4です。では、この二つだけが持っていて、他の集合が持っていない性質とは何でしょう。それは、状態が「待機」の行数と、集合全体の行数が一致する、という性質です。これをCASE式によって表現しているわけです。CASE式は、状態が「待機」なら1を、それ以外なら0を返します。いわばこのCASE式は、ある要素(=行)が集合に含まれるかどうかを決める関数を表しているのです。こういう関数を特性関数(characteristic function)、または集合を定義するという意味で定義関数と呼びます。

 ちなみに、HAVING句の条件は次のように書くことも可能です。

-- 全称文を集合で表現する:その2
SELECT team_id
  FROM Teams
GROUP BY team_id
HAVING MAX(status) = '待機'
   AND MIN(status) = '待機';

 このクエリの意味、分かりますか? ある集合について、その要素の最大値と最小値が一致したなら、実はその集合は一種類の値しか含んでいなかった、ということです。複数の値が含まれていたら、最小値と最大値は絶対にズレるはずですから。極値関数は、引数の列のインデックスを利用できるので、こちらの方がパフォーマンスは良いでしょう(値が3種類しかない今回のケースでは、大した意味はありませんが)。

 あるいは集合に対する条件をSELECT句に移して、総員スタンバイか、足りないメンバーがいるかを一覧表示してあげるのも、気が利いています。

-- 総員スタンバイかどうかをチームごとに一覧表示
SELECT team_id,
       CASE WHEN MAX(status) = '待機' AND MIN(status) = '待機'
            THEN '総員スタンバイ'
            ELSE '隊長! メンバーが足りません' END AS status
  FROM Teams
GROUP BY team_id;
team_id    status
-------   --------------------------
     1    隊長! メンバーが足りません
     2    隊長! メンバーが足りません
     3    総員スタンバイ
     4    総員スタンバイ
     5    隊長! メンバーが足りません

 ただし、SELECT句へ条件を移した場合、オプティマイザによる最適化を受けられる可能性は低くなるので、HAVING句よりもパフォーマンスは劣るでしょう。この場合、情報量とパフォーマンスはトレードオフの関係になるのです。


  • LINEで送る
  • このエントリーをはてなブックマークに追加

著者プロフィール

  • ミック(ミック)

    日本では、主にBI/DWHの設計からチューニングまでを専門とするデータベースエンジニアとして活動。2018年より米国シリコンバレーに活動拠点を移し、技術調査とビジネス開発に従事している。 主な著書・訳書: 『達人に学ぶSQL徹底指南書 第2版』(2018) 『SQL実践入門』(2015)...

バックナンバー

連載:達人に学ぶSQL

もっと読む

All contents copyright © 2005-2019 Shoeisha Co., Ltd. All rights reserved. ver.1.5