5. 「2日前の値」
次も移動累計を求めるSQLのアレンジ問題です。次は2日前からの移動累計ではなく、2日前の値を求めるSQLを考えます。なお、2日前のデータがない場合は、0として扱います。
まずは、テーブルのデータと出力結果を考えます。
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 |
答えは下記となります。
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は、2日前の値ではなく、75日前の値を求める場合などにLag
関数を大量に記述しないといけないので、相関サブクエリと外部結合で2日前の値を求める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;
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. 最大何人まで座れますか?」で使った考え方が掲載されてます。