CODEWITHSUNDEEP
sqlpostgresql
Dmitry
Dmitry

Reputation: 316

Posgresql LAG with condition

My data looks like this:

id user data date
1 1 1 2023-02-05
2 2 1 2023-02-05
3 1 2 2023-02-06
4 1 3 2023-02-07
5 2 5 2023-02-07

I want to get a difference between data of each row and a previous row for this user like this:

id user data date diff
1 1 1 2023-02-05
2 2 1 2023-02-05
3 1 2 2023-02-06 1
4 1 3 2023-02-07 1
5 2 5 2023-02-07 4

I can do this with LAG function but without condition that users for difference must be same. How can I do it with condition in postgres?

Upvotes: 2

Views: 387

Answers (2)

Zegarek
Zegarek

Reputation: 26302

As per the comment: window functions let you partition your input, narrowing down the context of each window the way you want it:

select *, 
       coalesce(data-(lag(data) over w1),0) as data_diff
from   test
window w1 as (partition by user order by date asc) 
order by date, 
        "user";

It's also handy to define the window separately to save space and handle null for first row in case of lag() or last row for lead() with coalesce().

Online demo

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522226

We can use LAG() as follows:

SELECT id, user, data, date,
       data - LAG(data) OVER (PARTITION BY user ORDER BY date) AS diff
FROM yourTable
ORDER BY date, user;

Upvotes: 1

Related Questions