SQL中怎样实现同比与环比聚合指标的快速计算-SQL-PHP中文网

应优先使用LAG()/LEAD()计算环比，需配合PARTITION BY和ORDER BY确保分组与排序正确；同比须用DATE_SUB等函数对齐自然周期，避免行偏移；务必用NULLIF()防除零，大表需建(region,month)联合索引并确保日期为DATE类型。

sql中怎样实现同比与环比聚合指标的快速计算

用 `LAG()` 和 `LEAD()` 直接算环比，别写自连接

环比（比上期）本质是当前行和前一行的值做运算，LAG() 就是干这个的。在 PostgreSQL、MySQL 8.0+、SQL Server、Oracle 等支持窗口函数的数据库里，它比 JOIN 自己拼上期表快得多，也更安全——不用处理日期缺失、分组错位等问题。

常见错误是忽略 PARTITION BY：比如按商品分组算销量环比，却只写了 ORDER BY date，结果所有商品数据混在一起排，LAG(sales) 拿到的是其他商品的上期值。

必须显式写 PARTITION BY product_id（或对应业务维度）
ORDER BY date 要确保时间字段无重复；如有秒级重复，加 id 做二级排序防不确定性
MySQL 5.7 及更早不支持窗口函数，此时只能用变量或子查询模拟，性能差且易出错

示例（月度销售额环比）：

SELECT
  month,
  sales,
  LAG(sales) OVER (PARTITION BY region ORDER BY month) AS prev_month_sales,
  ROUND((sales - LAG(sales) OVER (PARTITION BY region ORDER BY month)) / NULLIF(LAG(sales) OVER (PARTITION BY region ORDER BY month), 0), 4) AS mom_rate
FROM sales_summary;

同比要对齐自然周期，`DATE_SUB()` 或 `INTERVAL` 是关键

同比（比去年同期）不是简单减 12 行，而是要找「去年同月同日」或「去年同一统计周期」。用 LAG() 硬凑行数会崩——闰年、大小月、节假日调休都会让行偏移失效。

正确做法是：先构造一个能和当前记录匹配去年日期的字段，再用 JOIN 或 LEFT JOIN 关联。但更轻量的是用相关函数生成同比键，配合聚合后 JOIN 或子查询。

MySQL 用 DATE_SUB(month, INTERVAL 1 YEAR)，PostgreSQL 用 month - INTERVAL '1 year'
如果原始粒度是日，但要按月同比，先用 YEAR_MONTH(date) 或 TO_CHAR(date, 'YYYY-MM') 归一化，再关联
注意时区：跨时区汇总时，DATE_SUB 操作前确认字段是否已转为本地时区

示例（按月同比，避免行数偏移）：

SELECT 
  curr.month,
  curr.sales AS curr_sales,
  last_year.sales AS ly_sales,
  ROUND((curr.sales - last_year.sales) / NULLIF(last_year.sales, 0), 4) AS yoy_rate
FROM sales_summary curr
LEFT JOIN sales_summary last_year 
  ON curr.region = last_year.region 
  AND last_year.month = DATE_SUB(curr.month, INTERVAL 1 YEAR);

NULLIF() 不只是防除零，更是指标可信度的第一道过滤

同比/环比结果里出现 Inf、-Inf 或 NULL 不是计算失败，而是数据语义问题：上期为 0 时，增长百分比本身无业务意义。很多报表直接展示 “∞%”，用户看不懂，还可能触发告警误报。

一律用 NULLIF(denominator, 0) 替代裸除法，让结果为 NULL 而非报错或异常值
后续可视化层可统一将 NULL 显示为 “—” 或 “不可比”，比硬填 0 或 999999% 更严谨
若业务允许“上期为 0 视为新增”，需额外加判断逻辑，例如：CASE WHEN prev = 0 AND curr > 0 THEN 1 ELSE ... END