sql - It's possible to create a rule in preceding rows in sum?

Question

I have a historical stock variation values and have to calculate the stock value. But I also have a rule that the stock is max(stock, 0), or in other words, if stock is negative, I consider it as zero.

To make this in Big Query, I think the way is like this:

with stock as (
SELECT -7.34 variation, UNION ALL
SELECT -1.81 UNION ALL
SELECT -0.51 UNION ALL
SELECT 17.19 UNION ALL
SELECT -1.63 UNION ALL
SELECT 2.82 UNION ALL
SELECT -1.00 UNION ALL
SELECT 0.56 UNION ALL
SELECT -17.92 UNION ALL
SELECT 0.34 UNION ALL
SELECT 1.02 UNION ALL
SELECT 0.39 UNION ALL
SELECT 0.25 UNION ALL
SELECT 0.65 UNION ALL
SELECT 0.09 UNION ALL
SELECT -0.32 UNION ALL
SELECT -0.40 
)
select variation, sum(variation) over (rows unbounded preceding) as stock from stock

I think the solution is something like saying preceding has to be max(preceding, 0). But I didn't find how to do this.

The results I obtained in query is:

|variation|stock|
|---------|-----|
|-7.34    |-7.34|
|-1.81    |-9.15|
|-0.51    |-9.66|
|17.19    |7.53 |
|-1.63    |5.9  |
|2.82     |8.72 |
|-1.0     |7.72 |
|0.56     |8.28 |
|-17.92   |-9.64|
|0.34     |-9.3 |
|1.02     |-8.28|
|0.39     |-7.89|
|0.25     |-7.64|
|0.65     |-6.99|
|0.09     |-6.9 |
|-0.32    |-7.22|
|-0.4     |-7.62|

Answer 1

Waitting for answers