 # Math and SQL: Two of my favourite things Posted by

There has always been a special place in heart for the Fibonacci sequence. Decades ago in high school when comparing the ratio of successive items, as any naive student would, I thought I had stumbled upon some wonderful discovery that would assure my place in the pantheon of mathematical greats. Of course, it was somewhat disheartening to have my teacher subsequently crush that illusion when he threw me a reference book demonstrating my discovery had been well established just a mere 400 years earlier Nonetheless, here’s some mindless fun for the weekend. Whilst I know Fibonacci is the “poster child” for recursive program demos, I’ll keep it simple and go with an iterative version:

```

SQL> create or replace
2  function fibonacci(n int) return number is
3    t1 int := 1;
4    t2 int := 1;
5    t3 int;
6  begin
7    if n = 1 then return t1; end if;
8    if n = 2 then return t2; end if;
9    for i in 3 .. n
10    loop
11      t3 := t1 + t2;
12      t1 := t2;
13      t2 := t3;
14    end loop;
15    return t3;
16  end;
17  /

Function created.
```

and with a quick test, out pops the famed series loved by mathematicians and bunny breeders everywhere.

```

SQL> select fibonacci(level)
2  from   dual
3  connect by level <= 20;

FIBONACCI(LEVEL)
----------------
1
1
2
3
5
8
13
21
34
55
89
144
233
377
610
987
1597
2584
4181
6765

20 rows selected.
```

Lets take a slightly different take on that series now. I’ll take each term and divide by an ever increasing power of 10, so that we still get the same series but they are shifted further and further down the decimal point line.

```

SQL> select to_char(fibonacci(level)/power(10,level+1)) x
2  from   dual
3  connect by level <= 20;

X
------------------------------
.01
.001
.0002
.00003
.000005
.0000008
.00000013
.000000021
.0000000034
.00000000055
.000000000089
.0000000000144
.00000000000233
.000000000000377
.000000000000061
.00000000000000987
.000000000000001597
.0000000000000002584
.00000000000000004181
.000000000000000006765

20 rows selected.
```

Nothing really magical there, but let’s see what happens when you sum those terms. I’ll take it out to 120 powers of 10 which is close to the limit of the NUMBER datatype.

```
SQL> select sum(x) tot
2  from
3  (
4    select fibonacci(level)/power(10,level+1) x
5    from   dual
6    connect by level <= 120
7  );

TOT
----------------------------------------
.011235955056179775280898876404494382023

1 row selected.
```

At first glance you may be thinking that result is just another random summation in the infinity of random decimal summations out there. But here comes the sledgehammer moment of revelation. Lets compare the sum of the series to a simple fraction.

```

SQL> select 1/89 x
2  from dual;

X
----------------------------------------
.011235955056179775280898876404494382023

1 row selected.
```

Mind blown!

Truth be told, I’m not sure how this information is going to improve your database, but hey…all work and no play right?.

Credit: http://www2.math.ou.edu/~dmccullough/teaching/miscellanea/miner.html

Image Credit: https://commons.wikimedia.org/wiki/File:Fibonacci_spin_(cropped).jpg, Debmalya Mukherjee

1. Matthias Rogel says:

``` select sum(x) from ( select power(-1, mod(level+1, 2)) * fibonacci(level)/power(10,level+1) x from dual connect by level <= 120 ) /```

``` SUM(X) --------------------------------- .0091743119266055045871559633 select 1 / 109 from dual / ```

``` 1/109 --------------------------------- .0091743119266055045871559633 ```

2. Connor McDonald says:

nice 🙂

This site uses Akismet to reduce spam. Learn how your comment data is processed.