Search

Top 60 Oracle Blogs

Recent comments

Math and SQL: Two of my favourite things

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 Smile

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