SQL緊急救命室

第2回冗長性症候群~条件分岐をUNIONで表現するなかれ

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

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

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

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

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

ロバート

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

SQLにおける条件分岐

(AM10:00 休憩室。ワイリーが机に向かって一人で何かしている)

どってぃろーんどってぃろーん、ぽぽぽんぽーん、どってぃろーん…


(休憩室のドアを開けて)あら、鼻歌交じりで、ご機嫌ね。


ああ、どうも。うふふ、今日はロバート先生、用事で遅れるそうです。


なるほど、束の間の休息ね。それじゃ鼻歌の一つも飛び出すわけだわ。

そういうことです。どってぃろーん…


(変なメロディー…)ところでワイリー、あなた何やってるの?


え? ああ、これは大学の課題。今日が締め切りなんです。


UNIONで条件分岐するのは正しいか

ふうん、どれどれ…。

問1:商品を管理する図1のようなテーブルItemsが存在する。各商品について、税抜き価格(外税⁠⁠/税込み価格(内税)の両方を保持している。2002年から、法改正によって価格表示に税込み価格(内税)を表示することが義務付けられた。そこで、2001年までは税抜き価格を、2002年からは税込み価格を「価格」列として表示する結果(図1の色のついてない部分)を求めたい図2⁠。

図1 Itemsテーブル
図1 Itemsテーブル
図2 求めるべき結果
item_name| year | price
---------+------+-------
カップ   | 2000 | 500
カップ   | 2001 | 520
カップ   | 2002 | 630
カップ   | 2003 | 630
スプーン | 2000 | 500
スプーン | 2001 | 500
スプーン | 2002 | 525
スプーン | 2003 | 525
ナイフ   | 2000 | 600
ナイフ   | 2001 | 550
ナイフ   | 2002 | 577
ナイフ   | 2003 | 420

条件分岐問題の基礎ね。year 列の値を分岐の条件に使う、と。それで、あなたの回答は?

これです。はい!リスト1⁠ この解のポイントはですね、UNIONの代わりにUNION ALLを使うことでソートを回避して性能改善も図っていることです[1]⁠。条件が排他的だから問題ないわけです。

リスト1 問1に対するワイリーの解答
SELECT item_name, year, price_tax_ex AS price
  FROM Items
 WHERE year <= 2001
UNION ALL
SELECT item_name, year, price_tax_in AS price
  FROM Items
 WHERE year >= 2002;

イタタた…。


足の小指でもぶつけました?


いや、そうじゃなくて、あなたの解を見てアタマ痛くなったの! もう、先が思いやられるわ…。

UNIONを使うと実行計画が冗長になる

ヘレンがなぜ頭痛を覚えてしまったのか、詳しく見ていきましょう。

ワイリーの解は、機能的には問題ありません。正しい結果を得られるクエリになっています。問題は、一言で言うと冗長であることです。ほとんど同じ中身の2つのクエリを2 度実行しているからです。これは、SQLを無駄に長くして読みにくくするだけでなく、パフォーマンス上も無駄です。Oracle およびPostgreSQLで実行計画を見てみましょう図3、図4⁠。

図3 ワイリーの実行計画(Oracle)
----------------------------------------------------------------------------
| Id | Operation             | Name  | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT      |       |   13 |   611 |      6 (50)| 00:00:01 |
|  1 |  UNION-ALL            |       |      |       |            |          |
|* 2 |   TABLE ACCESS FULL | ITEMS |    7 |   329 |       3 (0)| 00:00:01 |
|* 3 |   TABLE ACCESS FULL | ITEMS |    6 |   282 |       3 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("YEAR"<=2001)
   3 - filter("YEAR">=2002)
図4 ワイリーの実行計画(PostgreSQL)
                       QUERY PLAN
------------------------------------------------------------
Append (cost=0.00..2.42 rows=12 width=47)
  -> Seq Scan on items (cost=0.00..1.15 rows=6 width=47)
          Filter: (year <= 2001)
  -> Seq Scan on items (cost=0.00..1.15 rows=6 width=47)
          Filter: (year >= 2002)

このように、ワイリーの解はItemsテーブルに対して2度のアクセスを実行していることがわかります。

これは大きな無駄です。シーケンシャルスキャンのコストはデータ量に線形に伸びていきます。

UNIONは便利な道具です。簡単にレコード集合をマージできるため、ともするとこれを条件分岐のためのツールとして使いたい誘惑に駆られます。しかし、これは危険思想です。ワイリーのように、安易にSELECT文全体を連ねて冗長なコードを記述したくなる心的傾向を冗長性症候群と呼ぶことにしましょう。

SQLにおける正しい条件分岐の書き方がどうなるか、ヘレンにお手本を見せてもらいましょう。

WHERE句で分岐させるのは素人

いい? SQLを使ううえで、条件分岐をWHERE句で行うのは素人のやることよ。プロはSELECT句で分岐させるリスト2⁠。

リスト2 問1に対するヘレンの解答
画像

ああ、またCASE式だ。これ使いこなせないんだよなあ。


もし「この問題を手続き型言語で解いたら?」と考えたとき、if文を使う個所があれば、それをSQLに翻訳したらCASE式を使う、と思うことね。

なるほど…。でもUNIONって、この前第1回のサブクエリと同じで問題を分割して考えられて便利だから、つい使っちゃうんです。

そのモジュール的思考を脱しない限り、SQLは上達しないわ。


うっ、厳しい。


SELECT句で分岐させると実行計画もすっきり

ヘレンの解の実行計画を見てみましょう図5、図6⁠。Itemsテーブルへのアクセスが1回に節約できていることがわかります。これは大雑把に言えば、ワイリーの解よりパフォーマンスが2倍向上することを意味します。

図5 ヘレンの実行計画(Oracle)

--------------------------------------------------------------------------
| Id | Operation            | Name  | Rows | Bytes | Cost (%CPU)|     Time |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT     |       |   12 |   564 |       3 (0)| 00:00:01 |
|  1 |  TABLE ACCESS FULL | ITEMS |   12 |   564 |       3 (0)| 00:00:01 |
--------------------------------------------------------------------------
図6 ヘレンの実行計画(PostgreSQL)
                     QUERY PLAN
-------------------------------------------------------
Seq Scan on items (cost=0.00..1.18 rows=12 width=51)

SQLのコードの良し悪しは、必ず実行計画レベルで判断しなければなりません。これは、本来はあまり良いことではありません。⁠ユーザがデータへのアクセスパスという物理レベルの問題を意識しなくてもよいようにしたい」というのがRDBとSQLが成し遂げようとした野望だったからです。でも、その野望を遂げるにはRDBとSQLはまだ非力なので、結果として中途半端に隠蔽(いんぺい)されたアクセスパスを、エンジニアがチェックする必要が残っているのです。

集計における条件分岐

(ワイリーとヘレンが話していると、ロバートが入ってくる)

遅れたな。なんだ、患者か?


患者といえば患者ね。ワイリーだけど。


う、酒くさっ。用事ってただの二日酔いじゃないですか。


細かいこと気にするな。どれ、見せてみろ…うっぷ、おまえ…!


ああ、いえ、今たっぷりヘレンさんから教わったところです、ハイ。


これだけじゃないだろう。次の問題も同じ間違え方してるじゃないか。

ああっ見ないで。あとで直そうと思ってたのに。


問2:都道府県別、男女それぞれの人口を記録するPopulationテーブルがある図7⁠。このテーブルから、図8のようにレイアウトを変更した結果を出力する方法を考えよ。性別「1」は男性、⁠2」は女性を意味するものとする。

図7 Itemsテーブル
図7 Itemsテーブル
図8 求める結果
prefecture | pop_men | pop_wom
-----------+---------+---------
香川       |      90 |     100
高知       |     100 |     100
徳島       |      60 |      40
愛媛       |     100 |      50
福岡       |      20 |     200

pop_menは男性の人口、pop_womは女性の人口

見ないでって言ったのに…リスト3⁠。


リスト3 問2に対するワイリーの解答
SELECT prefecture, SUM(pop_men) AS pop_men, SUM(pop_wom) AS pop_wom
  FROM ( SELECT prefecture, pop AS pop_men, null AS pop_wom ――┐
           FROM Population                                    
          WHERE sex = '1' --男性 ―――――――――――――――――――――――――――――─┘
          UNION
         SELECT prefecture, NULL AS pop_men, pop AS pop_wom ――┐
           FROM Population                                    
          WHERE sex = '2') TMP --女性 ―――――――――――――――――――――――――┘
 GROUP BY prefecture;  ――――③

…。


…。


…2人とも、場が重くなるんで黙り込まないでもらえます?


お前の白衣の中にゲロをぶちまけたい気分だ。


冗談でもそういうこと言わないでください。


さっきも言ったでしょう、条件分岐はSELECT句で行うものだ、って。正しい解はこうよリスト4⁠。


リスト4 問2に対するヘレンの解答
SELECT prefecture,
       SUM(CASE WHEN sex = '1' THEN pop ELSE 0 END) AS pop_men,
       SUM(CASE WHEN sex = '2' THEN pop ELSE 0 END) AS pop_wom
 FROM Population
GROUP BY prefecture;

集計における条件分岐もやっぱりCASE式

この問題は、CASE式の応用方法として有名な表側・表頭[2]のレイアウト変換です。本来、SQLはこういう結果のフォーマッティングを目的とした言語ではないのですが、割と実務で使う機会の多いワザなので覚えておきましょう。

ワイリーは、単純に1回目の検索で男性の人口列を作り(リスト3⁠、2 回目の検索で女性の人口列を作ればよい(リスト3⁠、と考えたのですが、それだけだと図9のように男性の人口と女性の人口が異なるレコードとして現れてしまいます。

図9 男性と女性の人口が分かれて表示
prefecture | pop_men | pop_wom
-----------+---------+---------
徳島       |      60 |
徳島       |         |      40
香川       |      90 |
香川       |         |     100
愛媛       |     100 |
愛媛       |         |      50
高知       |     100 |
高知       |         |     100
福岡       |      20 |
福岡       |         |     200

これを県単位に1 行に集約する必要があるため、⁠GROUP BY prefecture」⁠リスト3を追加しているわけです。これは一苦労なコードです。ですが、ヘレンの解が示すように、CASEをSELECT句で使い、男性の人口と女性の人口の列を作ってしまえば、Populationテーブルに2度もアクセスする必要はないのです。

(病院の外からサイレンが聞こえる。救急車が到着したようだ)

ほ、ほら、急患ですよ。こりゃたいへんだ。急がないと!


あ、こら! 待て!


集約の結果に対する条件分岐

(手術室に患者が運ばれてくる。患者を見た3人が驚いたことに…)

あら、これはまたタイムリーな…。この患者も冗長性症候群ね。


しかも重症だ。良かったな、ワイリー。下には下がいて。


…はい、カルテ。


カルテ:社員とその所属するチームを管理するテーブルEmployeesがある図10⁠。ここから、次の条件に応じて結果を取得したい。

  • 所属するチームが1つだけの社員は、1列にそのチーム名を表示する
  • 所属するチームが2つの社員は、⁠2つを兼務」という文字列を表示する
  • 所属するチームが3つ以上の社員は、⁠3つ以上を兼務」という文字列を表示する

結果は図11のようになるはずである。

図10 Employeesテーブル
図10 Employeesテーブル
図11 条件を満たした結果
  emp_name  | team
------------+------------------
Jim         | 開発
Bree        | 3つ以上を兼務
Joe         | 3つ以上を兼務
Carl        | 営業
Kim         | 2つを兼務

UNIONで分岐させるのは簡単だが…

ワイリー、従業員を①~③の条件で分類するとどうなる?


こうですね。


  • Jim、Carl
  • Kim
  • Bree、Joe

OK。患者のコードは、ある意味、この条件分岐を忠実に表現しているわリスト5⁠。

リスト5 患者のコード
SELECT emp_name, ―――――┐
       MAX(team)  AS team     
  FROM Employees              
 GROUP BY emp_name            
HAVING COUNT(*) = 1 ―――――┘
UNION
SELECT emp_name, ―――――――┐
       '2つを兼務'  AS team    
  FROM Employees             
 GROUP BY emp_name            
HAVING COUNT(*) = 2 ――――――┘
UNION
SELECT emp_name,  ――――――――┐
       '3つ以上を兼務'  AS team 
  FROM Employees             
 GROUP BY emp_name           
HAVING COUNT(*) >= 3; ――――――┘

自分でもきっとこう書いただろうから、意図がすごくよくわかります。


この問題のおもしろいところは、条件分岐がレコードの値ではなく、レコード数という集合の値に一段レベルが上がっているところだ。そのため、患者もWHERE句ではなくHAVING句で条件を指定している。愚策には変わりないがな。SQLではHAVING句で分岐させるのも素人のやることだ。それは結局、文レベルの分岐でしかないからだ。本当の解はこうだリスト6⁠。

リスト6 ロバートの治療
SELECT emp_name,
       CASE WHEN COUNT(*) = 1 THEN MAX(team)
            WHEN COUNT(*) = 2 THEN '2つを兼務'
            WHEN COUNT(*) >= 3 THEN '3つ以上を兼務'
        END AS team
  FROM Employees
 GROUP BY emp_name;

集約結果に対する分岐もSELECT句で

患者のコード(リスト5)は、ほとんど同じ構文のSELECT文(HAVING句の条件を変えただけ)を3つ並べています。一方、ロバートの解はSELECT句で分岐させることで、テーブルへのアクセスコストを3分の1に減らしています。

Oracleで、患者とロバートの実行計画を比較してみましょう図12、図13⁠。

図12 患者の実行計画(Oracle)
-----------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |    33 |   792 |     15 (80)| 00:00:01 |
|   1 |  SORT UNIQUE             |           |    33 |   792 |     15 (80)| 00:00:01 |
|   2 |   UNION-ALL              |           |       |       |            | |
|*  3 |    FILTER                |           |       |       |            | |
|   4 |     HASH GROUP BY        |           |    11 |   396 |      5 (40)| 00:00:01 |
|   5 |      TABLE ACCESS FULL | EMPLOYEES |    11 |   396 |       3 (0)| 00:00:01 |
|*  6 |    FILTER                |           |       |       |            | |
|   7 |     HASH GROUP BY        |           |    11 |   198 |      5 (40)| 00:00:01 |
|   8 |      TABLE ACCESS FULL | EMPLOYEES |    11 |   198 |       3 (0)| 00:00:01 |
|*  9 |    FILTER                |           |       |       |            | |
|  10 |     HASH GROUP BY        |           |    11 |   198 |      5 (40)| 00:00:01 |
|  11 |      TABLE ACCESS FULL | EMPLOYEES |    11 |   198 |       3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(COUNT(*)=1)
   6 - filter(COUNT(*)=2)
   9 - filter(COUNT(*)>=3)
図13 ロバートの実行計画(Oracle)
--------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |   11 |   396 |      4 (25)| 00:00:01 |
|   1 |  HASH GROUP BY        |           |   11 |   396 |      4 (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | EMPLOYEES |   11 |   396 |       3 (0)| 00:00:01 |
--------------------------------------------------------------------------------

ロバートの解は、Employeesテーブルへのアクセスを一度だけで済ませているため、極めてシンプルな実行計画を実現していることがおわかりいただけるでしょう。これを可能にしているのが、集約結果(COUNT関数の戻り値)をCASE式の入力にする、という技術です。

SELECT句においては、COUNTやSUMなど集約関数[3]の結果は1行につき1つに定まります。別の言い方をすれば、集約関数の結果はスカラ値[4]になります。そのため、CASE式の引数に集約関数を取るという、一見するとトリッキーなコーディングが可能なのです。

先ほどはヘレンが「WHERE句で分岐させるのは素人だ」という名言を吐きましたが、ロバートの言うとおり「HAVING句で分岐させるのも素人のやること」だということを、ぜひ忘れないでください。

手続き型と宣言型

うーん…。


何だ、便秘の牛みたいな顔して。


どんな顔ですか。いや、先生の治療は見事なものです。すごくエレガントで合理的で、素人同然の僕が見ても、一目ですばらしいとわかります。でも一つ疑問なんです。なぜ僕はうまくSQLが書けないのでしょう?

??


??


いや、そんな馬鹿を見るような顔しないでください。僕が勉強不足なのはわかります。でも、僕だって一応、正しい結果を出すためのSQL文にはたどり着けるわけですよ。不恰好ですけど。でも、SQLらしい上手なコードを書くことができないのはなぜなんだろう、と思って。お二人と何が違うのだろう。

ほう、これはこれは…お前は今、無意識に大事なポイントをついたぞ。

スキーマ問題ね。


隙間?

スキーマ(schema⁠⁠。枠組みとか見取り図という意味よ。パラダイムと言ってもいいわ。


そうだ。ワイリー、お前の疑問に対する答えを一言で言うならば、我々とお前とでは住んでいる世界が違うのだ。お前は手続き型の世界に住んでいる。そこでは基本単位は「文(statement⁠⁠」だ。だが、我々は宣言型の世界に住んでいる。ここでの基本単位は「式(expression⁠⁠」だ。2つの世界では、基本的な考え方の枠組み、つまりスキーマが違う。ものの見方を変えなければいけない。

はあ、なるほど…。と頷いてみたものの、まだよくわからないです。


ふむ。まあすぐにわからんのはしかたあるまい。どうせ今後も常についてまわる問題だ。詳しく説明する機会もあるだろう。

とりあえず、ほかの課題は全部見直しなさい。


うっ、今日も寝れそうにない…。


終わりに

SQLの初心者(時には中級者も)がUNIONによる分岐に頼ってしまう理由は、UNIONによる場合分けが、文ベースの手続き型のスキーマに従うものだからです。実際、UNIONで連結する対象はSELECT「文」です。これは、最初に手続き型言語でプログラミングの練習をする私たちのほとんどにとって、たいへん馴染み深い発想で、誰にでも理解できます。

一方、SQLのスキーマは宣言型です。この世界では、主役は「文」ではなく「式」です。手続き型言語がCASE「文」で分岐させるところを、SQLではCASE「式」によって分岐させます。SQL文の各パート─⁠─SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY─⁠─に記述するのは、すべて式です。列名や定数しか記述しない場合でもそうです[5]⁠。SQL文の中には、文は一切記述しないのです。

手続き型の世界から宣言型の世界へ、勇気を持って跳躍することが、SQL上達の鍵ですこの点については、次回以降でより詳しく取り上げていきたいと思います。

【参考資料】

1.ミック「CASE式のススメ前編)/後編)」
本稿でも見たように、SQLで条件分岐を表現するにはCASE式を使います。逆に言うと、もし分岐をCASE式以外で表現していたら、そのSQLは間違えている可能性が高い、と考えてください。
2.Joe Celko『SQLパズル 第2版』(翔泳社、2007年)
本書はすばらしいコードサンプルの宝庫です。SQLにおける分岐を練習する問題としては、⁠パズル13:2人かそれ以上か、それが問題だ」⁠パズル36:1人2役」が最適です。
3.ミック WEB+DBVol.60 連載「DBアタマアカデミー」第4回「クエリ評価エンジンと実行計画」
実行計画の読み方、DBMS内部でのSQLの実行のされ方などについて知りたい方はこちらを読むと基礎的なことがわかります。gihyo.jpでも公開しております。

おすすめ記事

記事・ニュース一覧