Sunday, June 28, 2015

Fibonacci Sequence Procedure

Well it has been awhile since I posted, sorry about that. Today I was watching reruns of the TV show Fringe and when Walter referenced the Fibonacci Sequence I got side tracked with MySQL options for this.

Now this post already existed:

So I took that post and expanded on it a little, the result is a procedure that you can call and return the range within the Fibonacci Sequence that you are after.

The procedure is below:

delimiter //
CREATE PROCEDURE `Fibonacci`(IN POS INT, IN RANG INT, IN LIMTED INT)
BEGIN
       select FORMAT(Fibonacci,0) AS Fibonacci from (
       select @f0 Fibonacci, @fn:=@f1+@f0, @f0:=@f1, @f1:=@fn
       from   (select @f0:=0, @f1:=1, @fn:=1) x,
       information_schema.STATISTICS p limit LIMTED) y LIMIT POS, RANG;
END//
delimiter ;


You can call this and pass whatever values and ranges you are after.

So if you want the 5th value (starting from 0) in the sequence and the next value

> CALL Fibonacci(5,2,100);
+-----------+
| Fibonacci |
+-----------+
|         5 |
|         8 |
+-----------+


So if you want the 30th value (starting from 0) in the sequence and the next value

> CALL Fibonacci(30,2,100);
+-----------+
| Fibonacci |
+-----------+
| 832,040   |
| 1,346,269 |
+-----------+


So if you want the 150th value (starting from 0) in the sequence and the next value

> CALL Fibonacci(150,2,1000);
+--------------------------------------------+
| Fibonacci                                  |
+--------------------------------------------+
| 9,969,216,677,189,305,000,000,000,000,000  |
| 16,130,531,424,904,583,000,000,000,000,000 |
+--------------------------------------------+


 So you get the idea. Now you can also expand the range of results if you want more than 2 just change the 2nd value in the procedure call.

> CALL Fibonacci(0,10,100);
+-----------+
| Fibonacci |
+-----------+
| 0         |
| 1         |
| 1         |
| 2         |
| 3         |
| 5         |
| 8         |
| 13        |
| 21        |
| 34        |
+-----------+

> CALL Fibonacci(30,5,100);
+-----------+
| Fibonacci |
+-----------+
| 832,040   |
| 1,346,269 |
| 2,178,309 |
| 3,524,578 |
| 5,702,887 |
+-----------+


 Anyway, hope someone finds it helpful and credit for the base of query does go to the original post.

No comments:

Post a Comment

@AnotherMySQLDBA