sqldatabasepostgresqldatabase-administrationpostgresql-11

Is there a way to update 2 values each time calculating data on each row?


I have the following table:

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

Populated with the following query (10 first lines only):

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)

I have the task to update this table follwing this pattern: 1st: startbalance must be coalesce(endbalance,0) from the last day, grouped by companynumber, idproduct, nrtank. So, using those 10 lines, at day 01/10/2023 startbalance should be 7600 and so on.

2nd: endbalance must be the startbalance of the day plus dailybalance. So, at those 10 lines, at day 01/10/2023 end balance should be 7600 + (- 1089.42) and so on.

The Final result must look something like this (forgive me if that doesn't look the most beautiful set of data you've seen today):

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

Is there some way to accomplish that? I'm trying (badly, I know) to use a for loop to update import.dre but I'm getting nowhere. If by any chance there's a better way to do it than for loop, I appreciate. I don't know how big import.dre can get.

I tried a lot of codes, but all of them updates the whole table (instead of calculating each row). The last function that i've created to debug is the following:

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;

This last query didn't assign nothing at startbalance. Tried another ones, but there is a lot of querys to put here (the best one that I got put 7600 on each day at tank 5 after but I don't remember how I got there)


Solution

  • You can do a CTE first to calculate the endbalance amd in the second steop you fill the startbalance.

    WITH CTE AS (SELECT idmov, 
                        companynumber,
                        idproduct, 
                        dtdate,
                        nrtank,
                        dailybalance,
                        startbalance,
                        SUM(COALESCE(endbalance,0)+dailybalance)
                           OVER w1 AS endbalance
                 FROM import.dre
                 WINDOW w1 AS (PARTITION BY companynumber,idproduct,nrtank 
                               ORDER BY dtdate))
    SELECT idmov, 
           companynumber,
           idproduct, 
           dtdate,
           nrtank,
           dailybalance,
           LAG(endbalance)OVER w2 AS startbalance,
           endbalance
    FROM CTE
    WINDOW w2 AS (PARTITION BY companynumber,idproduct,nrtank 
                  ORDER BY dtdate);
    
    idmov companynumber idproduct dtdate nrtank dailybalance startbalance endbalance
    1 104 10 2023-09-30 5 0 null 7600
    2 104 10 2023-10-01 5 -1089.42 7600 6510.58
    3 104 10 2023-10-02 5 -404.62 6510.58 6105.96
    4 104 10 2023-10-03 5 -470.57 6105.96 5635.39
    5 104 10 2023-10-04 5 -604.97 5635.39 5030.42
    6 104 10 2023-10-05 5 10381.23 5030.42 15411.65
    7 104 10 2023-10-06 5 -593.54 15411.65 14818.11
    8 104 10 2023-10-07 5 -713.27 14818.11 14104.84
    9 104 10 2023-10-08 5 -995.01 14104.84 13109.83
    10 104 10 2023-10-09 5 -736.25 13109.83 12373.58

    fiddle