Future forecasting based on a multiplier in BigQuery-CodePudding

I am trying to do semi forecasting with BigQuery. I have minutely data of users, and a minute multiplier (pre calculated based on past events) that should predict the next minute's value. I created a dataset with all future minutes for the relevant timeframe, future minute will have null value, it looks like that:

Now trying to calculate all future values based on the multiplier. I can't figure out how to apply this to more than 1 row, meaning; the first null row will be the prev value times the multiplier. But now what? How can I keep calculating it based on future values? The output should look like that:

So 100 is the only real value - then 100 * 1.1 will be 120, and then 120 * 1.2 will be 132, so on and so forth.

Appreciate the help guys, thanks!

CodePudding user response：

Do you think there is another way to solve this then?

Consider below approach

select *,
  round(first_value(value/multiplier) over win * exp(sum(ln(multiplier)) over win), 2) as new_value
from your_table
window win as (order by minute)

if applied to sample data in your question - output is

CodePudding user response：

We can do this with a recursive query:

with recursive cte as (
    select minute, value, multiplier, value new_value from mytable where minute = 1
    union all
    select t.minute, t.value, t.multiplier, c.value * t.multiplier
    from cte c
    inner join mytable t on t on t.minute = c.minute   1
)
select * from cte order by minute