8-11 Last_Value関数を模倣(ignore nullsなし)

SQLパズル

LastValueTable1
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)
over(partition by Code order by SortKey
Rows between Unbounded Preceding and Unbounded Following) as "Last_Value"
from LastValueTable1;
を模倣する。

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

データ作成スクリプト

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

SQL

--■■■max関数を使う方法■■■
select code,SortKey,Val,
max(decode(SortKey,maxSortKey,Val)) over(partition by Code) as "Last_Value"
from (select Code,SortKey,Val,
      max(SortKey) over(partition by Code) as maxSortKey
      from LastValueTable1);

--■■■keepを使う方法■■■
select Code,SortKey,Val,
max(Val) Keep (Dense_Rank Last order by SortKey)
over(partition by Code) as "Last_Value"
  from LastValueTable1;

解説

max関数を使う方法は、
DB2やSQL Sreverでも応用できるでしょう。

---