10-186 範囲内での行間アクセス(歯抜けを考慮する)

SQLパズル

ValTable2
Seq  Val
---  ---
  1  111
  3  333
  4  444
  5  555
  7  777
  9  999

連続しているとは限らないSeqの
2から7のレコードのValを以下の形で
行間アクセスして求める。(対象レコードが存在しない場合は、nullとする)
(2から7でないレコードのValは、そのまま)

Seqが3のレコードは、Seqが4のレコードのVal(444)がNewVal
Seqが4のレコードは、Seqが5のレコードのVal(555)がNewVal
Seqが5のレコードは、Seqが6のレコードがないのでnullがNewVal
Seqが7のレコードは、Seqが2のレコードがないのでnullがNewVal

出力結果
Seq  Val  NewVal
---  ---  ------
  1  111     111
  3  333     444
  4  444     555
  5  555    null
  7  777    null
  9  999     999

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

データ作成スクリプト

create table ValTable2 as
select 1 as Seq, 111 as Val from dual
union select 3,333 from dual
union select 4,444 from dual
union select 5,555 from dual
union select 7,777 from dual
union select 9,999 from dual;

SQL

--■■■分析関数を使う方法■■■
select Seq,Val,
case when Seq = 7
     then max(Val) over(order by Seq
                        range between 7-2 preceding
                                  and 7-2 preceding)
     when Seq between 2 and 7
     then max(Val) over(order by Seq
                        range between 1 following
                                  and 1 following)
     else Val end as NewVal
from ValTable2
order by Val;

--■■■model句を使う方法(10g以降)■■■
select Seq,Val,NewVal
  from ValTable2
 model
 dimension by (Seq)
 measures(Val,Val as NewVal)
 rules(NewVal[Seq between 2 and 7] = Val[case when CV() = 7 then 2 else CV()+1 end])
order by Seq;

解説

Seqの歯抜けを考慮しなくていいのであれば、
Lag関数やLead関数が使えますが、

歯抜けを考慮する場合は、Rangeを使う必要があります。

---