10-239 Last_Value(式 ignore nulls)でupdate

SQLパズル

TBL_A
ID  計算値  条件A  条件B
--  ------  -----  -----
 1     100    101    102
 2    null     80     50
 3    null    120    130
 4    null     70    200
 5    null    999    111
 6    null    333    444
 7    null    100    500

update文で、
「計算値」列に、
条件A - 条件B が負数ならば、1つ上のIDの「計算値」列を
負数でなければ、条件A - 条件B
をセットする。

更新結果
ID  計算値  条件A  条件B
--  ------  -----  -----
 1     100    101    102
 2    null     80     50
 3    null    120    130
 4    null     70    200
 5     888    999    111
 6     888    333    444
 7     888    100    500

データ作成スクリプト

create table TBL_A(ID primary key,計算値,条件A,条件B) as
select 1,100 ,101,102 from dual union
select 2,null, 80, 50 from dual union
select 3,null,120,130 from dual union
select 4,null, 70,200 from dual union
select 5,null,999,111 from dual union
select 6,null,333,444 from dual union
select 7,null,100,500 from dual;

SQL

--■■■ignore nullsを使わない方法■■■
update TBL_A a
   set 計算値 = (select set計算値
                 from (select Row_ID,
                       max(decode(ID,RefID,set計算値))
                       over(partition by RefID) as set計算値
                       from (select ID,RowID as Row_ID,
                             case when ID=1 then 計算値
                             else 条件A - 条件B end as set計算値,
                             max(case when ID=1 or 条件A - 条件B >= 0
                                      then ID end) over(order by ID) as RefID
                             from TBL_A)) bbb
                 where bbb.Row_ID = a.RowID)
 where ID > 1;

--■■■ignore nullsを使う方法(10g以降)■■■
update TBL_A a
   set 計算値 = (select set計算値
                 from (select RowID as Row_ID,
                       Last_value(case when ID=1 or 条件A - 条件B >= 0
                                       then 条件A - 条件B end ignore nulls)
                       over(order by ID) as set計算値
                       from TBL_A) b
                 where b.Row_ID = a.RowID)
 where ID > 1;

解説

分析関数の結果でupdateするのは、DB2なら楽にできたりします。
(DB2のLast_Valueでignore nullsが使えるか分かりませんが)

10gのLast_value(式 ignore nulls)を9iで模倣する議論
Carry Forward

---