SQL「OLAP関数」だじぇ

 WEB+DB PRESSのvol55の掲載から。

SQLにはOLAP関数っていうものがあります。

2003年に標準SQLで標準化されたそうです。

OLAP＝OnLine Analytical Processing

（稼働環境）

・Oracle

・SQLServer

・DB2

・ PostgreSQL(8.4以降）

説明に使うテーブル。

■口座テーブル(Accounts)

口座ID (account_id)	記録日 (record_date)	処理金額 (prc_amount)
A	2010-01-16	1,000
A	2010-01-17	20,000
A	2010-01-18	-3,000
A	2010-01-19	-500,000
A	2010-01-20	23,000
B	2010-01-16	48,000
B	2010-01-17	98,000
B	2010-01-18	-5,000
B	2010-01-19	30,000
B	2010-01-20	-100,000
C	2010-01-16	5,000
C	2010-01-17	12,000
C	2010-01-18	30,000
C	2010-01-19	1,000
C	2010-01-20	500

OLAP関数は、大きく以下2つに分けられる。

①通常の集約関数（SUM/AVG/COUNT/MAX/MIN）をOLAP関数として使う。

②RANK,ROW_NUMBERなどOLAP専用の関数

例えばこんなSQLになります。

SELECT
    account_id,
    record_date,
    prc_amt,
    AVG(prc_amt) OVER(PARTITION BY account_id ORDER BY record_date) AS cumulative_arg
FROM Accounts;

■実行結果

account_id	record_date	amount	cumulative_avg
A	2010-01-16	1000	1,000
A	2010-01-17	20000	10,500
A	2010-01-18	-3000	6,000
A	2010-01-19	500000	-120,500
A	2010-01-20	23000	-91,800
B	2010-01-16	48000	48,000
B	2010-01-17	98000	73,000
B	2010-01-18	5000	47,000
B	2010-01-19	30000	42,750
B	2010-01-20	-100000	14,200
C	2010-01-16	5000	5,000
C	2010-01-17	12000	8,500
C	2010-01-18	30000	15,667
C	2010-01-19	1000	12,000
C	2010-01-20	500	9,700

account_idごとに過去からその当日までの平均が計算されています。

集約されません。

PRECEDINGを使うと、直近Nレコードに制限できます。

SELECT
    account_id,
    record_date,
    prc_amt,
    AVG(prc_amt) OVER(PARTITION BY account_id
                      ORDER BY record_date
                      ROWS 2 PRECEDING) AS cumulative_arg
FROM Accounts;

■実行結果

account_id	record_date	amount	cumulative_avg
A	2010-01-16	1000	1,000
A	2010-01-17	20000	10,500
A	2010-01-18	-3000	6,000
A	2010-01-19	-500000	-161,000
A	2010-01-20	23000	-160,000
B	2010-01-16	48000	48,000
B	2010-01-17	98000	73,000
B	2010-01-18	-5000	47,000
B	2010-01-19	30000	41,000
B	2010-01-20	-100000	-25,000
C	2010-01-16	5000	5,000
C	2010-01-17	12000	8,500
C	2010-01-18	30000	15,667
C	2010-01-19	1000	14,333
C	2010-01-20	500	10,500

「ROWS 2 PRECEDING」は「カレントレコードから過去の直近2レコード」という制限という意味。

PRECEDINGの代わりにFOLLOWINGを付ければ逆になる。

便利そう。

実業務では、こんな計算が必要になることもたまにあるから。

知ってれば苦労しなかったこともあったろうなぁ・・・。