postgresql - 使用 Postgres 中 GROUP BY 查询中上一行的值更新行

Question

假设我有一个这样的表:

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

如果我想执行 ORDER BY DATE 和 GROUP BY account_id 并使用前一行的值更新每一行怎么办？

所以最终的结果应该是:

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

有什么好的方法可以在单个查询中做到这一点？

Answer 1

lag(value anyelement [, offset integer [, default anyelement ]]) 窗口函数会为你做这件事，基本上:

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;

结果:

 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)