1

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 - SUM(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?

Max Gibiansky
  • 111
  • 1
  • 3

1 Answers1

0

don't know if you are still looking for a solution but I ran into a similar problem.

I solved that using reduce function after aggregating the appropriate window as array. https://stackoverflow.com/questions/70470329/interesting-problem-concerned-with-updating-a-value-in-presto-sql/70484801#70484801

I think with slight modification this approach will work.

theGreedyLearner
  • 13
  • 2