Recursive Formula based on previous Row's Result-CodePudding

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, 3.1, 6.1 (for y_1, y_2, y_3).

Would that be possible?

CodePudding user response：

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

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)