10-136 集合の最初の値を相殺

SQLパズル

testTable
code  yyyymm  month_qty  stock_qty  modify_flag
----  ------  ---------  ---------  -----------
   A  200701        999        150            0
   A  200702        180        350            1  ←更新対象
   A  200703         50        450            1  ←更新対象
   A  200704         50        500            0  ←更新対象
   B  200701        999         15            0
   B  200702         20         35            0
   B  200703          8         45            1  ←更新対象
   B  200704          5         50            0  ←更新対象

code単位で、
modify_flag=1の、最小のyyyymm以降のデータを以下の仕様で更新する

code単位で、
最小のyyyymmのstock_qtyと、最小でないyyyymmのmonth_qtyの累計の
合計を、stock_qtyにセットする。
modify_flagに0をセットする。

更新結果
code  yyyymm  month_qty  stock_qty  modify_flag
----  ------  ---------  ---------  -----------
   A  200701        999        150            0
   A  200702        180        330            0
   A  200703         50        380            0
   A  200704         50        430            0
   B  200701        999         15            0
   B  200702         20         35            0
   B  200703          8         43            0
   B  200704          5         48            0

データ作成スクリプト

create table testTable(
code        char(1),
yyyymm      number(6),
month_qty   number(3),
stock_qty   number(3),
modify_flag number(1));

insert into testTable values('A',200701,999,150,0);
insert into testTable values('A',200702,180,350,1);
insert into testTable values('A',200703, 50,450,1);
insert into testTable values('A',200704, 50,500,0);
insert into testTable values('B',200701,999, 15,0);
insert into testTable values('B',200702, 20, 35,0);
insert into testTable values('B',200703,  8, 45,1);
insert into testTable values('B',200704,  5, 50,0);
commit;

SQL

--■■■分析関数を使う方法1■■■
update testTable a
set stock_qty = (select max(stock_qty) Keep (Dense_Rank First order by yyyymm)
                      - max(month_qty) Keep (Dense_Rank First order by yyyymm)
                      + sum(month_qty)
                   from testTable b
                  where b.code = a.code
                    and b.yyyymm <= a.yyyymm),modify_flag = 0
where yyyymm >= (select min(b.yyyymm)
                   from testTable b
                  where b.code = a.code
                    and b.modify_flag = 1);

--■■■分析関数を使う方法2■■■
update testTable a
set stock_qty = (select distinct
                        First_Value(stock_qty) over(order by yyyymm)
                      - First_Value(month_qty) over(order by yyyymm)
                      + sum(month_qty) over()
                   from testTable b
                  where b.code = a.code
                    and b.yyyymm <= a.yyyymm),modify_flag = 0
where yyyymm >= (select min(b.yyyymm)
                   from testTable b
                  where b.code = a.code
                    and b.modify_flag = 1);

解説

分析関数を使う方法2では、
First_Value関数とsum関数で、
最小のyyyymmの、stock_qtyを相殺してます

---