SQL 使用窗口函数计算同比 / 环比

SQL中计算同比和环比的核心是：先按时间分组聚合，再用LAG()拉取相邻周期值并运算；关键在时间对齐、分区清晰、空值处理。

SQL 中用窗口函数计算同比（Year-on-Year）和环比（Month-on-Month）的核心是：先按时间分组聚合，再用 LAG() 或 LEAD() 拉取相邻周期的值，最后做差值或比值运算。关键在于时间对齐、分区逻辑清晰、空值处理得当。

准备基础数据：按时间聚合

原始明细表（如订单表）通常按天记录，需先按年月（或年份）汇总，生成时间序列基准表：

• 用 GROUP BY YEAR(order_date), MONTH(order_date) 或 DATE_FORMAT(order_date, '%Y-%m')（MySQL）/ TO_CHAR(order_date, 'YYYY-MM')（PostgreSQL）统一时间粒度
• 聚合指标如 SUM(amount)、COUNT(*)，结果作为后续窗口计算的输入
• 建议显式生成日期字段（如 ym = DATE_TRUNC('month', order_date)），避免字符串排序错乱

计算环比：上月 vs 本月

使用 LAG(value, 1) OVER (ORDER BY ym) 获取前一个自然月的值：

• LAG(sales, 1) OVER (ORDER BY ym) 返回同列上一行（即上月）的销售额
• 环比增长量 = sales - LAG(sales)；环比增长率 = ROUND((sales - LAG(sales)) * 100.0 / NULLIF(LAG(sales), 0), 2)
• NULLIF(LAG(sales), 0) 防止除零；首行 LAG 返回 NULL，对应环比值也为 NULL，符合业务实际

计算同比：去年同期 vs 今年同期

关键在正确“跨年对齐”，推荐两种方式：

• 方式一（推荐）：按年+月双字段排序 + LAG(n) 固定偏移
  先按 year, month 排序，再用 LAG(sales, 12) OVER (ORDER BY year, month) —— 假设数据按月连续且无缺失，第13行就是去年同月
• 方式二（更鲁棒）：JOIN 自关联或使用时间偏移函数
  例如 PostgreSQL 可用 LAG(sales) OVER (ORDER BY ym) FILTER (WHERE ym = ym - INTERVAL '1 year')；但多数引擎不支持 FILTER，此时更适合用子查询或 CTE 先构造“去年同月”字段，再 LEFT JOIN
• 注意：若某月去年无数据（如新业务），LAG 返回 NULL，需结合 COALESCE 或保留 NULL 体现

  

  数据断层

注意事项与常见坑

实际写 SQL 时容易忽略这些细节：

• 时间字段必须可排序且无重复（如多个 2025-03 不会触发多行并列，否则 LAG 行为不确定）
• 不要在 WHERE 中提前过滤掉某个月份——否则 LAG 会跳过空缺，导致同比错位；应在聚合后过滤或用 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 控制窗口范围
• 区分「自然月同比」和「滚动12个月同比」：后者需用 SUM() OVER (ORDER BY ym ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) 先算滚动和，再套 LAG
• 不同数据库语法差异：Oracle 支持 ADD_MONTHS，Snowflake 用 DATEADD('month', -1, ym)，写法需适配