CODEWITHSUNDEEP
sqlgoogle-bigquery
Raffael
Raffael

Reputation: 20045

Recursive Formula based on previous Row's Result

Let's consider the following query:

with
    init as (
        select 0.1 as y0
    ),
    cte as (
        select 1 as i, 1 as x   -- x_1
        union all
        select 2 as i, 10 as x  -- x_2
        union all 
        select 3 as i, 100 as x -- x_3
        order by i asc
    )
select cte.x, init.y0 -- <- ?
from cte
join init
    on true

There is a CTE init specifying an inital value y_0 and a CTE cte specifying rows with a value x and an index i.

My question is whether I can write a select which realizes the following simple, recursive formula.

y_n+1 = y_n + x_n+1

So, the result should be 3 rows with values: 1.1, 11.1, 111.1 (for y_1, y_2, y_3).

Would that be possible?

Upvotes: 0

Views: 423

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

write a select which realizes the following simple, recursive formula.
y_n+1 = y_n + x_n+1

Consider below

select x, y0 + sum(x) over(order by i) as y
from cte, init

if applied to sample data in your question - output is

enter image description here

Note: the expected result you shown in your question - does not match the formula you provided - so obviously above output is different from one in your question :o)

Upvotes: 1

Raul Saucedo
Raul Saucedo

Reputation: 1780

You need to use the “OVER” statement. You can see more documentation about the syntax.

with
    init as (
        select 0.1 as y0
    ),
    cte as (
        select 1 as ts, 1 as i, 1 as x   -- x_1
        union all
        select 2, 2, 10 as x  -- x_2
        union all 
        select 3, 3, 100 as x
        union all
        select 4, 4, 109 as x -- x_3
        union all
        select 5, 5, 149 as x 
        order by i asc
    )

SELECT *,init.y0 + SUM(i) OVER(
    ORDER BY (ts) 
  ) AS res
FROM cte join init
    on true

Upvotes: 1

Related Questions