SQL緊急救命室

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

この記事を読むのに必要な時間:およそ 7 分

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)
PostgreSQLとMySQLは独自実装によって,SELECT句で付けた列の別名をGROUP BY句,HAVING句で参照できます。ただし,WHERE句では参照できません。

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

ああ,本当だ。リスト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句に書くことができます。

注3)
かなり細かい話ですが,修正したSQLのWHERE句の条件は「ORC.delivery_date - O.order_date >= 3」よりも「ORC.delivery_date >= O.order_date + 3」のほうが性能的には優れています。理由はdelivery_date列にインデックスがある場合に利用できるからです。

モデル変更で解く方法

うむ。これでいいだろう。今考えた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のシーケンス図における❸明細更新と❹注文更新の間隔をどんどん短くしたら,このデータ不整合の問題は解決しないのでしょうか。極端な話,❸と❹を同一トランザクションで処理するようにしたらいいんじゃないかな,と。

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

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


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

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

著者プロフィール

ミック

SI企業に勤務するDBエンジニア。主にデータウェアハウス業務に従事している。自身のサイト「リレーショナル・データベースの世界」でデータベースとSQLについての技術情報を公開している。『Web+DB Press』で「SQL緊急救命室」を連載中。

著書:『SQL ゼロからはじめるデータベース操作』(翔泳社,2010)『達人に学ぶ SQL徹底指南書』(翔泳社,2008)訳書:J.セルコ『SQLパズル 第2版』(翔泳社,2007)

Twitter:copinemickmack

SQL緊急救命室:サポートページ