Sample Header Ad - 728x90

Rolling sum with a floor in Athena/Presto

1 vote
1 answer
2674 views
In Athena, I want to calculate a rolling sum, over a window, that can't go below 0. For example, if summing up a column that has values
(1, 2, -1, -2, -1, -2, 1, 2)
I should get
(1, 3, 2, 0, 0, 0, 1, 3)
. Without the floor constraint, it's easy -
(X) OVER (PARTITION BY some_group ORDER BY ordering_col)
. Or if we just wanted to clip all the values at 0 after doing an actual cumulative sum. But I cannot figure out how to use window functions to achieve my desired result in general, though I can make it work for some special cases. Is this even possible?
Asked by Max Gibiansky (111 rep)
Mar 17, 2022, 06:15 PM
Last activity: Apr 7, 2025, 01:09 PM