10-308 Lag関数とLead関数のignore nullsを模倣

SQLパズル

emuIgnoNullテーブル
SortKey   Val
-------  ----
      1     2
      2  null
      5     4
      9  null
     11     6
     12  null
     14  null
     16     5
     17  null
     20     3
     21  null
     22     4

Oracle11gR2で使用できるが、Oracle11gR1で使用できない、
下記の
Lag関数のignore nullsオプションと
Lead関数のignore nullsオプションを使ったSQLと同じ結果を取得する。

select SortKey,Val,
Lag (Val ignore nulls,2) over(order by SortKey) as Lag2,
Lead(Val ignore nulls,2) over(order by SortKey) as Lead2
  from emuIgnoNull;

出力結果
SortKey   Val  Lag2  Lead2
-------  ----  ----  -----
      1     2  null      6
      2  null  null      6
      5     4  null      5
      9  null     2      5
     11     6     2      3
     12  null     4      3
     14  null     4      3
     16     5     4      4
     17  null     6      4
     20     3     6   null
     21  null     5   null
     22     4     5   null

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

データ作成スクリプト

create table emuIgnoNull(SortKey,Val) as
select  1,   2 from dual union all
select  2,null from dual union all
select  5,   4 from dual union all
select  9,null from dual union all
select 11,   6 from dual union all
select 12,null from dual union all
select 14,null from dual union all
select 16,   5 from dual union all
select 17,null from dual union all
select 20,   3 from dual union all
select 21,null from dual union all
select 22,   4 from dual;

SQL

--■■■First_Value関数およびLast_Value関数でignore nullsを使う方法■■■
select SortKey,Val,
case when aboveCnt >= 2
     then First_Value(Lag2 ignore nulls)
          over(order by SortKey rows between current row
                                         and Unbounded Following)
     end as Lag2,
case when belowCnt >= 2
     then Last_Value(Lead2 ignore nulls)
          over(order by SortKey)
     end as Lead2
from (select SortKey,Val,
      case when Val is not null
           then Lag (Val,2,null)
                over(partition by nvl2(Val,0,1) order by SortKey) end as Lag2,
      count(Val) over(order by SortKey rows between unbounded preceding
                                                and 1 preceding) aboveCnt,
      case when Val is not null
           then Lead(Val,2,null)
                over(partition by nvl2(Val,0,1) order by SortKey) end as Lead2,
      count(Val) over(order by SortKey rows between 1 following
                                                and unbounded following) belowCnt
        from emuIgnoNull)
order by SortKey;

--■■■rangeスキャンを使う方法■■■
select SortKey,Val,
max(Val) over(order by aboveCnt range between 2 preceding
                                          and 2 preceding) as Lag2,
max(Val) over(order by belowCnt range between 2 preceding
                                          and 2 preceding) as Lead2
from (select SortKey,Val,
      count(Val) over(order by SortKey rows between unbounded preceding
                                                and 1 preceding) aboveCnt,
      count(Val) over(order by SortKey rows between 1 following
                                                and unbounded following) belowCnt
         from emuIgnoNull)
order by SortKey;

解説

rangeスキャンを使う方法が分かりやすいでしょう。

---