Create a User Defined SQL Function to Calculate Fibonacci Numbers: Quick Tip

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/latest/HTML/index.htm#Authoring/ExtendingVertica/UDF-SQLFunctions/UsingUser-DefinedSQLFunctions.htm

Have fun!