Bharat
Bharat

Reputation: 2459

Postgresql - Difference in value between two successive rows grouped by category

I need to compute the difference of value between the most recent and next most recent rows in a Postgres table grouped on category. Here is the table:

category    timestamp   value
----------  ----------  -----
A           2018-04-09      5
B           2018-04-09      3
A           2018-04-12      2
A           2018-04-15      4
B           2018-04-25      8
C           2018-04-16      7

The query should group on category ordered on timestamp (descending) and compute the difference of value in the top two rows. Since there is only one row for C, it should be ignored. So the query should return the following result set:

category  diff
--------  ----
A            2
B            5

So the rows are grouped on category and the difference of values is taken for those categories for which at least two or more rows exist. While category C is ignored since it doesn't have two more more rows. The rows are ordered in descending order of timestamp. Finally, the results are ordered by category.

Can Postresql do that in one single query?

Upvotes: 0

Views: 1021

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You can approach this using row_number():

select category,
       sum(case when seqnum = 1 then value else - value end) as diff
from (select t.*,
             row_number() over (partition by category order by timestamp) as seqnum
      from t
     ) t
where seqnum in (1, 2)
group by category
having count(*) > 1;

Upvotes: 2

Related Questions