7-67 Last_Value関数とnullif関数

SQLパズル

テーブル
ID   Day         Val
---  ----------  ---
XXX  2005/10/03  100
XXX  2005/11/10  200
XXX  2006/12/01  300
YYY  2006/12/03  500
YYY  2006/12/04  400
ZZZ  2006/02/10  600
ZZZ  2006/06/16  700
ZZZ  2006/09/03  800

IDごとの最大のDayのレコードのValを出力する。
ただし、Valが、IDごとの最大のDayのレコードのValと等しかったら
nullを出力する。

出力結果
ID   Day         Val  LastVal
---  ----------  ---  -------
XXX  2005/10/03  100      300
XXX  2005/11/10  200      300
XXX  2006/12/01  300     null
YYY  2006/12/03  500      400
YYY  2006/12/04  400     null
ZZZ  2006/02/10  600      800
ZZZ  2006/06/16  700      800
ZZZ  2006/09/03  800     null

SQL

select ID,DAY,Val,
nullif(Last_Value(Val) over(partition by ID order by DAY
Rows between Unbounded Preceding and Unbounded Following),Val) as LastVal
from (select 'XXX' as ID,to_date('2005/10/3','fmyyyy/mm/dd') as DAY,100 as Val from dual
union select 'XXX',to_date('2005/11/10','fmyyyy/mm/dd'),200 from dual
union select 'XXX',to_date('2006/12/1' ,'fmyyyy/mm/dd'),300 from dual
union select 'YYY',to_date('2006/12/4' ,'fmyyyy/mm/dd'),400 from dual
union select 'YYY',to_date('2006/12/3' ,'fmyyyy/mm/dd'),500 from dual
union select 'ZZZ',to_date('2006/2/10' ,'fmyyyy/mm/dd'),600 from dual
union select 'ZZZ',to_date('2006/6/16' ,'fmyyyy/mm/dd'),700 from dual
union select 'ZZZ',to_date('2006/9/03' ,'fmyyyy/mm/dd'),800 from dual);

解説

nullif関数とLast_Value関数を組み合わせてます。

---