Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: May 27, 2008 - 10:55 am UTC

Last updated: May 27, 2008 - 6:47 pm UTC

Version: 9.0.2

Viewed 10K+ times! This question is

You Asked

Hi,

Can I get Cumulative Normal Distribution and Normal Distribution Density using PL/SQL?

Thanks
Ankit.

and Tom said...

http://docs.oracle.com/cd/B10501_01/server.920/a96540/functions29a.htm#82888

for example, check out the sql reference guide - all functions are documented, you might well find dozens more that you can use.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

cum_dist won't do it

Georg, May 27, 2008 - 5:43 pm UTC

I think you have to write your own PL/SQL function.

Here are examples in C++ and VB
http://www.sitmo.com/doc/Calculating_the_Cumulative_Normal_Distribution
Tom Kyte
May 27, 2008 - 6:47 pm UTC

If this is the function:
double N(const double x)
{
  const double b1 =  0.319381530;
  const double b2 = -0.356563782;
  const double b3 =  1.781477937;
  const double b4 = -1.821255978;
  const double b5 =  1.330274429;
  const double p  =  0.2316419;
  const double c  =  0.39894228;

  if(x >= 0.0) {
      double t = 1.0 / ( 1.0 + p * x );
      return (1.0 - c * exp( -x * x / 2.0 ) * t *
      ( t *( t * ( t * ( t * b5 + b4 ) + b3 ) + b2 ) + b1 ));
  }
  else {
      double t = 1.0 / ( 1.0 - p * x );
      return ( c * exp( -x * x / 2.0 ) * t *
      ( t *( t * ( t * ( t * b5 + b4 ) + b3 ) + b2 ) + b1 ));
    }
}



do NOT use plsql, just inline that right in the SQL statement itself. Use a view to hide it if you need.

Few more techniques

Chen Shapira, May 27, 2008 - 10:22 pm UTC

1. I'd love to know why the OP needed that. While Normal Distribution is very popular, most realistic statistical applications would use Student's T distribution. This is so common that Oracle contains a built in T Test function: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions157.htm#i1279931

2. Find a website with this table ( http://lilt.ilstu.edu/dasacke/eco138/ZTable.htm ), load the table into Oracle, and use it as much as you want.

3. Another interesting way to calculate the cumulative normal distribution in Oracle would be to use Oracle's dbms_random.normal to generate an approximation of the normal distribution and calculate the cumulative distribution on the result. Of course it is not very accurate:

select n,cume_dist ( ) over (order by n) cume_dist from
(select dbms_random.normal n from dual connect by level<=100);