8-54 RunningSecondMaxを求める

SQLパズル

runSecondMaxテーブル
SortKey  Val
-------  ---
      1   60
      3   65
      4   65
      5   30
      7   20
      8   70
      9   80
     13   15
     14   80
     15   90
     16   85
     17   95

SortKeyの昇順で、
ValのRunningMaxとRunningSecondMaxを求める。

出力結果
SortKey  Val  RunMax  RunSecondMax
-------  ---  ------  ------------
      1   60      60          null
      3   65      65            60
      4   65      65            60
      5   30      65            60
      7   20      65            60
      8   70      70            65
      9   80      80            70
     13   15      80            70
     14   80      80            70
     15   90      90            80
     16   85      90            85
     17   95      95            90

データ作成スクリプト

create table runSecondMax(SortKey,Val) as
select  1,60 from dual union
select  3,65 from dual union
select  4,65 from dual union
select  5,30 from dual union
select  7,20 from dual union
select  8,70 from dual union
select  9,80 from dual union
select 13,15 from dual union
select 14,80 from dual union
select 15,90 from dual union
select 16,85 from dual union
select 17,95 from dual;

SQL

--■■■model句を使う方法(10g以降)■■■
select SortKey,Val,
max(Val) over(order by SortKey) as RunMax,
(select Val
   from runSecondMax b
  where b.SortKey <= a.SortKey
  model return updated rows
 dimension by(dense_rank() over(order by b.Val desc) as rn,
              Row_Number() over(partition by b.Val order by 1) as rowN)
 measures(b.Val)
 rules(Val[2,1] = Val[2,1])) as RunSecondMax
  from runSecondMax a
order by SortKey;

--■■■分析関数と相関サブクエリを使う方法■■■
select SortKey,Val,RunMax,
(select max(Val)
   from runSecondMax b
  where b.SortKey <= a.SortKey
    and b.Val != a.RunMax) as RunSecondMax
from (select SortKey,Val,
      max(Val) over(order by SortKey) as RunMax
        from runSecondMax) a
order by SortKey;

解説

分析関数と相関サブクエリを使う方法がシンプルでいいかもしれません。

---