Update row with value from previous row in a GROUP BY query in Postgres

Question

Imagine I have a table like so:

account_id  date      value
1           1/1/2015  5
1           1/3/2015  7
1           1/7/2015  8
3           1/2/2015  4

What if I wanted to do an ORDER BY DATE and GROUP BY account_id and update each row with the value of the row before it?

So the end result should be:

account_id  date      value  prev_value
1           1/1/2015  5     null
1           1/3/2015  7     5
1           1/7/2015  8     7
3           1/2/2015  4     null

Any good way to do that in a single query?

Answer 1

lag(value anyelement [, offset integer [, default anyelement ]]) window function will do it for you, which basically:

returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default (which must be of the same type as value). Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null

WITH t(account_id,date,value) AS ( VALUES
  (1,'1/1/2015'::DATE,5),
  (1,'1/3/2015'::DATE,7),
  (1,'1/7/2015'::DATE,8),
  (3,'1/2/2015'::DATE,4)
)
SELECT
  *,
  lag(value,1) OVER (PARTITION BY account_id) AS prev_value
FROM t
GROUP BY 1,2,3
ORDER BY 1,2,3;

Result:

 account_id |   date   | value | prev_value 
------------+----------+-------+------------
          1 | 1/1/2015 |     5 |           
          1 | 1/3/2015 |     7 |          5
          1 | 1/7/2015 |     8 |          7
          3 | 1/2/2015 |     4 |           
(4 rows)