1

Im looking to recreate the SUM_FUNC_LIMIT column below where a subtraction occurs but is limited to reaching 0.

A SUM() window function will recreate SUM_FUNC, but I cannot create the SUM_FUNC_LIMIT column.

EXCEL FORMULA is =IF(D1+B2<0,0,D1+B2)

ROW [A] AMOUNT [B] SUM_FUNC [C] SUM_FUNC_LIMIT [D]
1 5 5 5
2 9 14 14
3 -6 8 8
4 -6 2 2
5 -6 -4 0
6 3 -1 3
7 -6 -7 0
8 -6 -13 0
9 7 -6 7
10 3 -3 10
11 -6 -9 4

Any help appreciated.

Have tried many things... but calling a previous entry within a new entry is not possible in SQL.

GMB
  • 216,147
  • 25
  • 84
  • 135
truecount
  • 11
  • 2
  • Looks like [Capping a running total](https://stackoverflow.com/a/53994970/5070879) case which could be achieved using [MATCH_RECOGNIZE](https://docs.snowflake.com/en/sql-reference/constructs/match_recognize) – Lukasz Szozda Jun 29 '23 at 14:11
  • @LukaszSzozda, could you please elaborate on how to use this function for his purpose? I'm unable to decipher from documentation and there is nothing open-source – truecount Jun 30 '23 at 01:59
  • The idea is to use MATCH_RECOGNIZE and pattern that "resets" the running total when it hits 0. **[db<>fiddle demo](https://dbfiddle.uk/rzQSRv0I)** As of time of writing it seems that this pattern is not possible for Snowflake due to functions that are [supported on DEFINE](https://docs.snowflake.com/en/sql-reference/constructs/match_recognize#limitations-on-window-functions-used-in-define-and-measures) – Lukasz Szozda Jun 30 '23 at 10:47

1 Answers1

0

As far as concerns, you can't implement this logic with window functions, you would typically need a recursive query. For table with a structure like mytable(rn, amount), where rn starts at 1, you could phrase it as:

with recursive cte as (
    select 0 as rn, 0 as amount, 0 as sum_func, 0 as sum_func_limit
    union all
    select t.rn, t.amount, t.amount + c.sum_func, greatest(t.amount + c.sum_func, 0) 
    from cte c
    inner join mytable t on t.rn = c.rn + 1
)
select * from cte where rn > 0
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thanks very much GMB, I've managed to achieve the result because of your response. Noting I think you meant (but it works with the edit): select t.rn, t.amount, t.amount + c.sum_func, greatest(t.amount + c.sum_func_limit, 0) – truecount Jun 29 '23 at 13:01