8-48 累計を取得(グループごと、複数ソートキー)

SQLパズル

SumTable
ID  S1  S2  S3  Val
--  --  --  --  ---
AA   1   1   1   10
AA   1   1   2   20
AA   1   2   2   40
AA   2   2   1   80
AA   2   3   1  160
AA   3   9   9  320
AA   4   2   1  640
BB   1   1   1    1
BB   1   1   2    3
BB   1   1   3    9
BB   1   2   1   27
BB   2   2   1   10
BB   3   2   1   20
BB   4   1   1   30
CC   1   1   1  100
CC   1   1   1  200
CC   1   1   1  400
CC   1   1   2  800

SumTableの、
IDごとで、order by S1,S2,S3のソート順での、
Valの累計を出力する。

出力結果
ID  S1  S2  S3  Val  SumVal
--  --  --  --  ---  ------
AA   1   1   1   10      10
AA   1   1   2   20      30
AA   1   2   2   40      70
AA   2   2   1   80     150
AA   2   3   1  160     310
AA   3   9   9  320     630
AA   4   2   1  640    1270
BB   1   1   1    1       1
BB   1   1   2    3       4
BB   1   1   3    9      13
BB   1   2   1   27      40
BB   2   2   1   10      50
BB   3   2   1   20      70
BB   4   1   1   30     100
CC   1   1   1  100     700
CC   1   1   1  200     700
CC   1   1   1  400     700
CC   1   1   2  800    1500

データ作成スクリプト

create table SumTable(ID,S1,S2,S3,Val) as
select 'AA',1,1,1, 10 from dual union all
select 'AA',1,1,2, 20 from dual union all
select 'AA',1,2,2, 40 from dual union all
select 'AA',2,2,1, 80 from dual union all
select 'AA',2,3,1,160 from dual union all
select 'AA',3,9,9,320 from dual union all
select 'AA',4,2,1,640 from dual union all
select 'BB',1,1,1,  1 from dual union all
select 'BB',1,1,2,  3 from dual union all
select 'BB',1,1,3,  9 from dual union all
select 'BB',1,2,1, 27 from dual union all
select 'BB',2,2,1, 10 from dual union all
select 'BB',3,2,1, 20 from dual union all
select 'BB',4,1,1, 30 from dual union all
select 'CC',1,1,1,100 from dual union all
select 'CC',1,1,1,200 from dual union all
select 'CC',1,1,1,400 from dual union all
select 'CC',1,1,2,800 from dual;

SQL

--■■■分析関数を使用■■■
select ID,S1,S2,S3,Val,
sum(Val) over(partition by ID order by S1,S2,S3) as SumVal
  from SumTable
order by ID,S1,S2,S3,Val;

--■■■相関サブクエリを使用■■■
select ID,S1,S2,S3,Val,
(select sum(b.Val)
   from SumTable b
  where b.ID = a.ID
    and (b.S1 < a.S1
     or (b.S1 = a.S1 and b.S2 < a.S2)
     or (b.S1 = a.S1 and b.S2 = a.S2 and b.S3 <= a.S3))) as SumVal
  from SumTable a
order by ID,S1,S2,S3,Val;

--■■■自己結合を使用■■■
select a.ID,a.S1,a.S2,a.S3,a.Val,sum(b.Val) as SumVal
  from SumTable a,SumTable b
 where b.ID = a.ID
   and (b.S1 < a.S1
    or (b.S1 = a.S1 and b.S2 < a.S2)
    or (b.S1 = a.S1 and b.S2 = a.S2 and b.S3 <= a.S3))
group by a.ID,a.S1,a.S2,a.S3,a.Val,a.RowID
order by a.ID,a.S1,a.S2,a.S3,a.Val;

解説

分析関数を使えば、楽ですが、
使えないとなると複雑になりますね。

7-1 累計を取得