CODEWITHSUNDEEP
google-bigquery
Timogavk
Timogavk

Reputation: 869

How to calculate cumulative sums of cumulative products using BigQuery&

I have a table with 2 columns x and y. I need to calculate specific sum for each rows. The formula for 4 rows looks like this: enter image description here

I know how to find cumulative product using EXP(SUM(LN(y)) OVER (ORDER BY x). I have an idea to use PIVOT but it seems impossible because it may calculated by more than 100 rows.

Any suggestions how to make it better??

Upvotes: 0

Views: 378

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

it may calculated by more than 100 rows.

Consider below approach - should work for reasonable number of rows

with temp as (
  select *, row_number() over(order by x) i
  from `project.dataset.table`
)
select x, y,  
  x * (
    ifnull(sum((select exp(sum(ln(z))) from t.z1 z where not z is null)), 0) +
    ifnull(sum((select 1 / exp(sum(ln(z))) from t.z2 z where not z is null)), 0)
  ) s
from (
  select t1.x, t1.y, t1.i,
    array_agg(if(t1.i < t2.i, null, t2.y)) over(partition by t1.x order by t2.i desc) z1,
    array_agg(if(t1.i < t2.i, t2.y, null)) over(partition by t1.x order by t2.i) z2
  from temp t1, temp t2
) t
group by x, y, i

If applied to below dummy data

enter image description here

output is

enter image description here

Upvotes: 2

Related Questions