2-3-34 日付の最小値-1から日付の最大値+1まで補完

SQLパズル

DayTable
     YMD  Val
--------  ---
20080528  111
20080603  555
20080605  999

DayTableの
最小のYMD-1から
最大のYMD+1まで
のレコードをValをnullとして補完する。

出力結果
     YMD   Val
--------  ----
20080527  null
20080528   111
20080529  null
20080530  null
20080531  null
20080601  null
20080602  null
20080603   555
20080604  null
20080605   999
20080606  null

データ作成スクリプト

create table DayTable(YMD,Val) as
select '20080528',111 from dual union
select '20080603',555 from dual union
select '20080605',999 from dual;

SQL

--■■■表関数を使わない方法■■■
col YMD for a10

select to_char(a.DayFrom+b.Counter-1,'yyyymmdd') as YMD,c.Val
from (select min(to_date(YMD,'yyyymmdd'))-1 as DayFrom,
             max(to_date(YMD,'yyyymmdd'))+1 as DayTo
        from DayTable) a
     Join (select RowNum as Counter from all_objects) b
       on (b.Counter <= a.DayTo-a.DayFrom +1)
     Left Join DayTable c
       on (c.YMD = to_char(a.DayFrom+b.Counter-1,'yyyymmdd'))
order by YMD;

--■■■表関数を使う方法■■■
create or replace package Pack02_03_24 Is
    type PrintType is record(
    YMD DayTable.YMD%type,
    Val DayTable.Val%type);

    type PrintTypeSet is table of PrintType;
end;
/

create or replace function FuncDayT return Pack02_03_24.PrintTypeSet PipeLined IS
    outR Pack02_03_24.PrintType;
    work char(8);
begin
    for rec in (select YMD,Val,Lead(YMD) over(order by YMD) as nextYMD,
                RowNum as rn,count(*) over() as recCnt
                  from DayTable order by YMD) Loop
        if (rec.rn = 1) then
            outR.YMD := to_char(to_date(rec.YMD,'yyyymmdd')-1,'yyyymmdd');
            outR.Val := null;
            pipe row(outR);
        end if;
        if (rec.rn = rec.recCnt) then
            outR.YMD := to_char(to_date(rec.YMD,'yyyymmdd')+1,'yyyymmdd');
            outR.Val := null;
            pipe row(outR);
        end if;

        outR.YMD := rec.YMD;
        outR.Val := rec.Val;
        pipe row(outR);

        if (rec.nextYMD is not null) then
            work := to_char(to_date(rec.YMD,'yyyymmdd')+1,'yyyymmdd');

            while (work < rec.nextYMD) Loop
                outR.YMD := work;
                outR.Val := null;
                pipe row(outR);

                work := to_char(to_date(work,'yyyymmdd')+1,'yyyymmdd');
            end Loop;
        end if;
    end Loop;
end;
/

sho err

select YMD,Val from table(FuncDayT)
order by YMD;

解説

DayTableへのアクセスを1回にするのは、表関数を使わないと難しそうですね。

---