分析関数の衝撃

PostgreSQLの分析関数の衝撃3
（数列を扱うSQLとrange指定）

Lag関数とLead関数の使用例

山岸賢治[著]

更新日: 2009/08/18
公開日: 2009/08/18

ポスト

ダウンロード SourceCode (2.3 KB)

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;

次のページ
5. 「2日前の値」