Postgres SQL best way to do rolling SUM with floor of 0-CodePudding

What is the best way to calculate a rolling sum of numbers in postgres that never go below 0. For example if you have 1 column table with values 0, -1, -1, 2. The output will be 2 because the rolling sum will never go below 0. If I group by the column and did a SUM() obviously the output would be 0 because it will go into negative numbers. Do I need a special function to calculate the rolling sum with a floor of 0?

CodePudding user response：

You may use SUM as an analytic function with a CASE expression. Assuming your data looks like this:

id | val
1  | 0
2  | -1
3  | -1
4  | 2

we can use the following query:

SELECT id, SUM(CASE WHEN val > 0 THEN val ELSE 0 END) OVER (ORDER BY id) rolling_sum
FROM yourTable
ORDER BY id;

This query generates the following output:

id | val | rolling_sum
1  | 0   | 0
2  | -1  | 0
3  | -1  | 0
4  | 2   | 2

CodePudding user response：

Try below query

SELECT dt.*,
-- find the lowest previous CumSum < 0       
-- and adjust the current CumSum to zero
Max(CASE WHEN CumSum < 0 THEN -CumSum ELSE 0 end)
   Over (PARTITION BY groupid
         ORDER BY pkid
         ROWS Unbounded Preceding)
     CumSum AS AdjustedSum
FROM 
( 
   SELECT pkid, numvalue, grouped,
   -- calculate a standard cumulative sum
  Sum(numvalue)
  Over (PARTITION BY groupid
        ORDER BY pkid
        ROWS Unbounded Preceding) AS CumSum
 FROM foo
) AS dt