PostgreSQL查询：实现行值与下一行值的差值计算

这事儿太简单了！在PostgreSQL里用窗口函数就能轻松搞定，比Python脚本高效多了，毕竟数据库层面处理数据本来就是它的强项。你需要的核心工具是LAG()窗口函数，它能帮你获取到当前行的上一行数据，然后直接计算差值即可。

基础查询语句（匹配你的输出格式） #

首先给出完全符合你示例输出的查询语句，它会计算差值并带上括号里的注释：

SELECT 
  id,
  value,
  CONCAT(
    COALESCE(value - LAG(value) OVER (ORDER BY id), value),
    ' (', value, '-', COALESCE(LAG(value) OVER (ORDER BY id)::TEXT, 'Null'), ')'
  ) AS diff
FROM mytable
ORDER BY id;

关键部分解释： #

• LAG(value) OVER (ORDER BY id)：这个窗口函数会按照id的排序规则，获取当前行的上一行的value值。如果是第一行（没有上一行），它会返回Null。
• COALESCE(value - LAG(...), value)：处理第一行的特殊情况——因为value - Null在PostgreSQL中结果是Null，用COALESCE可以把这个Null替换成当前行的value，这样就得到了你示例里第一行的4 (4-Null)效果。
• CONCAT(...)：把计算出的差值和注释文本拼接成你需要的格式。

创建视图 #

如果要把这个查询做成可复用的视图，只需要把上面的查询套进CREATE VIEW语句里就行：

CREATE VIEW mytable_with_diff AS
SELECT 
  id,
  value,
  CONCAT(
    COALESCE(value - LAG(value) OVER (ORDER BY id), value),
    ' (', value, '-', COALESCE(LAG(value) OVER (ORDER BY id)::TEXT, 'Null'), ')'
  ) AS diff
FROM mytable
ORDER BY id;

之后你只需要查询这个视图就能直接得到结果：

SELECT * FROM mytable_with_diff;

简化版（仅数值差值） #

如果你不需要括号里的注释，只需要纯数值的差值，查询可以简化成这样：

SELECT 
  id,
  value,
  COALESCE(value - LAG(value) OVER (ORDER BY id), value) AS diff
FROM mytable
ORDER BY id;

对应的视图创建语句也可以同步简化。

内容的提问来源于stack exchange，提问作者SMA