4、LAG函数入门：LAG()语法、作用、与自关联查询的性能对比

在批次偏移诊断中，最核心的需求是：获取当前行之前某一行（如上一批次）的某个字段值。传统 SQL 开发者习惯使用自关联（Self-Join）来实现，但窗口函数 LAG() 提供了更简洁、高效的解决方案。本节将系统讲解 LAG() 的语法、作用，并通过实际案例与自关联进行性能对比。

4.1 LAG() 函数语法与作用

LAG() 是一个窗口函数，用于访问同一结果集中当前行之前的某一行数据。其基本语法如下：

LAG(expression [, offset [, default]]) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
)

4.2 LAG() 与自关联查询的对比

假设我们有一张 batch_scores 表，记录了每个批次（batch_id）的分数（score），需要计算每个批次相对于上一个批次的分数变化。下面分别用 LAG() 和自关联实现。

4.2.1 使用 LAG() 实现

SELECT
    batch_id,
    score,
    LAG(score, 1, 0) OVER (ORDER BY batch_id) AS prev_score,
    score - LAG(score, 1, 0) OVER (ORDER BY batch_id) AS score_change
FROM batch_scores
ORDER BY batch_id;

4.2.2 使用自关联实现

SELECT
    curr.batch_id,
    curr.score,
    COALESCE(prev.score, 0) AS prev_score,
    curr.score - COALESCE(prev.score, 0) AS score_change
FROM batch_scores curr
LEFT JOIN batch_scores prev
    ON curr.batch_id = prev.batch_id + 1
ORDER BY curr.batch_id;

4.3 性能对比分析

两种方式在逻辑上等价，但性能差异显著。以下从执行计划、IO 开销和扩展性三个维度进行对比：

4.4 LAG() 执行流程示意图

下图展示了 LAG(score, 1, 0) OVER (ORDER BY batch_id) 在窗口内的数据流动过程：

4.5 自关联查询执行流程示意图

作为对比，下图展示了自关联查询的内部执行过程，可以看到需要两次表扫描和一次 JOIN 操作：

4.6 实际性能测试数据

在 500 万行测试数据上，两种方式的性能对比如下（测试环境：PostgreSQL 15, 16GB RAM, SSD）：