8-51 クロスジョインやmodel句で、行の追加

SQLパズル

NRowsTable
ID  Order_D    Process_D
--  ---------  ---------
 1  2005-9-25  2005-9-26
 2  2007-1-10  2007-1-11
 3  2008-3-27  2008-3-28

IDごとに、
2行を補完する。

Verifiedは、Order_Dの2日後
shippedは、Order_Dの3日後
とする。

出力結果
ID  Order_D    Process_D  Verified   shipped
--  ---------  ---------  ---------  ---------
 1  2005-9-25  2005-9-26  null       null
 1  2005-9-25  2005-9-26  2005-9-27  null
 1  2005-9-25  2005-9-26  2005-9-27  2005-9-28
 2  2007-1-10  2007-1-11  null       null
 2  2007-1-10  2007-1-11  2007-1-12  null
 2  2007-1-10  2007-1-11  2007-1-12  2007-1-13
 3  2008-3-27  2008-3-28  null       null
 3  2008-3-27  2008-3-28  2008-3-29  null
 3  2008-3-27  2008-3-28  2008-3-29  2008-3-30

SQLクックブックのレシピ11.12を参考にさせていただきました

データ作成スクリプト

create table NRowsTable(ID,Order_D,Process_D) as
select 1,date '2005-09-25',date '2005-09-26' from dual union
select 2,date '2007-01-10',date '2007-01-11' from dual union
select 3,date '2008-03-27',date '2008-03-28' from dual;

SQL

--■■■model句を使う方法(10g以降)■■■
select ID,Order_D,Process_D,Verified,shipped
  from NRowsTable
 model
 partition by(ID)
 dimension by(1 as soeji)
 measures(Order_D,Process_D,to_date(null) as Verified,to_date(null) as shipped)
 rules(
 Order_D  [for soeji in(2,3)] = Order_D[1],
 Process_D[for soeji in(2,3)] = Process_D[1],
 Verified [for soeji in(2,3)] = Order_D[1]+2,
 shipped[3] = Order_D[1]+3)
order by ID,soeji;

--■■■grouping setsを使う方法■■■
select ID,Order_D,Process_D,
case when group_ID() in(1,2) then Order_D+2 end as Verified,
decode(group_ID(),2,Order_D+3) as shipped
  from NRowsTable
group by grouping sets((ID,Order_D,Process_D),
                       (ID,Order_D,Process_D),
                       (ID,Order_D,Process_D))
order by ID,Verified desc,shipped desc;

--■■■union allを使う方法■■■
select ID,Order_D,Process_D,to_date(null) as Verified,to_date(null) as shipped from NRowsTable
union all
select ID,Order_D,Process_D,Order_D+2,to_date(null) from NRowsTable
union all
select ID,Order_D,Process_D,Order_D+2,Order_D+3 from NRowsTable
order by ID,4 desc,5 desc;

--■■■クロスジョインを使う方法■■■
select a.ID,a.Order_D,a.Process_D,
case when b.Column_Value in(2,3)
     then a.Order_D +2 end as Verified,
decode(b.Column_Value,3,a.Order_D+3) as shipped
  from NRowsTable a,table(sys.odciNumberList(1,2,3)) b
order by a.ID,Verified desc,shipped desc;

解説

行を追加する方法は、状況に応じて使い分けるといいでしょう。

---