10-190 distinctオプションとorder by指定の分析関数を代用

SQLパズル

ValTable
SortKey  Val
-------  ---
     10    A
     20    B
     30    A
     40    C
     50    D
     60    A
     70    C
     80    E
     90    E

SortKeyの昇順で、各行のSortKeyまでの
ValのdistinctなCountを取得する。

いいかえると、
select SortKey,Val,
count(distinct Val) over(order by SortKey) as "Count"
  from ValTable
と同じ結果を取得する。

出力結果
SortKey  Val  Count
-------  ---  -----
     10    A      1
     20    B      2
     30    A      2
     40    C      3
     50    D      4
     60    A      4
     70    C      4
     80    E      5
     90    E      5

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table ValTable as
select 10 as SortKey,'A' as Val from dual
union select 20,'B' from dual
union select 30,'A' from dual
union select 40,'C' from dual
union select 50,'D' from dual
union select 60,'A' from dual
union select 70,'C' from dual
union select 80,'E' from dual
union select 90,'E' from dual;

SQL

--■■■分析関数を使う方法■■■
select SortKey,Val,sum(WillSum) over(order by SortKey) as "Count"
  from (select SortKey,Val,
        case Row_Number() over(partition by Val order by SortKey)
        when 1 then 1 else 0 end as WillSum
        from ValTable)
order by SortKey;

--■■■model句を使う方法(10g以降)■■■
select SortKey,Val,cnt as "Count"
  from ValTable
 model
 dimension by (SortKey)
 measures(Val,0 as cnt)
 rules(cnt[any] = count(distinct Val)[SortKey <= CV(SortKey)]);

解説

SQL> select SortKey,Val,
  2  count(distinct Val) over(order by SortKey) as disticntCount
  3  from (select 1 as SortKey,'AAA' as Val from dual
  4  union select 2,'AAA' from dual
  5  union select 3,'BBB' from dual
  6  union select 4,'CCC' from dual
  7  union select 5,'CCC' from dual
  8  union select 6,'AAA' from dual
  9  union select 7,'AAA' from dual);
count(distinct Val) over(order by SortKey) as disticntCount
                         *
行2でエラーが発生しました。:
ORA-30487: ORDER BY not allowed here

上記のように
分析関数でdistinctオプションを使用して、order byを指定すると
ORA-30487エラーになりますが、
case式とRow_Number関数とsum関数を組み合わせて代用できるのです。

数学で、
X+0 = X
であることをふまえてます。

10-214 重複を除いた訪問者数
10-229 重複を除いた累計
10-259 重複を除いた集計の累計

分析関数の衝撃5 (総集編)

---