SQL緊急救命室

第5回時代錯誤症候群~進化し続けるSQLに取り残されるな!

ここはとある街の総合病院。

ここには通常の診療科のほかに、一風変わった診療科が存在する。

何軒もの病院をたらいまわしにされた、手の施しようのないSQLや、今すぐに改善が必要なSQLが担ぎ込まれる救命室である。

それがSQL緊急救命室、略してSER(SQL Emergency Room⁠⁠。

そう、ここは国内でも唯一のプログラミング専門外来である。

ロバート

救命室部長。腕の立つエンジニアだが、口が悪く性格はもっと悪い四十オヤジ。

時代遅れという名の罪

(PM3:00 休憩室。ロバート、ヘレン、ワイリーがそろっている)

そろそろ専門の希望を出す時期ね。もうどこにするか考えた?


はい。救命室に決めました。やりがいありますし。


?!


そ、そうだな。まあおもしろい仕事ではあるな。だが専門を選ぶときは慎重に自分の適性を見極めなければならんぞ、うむ。

はい。体力には自信があります。救命室は最もタフな専門ですから、自分の力を試したくて。

(このオタンコナスが……。)


(ロバートの下で潰れなかった初めての学生だからタフなのは認めるけど……。)大事な選択よ。ほかの選択肢もよく吟味して決めたほうがいいんじゃない?

はあ……でももう志望書提出しちゃいましたし……。


( ゚д゚)


あ、救急車来たみたいですよ。治療室に行きましょう。


お、おう……。


そうね……。


いやー楽しみだなあ。来年も頑張るぞー。


ふう……。


繰り返されるサブクエリ

これがカルテです。


カルテ1:ある部品の供給業者の一覧を管理するSuppliersテーブル図1を考える。今、各供給業者の出荷品数の影響度を都市(city)別に調べたい。そのため、出荷が可能か不可能かに応じて、それぞれの都市からの出荷品数の50%以上を占める供給業者を選択したい。求める結果は図2のようになる。

※)今回のコードはPostgreSQLで動作確認をしています。Oracle、SQL Server、DB2ともに動作します。MySQLには未実装の機能が含まれています。
図1 Suppliersテーブル
図1 Suppliersテーブル
図2 求める結果
 sup | city | ship_flg | item_cnt
-----+------+----------+----------
B    | 東京 | 不可     |       30
C    | 東京 | 可       |       40
E    | 大阪 | 不可     |       40
I    | 大阪 | 可       |       30

ちょっと複雑な業務仕様ですね。


在庫調整をする際に、出荷が停止になると影響の大きい業者を割り出そうという目的だな。現に今出荷が不可能な業者については、改善効果の大きな業者がわかる。

都市別の出荷可能品数と不可能品数は、それぞれの業者の取り扱い品数を合計すればよいから、図3のように整理できるわね。

図3 都市別の出荷可能品数と不可能品数
図3 都市別の出荷可能品数と不可能品数

……(黙々と何か書いている)できました!リスト1これはなかなか難しかった!

リスト1 ワイリーの解
SELECT SP.sup, -- 出荷可能業者のパート
       SP.city,
       SP.ship_flg,
       SP.item_cnt
  FROM Suppliers SP
       INNER JOIN
          (SELECT city,                       
                  SUM(item_cnt) AS able_cnt   
             FROM Suppliers                   
            WHERE ship_flg = '可'             │─地域別の出荷可能品数
            GROUP BY city) SUM_ITEM           
         ON SP.city = SUM_ITEM.city
        AND SP.item_cnt >= (SUM_ITEM.able_cnt * 0.5)
 WHERE SP.ship_flg = '可'
UNION ALL
SELECT SP.sup, -- 出荷不可能業者のパート
       SP.city,
       SP.ship_flg,
       SP.item_cnt
  FROM Suppliers SP
       INNER JOIN
          (SELECT city,                        
                  SUM(item_cnt) AS disable_cnt 
             FROM Suppliers                    
            WHERE ship_flg = '不可'             │―地域別の出荷不可能品数
            GROUP BY city) SUM_ITEM            
         ON SP.city = SUM_ITEM.city
        AND SP.item_cnt >= (SUM_ITEM.disable_cnt * 0.5)
 WHERE SP.ship_flg = '不可';

え、もう?


お前の解はいつも期待を裏切らないな。


先生にそう言っていただけると。はい。


誉めとらんわ! だいたいこの程度のことをするのにこのコードは長過ぎるぞ。美的センスのカケラもない。

先生にセンスのことでとやかく言われたくないですね。見た目が不恰好でも良いコードだってあるでしょう。

ない。SQLにおいて見た目は大事だ。シンプルさは正義であり力だ。

おおっ、なんか最近お亡くなりになったカリスマのようなセリフ……。


ワイリーの解は、出荷可能な業者と出荷不可能な業者に処理を分割してから、その結果をUNION ALLでマージするというものです。これは、非常に自然な考え方に沿った解ですが、その結果、ほとんど同じ処理を2回繰り返すことになっています。実際、UNION ALLの前段と後段で異なるのは、出荷可能フラグ(ship_flg)「可」「不可」というパラメータだけで、文としての構造は同じです。この冗長さを治療する方法を、今から考えていきます。まずは、2つも使っているサブクエリSUM_ITEMから対処しましょう。

共通表式

1つのSQLの中で同じサブクエリを何度も参照するようなケースでは、共通表式を使うことによって冗長な記述を簡潔に削減できるわ。リスト2のように書き換えるの。

リスト2 ヘレンの解:共通表式を利用
WITH SUM_ITEM AS (                                      
         SELECT city,                                   
                SUM(CASE WHEN ship_flg = '可'           
                         THEN item_cnt                  
                         ELSE NULL END) AS able_cnt,    
                SUM(CASE WHEN ship_flg = '不可'          
                         THEN item_cnt                  
                         ELSE NULL END) AS disable_cnt  
           FROM Suppliers                               │―共通表式
          GROUP BY city)                                
SELECT SP.sup,
       SP.city,
       SP.ship_flg,
       SP.item_cnt
  FROM Suppliers SP
       INNER JOIN SUM_ITEM
          ON SP.city = SUM_ITEM.city
         AND SP.item_cnt >= (SUM_ITEM.able_cnt * 0.5)
 WHERE SP.ship_flg = '可'
UNION ALL
SELECT SP.sup,
       SP.city,
       SP.ship_flg,
       SP.item_cnt
  FROM Suppliers SP
         INNER JOIN SUM_ITEM
            ON SP.city = SUM_ITEM.city
           AND SP.item_cnt >= (SUM_ITEM.disable_cnt * 0.5)
WHERE SP.ship_flg = '不可';

うむ。SQLには元来、⁠変数」という概念がない。そのため、コードの中で使いまわしたいレコード集合があっても、従来のSQLではそのたびにサブクエリを記述する必要があった。しかし、SQL:1999[1]で取り入れられた共通表式を使うことで、SQL内ならサブクエリをどこでも利用できる一種のグローバル変数として定義できるようになった。

これはすっきりしますね。1つ質問なんですが、共通表式を使ったときの実行計画はどうなるのでしょう。サブクエリをFROM句に記述する場合、その一つ一つが展開される計画になりますよね。共通表式にすると、アクセスも1回にまとめられるのでしょうか。だとしたらパフォーマンス上のI/Oコスト削減効果もすごいですね。

それは実装依存ね。優秀なオプティマイザなら、共通表式の結果を一時的な中間表として保持することでSuppliersテーブルへのアクセス回数を節約することを考えるでしょうね。でも、すべてのDBMSで期待できるかどうかは断言できないわ。

ロバートも言うとおり、共通表式は一種の変数です。SQL内限定のビュー(View)だと言ってもよいでしょう。特に他人の書いたSQLを読むときは、FROM句の中に大きなサブクエリが含まれていると、自分がどこを追っていたのかわからなくなり迷ってしまうことがよくあります。サブクエリが入れ子になっているときなどは特にそうです。共通表式は、そうした複雑なSQL文の構造を明快にできる機能です。

CASE式

ところでワイリー、共通表式で改善した解でも、まだ十分とは言えない。このコードを見て何か思い出すことはないか?

UNION ALL……ですかね。


そうだ。お前のクエリは、冗長性症候群でもある。この分岐をCASE式で表現しなおせば、さらに簡潔になるリスト3⁠。

リスト3 ロバートの解:分岐をCASE式で表現
WITH SUM_ITEM AS (
         SELECT city,
                SUM(CASE WHEN ship_flg = '可'
                         THEN item_cnt
                         ELSE NULL END) AS able_cnt,
                SUM(CASE WHEN ship_flg = '不可'
                         THEN item_cnt
                         ELSE NULL END) AS disable_cnt
           FROM Suppliers
          GROUP BY city)
SELECT SP.sup,
       SP.city,
       SP.ship_flg,
       SP.item_cnt
  FROM Suppliers SP
       INNER JOIN SUM_ITEM
          ON SP.city = SUM_ITEM.city
         AND SP.item_cnt >= CASE WHEN SP.ship_flg = '可' THEN SUM_ITEM.able_cnt       
                                 WHEN SP.ship_flg = '不可' THEN SUM_ITEM.disable_cnt  
                                 ELSE NULL END * 0.5;                                

第2回で取り上げた冗長性症候群を覚えているでしょうか。SQL:1992でCASE式が導入されるまで、UNION ALLを使って分岐を表現するのは、SQLにとって当然の方法でしたし、これは手続き型の発想で多くのプログラマにとって理解しやすいものであるため、CASE式の標準化から20年近く経過するにもかかわらず、現在でも多く見かける症状です。

そうか。条件によって比較の列を切り替えればよかったのか。このクエリだと、もうサブクエリSUM_ITEMも一度しかアクセスしませんよね。

うむ。だが今回のように中で集約を行っていたり、比較的大きなサブクエリの場合は、やはり可読性の観点から共通表式を使うことに意味がある。

年輩のDBエンジニアには、CASE式ではなく、DECODE(Oracle)やIF(MySQL)といった実装依存の関数を使い慣れている人もいるけど、CASE式に比べて表現力が弱いし移植性もないので、これらも使うべきでないわ。SQLは改訂のたびに劇的に構文が変わっていくので、時代錯誤症候群は致命的な病気よ。

今から新しく覚える僕は勝ち組ってことですか。


ふ、数年後にはあなたも時代遅れの仲間入りよ。常に勉強する努力が嫌いなら、この救命室には来ないほうがいいわ。

ぐぬぬ。


言語の進化とエンジニアの進化

さてここで少し、SQLの新機能がどのように追加されていくのかを説明しておこうと思います。ご存じの方もいるかもしれませんが、SQLには国際標準規格が定められていて、かつては米国のANSI、現在ではISOInternational Organization for Standardization国際標準化機構)が数年に一度のペースで改訂を行っています。その標準規格は「SQL:xxxx」⁠xxxxは改訂年)という名称で管理されており、最近ではSQL:1999、SQL:2003、SQL:2008といった規格が定められています表1注2⁠。

表1 直近3回の標準SQL改訂とDBMSでの対応状況(○=対応、△=制限あり、×=非対応)
名称主な機能追加Oracle
11gR2
SQLServer
2008 R2
DB2
V9.7
PostgreSQL
9.1
MySQL
5.5
1999SQL:1999再帰クエリ
トリガー
正規表現
[4]
非スカラ型
[1]

[2]

[5]

[6]
OLAP機能(ROLLUP、CUBE、GROUPING SETS)×
[7]
共通表式×
2003SQL:2003XML機能×
ウィンドウ関数×
シーケンスオブジェクト×
[3]
×
MERGE文×
[8]
オートナンバー型×
2008SQL:2008TRUNCATE文のサポート
INSTEAD OF トリガ×

※1)PL/SQLでのみ配列型、レコード型など使用可能
※2)ユーザ定義型として利用可能
※3)SQL Server 2012でサポート予定
※4)組み込み関数としては存在しない。ユーザ定義関数として定義可能
※5)SQL PLでのみ配列型、レコード型など使用可能
※6)ENUM、SETなどを使用可能
※7)ROLLUPのみ使用可能
※8)INSERT……ON DUPLICATE KEY UPDATEという独自構文として利用可能


改訂の内容は、基本的には機能追加です。一部廃止されたり非推奨になる機能も含まれていますが、一般的にDBMSの開発元はある程度の後方互換をサポートするため、ここで廃止されてもすぐにDBMSで使えなくなるわけではありません(徐々に廃止はされていくでしょうが⁠⁠。

新規追加された機能についても、そのサポートの進展はDBMSによってばらつきがあります。たとえば、本稿でも取り上げている共通表式やウィンドウ関数など、SQLコーディングの可能性を大きく広げる機能一つ取ってみても、PostgreSQLがウィンドウ関数をサポートしたのは8.4と最近ですし、MySQLはまだ未サポートの状態です。他方、Oracle、DB2、SQLServerなどは比較的早く改訂内容を取り入れる傾向にあります。

表1を見ていただいてもわかるように、1999年、2003年とSQLの機能追加はかなり大々的に行われており、どれ一つをとっても旧来のコーディングスタイルを大きく変えるものです[3]⁠。これはつまり、SQLは現在も活発に言語仕様が拡張されている言語であり、新しい機能をどんどん活用していかないと損だ、ということです。

SQLは寿命の長い言語か?

40年間主流であり続けた

SQLは寿命の長い言語だとよく言われます。たしかに、アプリケーションの実装言語が比較的早いサイクルで栄枯盛衰を繰り返すのに対して、SQLはリレーショナルデータベースの誕生からほぼ40年間、データベース操作言語の主流であり続けました。その理由は、リレーショナルデータベースが存在している以上はセットで存在しているから、というのもあるのですが、SQLが本当に非効率的な言語であるなら、もっと別の(おそらくは手続き型の)言語によって取って代わられてもおかしくなかったはずです。

自らを大きく変化させてきたSQL

実はSQLの場合、同じ「SQL」という名前で呼ばれていますが、その中では激しい新陳代謝が起こっているのです。たとえとして適切かどうかちょっと自信がないのですが、同じバンド名でありながら、構成メンバーはどんどん入れ替わっているような……それに似たところが、SQLにはあります。

その大きな理由は、かつてのSQLが実際に機能不足の言語だったことです。⁠SQLは⁠Scarcely Qualifies as a Language⁠[4]⁠言語としての資格に欠ける)の略だ⁠⁠─⁠─これは、かつて言われていた皮肉混じりのジョークです。かなり昔のネタなので、知っている人はけっこうベテランなのではと思いますが、そういう悪口を言われるぐらい、昔のSQLは機能不足でした。

そのため、SQLの進化は、基本構文そのものを捨てることで進んできました。このことは、相関サブクエリがウィンドウ関数によって、サブクエリが共通表式によって、UPDATEとINSERTがMERGE文によって置き換えられてきたことを見ても明らかです。SQLの言語拡張は、古い構文を捨てていくことによって進むのです。

これは、手続き型言語の進化のしかたと大きく異なります。手続き型言語の場合、ひととおりの基本構文を覚えれば、そのあとの言語拡張はほぼ純粋な機能追加という形で行われるため、基本構文のかなりの部分は、継続的に使える知識になります。その代わり、まったく別の新しい言語によって取って代わられることが頻繁に起きます。

こうしたことから、エンジニアにとって、SQLは勉強のコストがかかる言語だと言えます。しかしそれは、古い非効率的なコードを新しいコードに置き換えられるチャンスがある、ということでもあります[5]⁠。

見た目上の「寿命」が長いように見えたとしても、その内実はなかなか複雑な人生(言語生?)を送っているのです。

時代錯誤症候群は冗長性症候群を併発する

次の患者も、SQLではシンプルさが正義であることがよくわかる。新機能の力も発揮できる。


カルテ2:図4のような製造業者のテーブルManufacturersを追加する。部品の要求をかけている製造業者と同じ地域(=都市および地区が同じ)に存在する供給業者を、先のSuppliersテーブル(図1)から選択したい。もちろん、出荷が可能な供給業者に限る。

図4 Manufacturersテーブル
図4 Manufacturersテーブル

冗長さはコードをわかりにくくする

部品を要求している製造業者は、a、b、c、fの4つ。うち、⁠東京、江戸川区」のcと、⁠大阪、福島区」のfについては、マッチする供給業者がいない、と。すると結果は、図5のようになればいいですね。ようしリスト4、図6⁠。

図5 求める結果
sup | city | area
----+------+--------
A   | 東京 | 北区
C   | 東京 | 荒川区
リスト4 ワイリーの解(結果が正しくない⁠⁠ G | 大阪 | 北区
SELECT sup,
       city,
       area
  FROM Suppliers
 WHERE ship_flg = '可'
   AND city IN (SELECT city
                  FROM Manufacturers
                 WHERE req_flg = '要')
   AND area IN (SELECT area
                  FROM Manufacturers
                  WHERE req_flg = '要');
図6 リスト4の実行結果
sup | city | area
----+------+--------
A   | 東京 | 北区
C   | 東京 | 荒川区
G   | 大阪 | 北区

お前の解は、効率以前に結果が違うぞ。


あれ……本当だ。余計な「大阪、北区」の供給業者Gが入ってしまっている。製造業者eは部品を要求していないのに。

あなたの解は、こういう動きをするの。最初のINのサブクエリで部品を要求している製造業者の都市として、東京と大阪が選ばれる。次のINのサブクエリでは、部品を要求している地区として、北区、荒川区、江戸川区、福島区が選ばれる。すると結局、条件に合致する地域の組み合わせはこうなるの。

  • 東京 - 北区

  • 東京 - 荒川区

  • 東京 - 江戸川区

  • 東京 - 福島区 ←ありえない組み合わせ

  • 大阪 - 北区 ← 要求フラグが不要になっているため、論理的にありえない組み合わせ

  • 大阪 - 荒川区 ←ありえない組み合わせ

  • 大阪 - 江戸川区 ←ありえない組み合わせ

  • 大阪 - 福島区

ああっ、それで「大阪、北区」という条件に合致する供給業者Gが選ばれてしまったのか。偶然、東京と大阪に「北区」という同じ名前の区があったから。

比較できるのは列だけではない

そういうこと。いい? この問題では、⁠都市、地区」はこれで一続きのキーとして扱うべきなの。あなたみたいに分離したらダメリスト5⁠。city もareaも文字列型の値だから、連結演算子で結合してしまえば、これで1列のキーとして扱えるようになるわ[6]⁠。

リスト5 ヘレンの解:複数の列を連結して一つのキーとして扱う
SELECT sup,
       city,
       area
  FROM Suppliers
 WHERE ship_flg = '可'
   AND city || area IN (SELECT city || area
                          FROM Manufacturers
                         WHERE req_flg = '要');

うむ。そしてここからがようやく本題だ。リスト5はさらにリスト6のように書き換えるのが正しい。これはSQL-92で標準化された行式(row expression)だ。列と列の比較を複数列の比較に一般化したもので、原理的には比較演算子全体に(=、、>)拡張できる。実装されているかどうかはまた別問題だが[7]⁠。

リスト6 ロバートの解:行式を利用
SELECT sup,
       city,
       area
  FROM Suppliers
 WHERE ship_flg = '可'
   AND (city, area) IN (SELECT city, area
                          FROM Manufacturers
                         WHERE req_flg = '要');

なるほど。しかしこれってヘレンさんの解(リスト5)から書き換えるメリットって何なんでしょう? 実質的に同じことをやっているように見えるのですが。

メリットは大きいぞ。まず左辺の列に連結演算子を使わなくてよいため、インデックスの利用が期待できる。そして列のデータ型が文字列型以外でも汎用的に使える。比較したい列が数値型や日付型の場合、連結子を使うには型変換を使わねばならない。

ああ、そういうことか!


SQLの標準化の中で最も画期的だったのは、SQL-92(1992年改訂)です。もう20年近く前の話ですが、ここで導入された機能には重要なものが多く含まれています。例を挙げれば、本稿で取り上げた行式やCASE式、ほかにも日付型やトランザクション分離レベル、一時表なども導入されました。⁠ということはそれ以前には、そうした機能がなかったの?!」と驚く人もいると思います。昔のSQLが「欠陥言語」と悪口を叩かれていた理由がおわかりいただけるでしょう。これを言うと年輩の方から怒られてしまうかもしれませんが、SQLに悪いイメージを持っている人は、えてしてSQL-92以前のイメージで時が止まってしまっています

また、ロバートが言うように、SQLに追加される新機能を観察していると、そこには単純さの原則が存在していることが見て取れます。かつては難しい構文を使わなければ実現できなかった処理を、ずっと簡略的で、直観的にわかりやすく記述できるように進歩してきています。UNIONを連ねなければできなかった分岐はCASE式で、同じくUNIONを使っていた完全外部結合はFULL OUTER JOINで、複雑な相関サブクエリはウィンドウ関数で、簡単に書けるようになりました。かつ、SQLの長所は、そうした新機能のほうが概してパフォーマンスも優れた実行計画が立てられることです。利用しない手はありません。

良い新機能と悪い新機能

ほかにも覚えておいたほうがいい新機能ってあります?


そうね、まずはこれまでも何度か治療に使ってきたウィンドウ関数[8]⁠。これは自己結合や相関サブクエリを省略できる最高のツールね。それに、GROUP BY句の集計機能を拡張したCUBE/ROLLUPオプション。これは小計や合計のレコード(超集合行)も出力するもので、UNIONを省略できてとても便利よ。CASE式と組み合わせたときの効果は絶大だわ。

あとは新機能というわけではないけど、結合の構文は可読性と保守性の観点から、実装依存の方言ではなく標準化されたものを使うべきね。DBMSベンダーもそれを推奨しているわ。

逆に、新機能だからというだけで飛びついてはいけないものもある。


えっ、そうなんですか。


たとえばSQL:1999で導入された非スカラ型(配列型⁠⁠。これを安易に使うのは危険だ。関係モデルの大原則である第1正規形[9]を崩すことになるうえ、DBとインタフェースを持つアプリケーション側も対応が必要で、実装のサポートもあまり進んでおらず移植性にも難がある[10]⁠。

標準化といえど完璧ではないんですね。


そうだな。SQLは言語の理論的基礎がかっちりしているから、あまり無軌道な拡張に向いたキャラではないのだろう。それに、標準といえど人間が決めることだ。政治もあれば間違いもある。完璧などありえんさ。……ところで、人間の決めることといえば、お前の専門についてだがな……。

あ、このあと飲み会なんで、今日はこれで失礼します。いやー今日も勉強になりました。僕も来年はお二人みたいにバリバリやるぞー。

お、おう。お疲れ。


普段は威張ってるくせに、ロバートはこういうとき頼りにならないんだから、もう。

【参考資料】

1.ミック WEB+DB PRESS Vol.63 連載「SQL緊急救命室」第2回「冗長性症候群」
冗長なSQLコーディングの代表格が、条件分岐をUNION(ALL)で表現してしまうことでした。これをCASE式で簡潔に記述する方法を紹介しています。gihyo.jpでも公開しております。
2.ミック『SQL ゼロからはじめるデータベース操作』翔泳社、2010年
外部結合の標準構文、CUBE/ROLLUP、ウィンドウ関数、CASE式といったSQLの重要な機能の使い方の基礎を解説した書籍です。SQLを基礎から正しく学びたい方にお勧めです。
3.Wikipedia - SQL - Standardization
SQLの標準化は数年単位で行われています。各改訂の内容は、上記Wikipediaのサイトからたどるのが調べやすいでしょう。

おすすめ記事

記事・ニュース一覧