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

舞夢輝影 2026-01-21 00:00:00 次阅读

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)），避免字符串排序错乱

使用 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，符合业务实际

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

方式一（推荐）：按年+月双字段排序 + 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)，写法需适配