トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

10-235 クロスジョインとPartitioned Outer Joinその2

SQLパズル

testTable
Code1  Code2  Val  Target    Value
-----  -----  ---  --------  -----
  111    123  AAA  20080403     10
  111    123  BBB  20080430     20
  111    123  CCC  20080405     30
  222    123  AAA  20080402     40
  222    123  DDD  20080411     50
  222    789  BBB  20080416     60
  333    789  CCC  20080412     70

Code1,Code2ごとで、必ず3行となるように、
Valをnullとして行を補完する。(Code1,Code2ごとの行の数は、最大でも3行)

中間結果
Code1  Code2  Val   Target    Value
-----  -----  ----  --------  -----
  111    123  AAA   20080403     10
  111    123  BBB   20080430     20
  111    123  CCC   20080405     30
  222    123  AAA   20080402     40
  222    123  DDD   20080411     50
  222    123  null  null       null
  222    789  BBB   20080416     60
  222    789  null  null       null
  222    789  null  null       null
  333    789  CCC   20080412     70
  333    789  null  null       null
  333    789  null  null       null

上記の中間結果の各行に対して、
4月1日から4月30日までのデータをTargetとValueをnullとして補完する。
なお、出力結果は、12行*30行で、360行となる。

出力結果
day1      Code1  Code2  Val   Target    Value
--------  -----  -----  ----  --------  -----
20080401  111    123    AAA   null       null
20080402  111    123    AAA   null       null
20080403  111    123    AAA   20080403     10
省略
20080430  111    123    AAA   null       null
20080401  111    123    BBB   null       null
省略
20080430  111    123    BBB   20080430     20
20080401  111    123    CCC   null       null
省略
20080405  111    123    CCC   20080405     30
省略
20080430  111    123    CCC   null       null
20080401  222    123    AAA   null       null
20080402  222    123    AAA   20080402     40
省略
20080430  222    123    AAA   null       null
20080401  222    123    DDD   null       null
省略
20080411  222    123    DDD   20080411     50
省略
20080430  222    123    DDD   null       null
20080401  222    123    null  null       null
省略
20080430  222    123    null  null       null
20080401  222    789    BBB   null       null
省略
20080416  222    789    BBB   20080416     60
省略
20080430  222    789    BBB   null       null
20080401  222    789    null  null       null
省略
20080430  222    789    null  null       null
20080401  222    789    null  null       null
省略
20080430  222    789    null  null       null
20080401  333    789    CCC   null       null
省略
20080412  333    789    CCC   20080412     70
省略
20080430  333    789    CCC   null       null
20080401  333    789    null  null       null
省略
20080430  333    789    null  null       null
20080401  333    789    null  null       null
省略
20080430  333    789    null  null       null

こちらを参考にさせていただきました


データ作成スクリプト

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;


SQL

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;


解説

補完を伴うSQLでは、
Partitioned Outer Joinとクロスジョインを選択肢において、
SQLを考えるといいでしょう。

補完を伴うSQLでは、
表関数やmodel句を使うものいいでしょう。