sqlpostgresqloffsetlead

find the difference between current and previous value - postgresql

I'm trying to write a query that will show me the difference between the current value and the previous one from the query result. It seems to me that this can be done with the "OFFSET" or "LEAD" method. But I don't understand how.

Исходная таблица.

Name    Number  Dictionary  
---
Kate    300     Dict1       
Helena  200     Dict1       
Michael 150     Dict1       
John    100     Dict2

I want to select only data for Dict1 in my query, while in the new column I need to see the difference between the current and previous values from the result of the "Number" query

select * from table
where Dictionary='Dict1'

Name    Number  Dictionary  Difference value
---
Kate    300     Dict1       100  #(300-200)
Helena  200     Dict1       50 #(200-150)
Michael 150     Dict1       150 #(150-null)
Solution

  • Use the window function LEAD

    SELECT *, number - COALESCE(LEAD(number) OVER w,0)
FROM t
WHERE dictionary = 'Dict1'
WINDOW w AS (PARTITION BY dictionary ORDER BY number DESC
             ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING);

    Demo: db<>fiddle