I have a table sample with column 'observations':
Please help with the SQL command to get the following 'cumulative multiplication' output:
2
6
30
300
Add a comment
|
1 Answer
One method is a recursive CTE:
with tt as (
select t.*, row_number() over (order by obs) as seqnum
from t
),
cte as (
select obs as prod, seqnum
from tt
where seqnum = 1
union all
select cte.prod * tt.obs, tt.seqnum
from cte join
tt
on tt.seqnum = cte.seqnum + 1
)
select *
from cte;
Another uses arithmetic to implement a "product" window function:
select t.*,
exp(sum(log(obs)) over (order by obs))
from t;
Here is a db<>fiddle.
