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

Question

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.

Answer 1

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

Answer 2

Attention: This solution will work on a Postgres DB, not on a Redshift DB. I created this because first Postgres was tagged in the question as DB type. If I should remove the answer, please let me know.

Assuming the last line of your sample data is incorrect (should be useid 116C111 instead of 116C123), you could do something like this to do a multiplication:

CREATE AGGREGATE MULTIPLICATE(int8) (SFUNC = int8mul, STYPE = int8);

and then use this in your query:

SELECT
time,
useid,
value,
MULTIPLICATE(value) OVER (PARTITION BY useid ORDER BY useid, value) AS plt
FROM input 
GROUP BY useid, value, time
ORDER BY useid, value, time;

Please see the working example here: db<>fiddle

If your sample data really is correct, you should please describe the logic behind it.