cum_dist won't do it
May 27, 2008 - 5pm Central time zone
Reviewer: Georg from Germany
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
Followup May 27, 2008 - 6pm Central time zone:
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
May 27, 2008 - 10pm Central time zone
Reviewer: Chen Shapira from Cupertino, CA USA
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);
|