Create a User Defined SQL Function to Calculate Fibonacci Numbers

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

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)

Helpful Link:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/ExtendingVertica/UDF-SQLFunctions/UsingUser-DefinedSQLFunctions.htm

Have fun!

Sign In or Register to comment.