ここはとある街の総合病院。
ここには通常の診療科のほかに、一風変わった診療科が存在する。
何軒もの病院をたらいまわしにされた、手の施しようのないSQLや、今すぐに改善が必要なSQLが担ぎ込まれる救命室である。
それがSQL緊急救命室、略してSER(SQL Emergency Room) 。
そう、ここは国内でも唯一のプログラミング専門外来である。
ロバート 救命室部長。腕の立つエンジニアだが、口が悪く性格はもっと悪い四十オヤジ。
(AM10:00 休憩室。ワイリーが机に向かって一人で何かしている)
どってぃろーんどってぃろーん、ぽぽぽんぽーん、どってぃろーん…
(休憩室のドアを開けて)あら、鼻歌交じりで、ご機嫌ね。
ああ、どうも。うふふ、今日はロバート先生、用事で遅れるそうです。
なるほど、束の間の休息ね。それじゃ鼻歌の一つも飛び出すわけだわ。
そういうことです。どってぃろーん…
(変なメロディー…)ところでワイリー、あなた何やってるの?
え? ああ、これは大学の課題。今日が締め切りなんです。
UNIONで条件分岐するのは正しいか
ふうん、どれどれ…。
問1: 商品を管理する図1 のようなテーブルItemsが存在する。各商品について、税抜き価格(外税) /税込み価格(内税)の両方を保持している。2002年から、法改正によって価格表示に税込み価格(内税)を表示することが義務付けられた。そこで、2001年までは税抜き価格を、2002年からは税込み価格を「価格」列として表示する結果(図1の色のついてない部分)を求めたい(図2 ) 。
図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;
イタタた…。
足の小指でもぶつけました?
いや、そうじゃなくて、あなたの解を見てアタマ痛くなったの! もう、先が思いやられるわ…。
[1] ワイリーの言っていることは間違いではありません。UNIONは結果から重複行を削除するためにソートを行いますが、UNION ALLはソートをスキップします。今回は、年を基準に排他的な分岐を行っているため、UNIONもUNION ALLも結果が同値なため、互換可能です。
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テーブル
図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テーブル
図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 でも公開しております。
CASE式はどこに書けるか?
答えは、「 FROM句以外のすべての句」です。本文でも述べたように、CASE 式は式の一種です。式が書ける場所にはどこにでも書けるので、SELECT 句やWHERE 句以外にも、GROUP BY 句、HAVING 句、ORDER BY句でも使えます。FROM句はテーブルやビューを記述する場所なので、ここに戻り値がスカラ値となる式を書くことはできません(ただし、結合条
件を記述するON句には問題なくCASE式を使えます) 。