SQL 分析関数Better Life

SQL 分析関数

• 1. 仕事
• 2008 12/01 (Mon)

プロジェクトでプログラミング専任の中国人プログラマーが見慣れないSQLを記述していました。

Sum(実績数) over (partition by 年月)

間違いかと思って試しに実行してみましたが、問題なくデータを抽出できる。。。
早速、partition by でネットを検索してみると、参考になりそうなサイトが沢山ヒット。
単に私が未熟なだけでした。　調べてみると、「分析(ウィンドウ)関数」というもので、
Oracleに限らず、SQL ServerやDB2でも使用できる標準的機能だとか。

自身のSQLの知識で、「取得できないデータは無い」と思っていました。
確かに同等のデータを取得できるのは間違いないのですが、SQLが簡素でレスポンスが良いと言う
この「分析関数」は、正に目から鱗でした。
今日は、まだ分析関数を知らないという人の為に、少し解説してみたいと思います。

通常、集計を行う際にはGROUP BYで集計単位を指定し、数値項目をサマリーしたりしますね。
例えば品番、得意先で売上を集計する場合には、

SELECT 品番, 得意先, SUM(売上数) AS 売上数
FROM 売上
GROUP BY 品番, 得意先

もしここで、ある品番のシェアを算出したいと言う場合にはどうするでしょうか？
分析関数を使わない場合には、品番別に売上数をサマリーしたSELECT文を追記するか、
もしくはクライアントアプリケーションで集計処理を行うかという事になります。
しかし、集計関数を使うと、下記の様にシンプルなSQLでこれを実現できてしまいます。

SELECT 品番, 得意先, SUM(売上数) AS 売上数,
SUM(SUM(売上数)) OVER(PARTITION BY 品番) AS 品番売上数合計
FROM 売上
GROUP BY 品番, 得意先

分析関数で集計した「品番売上数合計」は文字通り品番別の売上サマリーを返しますから、
後は分母が0にならないようにケアした除算を行えばシェアが算出できる事になります。

レポート関数を使用すると更に簡単です。

SELECT 品番, 得意先, SUM(売上数) AS 売上数,
RATIO_TO_REPORT(SUM(売上数)) OVER(PARTITION BY 品番) AS シェア
FROM 売上
GROUP BY 品番, 得意先

RATIO_TO_REPORT() を使用するといきなりシェアを算出してくれます。これって便利ですよね。
今までこの手のものはシステムで算出した結果を提供するというよりは、最低限のデータを
Excel形式で提供し、必要に応じてユーザー側でExcel上で算出してもらうようにしていました。
なぜなら、レスポンスを犠牲にしたSQLを記述して必要かどうかわからない項目を提供する事が
適当でないと判断していたからです。しかし、今回紹介したような分析関数やレポート関数の
存在を知った今、その考え方自体を見直す必要がありそうです。