2-3-2 前日のレコードを取得

SQLパズル

table1
date1      Val1
---------  ----
2005/5/25     2
2005/5/24     1
2005/5/23     3
2005/5/20     6

Table1のDate1(Date型)と、Val1と、
Date1が前日のレコードのVal1(前日のレコードが存在しなければ0)
を取得する。
Table1のプライマリキーは、Date1とする。

出力結果
date1      Val1  BeforeVal1
---------  ----  ----------
2005/5/25     2           1
2005/5/24     1           3
2005/5/23     3           0
2005/5/20     6           0

データ作成スクリプト

create table table1(
date1 date,
Val1  number,
primary key(date1));

insert into table1 values(to_date('20050525','YYYYMMDD'),2);
insert into table1 values(to_date('20050524','YYYYMMDD'),1);
insert into table1 values(to_date('20050523','YYYYMMDD'),3);
insert into table1 values(to_date('20050520','YYYYMMDD'),6);
commit;

SQL

--■■■case式を使う方法■■■
select Date1,Val1,
case when exists(select 1 from Table1 b where b.Date1=a.Date1-1)
     then  (select b.Val1 from Table1 b where b.Date1=a.Date1-1)
     else 0 end as BeforeVal1
from Table1 a
order by Date1 desc;

--■■■decode関数を使う方法■■■
select Date1,Val1,
decode((select count(b.Date1) from Table1 b where b.Date1=a.Date1-1),0,0,
               (select b.Val1 from Table1 b where b.Date1=a.Date1-1)) as BeforeVal1
from Table1 a
order by Date1 desc;

--■■■nvlを使う方法(Val1がNot Nullの場合のみ使用できる)■■■
select Date1,Val1,
nvl((select b.Val1 from Table1 b where b.Date1=a.Date1-1),0) as BeforeVal1
from Table1 a
order by Date1 desc;

--■■■Lag関数を使う方法■■■
select Date1,Val1,
case when Lag(Date1) over(order by Date1) = Date1-1
     then Lag(Val1)  over(order by Date1) else 0 end as BeforeVal1
from Table1 a
order by Date1 desc;

--■■■Lead関数を使う方法■■■
select Date1,Val1,
case when Lead(Date1) over(order by Date1 desc) = Date1-1
     then Lead(Val1)  over(order by Date1 desc) else 0 end as BeforeVal1
from Table1 a
order by Date1 desc;

--■■■rangeを指定する方法■■■
select date1,Val1,
nvl(max(Val1) over(order by date1 desc
                   range between 1 following
                             and 1 following),0) as BeforeVal1
from table1
order by Date1 desc;

解説

case式とexistsを組み合わせる方法や、
case式と分析関数を組み合わせる方法などがあります。

---

分析関数の資料(SQLクリニック)
分析関数の資料(OTN)

---