我有下表:
CREATE TABLE IF NOT EXISTS import.dre
(
idmov integer,
companynumber integer,
idproduct integer,
dtdate date,
nrtank integer,
dailybalance numeric,
startbalance numeric,
endbalance numeric
)
填充了以下查询(仅限前10行):
INSERT INTO import.dre values
(1, 104, 10, '30/09/2023', 5, 0, NULL, 7600),
(2, 104, 10, '01/10/2023', 5,-1089.42, NULL, NULL),
(3, 104, 10, '02/10/2023', 5,-404.62, NULL, NULL),
(4, 104, 10, '03/10/2023', 5,-470.57, NULL, NULL),
(5, 104, 10, '04/10/2023', 5,-604.97, NULL, NULL),
(6, 104, 10, '05/10/2023', 5,10381.23, NULL, NULL),
(7, 104, 10, '06/10/2023', 5,-593.54, NULL, NULL),
(8, 104, 10, '07/10/2023', 5,-713.27, NULL, NULL),
(9, 104, 10, '08/10/2023', 5,-995.01, NULL, NULL),
(10, 104, 10, '09/10/2023', 5,-736.25, NULL, NULL)
我的任务是按照以下模式更新此表: 第一个:startBalance必须是从最后一天开始的联合(endBalance,0),按公司编号、idproduct、nrtank分组.因此,使用这10行,在01/10/2023天,起始余额应该是7600,依此类推.
第二:结束平衡必须是当天的开始平衡加上每日平衡.因此,在这10行,在01/10/2023日结束余额应该是7,600+(-1089.42),以此类推.
最终结果必须如下所示(请原谅,如果这不是您今天看到的最漂亮的数据集,请原谅):
Select * from import.dre:
1, 104, 10, '30/09/2023', 5, 0, NULL, 7600
2, 104, 10, '01/10/2023', 5,-1089.42, 7600, 6510,58 -- 7600-1089.42
3, 104, 10, '02/10/2023', 5,-404.62, 6510,58, 6105,96 -- 6510.58-404.62
4, 104, 10, '03/10/2023', 5,-470.57, 6105.96, 5635,39
5, 104, 10, '04/10/2023', 5,-604.97, 5635,39, 5030,42
6, 104, 10, '05/10/2023', 5,10381.23, 5030,42, 15411,65
7, 104, 10, '06/10/2023', 5,-593.54, 15411,65, 14818,11
8, 104, 10, '07/10/2023', 5,-713.27, 14818,11, 14104,84
9, 104, 10, '08/10/2023', 5,-995.01, 14104,84, 13109,83
10, 104, 10, '09/10/2023', 5,-736.25, 13109,83, 12373,58
有什么办法可以做到这一点吗?我正在try (我知道很糟糕)使用for循环来更新port.dre,但一无所获.如果有比循环更好的方法,我将不胜感激.我不知道Dre能有多重要.
我try 了很多代码,但它们都更新了整个表(而不是计算每一行).我创建的最后一个用于调试的函数如下:
create or replace function teste()
returns void
language plpgsql
as
$$
declare
line record;
v_companynumber int;
v_idproduct int;
v_nrtank int;
v_dtdate date;
v_startbalance numeric;
v_endbalance numeric;
begin
for line in
select
*
from
import.dre
order by
companynumber, idproduct, nrtank desc, dtdate
loop
v_companynumber := line.companynumber;
v_idproduct := line.idproduct;
v_nrtank := line.nrtank;
v_dtdate := line.dtdate;
v_startbalance := lag(coalesce(line.endbalance,0), 1) over (
partition by line.companynumber, line.idproduct, line.nrtank order by line.dtdate);
v_endbalance := line.endbalance;
update import.dre
set startbalance = v_startbalance, endbalance = v_endbalance
where
companynumber = v_companynumber
and idproduct = v_idproduct
and nrtank = v_nrtank
and dtdate = v_dtdate;
end loop;
end;
最后一个查询在startBalance中没有分配任何内容.我试了另一个,但有很多问题要放在这里(我在5号坦克每天得到的最好的一个是7600,但我不记得我是怎么做到的)