SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

達人に学ぶSQL

帰ってきたHAVING句

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


  • X ポスト
  • このエントリーをはてなブックマークに追加

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

  • X ポスト
  • このエントリーをはてなブックマークに追加

はじめに

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句よりもパフォーマンスは劣るでしょう。この場合、情報量とパフォーマンスはトレードオフの関係になるのです。

次のページ
一意集合と多重集合

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
達人に学ぶSQL連載記事一覧

もっと読む

この記事の著者

ミック(ミック)

日本では、主にBI/DWHの設計からチューニングまでを専門とするデータベースエンジニアとして活動。2018年より米国シリコンバレーに活動拠点を移し、技術調査とビジネス開発に従事している。主な著書・訳書:『達人に学ぶSQL徹底指南書 第2版』(2018)『SQL実践入門』(2015)Joe Celko『プログラマのためのSQL 第4版』(2015)翔泳社 - 著者ページ:https://www.shoeisha.co.jp/book/author/3964著者個人ページ:http://mickindex.sakura.ne.jp/

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/1420 2007/10/09 13:04

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング