A Postgres query to get subtraction of a value in a row by the value in the next row

Question

I have a table like(mytable):

id | value
=========
1 | 4
2 | 5
3 | 8
4 | 16
5 | 8
...

I need a query to give me subtraction on each rows by next row:

id | value | diff
=================
1 | 4 | 4 (4-Null)
2 | 5 | 1 (5-4)
3 | 8 | 3 (8-5)
4 | 16 | 8 (16-8)
5 | 8  | -8 (8-16)
...

Right now I use a python script to do so, but I guess it's faster if I create a view from this table.

Answer 1

You should use window functions - LAG() in this case:

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