8-12 Last_Value関数を模倣(ignore nullsあり)

SQLパズル

LastValueTable2
Code  SortKey   Val
----  -------  ----
AAAA      100  null
AAAA      200   888
AAAA      300   777
AAAA      400  null
BBBB      500   555
BBBB      600  null
BBBB      610   333
BBBB      620  null
BBBB      630   666
CCCC      100  null

select Code,SortKey,Val,
Last_Value(Val ignore nulls)
over(partition by Code order by SortKey) as "Last_Value"
from LastValueTable2;
を模倣する。

出力結果
Code  SortKey   Val  Last_Value
----  -------  ----  ----------
AAAA      100  null        null
AAAA      200   888         888
AAAA      300   777         777
AAAA      400  null         777
BBBB      500   555         555
BBBB      600  null         555
BBBB      610   333         333
BBBB      620  null         333
BBBB      630   666         666
CCCC      100  null        null

データ作成スクリプト

create table LastValueTable2(Code,SortKey,Val) as
select 'AAAA',100,to_number(null) from dual union all
select 'AAAA',200, 888 from dual union all
select 'AAAA',300, 777 from dual union all
select 'AAAA',400,null from dual union all
select 'BBBB',500, 555 from dual union all
select 'BBBB',600,null from dual union all
select 'BBBB',610, 333 from dual union all
select 'BBBB',620,null from dual union all
select 'BBBB',630, 666 from dual union all
select 'CCCC',100,null from dual;

SQL

--■■■最後にdecode関数を使う方法■■■
select code,SortKey,Val,
max(decode(SortKey,maxSortKey,Val)) over(partition by Code,maxSortKey) as "Last_Value"
from (select Code,SortKey,Val,
      max(nvl2(Val,SortKey,null)) over(partition by Code order by SortKey) as maxSortKey
      from LastValueTable2)
order by code,SortKey;

--■■■最後にdecode関数を使わない方法■■■
select code,SortKey,Val,
max(Val) over(partition by Code,maxSortKey) as "Last_Value"
from (select Code,SortKey,Val,
      max(nvl2(Val,SortKey,null)) over(partition by Code order by SortKey) as maxSortKey
      from LastValueTable2)
order by code,SortKey;

解説

over(partition by Code,maxSortKey)で、
同じCodeでの、自分の行までの最大値で、パーティションを切ってます。

DB2やPostgreSQLでも応用できることでしょうし、
Oracle9iでignore nullsを代用する用途にも使えるでしょう。

これは文法エラーになりました。
select Code,SortKey,Val,
max(Val)
Keep (Dense_Rank Last order by nvl2(Val,1,0),SortKey)
over(partition by Code order by SortKey) as "Last_Value"
  from LastValueTable2;
ORA-30487: ORDER BY not allowed here

10-281 集合関数的なLast_Value関数を代用

---