Pi Day, March 14

Geeks around the world will be celebrating Pi day, a shameless excuse to put the month before the day like the Americans do so we can talk Mathematics on “3.14” day Smile

So what better way to show how cool SQL can be with some expressions to approach Pi with continuous series

All we really need to know by way of background is the CONNECT BY LEVEL trick to generate arbirtary sequences of row, eg


SQL> select level from dual connect by level <= 5;

     LEVEL
----------
         1
         2
         3
         4
         5

Now we can get some approximations to Pi using the some of the standard SQL functions SQRT and POWER



SQL> select sqrt(8*sum(1/power((level*2-1),2))) pi
  2  from dual
  3  connect by level <= 100000
  4  order by 1 desc;

        PI
----------
3.14158947

1 row selected.

SQL>
SQL>
SQL> select sqrt(6*sum(1/(level*level))) pi
  2  from dual
  3  connect by level <= 100000
  4  order by 1 desc;

        PI
----------
 3.1415831

1 row selected.

SQL>
SQL> select 8*sum(1/((4*(level-1)+1)*(4*(level-1)+3))) pi
  2  from dual
  3  connect by level <= 100000;

        PI
----------
3.14158765

1 row selected.

SQL>
SQL>
SQL> select
  2   4*sum(
  3     power(-1,level-1)/(level*2-1)*
  4        ( 12*power(1/18,level*2-1)+
  5           8*power(1/57,level*2-1)-
  6           5*power(1/239,level*2-1))) pi
  7  from dual
  8  connect by level <= 100;

        PI
----------
3.14159265

1 row selected.

SQL>

Some methods to calculate Pi need factorials, but there isn’t a native SQL function for that. But in 12c, that’s no problem, we can define SQL functions on the fly directly inside our SQL statement !


SQL>
SQL> with
  2    function factorial(n int) return int is
  3      f int := 1;
  4    begin
  5      for i in 1 .. n loop
  6        f := f * i;
  7      end loop;
  8      return f;
  9    end;
 10  select  2*sum(
 11    power(2,level-1)*power(factorial(level-1),2)/factorial(2*level-1)
 12    ) pi
 13  from dual
 14  connect by level <= 17;
 15  /

        PI
----------
 3.1415864

1 row selected.

But what if you’re not on 12c yet ? Well, you should be! You can check out why it’s time to upgrade from Maria and myself chatting about it over coffee

But whilst you’re on 11g, there is still plenty of cool SQL options to play with Pi. Here we can use recursive SQL to generate Pi, based on the formula:

 

image



SQL> with term(numerator,product,seq) as
  2  ( select sqrt(2) numerator, sqrt(2)/2 product , 1 seq from dual
  3    union all
  4    select sqrt(2+numerator), sqrt(2+numerator)*product/2 , seq+1
  5    from   term, dual
  6    where  term.seq <= 16
  7  )
  8  select 2/product pi
  9  from term
 10  where seq = 16;

        PI
----------
3.14159265

1 row selected.

 

SQL…still the most awesome language out there!

Advertisements

2 thoughts on “Pi Day, March 14

  1. select
    sum(
    (
    4 / (8 * (level – 1) + 1) –
    2 / (8 * (level – 1) + 4) –
    1 / (8 * (level – 1) + 5) –
    1 / (8 * (level – 1) + 6)
    ) / power(16, level – 1)
    )
    from
    dual
    connect by
    level <= 10

    probably is the most performant way to get a very good approximation

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s