Fibonacci numbers in SQL

Pure SQL doesn’t support loops, recursions or user-defined functions. Besides, concatenating fields from multiple rows of a table or a subquery is not a standard aggregate function. This example uses:

• Binet’s formula and math functions round, power and sqrt to calculate n-th Fibonacci number;
• pseudocolumn level to construct a pseudotable t1 containing numbers 1 through 16;
• built-in function SYS_CONNECT_BY_PATH to concatenate the resulting numbers in ascending order.

 SELECT REPLACE(MAX(SYS_CONNECT_BY_PATH(fib||', ', '/')),'/','')||'...' fiblist 
 FROM ( 
 SELECT n, fib, ROW_NUMBER() 
 OVER (ORDER BY n) r 
 FROM (select n, round((power((1+sqrt(5))*0.5, n)-power((1-sqrt(5))*0.5, n))/sqrt(5)) fib 
 from (select level n
 from dual
 connect by level <= 16) t1) t2
 ) 
 START WITH r=1 
CONNECT BY PRIOR r = r-1; 

Comments

View the discussion thread.

blog comments powered by Disqus