📚 课程目录第4章 SQL窗口函数基础

4. SQL 窗口函数基础：ROW_NUMBER、RANK、DENSE_RANK、LAG、LEAD 语法与示例

在批次偏移诊断中，我们经常需要比较相邻批次（如批次 t 与批次 t-1）的特征分布差异，或对批次按时间顺序进行编号、排名。SQL 窗口函数（Window Functions）正是完成这类任务的利器。本节将系统讲解五个最常用的窗口函数：ROW_NUMBER、RANK、DENSE_RANK、LAG 和 LEAD。

4.1 窗口函数核心语法

所有窗口函数都遵循以下通用结构：

<窗口函数> OVER (
    [PARTITION BY <分组列>]
    [ORDER BY <排序列> [ASC|DESC]]
    [<窗口帧子句>]
)

PARTITION BY：可选，将数据划分为多个独立窗口（类似 GROUP BY 但不合并行）。
ORDER BY：定义窗口内的排序顺序，影响排名函数的结果和 LAG/LEAD 的偏移方向。
窗口帧子句：用于指定计算范围（如 ROWS BETWEEN …），本节基础函数通常不需要显式指定。

💡 关键区别： 窗口函数不会像 GROUP BY 那样减少行数；每一行都会保留，并额外生成一个窗口计算结果列。

4.2 排名函数：ROW_NUMBER、RANK、DENSE_RANK

这三个函数都用于为窗口内的行分配一个排名序号，但处理并列值（ties）的方式不同。

函数	并列值处理	序号是否连续	典型应用场景
`ROW_NUMBER()`	即使值相同，也强制分配唯一序号（随机或按物理顺序）	是	去重、分页、为每行生成唯一行号
`RANK()`	相同值获得相同序号，但后续序号会跳过（如 1,1,3）	否（有跳跃）	竞赛排名、需要体现并列后空缺的场景
`DENSE_RANK()`	相同值获得相同序号，后续序号不跳过（如 1,1,2）	是	密集排名、需要连续序号且保留并列

示例数据与查询：

-- 假设表 batch_stats 包含以下数据
-- batch_id | feature_name | ks_statistic
-- --------+-------------+-------------
-- B001     | age         | 0.12
-- B002     | age         | 0.12
-- B003     | age         | 0.08
-- B004     | age         | 0.15

SELECT
    batch_id,
    ks_statistic,
    ROW_NUMBER()   OVER (ORDER BY ks_statistic DESC) AS row_num,
    RANK()         OVER (ORDER BY ks_statistic DESC) AS rank,
    DENSE_RANK()   OVER (ORDER BY ks_statistic DESC) AS dense_rank
FROM batch_stats
WHERE feature_name = 'age';

结果对比：

batch_id	ks_statistic	row_num	rank	dense_rank
B004	0.15	1	1	1
B001	0.12	2	2	2
B002	0.12	3	2	2
B003	0.08	4	4	3

  📌 批次偏移诊断中的应用： 使用 ROW_NUMBER() 按时间顺序为每个特征的不同批次生成唯一序号，便于后续按序号进行偏移量计算。

4.3 偏移函数：LAG 与 LEAD

LAG 和 LEAD 用于访问同一窗口中前一行或后一行的数据，是实现“批次间对比”的核心工具。

函数	方向	语法	默认值
`LAG(column, offset, default)`	向前（上一行）	`LAG(ks_statistic, 1, 0) OVER (ORDER BY batch_time)`	当偏移超出窗口范围时返回 default（默认 NULL）
`LEAD(column, offset, default)`	向后（下一行）	`LEAD(ks_statistic, 1, 0) OVER (ORDER BY batch_time)`	同上

示例：计算相邻批次 KS 统计量的变化量

SELECT
    batch_id,
    batch_time,
    ks_statistic,
    LAG(ks_statistic, 1, 0) OVER (ORDER BY batch_time) AS prev_ks,
    ks_statistic - LAG(ks_statistic, 1, 0) OVER (ORDER BY batch_time) AS ks_change
FROM batch_stats
WHERE feature_name = 'age'
ORDER BY batch_time;

结果示例：

batch_id	batch_time	ks_statistic	prev_ks	ks_change
B001	2024-01-01	0.12	0.00	0.12
B002	2024-01-08	0.15	0.12	0.03
B003	2024-01-15	0.08	0.15	-0.07

⚠️ 常见陷阱： 使用 LAG/LEAD 时，如果 ORDER BY 列存在重复值，结果可能不确定。建议使用唯一时间戳或组合键（如 batch_id + batch_time）来保证排序稳定性。

4.4 综合应用：批次偏移诊断中的窗口函数组合

在实际诊断中，我们通常将排名函数与偏移函数结合使用：

WITH ranked_batches AS (
    SELECT
        feature_name,
        batch_id,
        batch_time,
        ks_statistic,
        ROW_NUMBER() OVER (
            PARTITION BY feature_name
            ORDER BY batch_time
        ) AS batch_seq
    FROM batch_stats
)
SELECT
    feature_name,
    batch_seq,
    batch_id,
    ks_statistic,
    LAG(ks_statistic, 1, 0) OVER (
        PARTITION BY feature_name
        ORDER BY batch_seq
    ) AS prev_ks,
    ks_statistic - LAG(ks_statistic, 1, 0) OVER (
        PARTITION BY feature_name
        ORDER BY batch_seq
    ) AS ks_delta
FROM ranked_batches
ORDER BY feature_name, batch_seq;

  🔑 核心要点：
  ROW_NUMBER 用于生成稳定的批次序号，避免因时间戳重复导致的排序歧义。
LAG 用于获取上一批次的 KS 统计量，计算偏移量 ks_delta。
PARTITION BY feature_name 确保每个特征独立计算，不会跨特征混淆。

4.5 本章小结

函数	核心用途	在批次偏移诊断中的角色
`ROW_NUMBER`	生成唯一行号	为每个特征-批次组合分配稳定序号
`RANK` / `DENSE_RANK`	处理并列排名	识别 KS 统计量相同的批次组
`LAG`	访问前一行数据	获取上一批次特征值，计算偏移量
`LEAD`	访问后一行数据	获取下一批次特征值，用于前瞻性分析