Pi day 2021

Posted by

As friend Bruno put it so eloquently… Smile

image

…but every day is a good day to do some SQL.  So here’s some SQL pi calculations I’ve tweeted out on this day over the years, that I thought I would capture here for posterity.

SQL rocks.


SQL> with t(n,p,s) as
  2  ( select sqrt(2) n, sqrt(2)/2 p , 1 s from dual
  3    union all
  4    select sqrt(2+n), sqrt(2+n)*p/2 , s+1
  5    from   t, dual
  6    where  t.s <= 16 )
  7  select 2/p pi from t where s = 16;

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

SQL> select sqrt(6*sum(1/power(level,2))) 
  2  from dual connect by level<1e5
  3  /

SQRT(6*SUM(1/POWER(LEVEL,2)))
-----------------------------
                    3.1415831
                    

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

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

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


SQL>  with t as
  2   ( select
  3       dbms_random.value(0,1) x,
  4       dbms_random.value(0,1) y
  5     from dual connect by level <= 1000000 )
  6  select
  7    count(case when sqrt(x*x+y*y) <= 1 then 1 end)/
  8    count(*)*4 pi
  9  from t;

      PI
--------
3.142468

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


with t(s,a,b,t,p) as
( select 1 s,1 a,1/sqrt(2) b,1/4 t,1 p from dual
union all
select  s+1 s,(a+b)/2 a,sqrt(a*b) b,t-p*power((a - ((a+b)/2)),2) t,2*p p
from t
where s < 10
)
select (a+b)*(a+b)/(4*t) pi
from t
where s = 10;

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


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


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

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



Got some thoughts? Leave a comment

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

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