Question 89833 in PostgreSQL Question posted in
The official documentation can be found here.

Posgresql LAG with condition – Postgresql

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?

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search