要件定義の不安をなくす。要件定義の不安をなくす。ユニファのバックエンド分割に学ぶイノベーションメタバース開発に必要な技術の基本知識をおさらい。Boost」など、さまざまなカンファレンスを企画・運営しています。BOOKS(コードジン・ブックス)は、CodeZineの連載からカットアップした、開発現場の課題解決に役立つ書籍シリーズです。KB)HAVING句はSQLの重要な機能のひとつですが、その真価は十分に知られていません。HAVING句もまた、集合指向というSQLの本質を理解するための重要な鍵であり、幅広い応用が可能です。本稿では、HAVING句の使い方を学びながら、「集合単位の操作」という集合指向言語の第二の特性を理解します。2NULLを含まない集合を探す関係除算でバスケット解析おわりに参考資料はじめにSQLというのは変わった言語です。いう印象は人によって差があると思いますが、おそらく最初に手続き型言語を学んだ正統派のプログラマやSEほど強くそう感じると思います。SQLに違和感を感じる理由は、いくつか考えられます。第一に、SQLが「集合指向」という発想に基づいて設計された言語で、この設計方針を持つ言語が少ないことです。第二に、それに劣らず大きいのが、最初に学んだ言語のスキーマ(概念の枠組み)が心理的モデルとして固定され、それを通して世界を見るようになるため、異なるスキーマを持つ言語の理解が妨げられることです。本稿では、HAVING句のさまざまな応用方法を紹介していきますが、その際、手続き型言語とSQLの考え方を比較します。それによって、私たちが手続き型言語で身に付けた無意識の心理的モデルを自覚し、集合指向という発想に感じる違和感を軽減したいと考えています。今回は『自己結合の使い方』に続くシリーズ第2回ということで、サブタイトルに「その2」とつけました。前回の主眼は、「テーブルを集合という抽象度の高い存在とみなす」という集合指向のとっかかりを説明することでした。今回はさらに踏み込んで「集合単位の操作」という特徴に焦点を当てます。前回の内容を前提とするものではありませんが、自己結合も登場するので、未読の方は前回を先に読まれると理解が増すでしょう。ServerDB2PostgreSQLMySQL(サブクエリを使うものについてはバージョン4.1以上)対象読者HAVING句の基本的な使い方は知っているけど、あまり実務で利用していない方。CASE式、自己結合、サブクエリについての知識があると望ましいです。データの歯抜けを探すでは、さっそく例を見ながら進めていくことにしましょう。次のような連番を持つテーブルがあるとします。システムで一意に割り振る数値を使う場合などに多く見かけます。ベンところが「連番」とあるものの、この数列は連続していません。4と7が抜けています。最初の問題はこのテーブルにデータの歯抜けが存在するか否かを調べることです。このサンプルのように数行なら一目瞭然ですが、100万行を目で確認する勇気のある人はいないでしょう。仮にこのテーブルがファイルで、手続き型言語を使って調べるなら、次のような手順になります。連番の昇順か降順にソートする。ループさせて、1行ずつ次の行と連番を比較する。この単純な手順の中にも、手続き型言語とファイルシステムの特徴が浮き彫りになっています。それは、ファイルのレコードは順序を持ち、それを扱うために、言語はソートを行うということです。テーブルの行は順序を持ちませんし、SQLもソートの演算子を持っていません(注1)。代わりにSQLは、複数行をひとまとめにして集合として扱います。従って、テーブル全体をひとつの集合とみなすと、解答は次のようになります。MAX(seq);結果gap-------------'歯抜けあり'このクエリの結果が1行返れば「歯抜けあり」、1行も返らなければ「歯抜けなし」です。COUNT(*)で数えた行数と連番の最大値が一致したなら、それは最初から最後まで抜けなくカウントアップできたという証拠だからです。MAX(seq)」となり、HAVING句の条件が真になります。3行のエレガントな解答です。BY句が存在しません。いう場合、テーブル全体が1行に集約されます。その場合でも、HAVING句は問題なく使えます。BY句と併用しなければならなかったので、今でも時々そう勘違いされていることがありますが、現在の標準SQLではHAVING句を単独で使えます。COUNT(*)」のように集約関数を使う必要があります。これでこのテーブルに歯抜けが存在することが判明しました。今度は、歯抜けの最小値を求めます。最小ときたらMIN関数。いうわけで次のように書きます。INを使ったサブクエリは、ある連番について、それより1つ大きい数値がテーブル内に存在するかどうかを調べるものです。ベン)の行について、次の数が見つからないため、条件が真になります。歯抜けが無い場合は、最大の連番8の次の数である9が得られます。繰り返しになりますが、テーブルはファイルではないので、行に順序がありません(上の「SeqTbl」を昇順で表示しているのは、あくまで見やすくするためです)。それゆえ、こういう行同士の比較を行う際にソートをしません。ところで、SeqTblにもしNULLが含まれていた場合、このクエリの結果は正しくなりません。正しくならないかすぐに理由が分からなかった人は、『3値論理とNULL』を参照してください。BY句はSQLの演算子ではなく、カーソル定義の一部です。BYは、結果を表示する目的には便利だが、それ自体はリレーショナル演算子ではない。15ページより)HAVING句でサブクエリ:最頻値を求める1984年、アメリカのヴァージニア大学は、その年の修辞コミュニケーション学科の卒業生の平均初任給が55,000ドルだと発表しました。当時は1ドル=240円ぐらいと考えると日本円で約1,320万円です。これだけ聞くと、卒業生の多くがかなりの高給取りだという印象を受けます。この数字にはトリックがありました。卒業生には、「大学史上最高の選手」と呼ばれたNBAの新星ラルフ・サンプソンが含まれていたのです。大学が使った卒業生テーブルは、イメージとしては次のような極端な分布のテーブルだったのです。10,000このことから分かるように、単純平均は「外れ値(outlier)」に影響を受けやすいという欠点があります。いうケースでは、集団の傾向をもっと正確に示す指標を使わねばなりません。そのひとつが「最頻値(mode)」です。これは、母集団の中で最も数の多かった値のことです。その意味で「流行値」という呼び名もあります。上の卒業生テーブルだと、20,000と10,000の2つです。これを求める方法を考えてみましょう。DBMSによっては、最頻値を求める独自の関数を用意しているものもあります。標準SQLでも簡単に求められます。考え方は、収入が同じ卒業生をひとまとめにする集合を作り、その集合群から要素数が最も多い集合を探すことです。SQLにとって、こういう集合操作はお手の物です。BYは、もとの集合から部分集合を作る働きをします(前回、ランキングの算出で再帰的な部分集合を作ったことを思い出してください)。BYのキーに使うと、次のような5つの部分集合S1~S5が得られます。収入(income)をキーにしたときの5つの部分集合この中で最大の要素数を持つのは、要素数が3のS3とS5です。したがって、この2つの集合が選択されます。『3値論理とNULL』でも触れたように、ALL述語は、NULLと空集合のケースに気をつければ極値関数で代用できます。今回は「最も多い」ですから、MAX関数を使います。);もし仮に「Graduates」テーブルがファイルで、手続き型言語で最頻値を求めようとするなら、どうなるでしょう。収入でソートした後、1行ずつループさせてコントロールブレイクを行い、同じ収入の行数が前に数えた収入の行数より大きければ、その収入を変数に代入して保存することになるでしょう。見たように、SQLにおいてはループも代入も現れません。HAVING句で自己結合:メジアンを求める平均が信用できない場合、最頻値と並んでよく使われる指標にメジアン(中央値、中位数)があります。これは、母集合のデータを昇順に並べてちょうど中央に来た値です。データの個数が偶数の場合は中央の2つの数の平均をとります。先の「Graduates」テーブルは10行なので、(スミス、20,000)と(ロレンス、15,000)の中間、17,500がメジアンになります。これをSQLで求めるにはどうすればよいでしょう。ソートして端から数えるという手続き的な方法は、もちろんとりません。集合指向的に考えた場合、ある値がちょうど母集合の中央にくるということはどうすれば分かるでしょうか?それには、母集合を上位と下位の2つの集合に分割し、しかもその際、2つの集合が真ん中の値を共有するようにしておくことです。すれば、共通部分の平均がメジアンになります。イメージとしては、下図のようになります。メジアンの考え方このように大小関係に基づいて部分集合を作るとなれば、自己非等値結合の出番です。2」の比較条件に「=」を付けることで、S1とS2をスッパリ切り分けるのではなく、わざと共通部分を持つようにすることです。2」とすると、データ数が偶数の場合にS1とS2が共通部分を持たなくなるケースが生じてしまい、メジアンが得られません。データ数が奇数であることが分かっている場合、FROM句のサブクエリは値をひとつしか持たないので、外側のAVG関数は不要です。偶数の場合を考慮した汎用的なクエリを考えるなら、AVG関数が必要になります。CASE式、自己結合、そしてHAVING句というSQLの持てる武器を駆使した、見事な解答です。帰ってきたHAVING句SQLで集合演算SQLで数列を扱うもっと読むこの記事の著者ミック(ミック)日本では、主にBI/DWHの設計からチューニングまでを専門とするデータベースエンジニアとして活動。2018年より米国シリコンバレーに活動拠点を移し、技術調査とビジネス開発に従事している。3.12」の最初のベータ版がリリース新着記事一覧を見るイベント今後の予定を見るCodeZineは、株式会社翔泳社が運営するソフトウェア開発者向けのWebメディアです。「デベロッパーの成長と課題解決に貢献するメディア」をコンセプトに、現場で役立つ最新情報を日々お届けします。イラストの無断転載を禁じます。記載されているロゴ、システム名、製品名は各社及び商標権者の登録商標あるいは商標です。