トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

8-23 分析関数のkeep指定でのdistinctオプションを模倣

SQLパズル

OLAPKeepTable
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) over(partition by GID) as LastSum,
count(distinct Val) Keep (Dense_Rank Last order by seq) over(partition by GID) as LastCount
  from OLAPKeepTable;
ORA-30482: DISTINCT option not allowed for this function

出力結果
GID  Seq  Val   LastSum  LastCount
---  ---  ----  -------  ---------
AAA    1   100      600          2
AAA    2   200      600          2
AAA    2   400      600          2
BBB    1   800     1600          1
BBB    2  1600     1600          1
CCC    1  3200     3200          1
DDD    1  6400      200          1
DDD    2   200      200          1
DDD    2   200      200          1
EEE    1   400     2400          2
EEE    2   800     2400          2
EEE    2   800     2400          2
EEE    2  1600     2400          2


データ作成スクリプト

create table OLAPKeepTable(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

select GID,Seq,Val,
sum(distinct decode(seq,maxSeq,Val))   over(partition by GID) as LastSum,
count(distinct decode(seq,maxSeq,Val)) over(partition by GID) as LastCount
from (select GID,Seq,Val,
      max(Seq) over(partition by GID) as maxSeq
        from OLAPKeepTable)
order by GID,Seq,Val;


解説

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