POSTGRESQL Subtract to each row the previous row

Question

I'm writting a function which at some step I have query result like the following one:

timestamp	Consumed Fuel	Processed Volume
23-09-2021 5:00:00	3000000	982135
23-09-2021 6:00:00	3000010	982136
23-09-2021 7:00:00	3000030	982137
23-09-2021 8:00:00	3000032	982152

And I have to translate it to this:

timestamp	Consumed Fuel	Processed Volume
23-09-2021 5:00:00	10	1
23-09-2021 6:00:00	20	2
23-09-2021 7:00:00	2	15

With one row less, and translating the cumulated values to some sort of "rate", subtracting to each row the previous one and labeling it with the previous timestamp. How I could achieve this?

Thanks for your time.

Answer 1

Have a look at window functions in PostgreSQL docs.

lead ( value anyelement [, offset integer [, default anyelement ]] ) → anyelement

Returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead returns default (which must be of the same type as value). Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to NULL.

SELECT
 tm,
 LEAD(ConsumedFuel) OVER (ORDER BY tm) - ConsumedFuel AS ConsumedFueld,
 LEAD(ProcessedVolume) OVER (ORDER BY tm) - ProcessedVolume AS ProcessedVolume
FROM
 t

db<>fiddle here

• Héctor: Seems nice, thanks. Would be possible to discard last row?
• McNets: LEAD function is not allowed in WHERE clause. You could add it as subquery. dbfiddle.uk/…
• Héctor: I've been tinkering and wouldn't ORDER BY tm DESC OFFSET 1 and then ORDER BY tm ASC be more consistent?
• McNets: Can you add a dbfiddle link please?
• Héctor: dbfiddle