model句21 複雑な計算結果を使った計算

SQLパズル

complexCalc
Val1  Val2  Val3  Val4  Flag
----  ----  ----  ----  ----
   1     2     3     4     0
   2     3     4     5     1
   3     4     5     6     0
   4     5     6     7     1

以下の代入演算を(上から順に)行った結果を求める。

Val1 := Val1+Val2+Val3+Val4;
Val2 := case when Flag = 0 then Val3+Val4
             else Val1+Val2 end;
Val3 := Val3+Val2;
Val4 := Val1+Val2+Val3;

出力結果
Val1  Val2  Val3  Val4  Flag
----  ----  ----  ----  ----
  10     7    10    27     0
  14    17    21    52     1
  18    11    16    45     0
  22    27    33    82     1

データ作成スクリプト

create table complexCalc(Val1,Val2,Val3,Val4,Flag) as
select 1,2,3,4,0 from dual union all
select 2,3,4,5,1 from dual union all
select 3,4,5,6,0 from dual union all
select 4,5,6,7,1 from dual;

SQL

--■■■インラインビューを何度も使う方法1■■■
select Val1,Val2,Val3,Val1+Val2+Val3 as Val4,Flag
from (select Val1,Val2,Val3+Val2 as Val3,Val4,Flag
from (select Val1,case when Flag = 0 then Val3+Val4
                       else Val1+Val2 end as Val2,Val3,Val4,Flag
from (select Val1+Val2+Val3+Val4 as Val1,Val2,Val3,Val4,Flag from complexCalc)))
order by Val1;

--■■■インラインビューを何度も使う方法2■■■
with work1 as (select Val1+Val2+Val3+Val4 as Val1,Val2,Val3,Val4,Flag from complexCalc),
work2 as (select Val1,case when Flag = 0 then Val3+Val4
                      else Val1+Val2 end as Val2,Val3,Val4,Flag from work1),
work3 as (select Val1,Val2,Val3+Val2 as Val3,Val4,Flag from work2)
select Val1,Val2,Val3,Val1+Val2+Val3 as Val4,Flag
  from work3
order by Val1;

--■■■model句を使う方法1■■■
select Val1,Val2,Val3,Val4,Flag
  from complexCalc
 model
 dimension by(RowNum as rn)
 measures(Val1,Val2,Val3,Val4,Flag)
 rules(
 Val1[any] = Val1[cv()]+Val2[cv()]+Val3[cv()]+Val4[cv()],
 Val2[any] = case when Flag[cv()] = 0 then Val3[cv()]+Val4[cv()]
                  else Val1[cv()]+Val2[cv()] end,
 Val3[any] = Val3[cv()]+Val2[cv()],
 Val4[any] = Val1[cv()]+Val2[cv()]+Val3[cv()])
order by Val1;

--■■■model句を使う方法2■■■
select Val1,Val2,Val3,Val4,Flag
  from complexCalc
 model
 dimension by(RowNum as rn)
 measures(Val1,Val2,Val3,Val4,Flag)
 rules(
 Val1[any] = max(Val1+Val2+Val3+Val4)[cv()],
 Val2[any] = max(case when Flag = 0 then Val3+Val4
                      else Val1+Val2 end)[cv()],
 Val3[any] = max(Val3+Val2)[cv()],
 Val4[any] = max(Val1+Val2+Val3)[cv()])
order by Val1;

--■■■model句を使う方法3■■■
select Val1,Val2,Val3,Val4,Flag
  from complexCalc
 model
 partition by(RowNum as PID)
 dimension by(0 as soeji)
 measures(Val1,Val2,Val3,Val4,Flag)
 rules(
 Val1[0] = max(Val1+Val2+Val3+Val4)[0],
 Val2[0] = max(case when Flag = 0 then Val3+Val4
                    else Val1+Val2 end)[0],
 Val3[0] = max(Val3+Val2)[0],
 Val4[0] = max(Val1+Val2+Val3)[0])
order by Val1;

--■■■表関数を使う方法■■■
create or replace package PackModel21 Is
    type PrintType is record(
    Val1 complexCalc.Val1%type,
    Val2 complexCalc.Val2%type,
    Val3 complexCalc.Val3%type,
    Val4 complexCalc.Val4%type,
    Flag complexCalc.Flag%type);

    type PrintTypeSet is table of PrintType;
end;
/

create or replace function PrintModel21 return PackModel21.PrintTypeSet PipeLined IS
    outR PackModel21.PrintType;
begin
    for rec in (select Val1,Val2,Val3,Val4,Flag
                  from complexCalc) Loop
        outR.Val1 := rec.Val1;
        outR.Val2 := rec.Val2;
        outR.Val3 := rec.Val3;
        outR.Val4 := rec.Val4;
        outR.Flag := rec.Flag;

        outR.Val1 := outR.Val1+outR.Val2+outR.Val3+outR.Val4;
        outR.Val2 := case when outR.Flag = 0 then outR.Val3+outR.Val4
                     else outR.Val1+outR.Val2 end;
        outR.Val3 := outR.Val3+outR.Val2;
        outR.Val4 := outR.Val1+outR.Val2+outR.Val3;

        pipe row(outR);
    end loop;
end;
/

sho err

select Val1,Val2,Val3,Val4,Flag from table(PrintModel21);

解説

インラインビューを多用するか、表関数を使うか、親言語で行う必要があった、
計算結果を使った計算の繰り返しも、model句を使えば、シンプルになります。

model句を使う方法3では、RowNumでパーティションを切ってあり、
パラレルに処理されて、パフォーマンスがいいかもしれません。

表関数を使う方法では、パッケージとfunctionを定義する必要がありますが、
model句では、必要ありません。

---