分析関数の衝撃

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

Lag関数とLead関数の使用例

山岸賢治[著]

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

ポスト

ダウンロード SourceCode (2.3 KB)

5. 「2日前の値」

　次も移動累計を求めるSQLのアレンジ問題です。次は2日前からの移動累計ではなく、2日前の値を求めるSQLを考えます。なお、2日前のデータがない場合は、0として扱います。

　まずは、テーブルのデータと出力結果を考えます。

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	before_prc_amt
2006-10-26	100	0
2006-10-28	200	100
2006-10-31	400	0
2006-11-03	800	0
2006-11-04	1600	0
2006-11-06	3200	1600
2006-11-11	6400	0

　答えは下記となります。

window関数で書いたSQL

select prc_date,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)
     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 before_prc_amt
from Accounts;

　前問と似たような考え方で、case式を使って、Lag関数で2行前のprc_dateを調べ、2日前ならprc_amtをcase式の値にし、同様に、Lag関数で1行前のprc_dateを調べ、2日前ならprc_amtをcase式の値にし、else句で2日前のデータがない場合は0として扱ってます。SQLのイメージは下記となります。

SQLのイメージ

　上記のSQLは、2日前の値ではなく、75日前の値を求める場合などにLag関数を大量に記述しないといけないので、相関サブクエリと外部結合で2日前の値を求めるSQLを紹介しておきましょう。

相関サブクエリで書いたSQL

select prc_date,prc_amt,
coalesce((select b.prc_amt
            from Accounts b
           where b.prc_date = a.prc_date-interVal '2 day')
         ,0) as before_prc_amt
from Accounts a;

外部結合で書いたSQL

select a.prc_date,a.prc_amt,
coalesce(b.prc_amt,0) as before_prc_amt
  from Accounts a Left Join Accounts b
    on a.prc_date-interVal '2 day' = b.prc_date;