减去表中同一列的两条记录

本文介绍了减去表中同一列的两条记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用PostgreSQL，我想减去同一张表的两条记录，并在同一查询中使用结果。

I am using PostgreSQL and I want to subtract two records of the same table and use the result in the same query.

这是表：

6 8 9

6 8 9

6 2 1

6 2 1

我想做什么：

Result = Score(i) - Score(i-1)

最后我想要这些的总和结果。在我的示例中，总和（结果）必须为9。

In the end I want the sum of these results. sum(result) must be 9 in my example.

推荐答案

您需要某种方法来确定分数中的行顺序。关系数据库中的表中没有自然顺序。因此，我假设您有一个 id （或时间戳等）来排序记录。还是保证 i 在每个新行中都更大？然后，您可以通过 i 进行订购。

You need some way to determine the sequence of rows in score. There is no "natural order" in a table in a relational database. So I assume you have an id (or a timestamp or something) to order your records by. Or is i guaranteed to be greater in every new row? Then you can just order by i.

查询本身很简单-一旦找到关于 窗口功能 ：

The query itself is simple - once you find out about window functions:

SELECT i - lag(i, 1, 0) OVER (ORDER BY id) AS result FROM score ORDER BY id;

包括@Clodoaldo的改进（请参阅评论）。

Including an improvement by @Clodoaldo (see comment).

lag(i, 1, 0) OVER (ORDER BY id)

等效于，但比以下更优雅：

is equivalent to, but more elegant than:

COALESCE(lag(i) OVER (ORDER BY id), 0)

目的是涵盖第一行的特殊情况，该行没有上一行。 在sqlfiddle上进行演示。

Purpose is to cover the special case of the first row that has no preceding row. Demo on sqlfiddle.

sum（result）是微不足道的，因为它必须等于最后一个 i 根据您的描述：

sum(result) is trivial because it is bound to equal the last i according to your description:

SELECT i FROM score ORDER BY id DESC LIMIT 1;