如何用SQL处理分组内同比差异_LAG函数实战应用

如何用SQL处理分组内同比差异：LAG函数实战应用

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

在数据分析中，计算分组内的同比是个高频需求。乍一看，用窗口函数 LAG() 似乎是个完美的解决方案。但实际操作过的人都知道，这里面的坑可不少。一个不小心，得出的结果看似合理，实则完全偏离了业务逻辑。问题的核心在于，LAG() 是按物理顺序取上一行，而同比要求的是逻辑上“同一分组内、时间严格对齐的上一年同期”。

为什么直接用 LAG() 算同比经常出错

直接使用 LAG() 计算同比，最常见的错误根源可以归结为一点：数据准备不足。

窗口函数 LAG(value, 1) 默认的行为是，在指定的分区（PARTITION BY）内，按照排序（ORDER BY）后的物理顺序，简单地取前一行。然而，同比计算需要的是“上一年同月”的数据，这本质上是一个基于时间逻辑的偏移，而非简单的物理相邻。

这就导致了几个典型的陷阱：

• 数据缺失：如果某个地区2023年2月没有销售记录（数据行缺失），那么当计算2024年2月的同比时，LAG() 会试图去取2023年1月的数据（物理上的前一行），这显然不是正确的“去年同期”值。
• 时间格式非标准：如果时间字段 year_month 存储为 CHAR(6) 类型的 '202301'，直接用它排序会按照字符串规则进行。结果就是，'202310' 会排在 '202301' 后面，导致排序错乱，LAG() 自然也就取错了行。
• 分组逻辑不完整：如果分区键设置不当，不同分组的数据可能会被混在一起排序。

所以，关键点在于：必须先确保每个分组内的时间维度是可排序且逻辑连续的，然后再应用 LAG()。 如果数据本身不满足这个条件，那么首要任务不是写窗口函数，而是先补全或规整时间维度。

用 LAG() 算月度同比的最小可行写法

假设我们有一张销售表 sales，包含字段：region（地区）、ym（CHAR(6) 格式，如 '202301'）、amount（销售额）。目标是计算每个地区每个月的销售额同比差额和增长率。

正确的做法需要两步：首先，将字符串格式的时间转换为标准的日期类型，确保排序正确；其次，在窗口函数中使用偏移量12（代表12个月，即一年），而不是1。

SELECT
  region,
  ym,
  amount,
  -- 计算同比差额：当前值 - 12个月前的值
  amount - LAG(amount, 12) OVER (
    PARTITION BY region
    ORDER BY TO_DATE(ym, 'YYYYMM')
  ) AS yoy_diff,
  -- 计算同比百分比：(当前值 - 去年同期值) / 去年同期值 * 100%
  ROUND(
    100.0 * (
      amount - LAG(amount, 12) OVER (
        PARTITION BY region
        ORDER BY TO_DATE(ym, 'YYYYMM')
      )
    ) / NULLIF(
      LAG(amount, 12) OVER (
        PARTITION BY region
        ORDER BY TO_DATE(ym, 'YYYYMM')
      ),
      0
    ),
    2
  ) AS yoy_pct
FROM sales;

这段代码有几个要点：

• LAG(amount, 12)：偏移量12是关键，它直接对应“一年前”。
• TO_DATE(ym, 'YYYYMM')：将字符串转换为日期，这是保证跨年、跨月正确排序的基础。不同数据库函数略有不同（如MySQL用STR_TO_DATE，BigQuery用PARSE_DATE）。
• NULLIF(..., 0)：一个简洁的技巧，用于防止去年同期值为0时出现除零错误。

这个方法前提是数据按月连续且无缺失。如果有月份缺失，LAG() 会跳过缺失的月份继续向前查找，导致结果错误或为NULL。

当数据不连续时：必须补时间维度再 LAG()

现实情况往往更复杂。如果某个地区在某个月份没有任何销售记录（不仅是金额为0，而是整行数据缺失），那么上述“最小可行写法”就会出错。因为 LAG(amount, 12) 会拉到实际存在的上一条记录，而不是逻辑上的“上一年同月”。

这时，我们必须先构造一个完整的“时间 × 分组”网格，确保每个地区在每个需要计算的月份都有一条记录（销售额用0或NULL填充），然后再应用窗口函数。

以PostgreSQL为例，可以使用 GENERATE_SERIES() 来补全时间维度：

WITH full_grid AS (
  -- 生成所有地区与所有月份的笛卡尔积
  SELECT DISTINCT region, ym
  FROM sales
  CROSS JOIN GENERATE_SERIES(
    '2022-01-01'::DATE,
    '2023-12-01'::DATE,
    '1 month'
  ) gs(d)
  CROSS JOIN LATERAL (SELECT TO_CHAR(gs.d, 'YYYYMM') AS ym) t
),
filled AS (
  -- 将原始数据填充到完整网格中，缺失值补0
  SELECT g.region, g.ym, COALESCE(s.amount, 0) AS amount
  FROM full_grid g
  LEFT JOIN sales s ON g.region = s.region AND g.ym = s.ym
)
SELECT
  region,
  ym,
  amount,
  -- 在补全后的数据上计算同比
  amount - LAG(amount, 12) OVER (
    PARTITION BY region
    ORDER BY TO_DATE(ym, 'YYYYMM')
  ) AS yoy_diff
FROM filled;

这个方法的精髓在于：

• 先补全，后计算：确保每个分组在时间轴上是连续的，这样 LAG(..., 12) 的偏移才是严格意义上的“12行之前”，对应“一年前的同一个月”。
• 注意性能：CROSS JOIN 生成笛卡尔积可能导致中间结果集急剧膨胀。对于大数据量，更优的做法是先获取每个分组的时间范围，再分别生成序列。

LAG() 和自连接做同比的性能与可读性权衡

除了窗口函数，另一种常见的思路是使用自连接（LEFT JOIN）来实现同比，即通过字符串操作计算出“去年同期”的月份，然后进行关联。例如：

ON a.region = b.region
AND a.ym = CONCAT((CAST(SUBSTR(b.ym,1,4) AS INT) - 1), SUBSTR(b.ym,5,2))

这种方法看似直观，但存在几个明显劣势：

• 易出错且不优雅：手动拼接年份和月份容易因数据类型转换或边界情况（如年份从1000变为999）而出错，代码可读性也较差。
• 性能瓶颈：自连接需要对每一行数据都执行一次关联查找，无法复用排序。当数据量增大时，其性能会显著低于只需一次排序和扫描的窗口函数。
• 索引利用困难：连接条件涉及字符串函数计算，通常难以有效利用索引。

相比之下，LAG(amount, 12) 的语义非常清晰：“取当前行之前第12行的值”。只要前提条件（时间连续、格式正确）得到满足，它的表达效率和计算效率都更高。

最后，还有一个极其容易忽略的细节：偏移量必须与业务周期严格对应。月度同比是12，周同比是52，季度同比是4。写错这个数字，比用错函数本身更难被发现，务必仔细核对。