SQL緊急救命室

第4回スーパーソルジャー病~すべての問題をやみくもにコーディングで解くべからず

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

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

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

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

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

ロバート

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

視野狭窄のもたらす病気

Keep It Simple, Stupid(シンプルにしておけ、この馬鹿)

―KISSの原則

(AM11:00 休憩室。ワイリーとヘレンが話している)

(写真を見ながら)ほ、ほんとうだ。毛がある。フサフサだ。生まれてからずっとあのいかめしい顔だと思ってたのに!

どんな人間よ、それ。ロバートにだってピーピー泣いてた子ども時代だってあるわ。噂じゃ、若いころはけっこう線が細くて大人しかったそうよ。あなたみたいに。

縁起でもないこと言わないでください。僕は禿げません。絶対に。毎日ちゃんとケアしてますから。

いやー、あなたの髪質細いから案外年とったらロバートみたいに……。

(ロバートが休憩室のドアを開けて首を出す)

おい、ここにいたのか。本日1人目のお客さんが到着したぞ。早く支度しろ。

ああ、はい。エヘヘ。


ウフフ。


なんだ……? 気味の悪い連中だな。人の顔じろじろ見て。


いえ、何でもありません。ささ、早く行きましょ。患者を待たせちゃ失礼ですよ。

SQLで解く方法

これがカルテです。


カルテ1:図1のような2つのテーブルOrders(注文)とOrderReceipts(注文明細)を考える。この2つのテーブルは、お中元の受け付けと配送を管理するためのものです。Ordersテーブルの1レコードが注文1件に対応し、OrderReceiptsはその注文内の商品単位で1レコードになっている。したがって、OrdersとOrderReceiptsは一対多の関係にある。

今、注文ごとに受付日(order_date)と商品の配送予定日(delivery_date)の差を求めて、それが3日以上ある場合は注文者に遅くなる旨の連絡を送りたい。さて、どの注文番号が該当するか、求めてほしい。

図1 OrdersテーブルとOrderReceiptsテーブル
図1 OrdersテーブルとOrderReceiptsテーブル

レベルの異なる情報を結合する方法

1つの注文に複数の商品が含まれる可能性があるから、OrdersテーブルとOrderReceiptsは一対多の関係になっているわけですね。今、受付日(order_date)と配送予定日(delivery_date)の関係を知りたいのだけど、それぞれ別のテーブルの列だから、結合を使わざるをえないですね。

基本的な考え方はそれでいいわ。結合すれば、商品単位で受付日と配送予定日を表すレコードが得られる。そのあとはどうするかしら?

あとは、受付日と配送予定日の差を求めればいいから……リスト1⁠。あれ、エラーになった。


リスト2 ワイリーの解:WHERE句に間違いあり
SELECT O.order_id,
       O.order_name,
       ORC.delivery_date - O.order_date AS diff_days
  FROM Orders O
       INNER JOIN OrderReceipts ORC
          ON O.order_id = ORC.order_id
 WHERE diff_days >= 3; ― エラーの原因
列"diff_days" は存在しません。行 7: WHERE diff_
days >= 3;[1]

……。


おっかしいなあ。このDBMSバグってませんか?


思い通りにいかないと道具のせいにするのは素人の常だが、現実にはプログラマの頭がバグっている可能性のほうがはるかに高い。これは統計学的に証明されている。疑うのなら自分の頭を先に疑え。

SQL文の解釈順序にご注意

このSQL文は、どんなDBMSでもエラーになります。理由は、SELECT句はSQL文の中で最後に解釈される句であるため、WHERE句の解釈時にはまだdiff_daysという列名は存在していないからです。この列名が存在し始めるのは、SQLの解釈における一番最後の段階においてです。

SQL文の各句が解釈される順序を次に示します。

FROM → WHERE → GROUP BY → HAVING → SELECT→ORDER BY

このように、WHERE句はSELECT句より先に解釈が行われるため、SELECT句で付けられた列の別名を参照できません。これと同様のことが、GROUP BY句やHAVING句とSELECT句との間にも成立します。GROUP BY句やHAVING句もSELECT句より前に解釈が行われるため、やはりSELECT句で付けられた列の別名を参照できないのです[2]⁠。

集約の単位には気をつけよう

ああ、本当だ。リスト2のように直したら動きました図2・注3


リスト2 ワイリーの解:修正版
SELECT O.order_id,
       O.order_name,
       ORC.delivery_date - O.order_date AS diff_days
  FROM Orders O
       INNER JOIN OrderReceipts ORC
          ON O.order_id = ORC.order_id
 WHERE ORC.delivery_date - O.order_date >= 3;
図2 リスト2の実行結果
order_id | order_name | diff_days
----------+------------+-----------
   10000 | 後藤信二   |         3
   10000 | 後藤信二   |         4
   10001 | 佐原商店   |         3
   10003 | 加地健太郎 |         5
   10003 | 加地健太郎 |         5

これでOKね。もしここから注文番号ごとの最大の遅延日数に絞り込みたければ、注文番号ごとに集約すればいいわ。

でもそれをやると、せっかく今は結果に含められている注文者名義の列を外さなくてはならないのでは?

もし注文番号と注文者名義が一対一に対応しないのならそのとおりよ。でも一対一に対応する場合は、リスト3のように注文者名義にも集約関数を使うことで結果に残すことができるわ図3⁠。

リスト3 ヘレンの解
SELECT O.order_id,
       MAX(O.order_name),
       MAX(ORC.delivery_date - O.order_date) AS max_diff_days
  FROM Orders O
       INNER JOIN OrderReceipts ORC
          ON O.order_id = ORC.order_id
 WHERE ORC.delivery_date - O.order_date >= 3
 GROUP BY O.order_id;
図3 リスト3の実行結果
order_id |     max    | max_diff_days
---------+------------+---------------
   10000 | 後藤信二   |             4
   10001 | 佐原商店   |             3
   10003 | 加地健太郎 |             5

GROUP BY句を使った場合、SELECT句に書くことのできる要素は次の3つに限られます。

  • ① 定数
  • ② GROUP BY句で使用されている
  • ③ 集約関数

今、order_name列はもちろん定数ではありませんし、GROUP BY句でも使われていません。そのため、そのままこれをSELECT句に書いてしまうとエラーになります。それを防ぐためには、⁠③ 集約関数」の形で書いてやればよい、ということです。ここでのMAX関数は別に最大値を求めるために使っているわけではなく、ある意味で、エラーを防ぐための便宜的措置です。MAX/MINはあらゆるデータ型に適用できるため、こういうときに重宝します。

なお、order_idとorder_nameが結局のところ一対一に対応する、という点から考えれば、GROUP BY句にorder_name 列を含めてGROUP BY order_id,order_nameとする解決策もあります。こうすれば、order_nameは「② GROUP BY句で使用されている」のカテゴリに入るため、MAX関数なしで裸でSELECT句に書くことができます。

モデル変更で解く方法

うむ。これでいいだろう。今考えたSQLがこの問題に対する解の一つだ。だがこれが、この問題に対する最適解かどうかには疑問がある。

もっとうまいSQL文の書き方があるってことですか?


いいや。この問題はSQLに頼らないことが解になる可能性がある、ということだ。お前の考えた解は、現状のテーブル構成は変更不可能であることを前提として、SQLで解決する方法だ。しかし、このやり方を取ろうとすると、結合や集約を含んだSQLになり、検索処理にかかるコストが高くなりがちだ。結合は実行計画の変動リスクを負うことで、性能を不安定にさせる要因でもある。

だが図4のように、配送が遅れる可能性のある注文のレコードに対してフラグを立てる列をOrdersテーブルに追加すれば、検索クエリはこのフラグだけを条件にすることが可能になるためずっとシンプルになる。フラグが1なら配送遅延あり、0なら遅延なしを意味する。

図4 Ordersテーブルに 配送遅延フラグを追加
図4 Ordersテーブルに 配送遅延フラグを追加

(手を叩きながら)これはコロンブスの卵ですね。確かにこのフラグがあれば、検索のSQL文も簡単で、全然悩む必要がなくなる。

お前はこの患者を見たとき、ほとんど反射的にSQLを考え始めたな。だがそれは性急過ぎる態度だ。問題を解決する手段はコーディングだけではないのに、プログラマはともすると、常に一つの方法に頼ろうとする。これをスーパーソルジャー病と名付けた。視野狭窄の一種だ。

耳の痛い言葉です……。最近ちょっとSQLが書けるようになってきて、問題を解くのが楽しくなってきたところだったんです。

それはあなたの成長でもあるから、けっして悪いことではないわ。でも、そろそろもう一歩高い視点から見るようになっていいころね。

モデルを変更するときの注意点

複雑なクエリに頭をひねらなくてよいという点で、確かにモデル変更は優れた解決策です。ただし、その注意点を3つ解説しておきましょう。採用するか否かを検討する際の観点として利用してください。

更新コストが高まる

この方法では、当然のことですがOrdersテーブルの配送遅延フラグ列に値を入れる処理が必要になるため、検索の負荷を更新に押し付ける格好になります。

もし、Ordersテーブルへのレコード登録時にすでにフラグの値が決まっているのならば、INSERT処理の中に吸収できるので更新コストはほとんど上がりません。しかし、登録時にはまだ個別の商品の配送予定日が決まっていないこともあるでしょう(現実の業務を考えると、むしろそのほうが多いでしょう⁠⁠。そういうケースでは、あとでフラグ列をUPDATEする必要があるため、更新コストが高くなります。

更新までのタイムラグが発生する

この方法には、データのリアルタイム性という問題が発生します。配送予定日が注文の登録後に更新されるケースでは、Ordersテーブルの配送遅延フラグ列と、OrderReceiptsテーブルの配送予定日の列との間で同期が取れていない時間帯が生まれます図5⁠。特に夜間バッチ更新などでフラグ列を一括更新するような非同期処理では、タイムラグが大きくなります。このタイムラグをどの程度許容できるかどうか、やはり業務要件と付き合わせて検討する必要があります。

図5 配送遅延フラグを更新する処理シーケンス
図5 配送遅延フラグを更新する処理シーケンス
モデル変更のコストが発生する

データモデルの変更は、コードベースの修正に比べて手戻りが大きくなります。変更対象のテーブルを使用するほかの処理に対する副作用も発生する可能性があるため、開発の後半に入ってからのモデル変更は大きなリスクがあります。モデリングというのは事前にあらゆる要因を想定しておかないと、あとになってから問題を引き起こすことが多いのです。

たしかにデータ同士の同期が取れていない時間帯が存在することは、この方法のデメリットですね。巧妙な方法に見えても、いいことばかりじゃないんだなあ。ところで、素朴な疑問を聞いてもいいですか。

お前の疑問は例外なく素朴だからいちいち断らんでいい。何だ?


どうも……。いや、図5のシーケンス図における❸明細更新と❹注文更新の間隔をどんどん短くしたら、このデータ不整合の問題は解決しないのでしょうか。極端な話、❸と❹を同一トランザクションで処理するようにしたらいいんじゃないかな、と。

解決するさ。トランザクションというのはそのように処理の同期を取りたい単位で設定するものだからな。しかし、それはさっきも言ったように性能とのトレードオフ、交換条件だ。❹の更新処理がオンライン中にジャンジャン発生しても性能要件を満たせるのならその方法もありだ。すべてはバランスしだいだな。

あちらを立てればこちらが立たず、というやつですね。


あちらとこちらが両立するような平衡点を探すのが、エンジニアの本当の仕事よ。コーディングや設計は、そのための付帯作業に過ぎないわ。

うーむ。ヘレンさんの言葉はいつもながら含蓄がありますね。

注文ごとの件数を求める

もう一つ、スーパーソルジャー病にかかりやすいケースを類題で見ておくとしよう。次のような患者がいると想定する。

カルテ2:先ほどの2つのテーブルOrders(注文)とOrderReceipts(注文明細)を再び利用する。今度は、注文番号ごとに何品注文されているかを取得したい。結果に含める列は次のとおりとする。

  • {注文番号、注文者名義、受付日、商品数}

再び、SQLで解くなら

商品の数はOrderReceiptsテーブルのほうを注文番号別にカウントする必要がある。一方で注文者名義や受付日はOrdersテーブルを参照しなければならない。これもやはり素直に解くのなら、結合と集約が必要になりますね。

集約について注意が必要なのは、注文番号をキーにGROUP BY句を使うと、結果のレコードが注文番号単位に集約されるので、注文者名義などの情報は結果に含められないわ。こういう場合はどうするんだった?

……わかった! さっきと同じで、集約関数をかぶせればいいんですねリスト4、図6⁠。


リスト4 ワイリーの解:集約関数を使う
SELECT O.order_id,
       MAX(O.order_name) AS order_name,
       MAX(O.order_date) AS order_date,
       COUNT(*) AS item_count
  FROM Orders O
       INNER JOIN OrderReceipts ORC
          ON O.order_id = ORC.order_id
 GROUP BY O.order_id;
図6 リスト4の実行結果
order_id | order_name | order_date | item_count
---------+------------+------------+------------
   10000 | 後藤信二   | 2011-08-22 |     3
   10001 | 佐原商店   | 2011-09-01 |     1
   10002 | 水原陽子   | 2011-09-20 |     2
   10003 | 加地健太郎 | 2011-08-05 |     3
   10004 | 相原酒店   | 2011-08-22 |     1
   10005 | 宮元雄介   | 2011-08-29 |     2

それでいいわ。もう一つのやり方としては、ウィンドウ関数を使うものもあるわリスト5、図7⁠。


リスト5 ヘレンの解:ウィンドウ関数を使う
SELECT O.order_id,
       O.order_name,
       O.order_date,
       COUNT(*) OVER (PARTITION BY O.order_id) AS item_count
  FROM Orders O
       INNER JOIN OrderReceipts ORC
          ON O.order_id = ORC.order_id;

MySQLはウィンドウ関数をサポートしていない

図7 リスト5の実行結果
order_id | order_name | order_date | item_count
---------+------------+------------+------------
   10000 | 後藤信二   | 2011-08-22 |          3
   10000 | 後藤信二   | 2011-08-22 |          3
   10000 | 後藤信二   | 2011-08-22 |          3
   10001 | 佐原商店   | 2011-09-01 |          1
   10002 | 水原陽子   | 2011-09-20 |          2
   10002 | 水原陽子   | 2011-09-20 |          2
   10003 | 加地健太郎 | 2011-08-05 |          3
   10003 | 加地健太郎 | 2011-08-05 |          3
   10003 | 加地健太郎 | 2011-08-05 |          3
   10004 | 相原酒店   | 2011-08-22 |          1
   10005 | 宮元雄介   | 2011-08-29 |          2
   10005 | 宮元雄介   | 2011-08-29 |          2

あれ、PARTITION BYだけ?ウィンドウ関数ってORDER BY句はいらないんですか?


構文上は別になくてもいいし、ここではORDER BY句を使うと、適切な結果は得られないわ。そうね、ORDER BY句を使うと、上の結果とどういう違いが出るか、そしてそれはなぜか、今日の宿題にしましょう[4]⁠。

ふう、今日もいい墓穴掘ったなあ。


ワイリーの解(集約関数)とヘレンの解(ウィンドウ関数)は、どちらも結合と集約を行うため、実行コストはほとんど同じです。実行計画も非常に近いものになります図8、9⁠。

図8 実行計画:集約関数
                                  QUERY PLAN
--------------------------------------------------------------------------------
 HashAggregate (cost=50.94..57.94 rows=400 width=90) ― 図9と異なる部分(GROUP BY)
   -> Hash Join (cost=19.00..44.44 rows=650 width=90)
         Hash Cond: (orc.order_id = o.order_id)
         -> Seq Scan on orderreceipts orc (cost=0.00..16.50 rows=650 width=4)
         -> Hash (cost=14.00..14.00 rows=400 width=90)
              -> Seq Scan on orders o (cost=0.00..14.00 rows=400 width=90)
図9 実行計画:ウィンドウ関数
--------------------------------------------------------------------------------------
 WindowAgg (cost=74.81..86.18 rows=650 width=90)
   -> Sort (cost=74.81..76.43 rows=650 width=90)  
         Sort Key: o.order_id ― 図8と異なる部分(ウィンドウ関数の集約操作)
         -> Hash Join (cost=19.00..44.44 rows=650 width=90)
              Hash Cond: (orc.order_id = o.order_id)
              -> Seq Scan on orderreceipts orc (cost=0.00..16.50 rows=650 width=4)
              -> Hash (cost=14.00..14.00 rows=400 width=90)
                    -> Seq Scan on orders o (cost=0.00..14.00 rows=400 width=90)

そのため、どちらのほうがより優れたコードかは、別の観点から判断する必要があります。この場合、ウィンドウ関数のほうが、よりやりたいことを素直に表現している(可読性)ことと、注文番号ではなく商品別に結果を出力したい場合にも対応できる(拡張性)ことの2点から、より好ましいと言えるでしょう。

モデル変更で解く方法

SQLによる解はこのぐらいで良いだろう。ではさっきと同じように、一歩身を引いて考えてみるとしよう。ソルジャーではなく指揮官になれ。コーディングを離れると、どのような別解がある?

やはりOrdersテーブルに「商品数」の情報を列として持つよう、モデルを変更するのがいいと思います図10⁠。商品数は、普通は注文の登録時に判明しているはずです。だから、OrdersテーブルへのINSERT文に吸収することが可能だと思います。

図10 Orders テーブルに商品数を追加
図10 Orders テーブルに商品数を追加

うむ。Ordersテーブルが最初からこういう定義であったなら、SQLで迷う者はおるまい。まさにバカに優しい設計、フールプルーフだな。

それはちょっとフールプルーフの思想を曲解しているような……まあいいけど。あと、この場合も注意が必要なのは、一度登録した注文を後から変更するような場合には商品数も修正される可能性があるので、先の問題と同じ非同期の期間が発生することね。

たしかに。そうなると結局、要件調整が発生しちゃうってことですね。やっぱりテーブル設計ってのは最初にキチッといろんなファクターを考えておくことが大事なんだなあ。

すべてをSQLで解くべきか

初級者よりも中級者がご用心

スーパーソルジャー病は、SQLに限らずプログラミング全般で発症します。その意味では、DBエンジニア以外の人にとっても本稿の教訓は適用できるものです。

この病気を特に発症しやすいステージが、初級者レベルを抜け出して、一通りのプログラミングができるようになったあたり、つまり中級者の入口ぐらいです。

このステージに達すると、自分がプログラミングでできることの幅も広がって、ちょっと難しい問題やひねりの効いた問題をプログラミングで解くのが楽しくなってくるころです。

それ自体はプログラマとしての成長と喜んでよいのですが、ともすると、難しい問題を難しいままの状態で解こうとしてしまう傾向につながります。本人にとってはパズルを解く楽しさがあるかもしれませんが、放っておくと無駄に複雑なプログラムができあがることになって、システム全体の観点では非効率で全体最適を損なう結果に陥りかねません。

データモデルを制す者はシステムを制す

本稿で見たように、特にデータベースにおいてはデータモデルのレベルで変更したほうがずっとシンプルかつ全体最適な解を達成できる問題は多くあります。こういうとき、テーブル構成に手をつけず、コーディングで何とかしようとするのは、無駄な労力を注ぎ込むのと同じです。

米国のプログラマであるEric Steven Raymondはエッセイ「伽藍とバザール」の中で、⁠賢いデータ構造と間抜けなコードのほうが、その逆よりずっとまし」注5という名言を吐きました。また、Frederick Phillips Brooks, Jr.も『人月の神話』「私にフローチャートだけを見せて、テーブルは見せないとしたら、私はずっと煙に巻かれたままになるだろう。逆にテーブルが見せてもらえるなら、フローチャートはたいてい必要なくなる。それだけで、みんな明白に分かってしまうからだ」注6と言いました。

2人に共通している認識は、データモデルがコードを決めるのであってその逆ではない、ということです。だから、間違ったデータモデルから出発してしまうと、その間違いをコーディングによって正すことはできないのです。コーディングに長けているだけでは、優れた戦術を駆使する兵士に過ぎません。コーディングは、あくまでシステムを作り上げる手段であって、目的ではありません。

戦術より戦略

スーパーソルジャーって、格好いい印象があるじゃないですか。視野を狭くするのはいけないとわかってはいても、心理的な誘惑がありますね。

戦略的失敗を一人の戦術的活躍でひっくり返すスーパーソルジャーは、見た目の活躍が華々しいから映画やドラマでは好んで描かれるキャラクターよ。でも現実には、一人のソルジャーがどれだけ頑張ってもダメな設計を挽回することはできないわ。仮に奇跡的に一度はそれができたとしても、次のプロジェクトで同じ戦略上の失敗を繰り返して、不毛な戦いが継続されるだけ。

そうだ。我々が目指すべきは、スーパーソルジャーではなくスーパーエンジニアだ。その仕事は、戦略の失敗を挽回する戦術を探すことではない。正しい戦略を選択することだ。

……先生、やっぱり僕は、先生のような大人になってもいいと思いました。

……やっぱり今日のお前、気味悪いぞ。


(写真をポケットに押し込みながら)やあねえ。素直に感心しているだけよ。若い子って素直でいいじゃない。オホホホ。

【参考資料】

1.ミック WEB+DB PRESS Vol.62 連載「SQL緊急救命室」第1回「サブクエリ・パラノイア」
結合を使用したSQL文が抱える性能問題について解説しています。gihyo.jpでも公開しております。
2.Gerald Marvin Weinberg『スーパーエンジニアへの道』(木村泉訳/共立出版/1991年)
20年前に書かれた本ですが、スーパーエンジニアは「スーパーソルジャー」でも「スーパープログラマ」でもないという、時代を超えて通じる真実を教えてくれる本です。

おすすめ記事

記事・ニュース一覧