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

I have input: time, custid, and value. I want to get plt colume. I just know window function have function sum partition but it don't have product or multiple function. So how can i solve this problem. The output is real number.

Input                   Output
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

Image

CodePudding user response：

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

CodePudding user response：

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.