4. 时间窗口偏差校验：时间切片与分布对比

时间窗口偏差是风控样本偏差中最隐蔽的一类——模型在训练期表现良好，上线后却因时间分布漂移而失效。本节聚焦两个核心操作：时间切片（将连续时间划分为等长窗口）与分布对比（比较不同窗口的样本特征与标签分布），通过 SQL 实现可复用的校验框架。

4.1 时间切片 SQL：DATE_TRUNC 与 WINDOW 函数

时间切片的核心是将时间戳字段按固定粒度截断，生成可聚合的窗口标识。PostgreSQL / Redshift 使用 DATE_TRUNC，BigQuery 使用 DATE_TRUNC 或 TIMESTAMP_TRUNC，Snowflake 使用 DATE_TRUNC，Hive / Spark SQL 使用 TRUNC 或 TO_DATE 配合格式化。

基础时间切片示例（按周聚合）：

-- 将每笔交易的时间戳截断到周起始日，并统计每周样本量
SELECT
    DATE_TRUNC('week', trans_time) AS week_start,
    COUNT(*)                        AS sample_cnt,
    COUNT(DISTINCT user_id)         AS user_cnt,
    AVG(amount)                     AS avg_amount
FROM trans_log
WHERE trans_time >= '2024-01-01'
  AND trans_time <  '2024-07-01'
GROUP BY DATE_TRUNC('week', trans_time)
ORDER BY week_start;

使用 WINDOW 函数计算窗口间变化率：

-- 计算每周样本量的环比变化率，快速定位异常窗口
WITH weekly_stats AS (
    SELECT
        DATE_TRUNC('week', trans_time) AS week_start,
        COUNT(*)                        AS sample_cnt
    FROM trans_log
    WHERE trans_time >= '2024-01-01'
    GROUP BY 1
)
SELECT
    week_start,
    sample_cnt,
    LAG(sample_cnt) OVER (ORDER BY week_start) AS prev_cnt,
    ROUND(
        (sample_cnt - LAG(sample_cnt) OVER (ORDER BY week_start)) 
        / NULLIF(LAG(sample_cnt) OVER (ORDER BY week_start), 0) * 100,
        2
    ) AS pct_change
FROM weekly_stats
ORDER BY week_start;

4.2 不同时间窗口的分布对比方法

分布对比的核心是回答三个问题：样本量是否稳定？特征分布是否漂移？标签（逾期/坏账）率是否突变？以下表格列出常用对比维度与 SQL 实现方式。

窗口间特征分布对比 SQL（以金额字段为例）：

-- 对比两个时间窗口的金额分布统计量
WITH window_a AS (
    SELECT
        AVG(amount)     AS avg_amt,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS med_amt,
        STDDEV(amount)  AS std_amt,
        COUNT(*)        AS cnt
    FROM trans_log
    WHERE trans_time >= '2024-01-01' AND trans_time < '2024-04-01'
),
window_b AS (
    SELECT
        AVG(amount)     AS avg_amt,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS med_amt,
        STDDEV(amount)  AS std_amt,
        COUNT(*)        AS cnt
    FROM trans_log
    WHERE trans_time >= '2024-04-01' AND trans_time < '2024-07-01'
)
SELECT
    '金额' AS feature_name,
    a.avg_amt AS w1_avg, b.avg_amt AS w2_avg,
    ROUND((b.avg_amt - a.avg_amt) / NULLIF(a.std_amt, 0), 4) AS std_shift,
    a.med_amt AS w1_med, b.med_amt AS w2_med,
    a.cnt AS w1_cnt, b.cnt AS w2_cnt
FROM window_a a, window_b b;

4.3 时间窗口偏差校验流程

下图展示从原始日志到偏差报告的完整校验链路，包含三个核心阶段：切片、对比、判定。

4.4 多窗口分布对比：基期 vs 当前期

实际风控校验中，通常选取一个稳定历史窗口作为基期，与当前窗口进行对比。基期选择原则：样本量充足、业务无重大变更、距离当前不超过 6 个月。

基期 vs 当前期对比 SQL（含 PSI 计算）：

-- 计算金额字段的 PSI（群体稳定性指标）
WITH base AS (
    SELECT
        CASE
            WHEN amount < 1000 THEN '0-1k'
            WHEN amount < 5000 THEN '1k-5k'
            WHEN amount < 20000 THEN '5k-20k'
            ELSE '20k+'
        END AS amt_bucket,
        COUNT(*) * 1.0 / SUM(COUNT(*)) OVER() AS base_pct
    FROM trans_log
    WHERE trans_time >= '2024-01-01' AND trans_time < '2024-04-01'
    GROUP BY 1
),
current AS (
    SELECT
        CASE
            WHEN amount < 1000 THEN '0-1k'
            WHEN amount < 5000 THEN '1k-5k'
            WHEN amount < 20000 THEN '5k-20k'
            ELSE '20k+'
        END AS amt_bucket,
        COUNT(*) * 1.0 / SUM(COUNT(*)) OVER() AS curr_pct
    FROM trans_log
    WHERE trans_time >= '2024-04-01' AND trans_time < '2024-07-01'
    GROUP BY 1
)
SELECT
    b.amt_bucket,
    ROUND(b.base_pct, 4) AS base_pct,
    ROUND(c.curr_pct, 4) AS curr_pct,
    ROUND((c.curr_pct - b.base_pct) * LN(c.curr_pct / b.base_pct), 6) AS psi_contrib
FROM base b
JOIN current c ON b.amt_bucket = c.amt_bucket
UNION ALL
SELECT
    'PSI = SUM(psi_contrib)' AS amt_bucket,
    NULL, NULL,
    ROUND(SUM((c.curr_pct - b.base_pct) * LN(c.curr_pct / b.base_pct)), 6)
FROM base b
JOIN current c ON b.amt_bucket = c.amt_bucket;

4.5 时间窗口偏差的常见模式与应对

根据实际项目经验，时间窗口偏差通常表现为以下三种模式，每种模式对应不同的根因与处理策略。

自动化校验脚本框架：

-- 每周自动执行，输出偏差预警记录
WITH weekly_metrics AS (
    SELECT
        DATE_TRUNC('week', trans_time) AS week_start,
        COUNT(*)                        AS sample_cnt,
        AVG(amount)                     AS avg_amount,
        SUM(CASE WHEN is_default = 1 THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS bad_rate
    FROM trans_log
    GROUP BY 1
)
SELECT
    week_start,
    sample_cnt,
    avg_amount,
    ROUND(bad_rate, 4) AS bad_rate,
    CASE
        WHEN sample_cnt < 0.7 * LAG(sample_cnt) OVER (ORDER BY week_start)
             THEN '样本量骤降预警'
        WHEN ABS(avg_amount - LAG(avg_amount) OVER (ORDER BY week_start)) 
             / NULLIF(LAG(avg_amount) OVER (ORDER BY week_start), 0) > 0.3
             THEN '均值漂移预警'
        WHEN ABS(bad_rate - LAG(bad_rate) OVER (ORDER BY week_start)) > 0.02
             THEN '坏账率突变预警'
        ELSE '正常'
    END AS alert_flag
FROM weekly_metrics
ORDER BY week_start;