How to create fibonacci with recursive query effectively

user_8458083 0
Viewed 203

you can test my code here : https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=61a67764f626bfadb1e9594e1ae08229

code to print the first n terms of the fibonacci sequel:

with fibo(s2,s1,n) as(
    select 1  ,1  ,1  from dual
    union all
    select s1+s2,s2,n+1 from fibo where n<12
)
select s2 from fibo;

It works but it use probably twice as much memory as needed. Each line contains the nth and n-1th terms of the sequel before the selection

Therefore i tried with Lag function

with fibo(s,n) as(
    select 1,1 from dual
    union all
    select LAG(s, 1, 0) OVER ( ORDER BY s) +LAG(s, 2, 0) OVER ( ORDER BY s),n+1 from fibo where n<12
)
Select * from fibo

But i get only a sequel of 1. (same things with lead function)

I have tried to understand what happens with this :

with test(s,d1,d2,n) as(
    select 1,0,0,1 from dual
    union all
    select
        2*s,LAG(s, 1, 0) OVER (a  ORDER BY s) ,
        LAG(s, 2, 0) OVER ( ORDER BY s),n+1
    from test where n<12
)
select * from test

It seems that lag returns always 0. Is it impossible to use lag and lead in recursive query? Or do i do something false?

oracle11gr2 plsql sql
1 Answers

"Recursive" queries are not recursive, they are iterative.
The iteration starts with the anchor (the part(s) of the UNION ALL that doesn't refer to the CTE).
For each following iteration, the result set of the previous iteration (aliased by the CTE) is used as the input.
The iteration stops when the result set is empty.

In your specific attempt the anchor returns a single record and so is every following query. Obviously LAG(s, 2, 0) will always return 0

user_63364790
Related