我正在使用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;更多推荐
减去表中同一列的两条记录
发布评论