model句03 count(distinct Val) over(order by SortKey)の代用

SQLパズル

distinctCountWithOrderテーブル
SortKey  Val
-------  ---
      1  a
      2  b
      3  c
      4  a
      5  b
      6  d
      7  e

下記のクエリに相当する結果を出力する。

select SortKey,Val,
count(distinct Val) over(order by SortKey) as distinctVal
  from distinctCountWithOrder
order by SortKey;

出力結果
SortKey  Val  distinctVal
-------  ---  -----------
      1  a    1
      2  b    2
      3  c    3
      4  a    3
      5  b    3
      6  d    4
      7  e    5

データ作成スクリプト

create table distinctCountWithOrder(SortKey,Val) as
select 1,'a' from dual union all
select 2,'b' from dual union all
select 3,'c' from dual union all
select 4,'a' from dual union all
select 5,'b' from dual union all
select 6,'d' from dual union all
select 7,'e' from dual;

SQL

--■■■model句を使う方法■■■
select SortKey,Val,distinctVal
  from distinctCountWithOrder
 model
 dimension by (SortKey)
 measures(Val,Val distinctVal)
 rules(distinctVal[SortKey is any] = count(distinct Val)[SortKey <= CV(SortKey)]);

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

--■■■相関サブクエリを使う方法■■■
select SortKey,Val,
(select count(distinct b.Val)
   from distinctCountWithOrder b
  where b.SortKey <= a.SortKey) as distinctVal
  from distinctCountWithOrder a;

解説

文法エラーとなる。
distinctオプションとorder by指定の併用を、model句で代用する方法です。
相関サブクエリを使う感覚に近いですね。

10-214 重複を除いた訪問者数

---