ここはとある街の総合病院。
ここには通常の診療科のほかに、
何軒もの病院をたらいまわしにされた、
それがSQL緊急救命室、
そう、
ロバート
救命室部長。腕の立つエンジニアだが、
Keep It Simple, Stupid
（シンプルにしておけ、 この馬鹿）
―KISSの原則
（AM11:00 休憩室。ワイリーとヘレンが話している）
どんな人間よ、
縁起でもないこと言わないでください。僕は禿げません。絶対に。毎日ちゃんとケアしてますから。
いやー、
（ロバートが休憩室のドアを開けて首を出す）
おい、
ああ、
ウフフ。
なんだ……？ 気味の悪い連中だな。人の顔じろじろ見て。
いえ、
SQLで解く方法
これがカルテです。
カルテ1：図1のような2つのテーブルOrders
今、
レベルの異なる情報を結合する方法
1つの注文に複数の商品が含まれる可能性があるから、
基本的な考え方はそれでいいわ。結合すれば、
あとは、
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文は、
SQL文の各句が解釈される順序を次に示します。
FROM → WHERE → GROUP BY → HAVING → SELECT→ORDER BYこのように、
集約の単位には気をつけよう
ああ、
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;order_id | order_name | diff_days ----------+------------+----------- 10000 | 後藤信二 | 3 10000 | 後藤信二 | 4 10001 | 佐原商店 | 3 10003 | 加地健太郎 | 5 10003 | 加地健太郎 | 5
これでOKね。もしここから注文番号ごとの最大の遅延日数に絞り込みたければ、
でもそれをやると、
もし注文番号と注文者名義が一対一に対応しないのならそのとおりよ。でも一対一に対応する場合は、
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;order_id | max | max_diff_days ---------+------------+--------------- 10000 | 後藤信二 | 4 10001 | 佐原商店 | 3 10003 | 加地健太郎 | 5
GROUP BY句を使った場合、
- ① 定数
- ② GROUP BY句で使用されている
- ③ 集約関数
今、
なお、
モデル変更で解く方法
うむ。これでいいだろう。今考えたSQLがこの問題に対する解の一つだ。だがこれが、
もっとうまいSQL文の書き方があるってことですか？
いいや。この問題は
だが図4のように、
お前はこの患者を見たとき、
耳の痛い言葉です……。最近ちょっとSQLが書けるようになってきて、
それはあなたの成長でもあるから、
モデルを変更するときの注意点
複雑なクエリに頭をひねらなくてよいという点で、
- 更新コストが高まる
この方法では、
当然のことですがOrdersテーブルの配送遅延フラグ列に値を入れる処理が必要になるため、 検索の負荷を更新に押し付ける格好になります。 もし、
Ordersテーブルへのレコード登録時にすでにフラグの値が決まっているのならば、 INSERT処理の中に吸収できるので更新コストはほとんど上がりません。しかし、 登録時にはまだ個別の商品の配送予定日が決まっていないこともあるでしょう （現実の業務を考えると、 むしろそのほうが多いでしょう）。そういうケースでは、 あとでフラグ列をUPDATEする必要があるため、 更新コストが高くなります。 - 更新までのタイムラグが発生する
この方法には、
データのリアルタイム性という問題が発生します。配送予定日が注文の登録後に更新されるケースでは、 Ordersテーブルの配送遅延フラグ列と、 OrderReceiptsテーブルの配送予定日の列との間で同期が取れていない時間帯が生まれます （図5）。特に夜間バッチ更新などでフラグ列を一括更新するような非同期処理では、 タイムラグが大きくなります。このタイムラグをどの程度許容できるかどうか、 やはり業務要件と付き合わせて検討する必要があります。 - モデル変更のコストが発生する
データモデルの変更は、
コードベースの修正に比べて手戻りが大きくなります。変更対象のテーブルを使用するほかの処理に対する副作用も発生する可能性があるため、 開発の後半に入ってからのモデル変更は大きなリスクがあります。モデリングというのは事前にあらゆる要因を想定しておかないと、 あとになってから問題を引き起こすことが多いのです。
たしかにデータ同士の同期が取れていない時間帯が存在することは、
お前の疑問は例外なく素朴だからいちいち断らんでいい。何だ？
どうも……。いや、
解決するさ。トランザクションというのはそのように処理の同期を取りたい単位で設定するものだからな。しかし、
あちらを立てればこちらが立たず、
あちらとこちらが両立するような平衡点を探すのが、
うーむ。ヘレンさんの言葉はいつもながら含蓄がありますね。
もう一つ、
カルテ2：先ほどの2つのテーブルOrders
- {注文番号、
注文者名義、 受付日、 商品数}
再び、SQLで解くなら
商品の数はOrderReceiptsテーブルのほうを注文番号別にカウントする必要がある。一方で注文者名義や受付日はOrdersテーブルを参照しなければならない。これもやはり素直に解くのなら、
集約について注意が必要なのは、
……わかった！ さっきと同じで、
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;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
それでいいわ。もう一つのやり方としては、
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はウィンドウ関数をサポートしていない
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
あれ、
構文上は別になくてもいいし、
ふう、
ワイリーの解
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)
-------------------------------------------------------------------------------------- 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)
そのため、
モデル変更で解く方法
SQLによる解はこのぐらいで良いだろう。ではさっきと同じように、
やはりOrdersテーブルに
うむ。Ordersテーブルが最初からこういう定義であったなら、
それはちょっとフールプルーフの思想を曲解しているような……まあいいけど。あと、
たしかに。そうなると結局、
初級者よりも中級者がご用心
スーパーソルジャー病は、
この病気を特に発症しやすいステージが、
それ自体はプログラマとしての成長と喜んでよいのですが、
データモデルを制す者はシステムを制す
本稿で見たように、
米国のプログラマであるEric Steven Raymondはエッセイ
2人に共通している認識は、
戦術より戦略
スーパーソルジャーって、
戦略的失敗を一人の戦術的活躍でひっくり返すスーパーソルジャーは、
そうだ。我々が目指すべきは、
……先生、
……やっぱり今日のお前、
【参考資料】
- 1．ミック WEB+DB PRESS Vol.
62 連載 「SQL緊急救命室」 第1回 「サブクエリ・ パラノイア」 - 結合を使用したSQL文が抱える性能問題について解説しています。gihyo.
jp でも公開しております。 - 2．Gerald Marvin Weinberg
『スーパーエンジニアへの道』 （木村泉訳／ 共立出版／ 1991年） - 20年前に書かれた本ですが、
スーパーエンジニアは 「スーパーソルジャー」 でも 「スーパープログラマ」 でもないという、 時代を超えて通じる真実を教えてくれる本です。