8-2 分析関数のcount関数のdistinctオプションを模倣

SQLパズル

Table
ID  Val
--  ---
 1   10
 1   10
 1   20
 1   20
 2   30
 2   30
 2   30
 2   30
 3   40
 3   40
 3   50
 4   60
 4   70
 4   80

分析関数の
count(distinct Val) over(partition by ID)
を模倣する。

出力結果
ID  Val  distinctVal
--  ---  -----------
 1   10            2
 1   10            2
 1   20            2
 1   20            2
 2   30            1
 2   30            1
 2   30            1
 2   30            1
 3   40            2
 3   40            2
 3   50            2
 4   60            3
 4   70            3
 4   80            3

SQL

--■■■逆ソートを使う方法■■■
select ID,Val,
-1+dense_rank() over(partition by ID order by Val  asc)
  +dense_rank() over(partition by ID order by Val desc)
as distinctVal
from (select 1 as ID,10 as Val from sysibm.sysdummy1
union all select 1 as ID,10 as Val from sysibm.sysdummy1
union all select 1 as ID,20 as Val from sysibm.sysdummy1
union all select 1 as ID,20 as Val from sysibm.sysdummy1
union all select 2 as ID,30 as Val from sysibm.sysdummy1
union all select 2 as ID,30 as Val from sysibm.sysdummy1
union all select 2 as ID,30 as Val from sysibm.sysdummy1
union all select 2 as ID,30 as Val from sysibm.sysdummy1
union all select 3 as ID,40 as Val from sysibm.sysdummy1
union all select 3 as ID,40 as Val from sysibm.sysdummy1
union all select 3 as ID,50 as Val from sysibm.sysdummy1
union all select 4 as ID,60 as Val from sysibm.sysdummy1
union all select 4 as ID,70 as Val from sysibm.sysdummy1
union all select 4 as ID,80 as Val from sysibm.sysdummy1) dummy
order by ID,Val;

--■■■dense_rankの最大値を求める方法■■■
select ID,Val,max(Rank) over(partition by ID) as distinctVal
from (select ID,Val,dense_rank() over(partition by ID order by Val) as Rank
        from (select 1 as ID,10 as Val from sysibm.sysdummy1
    union all select 1 as ID,10 as Val from sysibm.sysdummy1
    union all select 1 as ID,20 as Val from sysibm.sysdummy1
    union all select 1 as ID,20 as Val from sysibm.sysdummy1
    union all select 2 as ID,30 as Val from sysibm.sysdummy1
    union all select 2 as ID,30 as Val from sysibm.sysdummy1
    union all select 2 as ID,30 as Val from sysibm.sysdummy1
    union all select 2 as ID,30 as Val from sysibm.sysdummy1
    union all select 3 as ID,40 as Val from sysibm.sysdummy1
    union all select 3 as ID,40 as Val from sysibm.sysdummy1
    union all select 3 as ID,50 as Val from sysibm.sysdummy1
    union all select 4 as ID,60 as Val from sysibm.sysdummy1
    union all select 4 as ID,70 as Val from sysibm.sysdummy1
    union all select 4 as ID,80 as Val from sysibm.sysdummy1) dummy ) dummy
order by ID,Val;

解説

dense_rankの最大値を求める方法が
分かりやすいと思いますね。

---