sql - PostgreSQL 计算行之间的差异

Question

我尝试使用查询计算字段中行之间的差异:

Illustrations:input:year,month,fixesoutput:increase     year | month | fixes    | increase    ------+-------+----------+-----------     2006 | 04    |       1  | 0     2006 | 05    |       4  | 3     2006 | 06    |       3  | -1

根据修复中相邻行之间的差异增加列作为输出。

Answer 1

这就是窗口函数的用途:

select year, 
       month,
       fixes,
       fixes - lag(fixes) over (order by year, month) as increase,
from the_table;

更多详情请查看手册:
http://www.postgresql.org/docs/current/static/tutorial-window.html