7-64 カレンダーテーブルと結合

SQLパズル

カレンダーテーブル
日付
----------
2006/01/01
2006/01/02
・・・・
2006/12/30
2006/12/31

売上テーブル
ID  日付        数量
--  ----------  ----
 A  2006/02/01   100
 A  2006/02/03   200
 A  2006/02/05   300
 B  2006/03/01   100
 B  2006/03/04   200

売上テーブルの、IDごとに、
カレンダーテーブルと結合して、
以下の出力をする。

出力結果
ID  日付        数量
--  ----------  ----
 A  2006/02/01   100
 A  2006/02/02     0
 A  2006/02/03   200
 A  2006/02/04     0
 A  2006/02/05   300
 B  2006/03/01   100
 B  2006/03/02     0
 B  2006/03/03     0
 B  2006/03/04   200

データ作成スクリプト

create table カレンダー as
select to_date('20060101','yyyymmdd')+RowNum-1 as 日付
  from all_catalog
 where to_date('20060101','yyyymmdd')+RowNum-1 < to_date('20070101','yyyymmdd');

create table 売上(
ID   char(1),
日付 date,
数量 number(3));

insert into 売上 values('A',to_date('2006/02/01','yyyy/mm/dd'),100);
insert into 売上 values('A',to_date('2006/02/03','yyyy/mm/dd'),200);
insert into 売上 values('A',to_date('2006/02/05','yyyy/mm/dd'),300);
insert into 売上 values('B',to_date('2006/03/01','yyyy/mm/dd'),100);
insert into 売上 values('B',to_date('2006/03/04','yyyy/mm/dd'),200);
commit;

SQL

--■■■nvl関数を使う方法■■■
select a.ID,b.日付,
decode(a.日付,b.日付,a.数量,0) as 数量
from (select ID,数量,日付,
      nvl(Lead(日付-1) over(partition by ID order by 日付),日付) as Lead日付
      from 売上) a,カレンダー b
where b.日付 between a.日付 and a.Lead日付;

--■■■nvl関数を使わない方法■■■
select a.ID,b.日付,
decode(a.日付,b.日付,a.数量,0) as 数量
from (select ID,数量,日付,
      Lead(日付-1,1,日付) over(partition by ID order by 日付) as Lead日付
      from 売上) a,カレンダー b
where b.日付 between a.日付 and a.Lead日付;

解説

Lead関数で次の日付を取得してます。

---