複雑なSQLをデチューンしてみよう
初級編の続きは、
インデックスについて（カラオケ本で考えよう）
IN、EXISTSと違いについて
などなどを予定しているのですが、twitter で何度かつぶやいたもののまとめなので、ログなどと差は大きくはありません。
ちょっと訳あって、初級編をお休みして、複雑なSQLのデチューンについて書こうと思います。
というのも、本当に私は前提が抜けるというか、相手が理解したと勘違いして進めてしまうことが多いようです。
「『これはできないよ』と思う人が多いだろう」という予想で、複雑なSQLを演習問題として提示してきましたが、もしかすると、ハードルを上げすぎているのかと思い直しました。ストアドプロシージャで処理するにしても、そんな複雑なSQLでなくても当然できます。
私も提示しているSQLは、多分、現場では使いません。
実際に現場で使うのは、多少、デチューンしたものになりますので、何回かに分けてデチューンしていこうと思います。
まずは、演習問題
【要望】
食品メーカーのA社では、売上システムで請求処理をしていて、現在は消費税額を顧客の基準に合わせて計算している。
顧客マスタには以下の様な区分があり、
（消費税）丸め単位（1：明細単位、2：納品書単位、3：請求書単位）
（消費税）丸め方法（1：切捨て、2：四捨五入、3：切上げ）
その区分によって処理を分けている。
経理システムに連動するにあたり、請求締処理の段階で連動データを作ることになりました。
しかし、経理システムでは明細単位に消費税を持たねばならず、その合計が請求書の消費税額と一致する必要があるため、納品書単位、請求書単位の顧客のデータについては明細単位に消費税の誤差を配賦したデータを作ってください。
【配賦方法】
明細単位で丸めた消費税の合計と、納品書・請求書単位で算出した消費税の差を、明細単位の合計金額の多いものから順に1円ずつ増減する。
経理システムに連動するデータは以下のとおりです。
請求書NO
納品書NO
行NO
売上日
得意先ID
丸め単位
商品ID
商品区分
単価
数量
消費税
……
【テーブル】
関連するテーブルは次のとおり
売上
売上ID
納品書NO
請求書NO
得意先ID
売上日
……
売上明細
売上ID
行NO
商品ID
単価
数量
……
得意先
得意先ID
丸め単位（1：明細単位、2：納品書単位、3：請求書単位）
丸め方法（1：切捨て、2：四捨五入、3：切上げ）
……
商品
商品ID
商品区分
消費税率
……
※ OLAP関数を使います。PostgreSQL・MySQLなどではサブクエリーが相当必要です。
※ fn丸め(数値, 丸め方法)というストアドファンクションを使います。
さて、処理をイメージできましたでしょうか？
答え
SELECT a.請求書NO, a.納品書NO, a.行NO, a.売上日, a.得意先ID , a.丸め単位, a.商品ID, a.商品区分, a.単価, a.数量 , CASE WHEN a.丸め単位 = 1 THEN a.明細消費税 WHEN a.丸め単位 = 2 THEN a.明細消費税 + CASE WHEN ABS(a.納品書消費税 - a.納品明細合計) >= 納品SEQ THEN SIGN(a.納品書消費税 - a.納品明細合計) ELSE 0 END WHEN a.丸め単位 = 3 THEN a.明細消費税 + CASE WHEN ABS(a.請求書消費税 - a.請求明細合計) >= 請求SEQ THEN SIGN(a.請求書消費税 - a.請求明細合計) ELSE 0 END END AS 配賦後消費税 FROM (SELECT uh.請求書NO, uh.納品書NO, um.行NO, uh.売上日, uh.得意先ID , um.商品ID, p.商品区分, um.単価, um.数量 , um.単価 * um.数量 AS 明細合計 , fn丸め(um.単価 * um.数量 * p.消費税率, t.丸め方法) AS 明細消費税 , SUM(fn丸め(um.単価 * um.数量 * p.消費税率, t.丸め方法)) OVER (PARTITION BY uh.納品書NO) AS 納品明細合計 , fn丸め( SUM(um.単価 * um.数量) OVER (PARTITION BY uh.納品書NO) * p.消費税率, t.丸め方法) AS 納品書消費税 , ROW_NUMBER() OVER (PARTITION BY uh.納品書NO ORDER BY um.単価 * um.数量 DESC) AS 納品SEQ , SUM(fn丸め(um.単価 * um.数量 * p.消費税率, t.丸め方法)) OVER (PARTITION BY uh.請求書NO) AS 請求明細合計 , fn丸め( SUM(um.単価 * um.数量) OVER (PARTITION BY uh.請求書NO) * p.消費税率, t.丸め方法) AS 請求書消費税 , ROW_NUMBER() OVER (PARTITION BY uh.請求書NO ORDER BY um.単価 * um.数量 DESC) AS 請求SEQ , t.丸め方法, t.丸め単位 FROM 売上 uh INNER JOIN 売上明細 um ON uh.売上ID = um.売上ID INNER JOIN 得意先 t ON uh.得意先ID = t.得意先ID INNER JOIN 商品 p ON um.商品ID = p.商品ID WHERE uh.売上日 BETWEEN(締めの範囲) ) a;
注釈
実際にはテーブル構造が余りよくない。
こういう仕様があるときは、丸め単位を「売上」テーブルに非正規化しておいた方が良いでしょうし、消費税率も売上明細に非正規化しておく方が良いでしょう（これは消費税率が変わりそうな今では当然です）。
次回は、これをデチューンしていきます。
