PostgreSQL update query with "lag()" window function

Question

I have a task involving Postgresql database. I am not very experienced with SQL.

I have a table with weekly turnover of trade products.

For each week, the following information is provided: product, week number, weekly turnover (may be positive or negative, depending on weather more of the product has been purchased or sold). I have added a column with closing balance for each week. I have a closing balance in the table for all product for first week (week_number = 0), but "null" for all other weeks. A few exemplary records are provided below.

    product                     | week_number | turnover | closing_balace
--------------------------------+-------------+----------+----------------
 BLGWK-05.00*1250*KR-S235JRN0-A |           0 |    50.00 |    1240.00
 BLGWK-05.00*1250*KR-S355J2CN-K |           0 |    45.70 |     455.75
 BLGWK-05.00*1464*KR-DD11NIET-K |           0 |    30.01 |     300.00
 BLGWK-05.00*1500*KR-DD11NIET-R |           1 |    10.22 |
 BLGWK-05.00*1500*KR-S235J2CU-K |           1 |    88.00 |

I need a query to fill in all the "null" closing_balance's with following computation:

closing_balance = closing_balance of the same product for previous week + turnover for the week.

I tried this query:

update table_turnover 
set closing_balance = (select lag(closing_balance, 1) over (partition by product order by week_number) + turnover) 
where week_number > 0;

It never worked - the "null" values for closing_balance above "week 0" remained "null".

I also tried:

update table_turnover 
set closing_balance = (select 
                           case when week_number = 0 
                                   then closing_balance
                                   else (lag(closing_balance, 1) over (partition by product order by week_number) + turnover)
                           end
                       from table_turnover)

This one generates an error

more than one record returned by sub-query used as expression

Any idea how to do this computation?

Thank you in advance.

Gordon Linoff · Accepted Answer · 2018-03-01T13:28:40.173

2

Use a subquery in the from clause:

update table_turnover 
    set closing_balance = (ttprev.prev_closing_balance + ttprev.turnover) 
    from (select tt.*,
                 lag(closing_balance) over (partition by product order by 
week_number) as prev_closing_balance
          from table_turnover tt
         ) ttprev
    where ttprev.product = tt.product and ttprev.week_number = tt.week_number and
          week_number > 0;

Or, if you want to use a subquery in the select:

update table_turnover 
    set closing_balance = (turnover +
                           (select tt2.closing_balance 
                            from table_turnover tt2
                            where tt2.product = tt.product and tt2.week_number = tt.week_number - 1
                           )
                          )
    where week_number > 0;

For performance (on either version), you want an index on table_turnover(product, week_number, closing_balance).

edited Mar 01 '18 at 13:28

answered Feb 28 '18 at 19:35

Gordon Linoff

1,242,037
58
646
786

Thank you for your answer. Unfortunately, I can't make the query run. It gives an error: "no FROM clause for table tt in line 3". Any idea why this might be? – Broono Feb 28 '18 at 19:57
Ok. I made queries run, thank you. Unfortunately, they are crazy slow (it will take DAYS to execute through all the products). Any other ideas? – Broono Mar 01 '18 at 08:46
@Broono . . . How large are the tables? In any case, the index should fix the problem. – Gordon Linoff Mar 01 '18 at 13:28
Why do this `UPDATEs` process rows in right order? For example, if it try to update 3-rd week when 2-nd week is not filled yet, we will get `NULLs` on 3-rd week. `PostgreSQL` does not guarantee any order in tables due to it's versioning system and query plans. – Evgeny Nozdrev Oct 09 '18 at 09:24
@EvgenyNozdrev . . . If you have a question, then ask it as a question. – Gordon Linoff Oct 09 '18 at 12:03

PostgreSQL update query with "lag()" window function

1 Answers1

Linked