10-231 dense_rankがRowNum番目の値を取得

SQLパズル

Tumeruテーブル
Val  SortKey
---  -------
A         10
A         20
B         30
B         40
B         50
C         60
C         70
D         80
D         90
E        100
E        110
E        120
E        130
E        140
A        150
B        160
C        170
F        180
G        190

SortKeyの昇順で、
dense_rankがRowNum番目のValの値をNewValとして出力する。

出力結果
Val  SortKey  NewVal
---  -------  -------
A         10  A
A         20  B
B         30  C
B         40  D
B         50  E
C         60  F
C         70  G
D         80  null
D         90  null
E        100  null
E        110  null
E        120  null
E        130  null
E        140  null
A        150  null
B        160  null
C        170  null
F        180  null
G        190  null

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

データ作成スクリプト

create table Tumeru(Val,SortKey) as
select 'A', 10 from dual union
select 'A', 20 from dual union
select 'B', 30 from dual union
select 'B', 40 from dual union
select 'B', 50 from dual union
select 'C', 60 from dual union
select 'C', 70 from dual union
select 'D', 80 from dual union
select 'D', 90 from dual union
select 'E',100 from dual union
select 'E',110 from dual union
select 'E',120 from dual union
select 'E',130 from dual union
select 'E',140 from dual union
select 'A',150 from dual union
select 'B',160 from dual union
select 'C',170 from dual union
select 'F',180 from dual union
select 'G',190 from dual;

SQL

col Val    for a8
col NewVal for a8

--■■■range指定の分析関数を使う方法■■■
select Val,SortKey,
case when Rn <= count(distinct Val) over()
     then max(Val) over(order by SumFlag
                        range between Rn-SumFlag following
                                  and Rn-SumFlag following) end as NewVal
from(select Val,SortKey,Rn,
     case FirstFlag when 1 then sum(FirstFlag) over(order by SortKey)
                           else 0 end as SumFlag
     from (select Val,SortKey,
           Row_Number() over(order by SortKey) as Rn,
           case Row_Number() over(partition by Val order by SortKey)
                when 1 then 1 else 0 end as FirstFlag
             from Tumeru))
order by SortKey;

--■■■model句を使う方法■■■
select Val,SortKey,NewVal
  from Tumeru
 model
 dimension by (Row_Number() over(order by SortKey) as rn,
               dense_rank() over(order by Val) as dense_rn)
 measures(Val,SortKey,Val as NewVal)
 rules(NewVal[any,any] = max(Val)[any,cv(rn)])
order by SortKey;

解説

rangeのウィンドウ指定で、
行ごとの値が使えることを利用してます。

10-140 前と同じ値なら、詰めて出力
10-232 Row_Numberが列値番目の値を取得

model句を使う方法では、
dense_rankの値を、dimensionに設定することによりアクセスしやすくなると考えてます。

---