3. 窗口函数基础：ROW_NUMBER、RANK、DENSE_RANK、NTILE 与解释性场景

窗口函数（Window Functions）是 SQL 中用于在保持行级粒度的同时，对数据子集进行排序、分组聚合或偏移计算的强大工具。在模型解释性分析中，窗口函数常用于构建特征排序、分位数分段、异常检测标记以及对比基线生成。本章聚焦于四个核心排序窗口函数：ROW_NUMBER、RANK、DENSE_RANK 和 NTILE，并展示它们在解释性场景中的典型应用。

3.1 窗口函数核心语法

所有窗口函数均通过 OVER 子句定义窗口范围，基本语法结构如下：

<窗口函数>() OVER (
    [PARTITION BY 列1, 列2, ...]
    [ORDER BY 列3 [ASC|DESC], 列4 [ASC|DESC], ...]
    [ROWS|RANGE 帧子句]
)

• PARTITION BY：将数据划分为独立的分区，窗口函数在每个分区内独立计算。
• ORDER BY：定义分区内的排序顺序，直接影响排序函数的排名结果。
• 帧子句：进一步限定窗口内的行范围（本章排序函数通常省略帧子句）。

3.2 四大排序函数对比

3.3 函数行为演示

假设有一张模型特征重要性评分表 feature_importance，包含字段 model_id、feature_name、importance_score。以下 SQL 在同一查询中展示四种函数的差异：

SELECT
    model_id,
    feature_name,
    importance_score,
    ROW_NUMBER()    OVER (PARTITION BY model_id ORDER BY importance_score DESC) AS row_num,
    RANK()          OVER (PARTITION BY model_id ORDER BY importance_score DESC) AS rank,
    DENSE_RANK()    OVER (PARTITION BY model_id ORDER BY importance_score DESC) AS dense_rank,
    NTILE(4)        OVER (PARTITION BY model_id ORDER BY importance_score DESC) AS quartile
FROM feature_importance
WHERE model_id = 'model_v3'
ORDER BY importance_score DESC;

假设 model_v3 的部分特征重要性数据如下：

3.4 解释性场景应用

3.4.1 特征重要性 Top-N 筛选

使用 ROW_NUMBER 为每个模型选取最重要的前 K 个特征，用于模型简化或特征子集分析：

WITH ranked_features AS (
    SELECT
        model_id,
        feature_name,
        importance_score,
        ROW_NUMBER() OVER (PARTITION BY model_id ORDER BY importance_score DESC) AS rn
    FROM feature_importance
)
SELECT model_id, feature_name, importance_score
FROM ranked_features
WHERE rn <= 5;

3.4.2 特征重要性分档（DENSE_RANK）

使用 DENSE_RANK 将特征按重要性密度分为若干等级，便于观察重要性分布结构：

SELECT
    model_id,
    feature_name,
    importance_score,
    DENSE_RANK() OVER (PARTITION BY model_id ORDER BY importance_score DESC) AS importance_tier
FROM feature_importance
WHERE model_id = 'model_v3';

3.4.3 等频分箱与分位数分段（NTILE）

使用 NTILE 将预测值或特征值均匀分为 n 段，用于构建分位数特征或评估模型在不同分段的性能：

SELECT
    prediction_id,
    predicted_probability,
    actual_label,
    NTILE(10) OVER (ORDER BY predicted_probability) AS decile
FROM model_predictions
WHERE model_id = 'model_v3';

3.5 窗口函数在解释性分析中的典型流程

3.6 常见陷阱与最佳实践

• 排序方向一致性： 确保 ORDER BY 的方向与分析目标一致。特征重要性通常降序排列，异常分数通常升序排列。
• 分区键选择： 在多模型对比场景中，务必使用 PARTITION BY model_id 避免跨模型排名混淆。
• NTILE 组数选择： 组数不宜过多（通常 5~20 组），否则部分组样本过少导致统计不稳定。
• 并列数据处理： 若业务要求严格唯一排名，使用 ROW_NUMBER 并添加二级排序字段（如特征名称）打破平局。
• 性能注意： 窗口函数在大型数据集上可能产生较高的内存消耗，建议在分区键和排序列上建立索引。

3.7 综合案例：构建模型预测十分位评估表

以下 SQL 使用 NTILE 和 ROW_NUMBER 构建模型预测的十分位评估表，用于计算每个分段的响应率、捕获率和 Lift 值：

WITH decile_data AS (
    SELECT
        prediction_id,
        predicted_probability,
        actual_label,
        NTILE(10) OVER (ORDER BY predicted_probability DESC) AS decile,
        ROW_NUMBER() OVER (ORDER BY predicted_probability DESC) AS row_id
    FROM model_predictions
    WHERE model_id = 'model_v3'
)
SELECT
    decile,
    COUNT(*)                                         AS total_records,
    SUM(actual_label)                                AS positive_count,
    ROUND(AVG(actual_label), 4)                      AS response_rate,
    ROUND(SUM(actual_label) * 1.0 /
          (SELECT SUM(actual_label) FROM decile_data), 4) AS capture_rate,
    ROUND(AVG(actual_label) /
          (SELECT AVG(actual_label) FROM decile_data), 4) AS lift
FROM decile_data
GROUP BY decile
ORDER BY decile;