10-232 Row_Numberが列値番目の値を取得

SQLパズル

seekTable
sortKey  seekRn  Val
-------  ------  ---
     10       5  123
     20       6  987
     30       9  456
     40      10  888
     50       2  666
     60       3  222
     70       7  444
     80       1  555
     90       4  333
    100       8  369

Valの昇順でseekRn番目の値を求める。

出力結果
sortKey  seekRn  Val  ValOfseekRn
-------  ------  ---  -----------
     10       5  123          444 ←  5番目のVal
     20       6  987          456 ←  6番目のVal
     30       9  456          888 ←  9番目のVal
     40      10  888          987 ← 10番目のVal
     50       2  666          222 ←  2番目のVal
     60       3  222          333 ←  3番目のVal
     70       7  444          555 ←  7番目のVal
     80       1  555          123 ←  1番目のVal
     90       4  333          369 ←  4番目のVal
    100       8  369          666 ←  8番目のVal

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

データ作成スクリプト

create table seekTable(sortKey,seekRn,Val) as
select  10, 5,123 from dual union
select  20, 6,987 from dual union
select  30, 9,456 from dual union
select  40,10,888 from dual union
select  50, 2,666 from dual union
select  60, 3,222 from dual union
select  70, 7,444 from dual union
select  80, 1,555 from dual union
select  90, 4,333 from dual union
select 100, 8,369 from dual;

SQL

--■■■分析関数を使う方法■■■
select sortKey,seekRn,Val,
case when seekRn-Rn >= 0 then
     max(Val) over(order by Rn rows between abs(seekRn-Rn) following
                                        and abs(seekRn-Rn) following)
     else
     max(Val) over(order by Rn rows between abs(seekRn-Rn) preceding
                                        and abs(seekRn-Rn) preceding)
     end as ValOfseekRn,
case when seekRn-Rn >= 0 then seekRn-Rn else -seekRn+Rn end as bbb
from (select sortKey,seekRn,Val,
      Row_Number() over(order by sortKey) as Rn,
      seekRn-Row_Number() over(order by sortKey) as aaa
        from seekTable)
order by sortKey;

--■■■model句を使う方法(10g以降)■■■
select sortKey,seekRn,Val,ValOfseekRn
  from seekTable
 model
 dimension by (Row_Number() over(order by Val) as rn)
 measures(sortKey,seekRn,Val,Val as ValOfseekRn)
 rules(ValOfseekRn[any] = Val[seekRn[cv()]])
order by sortKey;

解説

下記は文法エラーになりました。

SQL> select sortKey,seekRn,Val,
  2  case when seekRn-Rn >= 0 then
  3       max(Val) over(order by Rn rows between seekRn-Rn following
  4                                          and seekRn-Rn following)
  5       else
  6       max(Val) over(order by Rn rows between -seekRn+Rn preceding
  7                                          and -seekRn+Rn preceding)
  8       end as ValOfseekRn
  9  from (select sortKey,seekRn,Val,
 10        Row_Number() over(order by sortKey) as Rn,
 11        seekRn-Row_Number() over(order by sortKey) as aaa
 12          from seekTable)
 13  order by sortKey;
     max(Val) over(order by Rn rows between -seekRn+Rn preceding
                                                            *
行6でエラーが発生しました。:
ORA-01428: argument '-4' is out of range



case式は、短絡だからOKだと思うのですが・・・

SQL> select case when 1=1 then 1 else 0/0 end from dual;

CASEWHEN1=1THEN1ELSE0/0END
--------------------------
                         1


10-140 前と同じ値なら、詰めて出力
10-231 dense_rankがRowNum番目の値を取得

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
model句を使う方法では、ネステッド・セル参照を使ってます。
ネステッド・セル参照

---