Sum of consecutive same-sign rows in Postgresql-CodePudding

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.

CodePudding user response：

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.