减去表中同一列的两条记录 [英] Subtract two records of the same column in a table

问题描述

我正在使用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;

这篇关于减去表中同一列的两条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！