CODEWITHSUNDEEP
google-bigquery
er0
er0

Reputation: 1834

Compute a value that uses result from previous row

I would like to compute a value for every row that is a function of the current row, and the result from the previous row. The specific function I have is:

v(i) = k(i) * v(i-1)

This doesn't work, but the general idea is something like

SELECT k * LAST_VALUE(cum) as cum from numbers

or the pseudocode:

prev_val = 10
for r in rows 
  prev_val = prev_val + prev_val * r.k
  r.v = prev_val

Here's a sample input table:

v k
1 0
1 3
1 2
1 5

And the desired output:

v k
1 0
4 3
12 2
72 5

The value of v in each row is computed by multiplying the value computed for the previous row by (k+1). e.g. 1 * (3+1) = 4, 4*(2+1) = 12.

Upvotes: 0

Views: 606

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Should be as easy as below example

select *, 1 * exp(sum(ln(1+k)) over(order by i)) as calculated_v
from your_table

if applied to sample data in your question - output is

Note; you must have some column that will define order of calculation - so I added column i for that exact reason - but it can be column of any type - -like date or timestamp, etc.

enter image description here

Upvotes: 1

Related Questions