How can I get a cumulative product with Snowflake?-CodePudding

I want to calculate the cumulative product across rows in Snowflake.

Basically I have monthly rates that multiplied accumulate across time.

(Some databases have the product() SQL function for that).

CodePudding user response：

A trick suggested by Sterling Paramore: Add logs, and then exponentiate it:

with data as (select $1 x from values (1),(2),(3),(4),(5))

select x
    , sum(x) over(order by x) sum
    , exp(sum(ln(x)) over(order by x)) mult
from data

CodePudding user response：

If a built-in function doesn't exist, it's usually possible to roll something custom using a

Note the empty over() clause which forces Snowflake to do a single sequential run over the data instead of splitting it into parallel chunks