4、特征分布对比的数学基础：均值、方差、分位数、KS统计量、PSI的SQL实现原理

在授信决策引擎的批次偏移监控中，特征分布对比是核心环节。本章将深入剖析五个关键统计量——均值、方差、分位数、KS统计量、PSI（群体稳定性指标）的数学定义，并给出它们在SQL中的高效实现原理。理解这些基础，是后续构建自动化偏移监控报表的基石。

4.1 均值与方差：一阶与二阶矩的SQL聚合

均值和方差分别描述特征的中心趋势和离散程度。在批次对比中，我们通常比较“训练集/开发集”与“线上实时批次”的这两个统计量。

• 均值（Mean）：\(\mu = \frac{1}{N}\sum_{i=1}^{N} x_i\)
• 方差（Variance）：\(\sigma^2 = \frac{1}{N}\sum_{i=1}^{N} (x_i - \mu)^2\)

SQL实现时，方差计算需注意数值稳定性。推荐使用VAR_POP（总体方差）或VAR_SAMP（样本方差）。对于大数据量，可先计算均值，再计算平方差之和。

-- 均值与方差SQL实现（单批次）
SELECT
    'batch_A' AS batch_id,
    COUNT(*) AS cnt,
    AVG(feature_amount) AS mean_amount,
    VAR_POP(feature_amount) AS var_pop_amount,
    STDDEV_POP(feature_amount) AS std_pop_amount
FROM scoring_result
WHERE batch_dt = '2025-03-01';

-- 两批次对比
SELECT
    batch_id,
    AVG(feature_amount) AS mean_amount,
    VAR_POP(feature_amount) AS var_pop_amount
FROM (
    SELECT 'train' AS batch_id, feature_amount FROM train_set
    UNION ALL
    SELECT 'online' AS batch_id, feature_amount FROM online_batch
) t
GROUP BY batch_id;

4.2 分位数：稳健的非参数对比

分位数（如P50、P90、P99）对异常值不敏感，能更稳健地反映分布形状变化。SQL中通过PERCENTILE_CONT或PERCENTILE_DISC实现。

-- 分位数SQL实现（两批次对比）
SELECT
    batch_id,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY feature_age) AS p25,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY feature_age) AS p50,
    PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY feature_age) AS p90
FROM (
    SELECT 'dev' AS batch_id, feature_age FROM dev_set
    UNION ALL
    SELECT 'prod' AS batch_id, feature_age FROM prod_set
) t
GROUP BY batch_id;

4.3 KS统计量：分布最大差异的量化

KS（Kolmogorov-Smirnov）统计量衡量两个经验分布函数之间的最大垂直距离。在风控中，常用于评估模型区分度，也可用于特征分布偏移检测。

数学定义：\(KS = \max_{x} |F_1(x) - F_2(x)|\)，其中\(F_1\)和\(F_2\)为两个批次的累积分布函数（CDF）。

SQL实现原理：将特征值排序后，逐点计算累积占比差，取绝对值最大值。

-- KS统计量SQL实现（两批次对比）
WITH sorted AS (
    SELECT
        feature_score,
        batch_id,
        COUNT(*) OVER (PARTITION BY batch_id) AS batch_cnt
    FROM combined_data
),
cdf AS (
    SELECT
        feature_score,
        batch_id,
        ROW_NUMBER() OVER (PARTITION BY batch_id ORDER BY feature_score) AS rn,
        batch_cnt
    FROM sorted
),
ecdf AS (
    SELECT
        feature_score,
        batch_id,
        rn * 1.0 / batch_cnt AS cum_prob
    FROM cdf
),
pivot_cdf AS (
    SELECT
        feature_score,
        MAX(CASE WHEN batch_id = 'batch_A' THEN cum_prob ELSE 0 END) AS cdf_A,
        MAX(CASE WHEN batch_id = 'batch_B' THEN cum_prob ELSE 0 END) AS cdf_B
    FROM ecdf
    GROUP BY feature_score
)
SELECT
    MAX(ABS(cdf_A - cdf_B)) AS ks_statistic
FROM pivot_cdf;

4.4 PSI（群体稳定性指标）：分箱后的分布偏移度量

PSI是风控领域最常用的特征稳定性指标。它通过将特征分箱后，比较两批次在各箱内的占比差异。

数学公式：\(PSI = \sum_{i=1}^{n} (A_i - B_i) \cdot \ln(\frac{A_i}{B_i})\)，其中\(A_i\)为实际批次（线上）在第i箱的占比，\(B_i\)为预期批次（开发集）在第i箱的占比。

SQL实现分为三步：分箱、计算占比、求和。

-- PSI SQL实现（基于等频分箱）
WITH bins AS (
    SELECT
        feature_value,
        NTILE(10) OVER (ORDER BY feature_value) AS bin_id  -- 等频分10箱
    FROM expected_set  -- 开发集
),
bin_edges AS (
    SELECT
        bin_id,
        MIN(feature_value) AS lower_bound,
        MAX(feature_value) AS upper_bound
    FROM bins
    GROUP BY bin_id
),
actual_counts AS (
    SELECT
        b.bin_id,
        COUNT(*) AS actual_cnt
    FROM actual_set a
    JOIN bin_edges b
        ON a.feature_value BETWEEN b.lower_bound AND b.upper_bound
    GROUP BY b.bin_id
),
expected_counts AS (
    SELECT
        bin_id,
        COUNT(*) AS expected_cnt
    FROM bins
    GROUP BY bin_id
),
psi_components AS (
    SELECT
        e.bin_id,
        a.actual_cnt * 1.0 / SUM(a.actual_cnt) OVER () AS actual_pct,
        e.expected_cnt * 1.0 / SUM(e.expected_cnt) OVER () AS expected_pct
    FROM expected_counts e
    LEFT JOIN actual_counts a ON e.bin_id = a.bin_id
)
SELECT
    SUM((actual_pct - expected_pct) * LN(actual_pct / expected_pct)) AS psi_value
FROM psi_components
WHERE actual_pct > 0 AND expected_pct > 0;  -- 避免除零

4.5 五类统计量的对比与选择

4.6 SQL实现性能优化要点

• 避免重复扫描：使用窗口函数一次性计算多个统计量，如AVG() OVER()配合STDDEV()。
• 分箱策略选择：PSI推荐等频分箱（每箱样本量均衡），避免空箱导致除零；等距分箱适用于均匀分布特征。
• 处理零占比：PSI计算时，若某箱实际占比为0，可做平滑处理（如加极小值ε=1e-6），或直接忽略该箱。
• 并行计算：对于多特征、多批次对比，使用GROUP BY feature_name并行计算，减少全表扫描次数。

-- 多特征PSI批量计算（优化版）
WITH bins AS (
    SELECT
        feature_name,
        feature_value,
        NTILE(10) OVER (PARTITION BY feature_name ORDER BY feature_value) AS bin_id
    FROM expected_set
),
bin_def AS (
    SELECT
        feature_name,
        bin_id,
        MIN(feature_value) AS lower,
        MAX(feature_value) AS upper
    FROM bins
    GROUP BY feature_name, bin_id
),
actual_stats AS (
    SELECT
        b.feature_name,
        b.bin_id,
        COUNT(*) AS actual_cnt
    FROM actual_set a
    JOIN bin_def b
        ON a.feature_name = b.feature_name
        AND a.feature_value BETWEEN b.lower AND b.upper
    GROUP BY b.feature_name, b.bin_id
),
expected_stats AS (
    SELECT
        feature_name,
        bin_id,
        COUNT(*) AS expected_cnt
    FROM bins
    GROUP BY feature_name, bin_id
)
SELECT
    feature_name,
    SUM((actual_pct - expected_pct) * LN(actual_pct / expected_pct)) AS psi
FROM (
    SELECT
        e.feature_name,
        e.bin_id,
        COALESCE(a.actual_cnt, 0) * 1.0 / SUM(COALESCE(a.actual_cnt, 0)) OVER (PARTITION BY e.feature_name) AS actual_pct,
        e.expected_cnt * 1.0 / SUM(e.expected_cnt) OVER (PARTITION BY e.feature_name) AS expected_pct
    FROM expected_stats e
    LEFT JOIN actual_stats a ON e.feature_name = a.feature_name AND e.bin_id = a.bin_id
) t
WHERE actual_pct > 0 AND expected_pct > 0
GROUP BY feature_name;