如何计算SQL中的同比和环比增长_使用LAG偏移量计算-SQL-PHP中文网

直接用lag()算同比/环比易错，因其仅按物理行序取值，不保障时间对齐；必须先按年月聚合、补全时间维度、严格排序并分区，再配合nullif防除零。

如何计算sql中的同比和环比增长_使用lag偏移量计算

为什么直接用 `LAG()` 算同比/环比容易出错

因为 LAG() 只负责取上一行数据，不保证“上一行就是去年同期”或“上个月”。比如按日期排序后，LAG(sales, 1) 拿到的是紧邻的前一条记录——但如果数据缺失（某月没销售）、有重复日期、或没严格按时间粒度分组（如混了日和月），结果就完全不对。

真正安全的做法是：先确保数据已按业务周期（年+月、年+季度）聚合并去重，再用 LAG() 偏移固定步长，且必须配合 ORDER BY 和 PARTITION BY 控制上下文。

同比需要跨年对齐：2024-05 的值对比 2023-05，不是简单往前跳12行（闰年、节假日、数据补录都可能打乱行序）
环比更依赖连续性：如果 2024-04 数据缺失，LAG(sales, 1) 会跳到 2024-03，导致环比值错误
MySQL 8.0+、PostgreSQL、SQL Server 2012+ 支持 LAG()，但 SQLite 和旧版 MySQL 不支持，需提前确认

按年月分组后用 `LAG()` 算同比（Y/Y）

核心是把日期转成统一的年月键（如 202405），再按该键排序，这样 LAG(val, 12) 才真对应去年同月。不能直接对 DATE 类型列 ORDER BY date 后偏移12行——月份天数不同，12行不等于12个月。

SELECT
  ym,
  sales,
  LAG(sales, 12) OVER (ORDER BY ym) AS last_year_sales,
  ROUND((sales - LAG(sales, 12) OVER (ORDER BY ym)) / NULLIF(LAG(sales, 12) OVER (ORDER BY ym), 0), 4) AS yy_growth
FROM (
  SELECT
    YEAR(order_date) * 100 + MONTH(order_date) AS ym,
    SUM(amount) AS sales
  FROM orders
  WHERE order_date >= '2022-01-01'
  GROUP BY YEAR(order_date), MONTH(order_date)
) t;

ym 是关键中间列，强制按年月自然序排列，避免日期函数在 ORDER BY 中引发隐式转换问题
NULLIF(..., 0) 防止除零错误，比 CASE WHEN 更简洁
如果数据只到 2024-05，那么 2024-05 行的 last_year_sales 是 2023-05 值，但 2023-05 行的 last_year_sales 是 NULL（前面没12个点）

用 `LAG()` 算环比（M/M）时怎么处理断点

环比失败最常见的原因是时间断层。比如你有 2024-01、2024-03 的数据，但缺 2024-02 ——这时 LAG(sales, 1) 会让 2024-03 对比 2024-01，结果失真。

解决办法不是硬补数据，而是先生成完整的时间序列，再左连接原始聚合结果：

WITH month_series AS (
  SELECT 202401 AS ym UNION ALL SELECT 202402 UNION ALL SELECT 202403 UNION ALL SELECT 202404
),
t AS (
  SELECT ym, COALESCE(sales, 0) AS sales
  FROM month_series
  LEFT JOIN (
    SELECT YEAR(order_date)*100+MONTH(order_date) AS ym, SUM(amount) AS sales
    FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-04-30'
    GROUP BY YEAR(order_date), MONTH(order_date)
  ) o USING (ym)
)
SELECT
  ym,
  sales,
  LAG(sales, 1) OVER (ORDER BY ym) AS prev_month_sales,
  CASE 
    WHEN LAG(sales, 1) OVER (ORDER BY ym) = 0 THEN NULL
    ELSE ROUND((sales - LAG(sales, 1) OVER (ORDER BY ym)) / LAG(sales, 1) OVER (ORDER BY ym), 4)
  END AS mm_growth
FROM t;

手动生成 month_series 是最可控的方式；用递归 CTE 或日历表也行，但别依赖 LAG() 自动跳过空档
COALESCE(sales, 0) 把缺失月设为 0，避免环比计算中出现 NULL 干扰逻辑（根据业务决定是否合理）
注意：环比分母为 0 时，NULLIF 会返回 NULL，但有些场景要区分“0增长”和“不可算”，此时用 CASE 显式控制更稳妥

`LAG()` 的性能和替代方案提醒

LAG() 是窗口函数，性能取决于排序字段是否走索引。如果 ORDER BY ym 对应的列没有索引，千万级数据下可能慢到超时。

确保 ym 列（或等价的年月表达式）上有索引；在 MySQL 中可建生成列索引：ALTER TABLE orders ADD COLUMN ym INT AS (YEAR(order_date)*100+MONTH(order_date)), ADD INDEX idx_ym(ym);
如果数据库版本太老（如 MySQL 5.7），无法用 LAG()，可用自连接模拟：LEFT JOIN t t2 ON t2.ym = t.ym - 1，但要注意 -1 在跨年时失效（202401 - 1 = 202400，不是 202312）
ClickHouse、Doris 等 OLAP 引擎支持 toYYYYMM() 直接生成年月，比手算更稳；而 Presto/Trino 需用 year(d)*100 + month(d) 或 date_format(d, '%Y%m')