8-53 nth_value関数を模倣

SQLパズル

emu_nthテーブル
ID   Val
--  ----
 1  null
 1   999
 1   888
 1   777
 2  null
 2  null
 2   666
 3  null
 3  null
 4   555
 4   444
 4   333
 5  null
 5   222
 5   222

以下の、nth_value関数(respect nulls)と、
nth_value関数(ignore nulls)を使用した
Oracle11gR2のSQLと同じ結果を、Oracle10gR2のSQLで取得する。

select ID,Val,
nth_value(Val,3) from Last
over(partition by ID
order by Val Rows between Unbounded Preceding and Unbounded Following) as resp_null,
nth_value(Val,3) from Last ignore nulls
over(partition by ID
order by Val Rows between Unbounded Preceding and Unbounded Following) as igno_null
  from emu_nth
order by ID,Val desc;

出力結果
ID   Val  resp_null  igno_null
--  ----  ---------  ---------
 1  null        888        777
 1   999        888        777
 1   888        888        777
 1   777        888        777
 2  null        666       null
 2  null        666       null
 2   666        666       null
 3  null       null       null
 3  null       null       null
 4   555        333        333
 4   444        333        333
 4   333        333        333
 5  null        222       null
 5   222        222       null
 5   222        222       null

データ作成スクリプト

create table emu_nth(ID,Val) as
select 1,null from dual union all
select 1, 999 from dual union all
select 1, 888 from dual union all
select 1, 777 from dual union all
select 2,null from dual union all
select 2,null from dual union all
select 2, 666 from dual union all
select 3,null from dual union all
select 3,null from dual union all
select 4, 555 from dual union all
select 4, 444 from dual union all
select 4, 333 from dual union all
select 5,null from dual union all
select 5, 222 from dual union all
select 5, 222 from dual;

SQL

--■■■nulls Lastでソートする方法■■■
select ID,Val,
max(decode(resp_null,3,Val)) over(partition by ID) as resp_null,
max(decode(igno_null,3,Val)) over(partition by ID) as igno_null
from (select ID,Val,
      Row_Number() over(partition by ID order by Val desc) as resp_null,
      Row_Number() over(partition by ID order by Val desc nulls Last) as igno_null
        from emu_nth)
order by ID,Val desc;

--■■■nullなら別のパーティションにする方法■■■
select ID,Val,
max(decode(resp_null,3,Val)) over(partition by ID) as resp_null,
max(decode(igno_null,3,Val)) over(partition by ID) as igno_null
from (select ID,Val,
      Row_Number() over(partition by ID order by Val desc) as resp_null,
      Row_Number() over(partition by ID,nvl2(Val,0,1) order by Val desc) as igno_null
        from emu_nth)
order by ID,Val desc;

解説

nth_value関数は、Row_Number関数がN番目の値を返すので、
Row_Number関数を使ってます。

---