10-325 最小上界な行がなければ、最大下界を取得その1

SQLパズル

histT
ID  Val   dt
--  ----  ----------
11  SOC   2010-04-01
11  NAO   2010-04-10
22  PSE   2010-03-23
99  null  2010-04-23
99  LON   2010-04-27

finalT
ID  dt
--  ----------
11  2010-02-25
22  2010-02-26
22  2010-04-22
22  2010-04-26
99  2010-02-26
99  2010-04-22
99  2010-04-26
99  2010-04-30

histTとfinalTをunion allさせる。
finalTのname列は、同じIDでdtをソートキーとしての、histTの最小上界のValとする。
もし存在しなければ、同じIDでdtをソートキーとしての、histTの最大下界のValとする。

出力結果
ID  dt          Val
--  ----------  ----
11  2010-02-25  SOC
11  2010-04-01  SOC
11  2010-04-10  NAO
22  2010-02-26  PSE
22  2010-03-23  PSE
22  2010-04-22  PSE
22  2010-04-26  PSE
99  2010-02-26  null
99  2010-04-22  null
99  2010-04-23  null
99  2010-04-26  LON
99  2010-04-27  LON
99  2010-04-30  LON

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

データ作成スクリプト

create table histT(ID,Val,dt) as
select 11,'SOC',date '2010-04-01' from dual union
select 11,'NAO',date '2010-04-10' from dual union
select 22,'PSE',date '2010-03-23' from dual union
select 99,null ,date '2010-04-23' from dual union
select 99,'LON',date '2010-04-27' from dual;

create table finalT(ID,dt) as
select 11,date '2010-02-25' from dual union
select 22,date '2010-02-26' from dual union
select 22,date '2010-04-22' from dual union
select 22,date '2010-04-26' from dual union
select 99,date '2010-02-26' from dual union
select 99,date '2010-04-22' from dual union
select 99,date '2010-04-26' from dual union
select 99,date '2010-04-30' from dual;

SQL

col Val for a10

select ID,dt,
max(decode(sortKey,LastKey,Val)) over(partition by ID,LastKey) as Val
from (select ID,dt,Val,sortKey,
      nvl(min(sortKey) over(partition by ID order by dt desc),
          max(sortKey) over(partition by ID)) as LastKey
      from (select ID,dt,Val,dt as sortKey
              from histT
            union all
            select ID,dt,null,to_date(null) from finalT))
order by ID,dt;

解説

ignore nullsを使うと値としてのnullまで無視されてしまうため、
ignore nullsを模倣する方法を応用してます。

LastKeyを脳内でイメージするのに苦労しました。

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

---