Sum of consecutive same-sign rows in Postgresql

user_2091169 0
Viewed 53

Suppose the following table:

date   |  user    |   amount
-----------------------------
01-01  |  John    |       +3
01-02  |  John    |       +2
01-03  |  John    |       +1
01-04  |  John    |       -5
01-05  |  John    |       +1
01-01  |  Jack    |       +2
01-02  |  Jack    |       -1
01-03  |  Jack    |       -6

What would be a way to group the amount by user and sign and apply the sum over consecutive amounts of same sign, for each user? So as to give this output:

date   |  user    |   amount
-----------------------------
01-01  |  John    |       +6  <- this is the sum of all consecutive same sign
01-04  |  John    |       -5
01-05  |  John    |       +1
01-01  |  Jack    |       +2
01-02  |  Jack    |       -7  <- this is the sum of all consecutive same sign

I've tried using window functions but the closest I could get to was:

select
    sum(amount) over (partition by user, sign(amount) order by date)
from my_table

Which doesn't lead to the desired output.

cumulative-sum window-functions group-by postgresql sql
1 Answers

You can use LAG() window function to check if the previous amount has the same sign as the current amount and create a boolean flag which you can use with SUM() window function to create the consecutive groups with the same sign and then aggregate:

SELECT MIN(date) date,
       "user",
       SUM(amount) amount
FROM (       
  SELECT *, SUM(flag) OVER (PARTITION BY "user" ORDER BY date) grp
  FROM (
    SELECT *, COALESCE(SIGN(amount) <> LAG(SIGN(amount)) OVER (PARTITION BY "user" ORDER BY date), true)::int flag 
    FROM my_table
  ) t  
) t 
GROUP BY "user", grp
ORDER BY "user", date;

See the demo.

user_104988280
Related