create table test(Code1,Code2,Val,Target,Value) as
select 111,123,'AAA',20080403,10 from dual union
select 111,123,'BBB',20080430,20 from dual union
select 111,123,'CCC',20080405,30 from dual union
select 222,123,'AAA',20080402,40 from dual union
select 222,123,'DDD',20080411,50 from dual union
select 222,789,'BBB',20080416,60 from dual union
select 333,789,'CCC',20080412,70 from dual;
col Val for a10
--■■■Partitioned Outer Joinにこだわった方法(10g以降)■■■
select c.day1,d.Code1,d.Code2,d.Val,d.Target,d.Value
from (select 20080401 + RowNum -1 as day1
from all_catalog
where RowNum <= 30) c
Left outer Join (select b.Code1,b.Code2,b.Val,b.Target,b.Value,a.Counter
from (select 1 as Counter from dual union all
select 2 from dual union all
select 3 from dual) a
Left outer join (select Code1,Code2,Val,Target,Value,
Row_Number() over(partition by Code1,Code2 order by Val) as Rn
from test) b
partition by (b.Code1,b.Code2)
on (a.Counter = b.Rn)) d
partition by (d.Code1,d.Code2,d.Val,d.Counter)
on (d.Target = c.day1)
order by d.Code1,d.Code2,d.Val,d.Counter,c.day1;
--■■■Partitioned Outer Joinとクロスジョインを使う方法(10g以降)■■■
select c.day1,b.Code1,b.Code2,b.Val,
decode(b.Target,c.day1,b.Target) as Target,
decode(b.Target,c.day1,b.Value) as Value
from (select 1 as Counter from dual union all
select 2 from dual union all
select 3 from dual) a
Left outer join (select Code1,Code2,Val,Target,Value,
Row_Number() over(partition by Code1,Code2 order by Val) as Rn
from test) b
partition by (b.Code1,b.Code2)
on (a.Counter = b.Rn)
cross Join (select 20080401 + RowNum -1 as day1
from all_catalog
where RowNum <= 30) c
order by b.Code1,b.Code2,b.Val,a.Counter,c.day1;
--■■■クロスジョインとdecode関数を組み合わせる方法■■■
select day1,Code1,Code2,Val,Target,Value
from (select b.day1,c.Code1,c.Code2,a.Counter,decode(Counter,1,Val) as Val,
case when Counter = 1 and c.Target = b.day1 then c.Target end as Target,
case when Counter = 1 and c.Target = b.day1 then c.Value end as Value,
Row_Number() over(partition by c.Code1,c.Code2,b.day1 order by Counter) as rn
from (select 1 as Counter from dual union all
select 2 from dual union all
select 3 from dual) a,
(select 20080401 + RowNum -1 as day1
from all_catalog
where RowNum <= 30) b,test c)
where rn <= 3
order by Code1,Code2,Val,Counter,day1;
--■■■model句を使う方法(10g以降)■■■
select day1,Code1,Code2,Val,Target,Value
from test
model
partition by (Code1,Code2)
dimension by (Row_Number() over(partition by Code1,Code2 order by Val) as Rn,Target as day1)
measures(Target,Val,Value)
rules(Val [for Rn from 1 to 3 INCREMENT 1,for day1 from 20080401 to 20080430 INCREMENT 1]
= max(Val)[CV(),any],
Target[for Rn from 1 to 3 INCREMENT 1,for day1 from 20080401 to 20080430 INCREMENT 1]
= Target[cv(),cv()])
order by Code1,Code2,Val,Rn,day1;