Subtract two records of the same column in a table

问题:

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

Here is the table:

Scores

6
8
9

---

Result

6
2
1

What I want to do:

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

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

回答1:

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;

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)

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

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;

回答2:

Something like

SELECT SUM(COALESCE(rx.diff,rx.val))
  FROM
(SELECT x.val,
        x.val - lag(x.val) over () as diff
  FROM (SELECT unnest(ARRAY[6,8,9]) as val) AS x) AS rx

Substituting your table select for my unnest which just generates the data as per your example.