如何用SQL处理分组内同比差异_LAG函数实战应用-SQL-PHP中文网

LAG()算同比易错因默认按物理顺序取上一行，而同比需同分组内时间严格对齐；数据缺失、时间格式非标准或分组错误均致结果不可信，须先确保时间可排序且连续，或补全时间维度后再用LAG()。

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

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

因为 LAG() 默认按 ORDER BY 的物理顺序取上一行，而同比要求“同一分组内、时间字段严格对齐的上一年同月/同期”，一旦数据缺失（比如 2023-02 没记录）、或时间字段非标准格式（如存为 CHAR）、或分组逻辑没写对，LAG() 就会拉错行——结果看似有数，实则完全不可信。

关键点在于：必须先确保每个分组内时间可排序且连续（或至少能映射到标准周期），再用 LAG()；否则得先补全时间维度，或改用窗口 + 条件偏移。

LAG(value, 1) OVER (PARTITION BY region ORDER BY year_month) —— 表面正确，但若 year_month 是 '202301' 字符串，排序会变成 '202301', '202302', ..., '202310'（因为字符串比较），导致 '202310' 后面接 '202301'（错乱）
正确做法是把 year_month 转成日期类型：TO_DATE(year_month, 'YYYYMM') 或 MAKE_DATE(SUBSTR(year_month,1,4)::INT, SUBSTR(year_month,5,2)::INT, 1)
如果某地区 2022-03 缺失，LAG() 会跳到 2022-02，而非真正同比所需的 2022-03 —— 这时必须先生成完整时间 × 分组笛卡尔积，再 LEFT JOIN 原表

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

假设表 sales 有字段：region（地区）、ym（CHAR(6) 格式如 '202301'）、amount（销售额）。目标：每行算「比去年同期增长多少」。

核心是两步：① 把 ym 转成可排序的日期基准；② 在分组内按该基准排序，用 LAG() 拉前 12 行（不是前 1 行）。

SELECT
  region,
  ym,
  amount,
  amount - LAG(amount, 12) OVER (
    PARTITION BY region 
    ORDER BY TO_DATE(ym, 'YYYYMM')
  ) AS yoy_diff,
  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 行，前提是数据按月连续且无缺失；若缺月，结果自动为 NULL
NULLIF(..., 0) 防止除零错误，比 CASE WHEN ... = 0 THEN NULL ELSE ... END 更简洁
PostgreSQL/Redshift 支持 TO_DATE()；MySQL 用 STR_TO_DATE(ym, '%Y%m')；BigQuery 用 PARSE_DATE('%Y%m', ym)

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

如果原始表里某些月份根本没记录（比如华东区 2022-04 销售额为 0，但没这条数据），直接 LAG(amount, 12) 会拉到 2022-03，同比就失效了。这时不能硬套 LAG()，得先构造完整的时间 × 分组集合。

以 PostgreSQL 为例，用 GENERATE_SERIES() 补全 2022-01 到 2023-12 所有年月：

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 (
  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;

补全后，所有分组在所有目标月份都有记录（空值转为 0），LAG() 才真正对应“上一年同月”
补全范围必须覆盖全部分组的最大/最小时间跨度，否则边缘月份仍会拉错
大数据量下 GENERATE_SERIES + CROSS JOIN 可能膨胀严重，需评估性能；替代方案是先取各分组的 min/max 时间，再分别生成区间

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

有人倾向用 LEFT JOIN 自连接实现同比（比如 ON a.region = b.region AND a.ym = LPAD((SUBSTR(b.ym,1,4)::INT - 1)::TEXT,4,'0') || SUBSTR(b.ym,5)），觉得更直观。但实际中问题不少：

字符串拼接年份易出错，比如 '202301' → 减 1 年应得 '202201'，但若写成 SUBSTR(ym,1,4)-1 || SUBSTR(ym,5)，在 MySQL 中可能隐式转成数字再拼，导致 2023-1||'01' → 202201（侥幸对），但在其他引擎行为不一
自连接无法利用窗口函数的排序复用，每行都要扫描匹配，数据量大时慢一个数量级
LAG() 一次排序、一次扫描即可完成所有偏移计算；自连接需对每行重新查找，且 JOIN 条件难加索引
可读性上，LAG(amount, 12) 比一堆 SUBSTR + CONCAT 更直白，只要前提（时间连续、类型正确）被满足