如何在Oracle中实现正态分布、逆正态分布及转换指定Excel公式？

1. 关键函数的自定义实现 #

1.1 标准正态分布逆函数（对应Excel的NORM.S.INV(p)） #

Oracle没有内置标准正态分位数函数，采用Beasley-Springer-Moro近似算法实现自定义函数：

CREATE OR REPLACE FUNCTION norm_s_inv(p IN NUMBER) RETURN NUMBER IS
    a0 CONSTANT NUMBER := 2.50662823884;
    a1 CONSTANT NUMBER := -18.61500062529;
    a2 CONSTANT NUMBER := 41.39119773534;
    a3 CONSTANT NUMBER := -25.44106049637;
    b0 CONSTANT NUMBER := -8.47351093090;
    b1 CONSTANT NUMBER := 23.08336743743;
    b2 CONSTANT NUMBER := -21.06224101826;
    b3 CONSTANT NUMBER := 3.13082909833;
    c0 CONSTANT NUMBER := 0.3374754822726147;
    c1 CONSTANT NUMBER := 0.9761690190917186;
    c2 CONSTANT NUMBER := 0.1607979714918209;
    c3 CONSTANT NUMBER := 0.0276438810333863;
    c4 CONSTANT NUMBER := 0.0038405729373609;
    c5 CONSTANT NUMBER := 0.0003951896511919;
    c6 CONSTANT NUMBER := 0.0000321767881768;
    c7 CONSTANT NUMBER := 0.0000002888167364;
    c8 CONSTANT NUMBER := 0.0000003960315187;
    x NUMBER;
    y NUMBER;
    r NUMBER;
BEGIN
    IF p <= 0 OR p >= 1 THEN
        RAISE_APPLICATION_ERROR(-20001, 'p必须介于0和1之间');
    END IF;
    y := p - 0.5;
    IF ABS(y) < 0.42 THEN
        r := y * y;
        x := y * (((a3 * r + a2) * r + a1) * r + a0) / ((((b3 * r + b2) * r + b1) * r + b0) * r + 1);
    ELSE
        r := p;
        IF y > 0 THEN
            r := 1 - p;
        END IF;
        r := SQRT(-LN(r));
        x := r - (((((((c8 * r + c7) * r + c6) * r + c5) * r + c4) * r + c3) * r + c2) * r + c1) * r + c0;
        IF y < 0 THEN
            x := -x;
        END IF;
    END IF;
    -- 迭代一次提升精度
    x := x - (CDF_NORMAL(x) - p) / PDF_NORMAL(x);
    RETURN x;
END;
/

1.2 标准正态累积分布函数（对应Excel的NORM.S.DIST(z, TRUE)） #

利用Oracle内置ERF函数实现，精度更高：

CREATE OR REPLACE FUNCTION cdf_normal(z IN NUMBER) RETURN NUMBER IS
BEGIN
    RETURN 0.5 * (1 + ERF(z / SQRT(2)));
END;
/

1.3 标准正态概率密度函数（用于逆函数迭代修正） #

CREATE OR REPLACE FUNCTION pdf_normal(z IN NUMBER) RETURN NUMBER IS
BEGIN
    RETURN EXP(-z*z/2) / SQRT(2 * 3.141592653589793);
END;
/

2. 完整Excel公式的Oracle实现 #

直接调用自定义函数，还原原Excel公式逻辑：

SELECT 
    ((0.05 * CDF_NORMAL(
        (1 / SQRT(1 - 0.3)) * NORM_S_INV(0.3) + 
        (SQRT(0.04 / (1 - 0.04)) * NORM_S_INV(0.999))
    ) - 0.09 * 0.3)) * 12.5 AS result
FROM DUAL;

说明 #

• CDF_NORMAL(z)与Excel的NORM.S.DIST(z, TRUE)功能完全一致
• NORM_S_INV(p)与Excel的NORM.S.INV(p)功能完全一致
• 自定义逆函数通过近似算法加迭代修正，精度可满足绝大多数业务场景需求

内容的提问来源于stack exchange，提问作者navid sedigh