Create a User Defined SQL Function to Calculate Fibonacci Numbers
Jim_Knicely
- Select Field - Administrator
The Fibonacci Sequence is the series of numbers (i.e. 0, 1, 1, 2, 3, 5, 8, 13, 21, 34, ...) where the next number in the sequence is found by taking the sum of the previous two numbers.
Calculating these numbers in SQL would be a bit complicated if it were not for the “Golden Ratio”! What is that? If you take any two successive (one after the other) Fibonacci Numbers, their ratio is very close to the Golden Ratio "φ" which is approximately equal to 1.618. Knowing that, we can create a User Defined Function in Vertica that returns the Fibonacci Number for a given Fibonacci Sequence!
Example:
dbadmin=> CREATE OR REPLACE FUNCTION fibonacci_number (n INT)
dbadmin-> RETURN INT
dbadmin-> AS
dbadmin-> BEGIN
dbadmin-> RETURN (((1.618034^n) - (-0.618034^n)) / SQRT(5))::INT;
dbadmin->
dbadmin-> END;
CREATE FUNCTION
dbadmin=> SELECT n fibonacci_sequence,
dbadmin-> fibonacci_number(n)
dbadmin-> FROM (SELECT ROW_NUMBER() OVER () - 1 AS n FROM columns) foo
dbadmin-> LIMIT 11;
fibonacci_sequence | fibonacci_number
--------------------+------------------
0 | 0
1 | 1
2 | 1
3 | 2
4 | 3
5 | 5
6 | 8
7 | 13
8 | 21
9 | 34
10 | 55
(11 rows)
Have fun!
0