トップページに戻る
次の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関数でフィルタをかけてます。