10-73 日付の埋め立てその2

SQLパズル

hoge2テーブル
CD  CD_EDA    S_DATE    FLG
--  ------  ----------  ---
 1       1  2006/01/01    1
 1       1  2006/01/03    2
 1       1  2006/01/05    3
 1       2  2006/01/01    1
 1       2  2006/01/06    2
 1       3  2006/01/01    1
 2       1  9999/12/20    1
 2       2  9999/12/22    2
 3       1  9999/12/25    1

CDとCD_EDAの組み合わせごとに、
s_dateが出てくるまで、
flgに前のs_dateと同じflgの値を入れつつ
日付を一日ずつ足していく
といった出力をする。

出力結果
CD  CD_EDA    S_DATE    FLG
--  ------  ----------  ---
 1       1  2006/01/01    1
 1       1  2006/01/02    1
 1       1  2006/01/03    2
 1       1  2006/01/04    2
 1       1  2006/01/05    3
 1       2  2006/01/01    1
 1       2  2006/01/02    1
 1       2  2006/01/03    1
 1       2  2006/01/04    1
 1       2  2006/01/05    1
 1       2  2006/01/06    2
 1       3  2006/01/01    1
 2       1  9999/12/20    1
 2       1  9999/12/21    1
 2       2  9999/12/22    2
 3       1  9999/12/25    1

データ作成スクリプト

create table hoge2(
cd     number(1),
cd_eda number(1),
s_date date,
flg    number(1));

insert into hoge2 values (1,1,to_date('20060101','yyyymmdd'),1);
insert into hoge2 values (1,1,to_date('20060103','yyyymmdd'),2);
insert into hoge2 values (1,1,to_date('20060105','yyyymmdd'),3);
insert into hoge2 values (1,2,to_date('20060101','yyyymmdd'),1);
insert into hoge2 values (1,2,to_date('20060106','yyyymmdd'),2);
insert into hoge2 values (1,3,to_date('20060101','yyyymmdd'),1);
insert into hoge2 values (2,1,to_date('99991220','yyyymmdd'),1);
insert into hoge2 values (2,1,to_date('99991221','yyyymmdd'),1);
insert into hoge2 values (2,2,to_date('99991222','yyyymmdd'),2);
insert into hoge2 values (3,1,to_date('99991225','yyyymmdd'),1);
commit;

SQL

--■■■all_catalogへのアクセスにCountStopを使わない方法■■■
select CD,CD_EDA,S_DATE+Counter-1 as S_DATE,FLG
from (select CD,CD_EDA,S_DATE,FLG,
      Lead(S_DATE) over(partition by CD,CD_EDA order by S_DATE) as LeadDate
      from hoge2) a,(select RowNum as Counter from all_catalog) b
where b.Counter <= nvl(a.LeadDate-a.S_DATE,1)
order by CD,CD_EDA,S_DATE;

--■■■all_catalogへのアクセスにCountStopを使う方法■■■
select CD,CD_EDA,S_DATE+Counter-1 as S_DATE,FLG
from (select CD,CD_EDA,S_DATE,FLG,
      Lead(S_DATE) over(partition by CD,CD_EDA order by S_DATE) as LeadDate
      from hoge2) a,(select RowNum as Counter from all_catalog
                      where RowNum <= (select max(max(s_date)-min(s_date))
                                         from hoge2
                                        group by CD,CD_EDA)) b
where b.Counter <= nvl(a.LeadDate-a.S_DATE,1)
order by CD,CD_EDA,S_DATE;

--■■■model句を使う方法(10g以降)■■■
select CD,CD_EDA,S_DATE,FLG
  from hoge2
 model
 partition by (RowNum as PID)
 dimension by (0 as soeji)
 measures(CD,CD_EDA,S_DATE,FLG,
          Lead(S_DATE,1,S_DATE+1)
          over(partition by CD,CD_EDA order by S_DATE) as nextDt)
 rules ITERATE (1000) UNTIL (S_DATE[0]+ITERATION_NUMBER = nextDt[0]-1)
 (S_DATE[0+ITERATION_NUMBER]=S_DATE[0]+ITERATION_NUMBER,
  CD[any]=cd[0],CD_EDA[any]=CD_EDA[0],FLG[any]=flg[0])
order by CD,CD_EDA,S_DATE;

解説

自レコードと次のレコードとの、日の差を、
結合するレコード数としてます。

---