环比增长率的计算公式是（当前值上期值）/上期值×100%。这件事不能简单写个LAG()就算结束，因为只有配合PARTITION BY完成分组、再结合ORDER BY按时间排序，才能准确锁定真正的“上期”值。与此同时，上期为NULL或0这类边界情况也不能忽略，必须用CASE或NULLIF显式处理；否则，轻则结果失真，重则直接触发除零错误。

什么是环比增长率，为什么不能直接用LAG()就完事？

环比增长率的计算公式并不复杂：(当前值 - 上期值) / 上期值 × 100%。真正容易出问题的地方，在于这里的“上期”不是随便取一条上一行数据，而是必须限定在同一分组内，并且按照时间严格排序后得到的前一条记录。很多人写完 LAG(value) 就直接拿来做除法，结果要么碰上 division by zero，要么因为 NULL 导致占比失真。说到底，问题通常就出在两处：一是没有处理上期值为 0 或 NULL 的情况，二是没有把分组边界限制清楚。

• 必须配合 PARTITION BY 按业务维度（如 product_id、region）分组
• 必须用 ORDER BY 明确时间字段（如 month_date），否则 LAG() 返回顺序不可控
• 上期值为 0 时，直接除会报错；为 NULL 时结果也是 NULL，需显式判断

SELECT
month_date,
product_id,
sales,
LAG(sales) OVER (PARTITION BY product_id ORDER BY month_date) AS prev_sales
FROM sales_table;

怎么安全计算环比并避免除零和NULL干扰？

核心是把除法包装进 CASE，同时覆盖三种边界：上期为 NULL（首条记录）、上期为 0、正常非零值。

• 用 LAG(sales) OVER (PARTITION BY product_id ORDER BY month_date) 获取同组上期值
• CASE WHEN prev_sales IS NULL THEN NULL 处理首行（无上期）
• WHEN prev_sales = 0 THEN NULL 避免除零错误（也可改用 NULLIF(prev_sales, 0)）
• 其余情况再做除法，并乘以 100 得百分比

SELECT
month_date,
product_id,
sales,
ROUND(
CASE 
WHEN LAG(sales) OVER (PARTITION BY product_id ORDER BY month_date) IS NULL THEN NULL
WHEN LAG(sales) OVER (PARTITION BY product_id ORDER BY month_date) = 0 THEN NULL
ELSE (sales - LAG(sales) OVER (PARTITION BY product_id ORDER BY month_date)) * 100.0 
 / LAG(sales) OVER (PARTITION BY product_id ORDER BY month_date)
END,
2
) AS mom_rate
FROM sales_table;

为什么推荐用子查询或CTE复用LAG()，而不是重复写三次？

上面示例里 LAG() 写了三次，不仅冗长，还可能因排序微小差异导致结果不一致（比如某次漏写 ORDER BY）。真实环境数据量一大，性能也会受影响。

• 直接在子查询或 CTE 中算出 prev_sales，主查询只引用一次
• 所有逻辑基于同一份窗口结果，保证一致性
• 修改排序或分组时只需改一处

WITH sales_with_prev AS (
SELECT
month_date,
product_id,
sales,
LAG(sales) OVER (PARTITION BY product_id ORDER BY month_date) AS prev_sales
FROM sales_table
)
SELECT
month_date,
product_id,
sales,
ROUND(
CASE 
WHEN prev_sales IS NULL OR prev_sales = 0 THEN NULL
ELSE (sales - prev_sales) * 100.0 / prev_sales
END,
2
) AS mom_rate
FROM sales_with_prev;

不同数据库对NULLIF和除法的处理有差异，要注意什么？

NULLIF(x, 0) 是 ANSI 标准函数，但部分旧版 MySQL（<5.7）或某些嵌入式 SQLite 可能不支持；而除法结果类型也影响精度。

• PostgreSQL / SQL Server：100.0 / prev_sales 自动转浮点，没问题
• MySQL：整数除整数会截断，必须写 100.0 或 CAST(100 AS DECIMAL)
• 如果用 NULLIF(prev_sales, 0)，记得外面再套一层 COALESCE(..., NULL) 避免意外 0/0
• 生产环境建议统一用 CASE，兼容性最强，语义也最清晰

真正麻烦的不是公式本身，而是分组边界是否干净、时间字段是否有重复或空缺、以及上期值为 0 时业务上究竟该标 NULL 还是特殊标记——这些得看报表需求，SQL 只负责算得稳。

《夸克》非常好用的免费AI浏览器

下载APP查看