Reputation: 2459
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
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