SQL緊急救命室

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

初出：WEB+DB PRESS Vol.65（2011年10月24日発売）

SQL, スーパーソルジャー病, 検索

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

注文ごとの件数を求める

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

カルテ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点から，より好ましいと言えるでしょう。

注4）
宿題の解答は，技術評論社のWEB+DB PRESS本誌サポートサイトおよび筆者のサポートサイトで公開します。

モデル変更で解く方法

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

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

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

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

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

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

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

SQL, スーパーソルジャー病, 検索

著者プロフィール

ミック

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

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

Twitter：copinemickmack

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