CODEWITHSUNDEEP
sqlpostgresqlwindow-functionspostgresql-9.6
iamthebull
iamthebull

Reputation: 109

Update column values sequentially where the new value is based on updated values in a 'previous' row

Consider the following table:

select id, val, newval from test1;
 id | val | newval
----+-----+--------
  1 |   1 |      0
  2 |   2 |      0
  3 |   5 |      0
  4 |   9 |      0
  5 |  10 |      0

I am looking for an update query that can sequentially update the values in the newval column where the updated value is the difference between val and val.prev (the value of val in the previous row) plus the UDATED value of newval from the previous row. The result would be:

select id, val, newval from test1;
 id | val | newval
----+-----+--------
  1 |   1 |      0 = set to zero since result would be NULL (no previous row exists)
  2 |   2 |      1 = 2  - 1 + 0
  3 |   5 |      4 = 5  - 2 + 1
  4 |   9 |      8 = 9  - 5 + 4
  5 |  10 |      9 = 10 - 9 + 8
                              ^ - uses updated value of newval

I came close to a solution with the following:

WITH tt AS (
    SELECT id, COALESCE(val - lag(val) OVER w + lag(newval) OVER w,0) as nv 
    FROM test1
    WINDOW w AS (ORDER BY id)
    )
UPDATE test1 SET newval = tt.nv FROM tt
WHERE test1.id = tt.id;

Which gives the following result:

select id, val, newval from test1;
 id | val | newval
----+-----+--------
  1 |   1 |      0
  2 |   2 |      1 = 2  - 1 + 0
  3 |   5 |      3 = 5  - 2 + 0
  4 |   9 |      4 = 9  - 5 + 0
  5 |  10 |      1 = 10 - 9 + 0
                              ^ - uses old value of newval

but this solution does not use the updated values of newval. It uses the old values.

I know I can write a function to loop through the table one row at a time but from what I have read this method would be inefficient and normally discouraged. My actual application is more complicated and involves large tables so efficiency is important.

Upvotes: 0

Views: 401

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

I am pretty sure that your logic reduces to:

select id, val, (val - 1) as newval
from t;

Why? You are taking a cumulative sum of the difference between val and val in the previous row. The cumulative difference ends up being the most recent value minus the first value.

The first value is 1. The above hardcodes that value. It would be easy enough to adjust the logic for the first value:

select id, val,
       (val - first_value(val) over (order by id) as newval
from t;

Upvotes: 2

Related Questions