OLAP（分析）関数について -- 完成
前回の続きです。やっと完成です。
ざっくりとした考え方（毎回）
GROUP BY は集約するので、結果が（集約キーを出力すれば）一意になる。つまり、出力される結果が一意になるまで集約される。
しかし、OLAP（分析）関数は、SELECTされた結果を区切って処理する。そのため、レコード数に変化はない。
集約する → GROUP BY
区切る → PARTITION BY
となる。これだけが分かっていれば実は簡単です。
SQLの完成。
前回説明したように、OLAP（分析）関数を使ってイメージの通り順に考えて行くと、サブクエリーの中には不要なものも入っていますが、以下の様なSQLが完成します。
SELECT
納品書番号, 行, 商品ID, 単価, 数量, 消費税率
, 明細計, 納品書合計金額, 納品書消費税額
, CASE WHEN ABS(消費税誤差) >= 金額の多い順 THEN 明細毎の消費税 + SIGN(消費税誤差)
ELSE 明細毎の消費税 END AS 補正後消費税
FROM
(SELECT
納品書番号, 行, 商品ID, 単価, 数量, 消費税率
, 単価 * 数量 AS 明細計
, ROUND(単価 * 数量 * 消費税率) AS 明細毎の消費税
, SUM(ROUND(単価 * 数量 * 消費税率)) OVER (PARTITION BY 納品書番号) AS 明細毎の消費税合計
, SUM(単価 * 数量) OVER (PARTITION BY 納品書番号) AS 納品書合計金額
, ROUND(SUM(単価 * 数量 * 消費税率) OVER (PARTITION BY 納品書番号)) AS 納品書消費税額
, SUM(ROUND(単価 * 数量 * 消費税率)) OVER (PARTITION BY 納品書番号)
- ROUND(SUM(単価 * 数量 * 消費税率) OVER (PARTITION BY 納品書番号)) AS 消費税誤差
, ROW_NUMBER() OVER (PARTITION BY 納品書番号 ORDER BY 単価 * 数量 DESC) AS 金額の多い順
FROM 納品書明細
WHERE （抽出条件）
) a;
順位と誤差の絶対値と比べ、符号（SIGN）を足す。SIGNが思いつかなければ、更にCASE式を重ねても問題ありません（読みにくくなるけどね）が、そこがトリッキーと考える人もいるかも知れません（というか多いようです）。そういう人達は、手続き型で考えている、つまり、頭の中でアルゴリズムを組んでいて『誤差がなくなるまで繰り返す』となっていから、「トリッキー」と感じたり、「意味が分からない」となってしまうのです。
しかし、プログラムとは関係なく、エクセルで、エクセルの関数と式を使って表を完成させようとすれば、順位と誤差の絶対値を比べればよいと気づくはずです。（こればっかりは自分でやってみないとどうしようもないですよ）
一気にできるといいながら、なぜサブクエリーを使っているかというと、SQLの欠点として、SELECT句で付けた列のエイリアスを、そのSELECT句で使えないからです。エクセルで言うと、式が複雑になるために出力用のシートの他に計算用のシートを分けて使っているだけのイメージで、パフォーマンスには影響しません。
オプティマイザを作る気になって考える。
なぜ、SELECT句で付けたエイリアスを、そのSELECT句で使えないかというと……、オプティマイザを作る側に立てば何となく分かると思います。
SELECT
……
, SUM(単価 * 数量) OVER (PARTITION BY 納品書番号)
……
, MAX(数量) OVER (PARTITION BY 納品書番号)
……
というSQLをオプティマイザが解析し、以下の様なプログラムを作って実行してくれます。
retArray.Sort(納品書番号);
for(Row row : retArray){
// SUMの処理
// MAXの処理
}
オプティマイザは、SELECT句に書かれた順番は関係なく効率的な順序で処理していきます。しかし、そこに別名があれば、まずそれを先に処理しなければならないという条件が追加されることになります。これを嫌っているのかも知れません。
それぐらい別名を元のカラムに展開すればできると思うけどね……。
（別名が式の中で入れ子になったとしても、ちょっとした再帰プログラム書けばできるやん……。）
もう一つの理由も憶測ですが、言語仕様を決めたときのミスが原因でしょう。
例えば、
SELECT
SUM(m.金額) AS 金額
って通るでしょう。別のエイリアスを考えるのがめんどくさいから私も結構やります。
エイリアスの基準をアバウトなまま言語仕様を作ってしまったので、アバウトさを維持したまま、SELECT句で設定したエイリアスを、そのSELECT句で使ったSQLを解析するオプティマイザを作るのが難しいからではないかと考えています。
まあ、どちらかの理由だろうけれど、私がSQLの仕様を決める立場なら「識別子を追加しよう」って運動を起こすのですが、お呼びじゃないので（苦笑）。
サブクエリーを前に書く。
それはともかく、もう少しスマートな書き方もあります。
WITH
計算済納品書明細 AS
(SELECT
納品書番号, 行, 商品ID, 単価, 数量, 消費税率
, 単価 * 数量 AS 明細計
, ROUND(単価 * 数量 * 消費税率) AS 明細毎の消費税
, SUM(ROUND(単価 * 数量 * 消費税率)) OVER (PARTITION BY 納品書番号) AS 明細毎の消費税合計
, SUM(単価 * 数量) OVER (PARTITION BY 納品書番号) AS 納品書合計金額
, ROUND(SUM(単価 * 数量 * 消費税率) OVER (PARTITION BY 納品書番号)) AS 納品書消費税額
, SUM(ROUND(単価 * 数量 * 消費税率)) OVER (PARTITION BY 納品書番号)
- ROUND(SUM(単価 * 数量 * 消費税率) OVER (PARTITION BY 納品書番号)) AS 消費税誤差
, ROW_NUMBER() OVER (PARTITION BY 納品書番号 ORDER BY 単価 * 数量 DESC) AS 金額の多い順
FROM 納品書明細
WHERE （抽出条件）
)
SELECT
納品書番号, 行, 商品ID, 単価, 数量, 消費税率
, 明細計, 納品書合計金額, 納品書消費税額
, CASE WHEN ABS(消費税誤差) >= 金額の多い順 THEN 明細毎の消費税 + SIGN(消費税誤差)
ELSE 明細毎の消費税 END AS 補正後消費税
FROM
計算済納品書明細;
まあ、同じですけど、個人的にはこちらの方がきれいで読みやすいと思います。いずれにしても、SELECT句で使えるエイリアスの識別子を追加してくれたら、こういうサブクエリーは不要になります。なんとかして欲しいところですね。
まとめ
とにかく、SQLはイメージが大事です。頭の中でループのイメージが出た時点で書けなくなります。つまり、今回の例では『誤差がなくなるまで繰り返す』ってなった時点で SQL じゃなくなっています。
SQLじゃないイメージから作られた SQL は文法上は正しくても、イメージに戻せませんから読めません。メンテもできません。
慣れるまでは、エクセルで欲しい結果を作ってみて下さい。IF関数も含め、関数で欲しい結果が表現できれば SQL でできます。
例えば、
IF関数 → CASE式
VLOOKUP関数 → SELECT句のサブクエリー
別シートで計算 → FROM句のサブクエリー
SQLにない関数 → ストアドファンクション
というイメージで考えればよいでしょう。
慣れたら、エクセルなしで頭の中にエクセルのイメージができる様になるし、更に、慣れたら、要望 ＝ SQL という状態になります。
SQLが難しく感じる人はイメージがゴールのイメージができてないからです。
SELECT
……
, SUM(単価 * 数量) OVER (PARTITION BY 納品書番号)
……
, MAX(数量) OVER (PARTITION BY 納品書番号)
……
とすれば
retArray.Sort(納品書番号);
for(Row row : retArray){
// SUMの処理
// MAXの処理
}
のようなプログラムを自動的に作って実行してくれるのに、それを改めて作り直して、ネットワークを挟んでループして「なにが悪い！」と言われても……■■自■■主■■規■■制■■。
もしよかったら、以下も一度読んでみてください。
http://d.hatena.ne.jp/Sikushima/20101111/1289463762
http://d.hatena.ne.jp/Sikushima/20101112/1289541967
http://d.hatena.ne.jp/Sikushima/20101122/1290417818
