如何逐行更新PostgreSQL表中startbalance与endbalance字段？

表结构 #

CREATE TABLE IF NOT EXISTS import.dre
(
    idmov integer,
    companynumber integer,
    idproduct integer,
    dtdate date,
    nrtank integer,
    dailybalance numeric,
    startbalance numeric,
    endbalance numeric
)

初始数据 #

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：同companynumber、idproduct、nrtank分组下，前一日的coalesce(endbalance, 0)；第一行无前置数据，保持NULL
• endbalance：当日startbalance + dailybalance；第一行保持原有值

尝试过的方法（未成功） #

编写了PL/pgSQL循环函数，但无法正确赋值startbalance：

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;
$$

最优解决方案（无循环，基于窗口函数） #

利用窗口函数计算递推的余额值，通过单条UPDATE语句完成批量更新，效率远高于循环，适合大数据量场景：

WITH calculated_balances AS (
    SELECT
        idmov,
        -- 取前一行的计算后余额作为当前行的startbalance
        LAG(calculated_endbalance) OVER (
            PARTITION BY companynumber, idproduct, nrtank 
            ORDER BY dtdate
        ) AS new_startbalance,
        calculated_endbalance
    FROM (
        SELECT
            idmov,
            companynumber,
            idproduct,
            nrtank,
            dtdate,
            -- 计算累计余额：初始值 + 从第二行到当前行的dailybalance总和
            FIRST_VALUE(endbalance) OVER (
                PARTITION BY companynumber, idproduct, nrtank 
                ORDER BY dtdate
            ) + 
            SUM(dailybalance) OVER (
                PARTITION BY companynumber, idproduct, nrtank 
                ORDER BY dtdate
                ROWS BETWEEN 1 FOLLOWING AND CURRENT ROW
            ) AS calculated_endbalance
        FROM import.dre
    ) AS sub
)
UPDATE import.dre d
SET 
    startbalance = cb.new_startbalance,
    endbalance = cb.calculated_endbalance
FROM calculated_balances cb
WHERE d.idmov = cb.idmov;

逻辑说明 #

1. 内层子查询：

   • 用FIRST_VALUE(endbalance)获取分组内第一行的初始余额（7600）
   • 用SUM(dailybalance)计算从第二行到当前行的dailybalance累计和，与初始值相加得到当前行的最终endbalance

2. 外层CTE：

   • 用LAG(calculated_endbalance)获取前一行的计算后余额，作为当前行的startbalance

3. 最终UPDATE：

   • 将CTE计算出的结果关联原表，批量更新两个余额字段

验证更新结果：

SELECT * FROM import.dre ORDER BY dtdate;

将得到符合需求的递推余额数据。

内容的提问来源于stack exchange，提问作者lamarcondes16