10-220 必要な結合数(JoinCount)を求める

SQLパズル

MyTable
dt          shohin_cd  suryo
----------  ---------  -----
2007/10/01        100      4
2007/11/10        100      8
2007/12/01        100     -6
2007/10/19        105      7
2007/12/15        105      6
2007/08/18        325      7

shohin_cdごとに、今日の日付(2007/12/19)までの
レコードを補完する。

出力結果
dt        shohin_cd  suryo
--------  ---------  -----
07-10-01        100      4
07-10-02        100      4
省略
07-11-08        100      4
07-11-09        100      4
07-11-10        100      8
07-11-11        100      8
省略
07-11-29        100      8
07-11-30        100      8
07-12-01        100     -6
07-12-02        100     -6
省略
07-12-18        100     -6
07-12-19        100     -6
省略
07-10-19        105      7
07-10-20        105      7
省略
07-12-18        105      6
07-12-19        105      6
省略
07-08-18        325      7
07-08-19        325      7
省略
07-12-18        325      7
07-12-19        325      7

データ作成スクリプト

create table MyTable(dt,shohin_cd,suryo) as
select to_date('20071001','YYYYMMDD'),100, 4 from dual union
select to_date('20071110','YYYYMMDD'),100, 8 from dual union
select to_date('20071201','YYYYMMDD'),100,-6 from dual union
select to_date('20071019','YYYYMMDD'),105, 7 from dual union
select to_date('20071215','YYYYMMDD'),105, 6 from dual union
select to_date('20070818','YYYYMMDD'),325, 7 from dual;

SQL

--■■■model句を使わない方法■■■
select a.dt+b.Counter-1 as dt,a.shohin_cd,a.suryo
  from (select dt,shohin_cd,suryo,
        Lead(dt,1,trunc(sysdate+1))
        over(partition by shohin_cd order by dt)-dt as JoinCount
         from MyTable) a,
        (select RowNum as Counter from all_catalog) b
 where b.Counter <= a.JoinCount
order by a.shohin_cd,dt;

--■■■model句を使う方法(10g以降)■■■
select dt,shohin_cd,suryo
  from MyTable
 model
 partition by (RowNum as PID)
 dimension by (0 as soeji)
 measures(dt,shohin_cd,suryo,
          Lead(dt,1,date '2007-12-19'+1)
          over(partition by shohin_cd order by dt) as nextDt)
 rules ITERATE (1000) UNTIL (dt[0]+ITERATION_NUMBER = nextDt[0]-1)
 (dt[0+ITERATION_NUMBER]=dt[0]+ITERATION_NUMBER,
  shohin_cd[any]=shohin_cd[0],suryo[any]=suryo[0])
order by shohin_cd,dt;

解説

+1や、-1は、実際の値をイメージして考える、
ドラゴン桜の東京ドーム発想法を使うと分かりやすいでしょう。

Lead関数で次の行の値を求め、必要な結合数(JoinCount)を求めてます。
Lead関数の第3引数に、trunc(sysdate+1)を指定してますが、
Lead関数とLag関数は、nvl関数に近い機能を持っているのです。

---