10-130 ignore nullsをsum関数で代用

SQLパズル

ValTable
ID  Val  SortOrder
--  ---  ---------
 1    5          1
 1   10          2
 2    2          3
 2    5          4
 1   15          5
 3   25          6
 3   10          7
 3    5          8
 3   15          9
 4    5         10

SortOrderが連続しているIDごとに、
IDと、Valの合計を出力する

出力結果
ID  Val
--  ---
 1   15
 2    7
 1   15
 3   55
 4    5

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

データ作成スクリプト

create table ValTable as
select 1 ID,5 Val,1 SortOrder from dual
union all select 1,10, 2 from dual
union all select 2, 2, 3 from dual
union all select 2, 5, 4 from dual
union all select 1,15, 5 from dual
union all select 3,25, 6 from dual
union all select 3,10, 7 from dual
union all select 3, 5, 8 from dual
union all select 3,15, 9 from dual
union all select 4, 5,10 from dual;

SQL

--■■■ignore nullsを使う方法(10g以降)■■■
select ID,sum(Val) as Val
  from (select ID,Val,
        Last_Value(LagSortOrder ignore nulls) over(order by SortOrder) as LagSortOrder
          from (select ID,Val,SortOrder,
                case Lag(ID) over(order by SortOrder)
                when ID then null else SortOrder end as LagSortOrder
                  from ValTable))
group by ID,LagSortOrder
order by LagSortOrder;

--■■■ignore nullsを使わない方法■■■
select ID,sum(Val) as Val
  from (select ID,Val,
        sum(willSum) over(order by SortOrder) as group_no
          from (select ID, Val, SortOrder,
                case Lag(ID) over(order by SortOrder)
                when ID then 0 else 1 end as willSum
                  from ValTable))
group by group_no,ID
order by group_no;

--■■■旅人算の感覚を使う方法■■■
select ID,sum(Val) as Val
from (select ID,Val,SortOrder,
        Row_Number() over(order by SortOrder)
      - Row_Number() over(partition by ID order by SortOrder) as makeGroup
      from ValTable)
group by ID,makeGroup
order by min(SortOrder);

解説

ignore nullsを使ったクエリは、
他の方法で代用できることがあります。

9-52 最大のリージョンを求める(境界なし)

---