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;
最後に
本稿では、『分析関数の衝撃3 （後編）』をPostgreSQL 8.4用にリニューアルした内容を扱いました。次回は、『分析関数の衝撃4 （完結編）』をPostgreSQL8.4用にリニューアルした内容を扱います。
参考資料
- 『9.19. ウィンドウ関数』（PostgreSQL 8.4.0文書）
PostgreSQLのマニュアルです。ウィンドウ関数に関する説明です。
- 『SQLクックブック』
「レシピ10.3 連続する値の範囲の最初と最後を求める」に本稿の「3. 最大何人まで座れますか？」で使った考え方が掲載されてます。
