SQL窗口函数实战：LAG与LEAD函数在时间序列数据分析中的应用

在SQL数据分析中，经常需要将当前行的数据与它的前一行或后一行进行比较，例如计算月度环比增长率或预测下一个周期的数值。传统方法如自连接或子查询往往使SQL语句变得复杂且低效。而LAG()和LEAD()这两个强大的窗口函数，正是为这类场景量身定做的解决方案，它们能像为数据装上“前后眼”一样，轻松实现跨行访问。

业务场景：计算月度销售额环比

假设我们有一张名为MonthlySales的月度销售汇总表，数据如下：

管理层希望看到的不仅仅是当月的销售额，更希望了解月度间的变化趋势：“本月相比上月是增长还是下跌？具体变化了多少百分比？”

使用LAG()函数，我们可以优雅地解决这个问题。

LAG() 函数：回溯历史数据

LAG()函数允许你访问结果集中当前行之前的指定物理偏移行的数据。其基本语法为：

LAG(column_name, offset, default_value) OVER (ORDER BY order_column)

• column_name：需要获取历史值的列。
• offset：向前回溯的行数（默认为1，即上一行）。
• default_value：当没有前一行（例如首行）时返回的默认值（默认为NULL）。
• OVER (ORDER BY ...)：定义窗口内行的逻辑顺序，这是函数正确工作的核心。

基础应用：获取上月销售额

SELECT
    YearMonth,
    TotalSales AS CurrentMonthSales,
    -- 关键：获取上一行的TotalSales
    LAG(TotalSales) OVER (ORDER BY YearMonth) AS PreviousMonthSales
FROM MonthlySales;

查询结果清晰展示了当前月与上月的对比：

进阶计算：直接得出环比增长额与增长率

SELECT
    YearMonth,
    TotalSales AS CurrentMonthSales,
    LAG(TotalSales) OVER (ORDER BY YearMonth) AS PreviousMonthSales,
    -- 计算环比增长额
    TotalSales - LAG(TotalSales) OVER (ORDER BY YearMonth) AS GrowthAmount,
    -- 计算环比增长率（使用ROUND保留两位小数，并处理除数为NULL的情况）
    ROUND(
        (TotalSales - LAG(TotalSales) OVER (ORDER BY YearMonth)) * 1.0 /
        LAG(TotalSales) OVER (ORDER BY YearMonth) * 100,
    2) AS GrowthRatePercent
FROM MonthlySales;

通过这个结果，可以明确地指出：三月份销售额环比小幅下滑4.17%，但四月份强势反弹，环比增长达13.04%。

LEAD() 函数：前瞻未来数据

与LAG()相对应，LEAD()函数用于访问当前行之后的指定行的数据，参数结构完全相同。它在预测和基于后续值进行计算的场景中非常有用。

业务场景：基于趋势设定下月目标
例如，我们希望参考下个月的实际销售额来设定一个更具挑战性的本月目标（假设目标为下月实际值的105%）。

SELECT
    YearMonth,
    TotalSales AS CurrentMonthSales,
    -- 关键：获取下一行的TotalSales（即下月实际值）
    LEAD(TotalSales) OVER (ORDER BY YearMonth) AS NextMonthActualSales,
    -- 基于下月实际值计算本月目标
    LEAD(TotalSales) OVER (ORDER BY YearMonth) * 1.05 AS NextMonthGoal
FROM MonthlySales;

核心要点与高级用法

1. 依赖正确的排序：LAG()和LEAD()的结果完全由OVER (ORDER BY ...)子句定义的顺序决定。错误的排序将导致逻辑错误。
2. 处理边界NULL值：对于第一行，LAG()返回NULL；对于最后一行，LEAD()返回NULL。在业务计算中，通常使用COALESCE或ISNULL函数提供默认值。

   COALESCE(LAG(TotalSales) OVER (...), TotalSales) -- 若没有上一行，则用当前行值代替

3. 结合PARTITION BY进行分组计算：这是进行复杂数据分析的关键。PARTITION BY可以将数据分成多个组，函数在每个组内独立计算。

   -- 计算每个产品（ProductId）自身的月度环比
   LAG(Sales) OVER (PARTITION BY ProductId ORDER BY YearMonth) AS PrevMonthSalesPerProduct

   这样，计算就会在每个ProductId分区内单独进行，互不干扰。

总结

LAG()和LEAD()函数极大地简化了基于行的跨行计算逻辑，将我们从繁琐的自连接中解放出来。

• LAG()：用于回顾历史，典型场景包括计算环比、判断连续状态（如连续登录）、求与前一记录的差值。
• LEAD()：用于展望未来，常用于预估、排期或计算事件间隔。

掌握这两个函数，能显著提升处理时间序列和需要上下文关联数据的SQL查询能力与效率。