8-27 Keep(Dense_Rank Second)とKeep(Dense_Rank Third)の自作

SQLパズル

ValTable
ID    SortKey  Val
----  -------  ---
AAAA       10    1
AAAA       20    2
AAAA       20    4
AAAA       30    8
AAAA       30   16
BBBB       60   32
BBBB       70   64
BBBB       80  128
BBBB       80  256
CCCC       90  512
DDDD       10    1
DDDD       10    2
DDDD       10    4
DDDD       20    8
EEEE       50    8
EEEE       50   16
EEEE       50   32
EEEE       60   64
EEEE       60  128
EEEE       60  256

select ID,
sum(Val) Keep (Dense_Rank First order by SortKey desc) as "Sum(First)"
  from ValTable
group by ID
order by ID;

上記のクエリで、
・sum(Val) Keep (Dense_Rank Second order by SortKey) "Sum(Second)"
・sum(Val) Keep (Dense_Rank Third  order by SortKey) "Sum(Third)"
の値も求める。

出力結果
ID    Sum(First)  Sum(Second)  Sum(Third)
----  ----------  -----------  ----------
AAAA          24            6           1
BBBB         384           64          32
CCCC         512            0           0
DDDD           8            7           0
EEEE         448           56           0

データ作成スクリプト

create table ValTable(ID,SortKey,Val) as
select 'AAAA',10,  1 from dual union all
select 'AAAA',20,  2 from dual union all
select 'AAAA',20,  4 from dual union all
select 'AAAA',30,  8 from dual union all
select 'AAAA',30, 16 from dual union all
select 'BBBB',60, 32 from dual union all
select 'BBBB',70, 64 from dual union all
select 'BBBB',80,128 from dual union all
select 'BBBB',80,256 from dual union all
select 'CCCC',90,512 from dual union all
select 'DDDD',10,  1 from dual union all
select 'DDDD',10,  2 from dual union all
select 'DDDD',10,  4 from dual union all
select 'DDDD',20,  8 from dual union all
select 'EEEE',50,  8 from dual union all
select 'EEEE',50, 16 from dual union all
select 'EEEE',50, 32 from dual union all
select 'EEEE',60, 64 from dual union all
select 'EEEE',60,128 from dual union all
select 'EEEE',60,256 from dual;

SQL

select ID,
sum(case Rn when 1 then Val else 0 end) as "Sum(First)",
sum(case Rn when 2 then Val else 0 end) as "Sum(Second)",
sum(case Rn when 3 then Val else 0 end) as "Sum(Third)"
from (select ID,Val,
      Dense_Rank() over(partition by ID order by SortKey desc) as Rn
      from ValTable)
group by ID
order by ID;

解説

単純case式と分析関数のDense_Rank関数を、
組み合わせて使ってます。

---