4. 「2日前からの累計」
次は移動累計を求めるSQLのアレンジ問題です。『PostgreSQLの分析関数の衝撃2』では、『相関サブクエリで行と行を比較する』の移動累計を求めるSQLを扱いましたが、これをアレンジして2日前からの移動累計を求めるSQLを考えます。
まずは、値を少し変更したテーブルのデータと出力結果を考えます。
Accounts
| prc_date | prc_amt |
| 2006-10-26 | 100 |
| 2006-10-28 | 200 |
| 2006-10-31 | 400 |
| 2006-11-03 | 800 |
| 2006-11-04 | 1600 |
| 2006-11-06 | 3200 |
| 2006-11-11 | 6400 |
出力結果
| prc_date | prc_amt | mvg_sum |
| 2006-10-26 | 100 | 100 |
| 2006-10-28 | 200 | 300 |
| 2006-10-31 | 400 | 400 |
| 2006-11-03 | 800 | 800 |
| 2006-11-04 | 1600 | 2400 |
| 2006-11-06 | 3200 | 4800 |
| 2006-11-11 | 6400 | 6400 |
OracleやDB2の分析関数では、range 2 Precedingといった指定ができますので下記のようなSQLが使えましたが、PostgreSQL 8.4では文法エラーになります。
PostgreSQL8.4では文法エラー
select prc_date,prc_amt, sum(prc_amt) over(order by prc_date range interVal '2 day' Preceding) as mvg_sum from Accounts;
答えは下記となります。
window関数で書いたSQL
select prc_date,prc_amt,
prc_amt+
case when Lag(prc_date,2) over(order by prc_date) >= prc_date-interVal '2 day'
then Lag(prc_amt,2) over(order by prc_date) else 0 end +
case when Lag(prc_date) over(order by prc_date) >= prc_date-interVal '2 day'
then Lag(prc_amt) over(order by prc_date) else 0 end as mvg_sum
from Accounts;
case式を使って、Lag関数で2行前のprc_dateを調べ、2日前以降ならprc_amtを加算し、同様に、Lag関数で1行前のprc_dateを調べ、2日前以降ならprc_amtを加算しています。SQLのイメージは下記となります。
SQLのイメージ
上記のSQLは、2日前からの累計ではなく、75日前からの累計を求める場合などにLag関数を大量に記述しないといけないので、相関サブクエリで2日前からの累計を求めるSQLを紹介しておきましょう。
相関サブクエリで書いたSQL
select prc_date,prc_amt,
(select sum(b.prc_amt)
from Accounts b
where b.prc_date between a.prc_date-interVal '2 day'
and a.prc_date) as mvg_sum
from Accounts a;
