Implement normal distribution and inverse normal distribution in Oracle

Question

I want to have this excel formula in Oracle:

=((0.05*NORM.S.DIST((1/(SQRT(1-0.3))*NORM.S.INV(0.3)+(SQRT(0.04/(1-0.04))*NORM.S.INV(0.999))),TRUE())-0.09*0.3))*12.5

I have found dbms_random.normal in Oracle which fits for normal distribution

 select dbms_random.normal  from dual;  

But it dose not have inputs and also does not have a reverse function.

Can anybody help me to implement this formula in Oracle?

Answer 1

NORM.S.INV function can be calculated approximately using a method like the Beasley-Springer-Moro algorithm, which is an approximation to the inverse of the cumulative distribution function (CDF) for the standard normal distribution.

You can implement this approximation in SQL or PL/SQL.A sample code as an example.

 CREATE OR REPLACE FUNCTION NORM_S_INV(p_percentile IN NUMBER) RETURN NUMBER IS
        t NUMBER;
        a1 CONSTANT NUMBER := -39.69683028665376;
        a2 CONSTANT NUMBER := 220.9460984245205;
        a3 CONSTANT NUMBER := -275.9281420734326;
        a4 CONSTANT NUMBER := 138.3577518672690;
        a5 CONSTANT NUMBER := -30.66479806614716;
        a6 CONSTANT NUMBER := 2.00003745918373;
        b1 CONSTANT NUMBER := -15.68278812175347;
        b2 CONSTANT NUMBER := 82.77599823529718;
        b3 CONSTANT NUMBER := -225.0292580178411;
        b4 CONSTANT NUMBER := 274.3133571831150;
        b5 CONSTANT NUMBER := -115.8832534587973;
        b6 CONSTANT NUMBER := 15.1012580004950;
        p LOW NUMBER := 0.02425;
    BEGIN
        IF (p_percentile < 0.5) THEN
            t := SQRT(LOG(1.0 / (p_percentile * p_percentile)));
            RETURN ((((((a1 * t + a2) * t + a3) * t + a4) * t + a5) * t + a6) / 
                    (((((b1 * t + b2) * t + b3) * t + b4) * t + b5) * t + b6));
        ELSE
            t := SQRT(LOG(1.0 / ((1 - p_percentile) * (1 - p_percentile))));
            RETURN -(((((a1 * t + a2) * t + a3) * t + a4) * t + a5) * t + a6) /
                    (((((b1 * t + b2) * t + b3) * t + b4) * t + b5) * t + b6);
        END IF;
    END;
    /

Above function can then be invoked in SQL to replicate excel logic:

WITH params AS (
    SELECT 0.3 AS p1, 0.04 AS p2, 0.999 AS p3
)
SELECT 
    (0.05 * NORM_S_INV(p1) / SQRT(1 - 0.3) + 
     SQRT(p2 / (1 - p2)) * NORM_S_INV(p3) - 
     0.09 * 0.3) * 12.5
FROM params;