find the difference between current and previous value

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)

CodePudding user response：

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

CodePudding user response：

This is working for me by partitioning over the dictionary itself.

SELECT name, number, dictionary, 
number - LEAD(number,1) OVER (PARTITION BY dictionary) as "Difference_value"
FROM users
WHERE dictionary = 'Dict1';

It also solves the issue of the last row being extracted from the first one (300-150).

The last line with the explicit WHERE is optional if you want to see the difference for Dict2 as well.

DB-Fiddle