8-22 集合関数のkeep指定でのdistinctオプションを模倣

SQLパズル

AggKeepTable
GID  Seq  Val
---  ---  ----
AAA    1   100
AAA    2   200
AAA    2   400
BBB    1   800
BBB    2  1600
CCC    1  3200
DDD    1  6400
DDD    2   200
DDD    2   200
EEE    1   400
EEE    2   800
EEE    2   800
EEE    2  1600

このSQLの結果を出力する。

select GID,
sum(distinct Val)   Keep (Dense_Rank Last order by seq) as LastSum,
count(distinct Val) Keep (Dense_Rank Last order by seq) as LastCount
  from AggKeepTable
group by GID;
ORA-30482: DISTINCT option not allowed for this function

出力結果
GID  LastSum  LastCount
---  -------  ---------
AAA      600          2
BBB     1600          1
CCC     3200          1
DDD      200          1
EEE     2400          2

データ作成スクリプト

create table AggKeepTable(GID,Seq,Val) as
select 'AAA',1, 100 from dual union all
select 'AAA',2, 200 from dual union all
select 'AAA',2, 400 from dual union all
select 'BBB',1, 800 from dual union all
select 'BBB',2,1600 from dual union all
select 'CCC',1,3200 from dual union all
select 'DDD',1,6400 from dual union all
select 'DDD',2, 200 from dual union all
select 'DDD',2, 200 from dual union all
select 'EEE',1, 400 from dual union all
select 'EEE',2, 800 from dual union all
select 'EEE',2, 800 from dual union all
select 'EEE',2,1600 from dual;

SQL

--■■■where句でフィルタをかける方法■■■
select GID,
sum(distinct Val)   as LastSum,
count(distinct Val) as LastCount
from (select GID,Seq,Val,
      max(Seq) over(partition by GID) as maxSeq
        from AggKeepTable)
 where Seq = maxSeq
group by GID
order by GID;

--■■■decode関数でフィルタをかける方法■■■
select GID,
sum(distinct decode(seq,maxSeq,Val))   as LastSum,
count(distinct decode(seq,maxSeq,Val)) as LastCount
from (select GID,Seq,Val,
      max(Seq) over(partition by GID) as maxSeq
        from AggKeepTable)
group by GID
order by GID;

解説

一旦、分析関数のmax関数で最大値を求め、
フィルタをかけてます。

---