OLAP（分析）関数について -- OLAP（分析）関数はSELECT句で並び替え
前回の続きです。やっとOLAP（分析）関数までたどり着きました。
ざっくりとした考え方（毎回）
GROUP BY は集約するので、結果が（集約キーを出力すれば）一意になる。つまり、出力される結果が一意になるまで集約される。
しかし、OLAP（分析）関数は、SELECTされた結果を区切って処理する。そのため、レコード数に変化はない。
集約する → GROUP BY
区切る → PARTITION BY
となる。これだけが分かっていれば実は簡単です。
納品書合計金額までをOLAP（分析）関数で計算する。
OLAP（分析）関数で処理すると
SELECT
納品書番号, 行, 商品ID, 単価, 数量, 消費税率
, 単価 * 数量 AS 合計
, ROUND(単価 * 数量 * 消費税率) AS 明細毎の消費税
, SUM(単価 * 数量) OVER (PARTITION BY 納品書番号) AS 納品書合計金額
FROM 納品書明細
WHERE （抽出条件）;
以下の様なイメージです。
SQL は FROM、WHERE、GROUP BY、HAVING、SELECT の順で処理されますが、OLAP（分析）関数は、FROM、WHERE、GROUP BY、HAVING、まで処理された結果の配列（内部処理は配列とは限らないけど……まあ、その辺は適当に読み替えて）にアクセスして処理します。結合したり抽出した結果を並べ替え、区切り（その範囲をWindowと呼ぶ）を入れて処理するのがOLAP関数です。
つまり、サブクエリーと違ってテーブルの実データに再びアクセスしないのが特徴です。
OLAP（分析）関数にはいろんな種類がありますが、ほとんどが集計関数と同じです。集計関数に加えてFIRST_VALUE（Windowの最初にある値）、ROW_NUMBER（Windowの範囲で並び替えた後の順番）、RANK（Windowの範囲での順位）など、集計関数ではできない関数もあります。（詳しくはそれぞれのRDBMSのマニュアルを見てください）
OLAP（分析）関数の例
例えば、
SUM(単価 * 数量) OVER (PARTITION BY 納品書番号)
「戻り値の配列で、納品書番号が同じになる行の『単価 * 数量』の合計を求める」ということになります。
COUNT(*) OVER (PARTITION BY 納品書番号)
とすれば、納品書番号毎の数が返ります。
また、PARTITION BY は必須ではありませんから、
COUNT(*) OVER ()
とすれば、SELECTした結果の件数が返ります。
並べ替えが必要な場合は ORDER BY を付けます。これは普段使っている ORDER BY と同様です。
ROW_NUMBER() OVER (PARTITION BY 納品書番号 ORDER BY 単価 * 数量 DESC)
Oracle や DB2 には他にも多彩なオプションがありますが、まだ、RDBMSによって対応がまちまちですので、詳しくはそれぞれのRDBMSのマニュアルを読んでください。細かい文法よりもイメージの方がはるかに重要です。
実行計画から……。
SQLServerで試した実行計画から、イメージでは必要な要素をコピーしていますが、配列をそのままソートするパターンの実行結果を返してくる RDBMS もあるでしょう。何度も書いていますが、SQLには「どう処理して欲しい」という記述はありません。ですから、要素をコピーして処理するか、元の配列を丸ごと並べ替えて処理するかは、オプティマイザ任せです。
また、
SELECT
……
, SUM(単価 * 数量) OVER (PARTITION BY 納品書番号)
, MAX(数量) OVER (PARTITION BY 商品ID)
……
のように、SELECT句の中に「納品書番号」「商品ID」と違ったキーを指定すると、当然、2回ソートが必要になります。では、以下の様に同じキーを指定するとどうなるか。
SELECT
……
, SUM(単価 * 数量) OVER (PARTITION BY 納品書番号)
……
, MAX(数量) OVER (PARTITION BY 納品書番号)
……
これもオプティマイザ任せですが、多くの RDBMS で複数のOLAP（分析）関数をまとめて処理してくれます。
つまり、イメージとして
retArray.Sort(納品書番号);
for(Row row : retArray){
// SUMの処理
}retArray.Sort(納品書番号);
for(Row row : retArray){
// MAXの処理
}
こんなダサイ処理ではなく、SQL文を解析して
retArray.Sort(納品書番号);
for(Row row : retArray){
// SUMの処理
// MAXの処理
}
とまとめてくれるわけです。
オプティマイザでもこのぐらいのことはやってくれるのですが、O/Rマッパなどを使って処理しているとソートキーなどを意識しないようになる人もいる。結果、オプティマイザ以下のアルゴリズムしか書けない連中が出てくる。そういうプログラムを見ると■■自■■主■■規■■制■■。
それはさておき、今回の要望を実現するには、以下の様に ORDER BY が違うものを利用することになる。
SELECT
……
, SUM(単価 * 数量) OVER (PARTITION BY 納品書番号)
……
, ROW_NUMBER() OVER (PARTITION BY 納品書番号 ORDER BY 単価 * 数量 DESC)
……
SUM はどんな順序でも、同じ結果になるので、
retArray.Sort(納品書番号, 単価 * 数量 DESC);
for(Row row : retArray){
// SUMの処理
// ROW_NUMBERの処理
}
として欲しいところだが、SQLServer 2005のオプティマイザでは ORDER BY が入るとキーが違うと考える様で、残念ながらまとめてはくれず2回ソートしてしまう。しかし、オプティマイザはバージョンが上がる度に賢くなるので、2008や2012などを試すと1回のソートになるかも知れないし、Oracleなどでも、まとめてくれるかも知れない。（私はマニアじゃないので他は試してないけれど……）
SQL、特に、OLAP（分析）関数の実行計画を見ればOO言語に近いという意味が分かります。「SQLは仕様書である」というイメージにも近づくかも知れませんから、是非、一度、実行計画を見てください。
