Sunday, October 21, 2012

Oracle Normal Distribution Function

Oracle function dbms_random.normal generates a standard normal distribution number with zero mean and one standard deviation.
SQL> select dbms_random.normal from dual;
NORMAL
----------
.781195382
The following query generates 6,000 random numbers using dbms_random.normal and then calculate their mean and standard deviation.We can see that their mean is close to zero and standard deviation close to one.
SQL> select avg(v), stddev(v), count(1) from (select dbms_random.normal v from V_6K_OBS);
AVG(V) STDDEV(V) COUNT(1)
---------- ---------- ----------
-.00110804 1.00954138 6000