Search code examples
sqlamazon-redshiftdbt

SQL-how can i product/ multiply across partitions with window functions

The input I have is composed of the following columns: time, UseID, and value. I want to get the plt column as the product of successive numbers.

I know about the existence of the SUM window function to apply sum over rows, but how to handle the product?

time UseID value plt
t1 116C123 a a
t2 116C123 b a*b
t3 116C123 c a*b*c
t4 116C123 d a*b*c*d
t2 116C111 a1 a1
t3 116C111 b1 a1*b1
t4 116C111 c1 a1*b1*c1

Note: the output should be in the domain of real numbers.

Solution

  • You can use logarithms for this!

    log(x) + log(y) = log(x*y) ==> x*y = exp(log(x) + log(y))

    so your query becomes:

    select
    time,
    UseID,
    value,
    exp(
        sum(ln(value)) over (
            partition by UseID
            order by time asc
            rows between unbounded preceding and current row
        )
    ) as plt
from your_table