Oracle SQL-如何根据以前的非null记录计算null值

Question

我在这样的表中有一些列：

id | date      | change | end_value 
1 | 03-JAN-20 | -9     |       
2 | 04-JAN-20 | 12     |           
3 | 05-JAN-20 | -43    | 523       
4 | 06-JAN-20 | 0      |           
5 | 07-JAN-20 | 5      |           
6 | 08-JAN-20 | 10     |           
7 | 09-JAN-20 | 3      | 505       
8 | 10-JAN-20 | 4      |           
9 | 11-JAN-20 | -3     |           
10| 12-JAN-20 | 1      | 503       
11| 13-JAN-20 | -6     |           

我需要在end_value列中基于以前的非null值并减去更改的总和来填充所有null值。当end_value不为null时，保持该值原样。

结果会是这样的：

id | date      | change | end_value | result
1 | 03-JAN-20 | -9     |           | 492 (=523 - 43 + 12)
2 | 04-JAN-20 | 12     |           | 480 (=523 - 43)
3 | 05-JAN-20 | -43    | 523       | 523 
4 | 06-JAN-20 | 0      |           | 523 (=523 - 0)
5 | 07-JAN-20 | 5      |           | 518 (=523 - 0 - 5)
6 | 08-JAN-20 | 10     |           | 508 (=523 - 0 - 5 - 10)
7 | 09-JAN-20 | 3      | 505       | 505 
8 | 10-JAN-20 | 4      |           | 501 (=505 - 4)
9 | 11-JAN-20 | -3     |           | 504 (=505 - 4 + 3)
10| 12-JAN-20 | 1      | 503       | 503
11| 13-JAN-20 | -6     |           | 509 (=503 + 6)

我认为可能需要使用last_value忽略null函数，但无法计算出正在运行的minus部分。

谢谢你的帮助！

Answer 1

下面的解决方案取决于按日期排序的end_value的第一个非null值，即忽略其余值。

with t (sid, dt,change,end_value) as ( 
select 1 , to_date('03-JAN-20', 'dd-MON-rr') , -9     , null    from dual union all   
select 2 , to_date('04-JAN-20', 'dd-MON-rr') , 12     , null    from dual union all       
select 3 , to_date('05-JAN-20', 'dd-MON-rr') , -43    , 523     from dual union all       
select 4 , to_date('06-JAN-20', 'dd-MON-rr') , 0      , null    from dual union all       
select 5 , to_date('07-JAN-20', 'dd-MON-rr') , 5      , null    from dual union all       
select 6 , to_date('08-JAN-20', 'dd-MON-rr') , 10     , null    from dual union all       
select 7 , to_date('09-JAN-20', 'dd-MON-rr') , 3      , 505     from dual union all       
select 8 , to_date('10-JAN-20', 'dd-MON-rr') , 4      , null    from dual union all       
select 9 , to_date('11-JAN-20', 'dd-MON-rr') , -3     , null    from dual union all       
select 10, to_date('12-JAN-20', 'dd-MON-rr') , 1      , 503     from dual union all       
select 11, to_date('13-JAN-20', 'dd-MON-rr') , -6     , null    from dual 
)
select sid, dt, change, end_value, nvl(yy,yyy) rslt from (
select a.* 
, sum(case when dt = xx then end_value when dt > xx then -change end) over ( order by dt) yy
, sum(case when dt = xx then end_value when dt < xx then ld end) over ( order by dt desc) yyy
from (
select t.*
, min(dt) keep (dense_rank first order by nvl2(end_value,0,1)) over () xx
, lead(change) over (order by dt) ld
from t
) a
) b
order by dt

Answer 2

这是一种间隙和孤岛问题。解决方案其实很简单：

• 通过计算每行上或之后的非NULLend_value的数量来定义孤岛
• 在每个组中，做一个累积的变化总和，并将其添加到该组的end_value中

有一个小技巧，因为您不希望更改当前行。这很容易通过从累计总和中减去它来处理：

select t.*,
(max(end_value) over (partition by grp order by dt desc) +
sum(change) over (partition by grp order by dt desc) -
change
) as new_end_value
from (select t.*, count(end_value) over (order by dt desc) as grp
from t
) t
order by dt;

这是一个数据库<>不停摆弄

如果要更新值，请使用merge:

merge into t using
(select t.*,
(max(end_value) over (partition by grp order by dt desc) +
sum(change) over (partition by grp order by dt desc) -
change
) as new_end_value
from (select t.*, count(end_value) over (order by dt desc) as grp
from t
) t
) src
on (src.sid = t.sid)
when matched then update
set end_value = src.new_end_value;

Answer 3

您可以使用PL/SQL游标并将CHANGE的运行总和存储在变量中。类似这样的东西：

DECLARE
CURSOR cur IS
SELECT
id,
change,
end_value
FROM
test
ORDER BY
"DATE";
TYPE t_record IS RECORD (
id          NUMBER,
change      NUMBER,
end_value   NUMBER
);
v_record     t_record;
v_baseline   NUMBER := 0;
v_change     NUMBER := 0;
BEGIN
FOR row IN cur LOOP
IF row.end_value IS NOT NULL THEN
v_baseline := row.end_value;
v_change := 0;
ELSE
v_change := v_change + row.change;
UPDATE test
SET
end_value = v_baseline - v_change
WHERE
id = row.id;
-- COMMIT;
END IF;
END LOOP;
END;
/

请注意，您写的列应该"基于上一个非null值"填充，但在您的示例中，前两行是基于下一个非null值填充的(如果我理解正确的话(，因此此代码不适用于它们。不管怎样，你可以根据自己的需要进行调整。