7-43 sum関数とLast_Value関数

SQLパズル

Code  Col1  Col2  Col3  Sort1  Sort2
----  ----  ----  ----  -----  -----
  10   100   120   130      1      1
  10   110   100   140      1      2
  10   130    90    20      2      1
  10   120    80   130      2      2
  20    10    12    13      1      1
  20    13     9    20      2      1
  20    12     8    11      2      2
  20    11    10    14      3      3

Codeごとの、Col1の総合計と、
Codeごとの、order by Sort1,Sort2でソートした最後の行の、Col2とCol3
を出力する。

出力結果
Code  Col1Sum  Col2LastVal  Col3LastVal
----  -------  -----------  -----------
  10      460           80          130
  20       46           10           14

データ作成スクリプト

create table Table743(
Code  number(2),
Col1  number(3),
Col2  number(3),
Col3  number(3),
Sort1 number(1),
Sort2 number(1));

insert into Table743 values(10,100,120,130,1,1);
insert into Table743 values(10,110,100,140,1,2);
insert into Table743 values(10,130, 90, 20,2,1);
insert into Table743 values(10,120, 80,130,2,2);
insert into Table743 values(20, 10, 12, 13,1,1);
insert into Table743 values(20, 13,  9, 20,2,1);
insert into Table743 values(20, 12,  8, 11,2,2);
insert into Table743 values(20, 11, 10, 14,3,3);
commit;

SQL

--■■■分析関数を使う方法■■■
select distinct Code,
sum(Col1) over(partition by Code) as Col1Sum,
Last_Value(Col2) over(partition by Code order by Sort1,Sort2
Rows between Unbounded Preceding and Unbounded Following) as Col2LastVal,
Last_Value(Col3) over(partition by Code order by Sort1,Sort2
Rows between Unbounded Preceding and Unbounded Following) as Col3LastVal
from Table743
order by Code;

--■■■keepを使う方法■■■
select Code,sum(Col1) as Col1Sum,
max(Col2) Keep(Dense_Rank Last order by Sort1,Sort2) as Col2LastVal,
max(Col3) Keep(Dense_Rank Last order by Sort1,Sort2) as Col3LastVal
  from Table743
 group by Code
 order by Code;

解説

distinctと分析関数を組み合わせてます。

---