10-303 リセット機能付の累計

SQLパズル

modelRun
SK  Val
--  ---
 1    0
 2   10
 3   20
 4  -15
 5  -20
 6   10
 7    5
 8   50
 9   15
10    0
11  -10
12  -30
13  -40
14  100
15  -20
16   40
17   35
18   20
19   10

SKをソートキーとしてのValの累計を求める。
ただし、累計がプラスならリセットして0とする。
また、累計がマイナスなら0として表示する。

出力結果
SK  Val  runSum
--  ---  ------
 1    0       0
 2   10      10
 3   20      20
 4  -15       0    -15
 5  -20       0    -35 (-15-20)
 6   10       0    -25 (-15-20+10)
 7    5       0    -20 (-15-20+10+5)
 8   50      30     30 (-15-20+10+5+50)
 9   15      15
10    0       0
11  -10       0    -10
12  -30       0    -40 (-10-30)
13  -40       0    -80 (-10-30-40)
14  100      20     20 (-10-30-40+100)
15  -20       0    -20
16   40      20     20 (-20+40)
17   35      35
18   20      20
19   10      10

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

データ作成スクリプト

create table modelRun(SK,Val) as
select  1,  0 from dual union
select  2, 10 from dual union
select  3, 20 from dual union
select  4,-15 from dual union
select  5,-20 from dual union
select  6, 10 from dual union
select  7,  5 from dual union
select  8, 50 from dual union
select  9, 15 from dual union
select 10,  0 from dual union
select 11,-10 from dual union
select 12,-30 from dual union
select 13,-40 from dual union
select 14,100 from dual union
select 15,-20 from dual union
select 16, 40 from dual union
select 17, 35 from dual union
select 18, 20 from dual union
select 19, 10 from dual;

SQL

--■■■model句を使う方法(10g以降)■■■
select SK,Val,greatest(runSum,0) as runSum
  from modelRun
 model
dimension by(SK)
measures(Val,0 as runSum)
rules(
runSum[any] order by SK =
 Least(presentv(runSum[cv()-1],runSum[cv()-1],0),0)
+Val[cv()]);

--■■■表関数を使う方法■■■
create or replace Package Pack10_303 Is
    type ReturnType is record(
    SK     modelRun.SK%type,
    Val    modelRun.Val%type,
    runSum number(3));

    type ReturnTypeSet is table of ReturnType;
end;
/

create or replace function modelRunFunc return Pack10_303.ReturnTypeSet PipeLined IS
    outR Pack10_303.ReturnType;
    runSum number(3) :=0;
begin
    for rec in (select SK,Val from modelRun order by SK) Loop
        runSum := Least(runSum,0) + rec.Val;

        outR.SK := rec.SK;
        outR.Val := rec.Val;
        outR.runSum := greatest(runSum,0);

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

sho err

select SK,Val,runSum from table(modelRunFunc);

解説

model句の使いどころでしょうねぇ

10-305 Lagな累計(ゼロクリアあり)

---