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

SQLパズル

LagSumTable
sortKey  Val
-------  ---
      1    1
      2    2
      3    4
      4    8
      5    0
      6    0
      7    2
      8    4
      9    8
     10    0
     11    5
     12    6

sortKeyの昇順でValの累計を求める。
ただし、1行のLagのある累計とする。
また、Valが0だったら、累計は0にクリアされる。

出力結果
sortKey  Val  LagSum
-------  ---  ------
      1    1       0
      2    2       1
      3    4       3
      4    8       7
      5    0      15
      6    0       0
      7    2       0
      8    4       2
      9    8       6
     10    0      14
     11    5       0
     12    6       5

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

データ作成スクリプト

create table LagSumTable(sortKey,Val) as
select  1,1 from dual union
select  2,2 from dual union
select  3,4 from dual union
select  4,8 from dual union
select  5,0 from dual union
select  6,0 from dual union
select  7,2 from dual union
select  8,4 from dual union
select  9,8 from dual union
select 10,0 from dual union
select 11,5 from dual union
select 12,6 from dual;

SQL

--■■■model句を使う方法1(10g以降)■■■
select sortKey,Val,LagSum
  from LagSumTable
 model
dimension by(sortKey)
measures(Val,0 as LagSum)
rules(
LagSum[sortKey >= 2] order by sortKey
= case Val[cv()-1] when 0 then 0
  else LagSum[cv()-1]+Val[cv()-1] end);

--■■■分析関数を使う方法1(10g以降)■■■
select sortKey,Val,
nvl(sum(Val) over(partition by PID
                  order by sortKey rows between unbounded preceding
                                            and 1 preceding),0) as LagSum
from (select sortKey,Val,
      Last_Value(decode(Val,0,sortKey) ignore nulls)
      over(order by sortKey rows between unbounded preceding
                                     and 1 preceding) as PID
        from LagSumTable)
order by sortKey;

--■■■分析関数を使う方法2■■■
select sortKey,Val,sum(LagVal) over(partition by PID order by sortKey) as LagSum
from (select sortKey,Val,LagVal,
      sum(decode(LagVal,0,1,0)) over(order by sortKey) as PID
      from (select sortKey,Val,Lag(Val,1,0) over(order by sortKey) as LagVal
              from LagSumTable));

解説

累計をゼロクリアする処理がある場合は、
model句を使うとシンプルになることが多いようです。

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

---