SQL緊急救命室

第5回 時代錯誤症候群~進化し続けるSQLに取り残されるな!

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

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

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

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

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

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

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

ヘレン
救命室副部長。若いながらもロバートに次ぐ実力を持つ才媛。救命室の良心。

ワイリー
インターンで救命室に配属された不運な学生。無給で治療から雑務全般にこき使われる。エンジニアとしては新人に毛が生えたレベル。

時代遅れという名の罪

(PM3:00 休憩室。ロバート,ヘレン,ワイリーがそろっている)

そろそろ専門の希望を出す時期ね。もうどこにするか考えた?


はい。救命室に決めました。やりがいありますし。


?!


そ,そうだな。まあおもしろい仕事ではあるな。だが専門を選ぶときは慎重に自分の適性を見極めなければならんぞ,うむ。

はい。体力には自信があります。救命室は最もタフな専門ですから,自分の力を試したくて。

(このオタンコナスが……。)


(ロバートの下で潰れなかった初めての学生だからタフなのは認めるけど……。)大事な選択よ。ほかの選択肢もよく吟味して決めたほうがいいんじゃない?

はあ……でももう志望書提出しちゃいましたし……。


( ゚д゚)


あ,救急車来たみたいですよ。治療室に行きましょう。


お,おう……。


そうね……。


いやー楽しみだなあ。来年も頑張るぞー。


ふう……。


繰り返されるサブクエリ

これがカルテです。


カルテ1:ある部品の供給業者の一覧を管理するSuppliersテーブル図1を考える。今,各供給業者の出荷品数の影響度を都市(city)別に調べたい。そのため,出荷が可能か不可能かに応じて,それぞれの都市からの出荷品数の50%以上を占める供給業者を選択したい。求める結果は図2のようになる。

※)今回のコードはPostgreSQLで動作確認をしています。Oracle,SQL Server,DB2ともに動作します。MySQLには未実装の機能が含まれています。

図1 Suppliersテーブル

図1 Suppliersテーブル

図2 求める結果

 sup | city | ship_flg | item_cnt
-----+------+----------+----------
B    | 東京 | 不可     |       30
C    | 東京 | 可       |       40
E    | 大阪 | 不可     |       40
I    | 大阪 | 可       |       30

ちょっと複雑な業務仕様ですね。


在庫調整をする際に,出荷が停止になると影響の大きい業者を割り出そうという目的だな。現に今出荷が不可能な業者については,改善効果の大きな業者がわかる。

都市別の出荷可能品数と不可能品数は,それぞれの業者の取り扱い品数を合計すればよいから,図3のように整理できるわね。

図3 都市別の出荷可能品数と不可能品数

図3 都市別の出荷可能品数と不可能品数

……(黙々と何か書いている)できました!リスト1これはなかなか難しかった!

リスト1 ワイリーの解

SELECT SP.sup, -- 出荷可能業者のパート
       SP.city,
       SP.ship_flg,
       SP.item_cnt
  FROM Suppliers SP
       INNER JOIN
          (SELECT city,                       
                  SUM(item_cnt) AS able_cnt   
             FROM Suppliers                   
            WHERE ship_flg = '可'             │─地域別の出荷可能品数
            GROUP BY city) SUM_ITEM           
         ON SP.city = SUM_ITEM.city
        AND SP.item_cnt >= (SUM_ITEM.able_cnt * 0.5)
 WHERE SP.ship_flg = '可'
UNION ALL
SELECT SP.sup, -- 出荷不可能業者のパート
       SP.city,
       SP.ship_flg,
       SP.item_cnt
  FROM Suppliers SP
       INNER JOIN
          (SELECT city,                        
                  SUM(item_cnt) AS disable_cnt 
             FROM Suppliers                    
            WHERE ship_flg = '不可'             │―地域別の出荷不可能品数
            GROUP BY city) SUM_ITEM            
         ON SP.city = SUM_ITEM.city
        AND SP.item_cnt >= (SUM_ITEM.disable_cnt * 0.5)
 WHERE SP.ship_flg = '不可';

え,もう?


お前の解はいつも期待を裏切らないな。


先生にそう言っていただけると。はい。


誉めとらんわ! だいたいこの程度のことをするのにこのコードは長過ぎるぞ。美的センスのカケラもない。

先生にセンスのことでとやかく言われたくないですね。見た目が不恰好でも良いコードだってあるでしょう。

ない。SQLにおいて見た目は大事だ。シンプルさは正義であり力だ。

おおっ,なんか最近お亡くなりになったカリスマのようなセリフ……。


ワイリーの解は,出荷可能な業者と出荷不可能な業者に処理を分割してから,その結果をUNION ALLでマージするというものです。これは,非常に自然な考え方に沿った解ですが,その結果,ほとんど同じ処理を2回繰り返すことになっています。実際,UNION ALLの前段と後段で異なるのは,出荷可能フラグ(ship_flg)「可」「不可」というパラメータだけで,文としての構造は同じです。この冗長さを治療する方法を,今から考えていきます。まずは,2つも使っているサブクエリSUM_ITEMから対処しましょう。

著者プロフィール

ミック

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

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

Twitter:copinemickmack

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

コメント

  • その通りです

    はい、EXISTSを使えばINは常に書き換え可能です。

    Commented : #2  ミック (2012/04/05, 21:28)

  • EXISTSの使用

    リスト6はEXISTSを使えばSQL Serverでも実行できるのではないでしょうか?

    https://gist.github.com/2233293

    Commented : #1  伊藤淳一 (2012/03/29, 13:32)

コメントの記入