mysql存储过程如何计算同比环比数据_复杂SQL统计逻辑封装

MySQL存储过程需避免直接用YEAR()/MONTH()做跨年/跨月判断，应改用DATE_SUB对齐时间粒度；SUM()须用COALESCE兜底为0；SELECT ... INTO前需检查数据存在性；同比环比分母为0或负数时须用CASE WHEN保护。

存储过程里直接用 YEAR() 和 MONTH() 会出错

在MySQL存储过程中，直接在WHERE条件里依赖YEAR()或MONTH()这类函数结果来做跨年或跨月的边界判断，是个典型的“坑”。比如，YEAR(date) = year - 1看起来没问题，但一旦存储过程在1月份被调用，month - 1就会变成0，导致条件MONTH(date) = 0永远无法匹配任何数据。结果呢？查询不到数据，previousTotal变量就成了NULLNULL值，让整个统计逻辑失效。

正确的做法是，使用日期运算函数来对齐时间粒度，从根本上规避边界问题：

• 同比计算：使用DATE_SUB(date, INTERVAL 1 YEAR)来对齐到上一年，然后再进行分组或过滤，而不是直接对年份做减法。
• 环比计算：同样，使用DATE_SUB(date, INTERVAL 1 MONTH)来精确找到上个月的日期范围，这比单纯依赖MONTH() - 1要可靠得多。
• 空值兜底：这是关键一步，必须为SUM()查询加上COALESCE(..., 0)。否则，只要参与求和的字段里有一个NULL，整个增长率的计算结果就会变成NULL，前功尽弃。

SELECT ... INTO 赋值前没检查是否存在数据

另一个常见的疏忽发生在使用SELECT ... INTO进行变量赋值时。比如，执行SELECT SUM(amount) INTO currentTotal FROM sales WHERE ...，如果WHERE条件没有命中任何一行数据，那么currentTotal变量并不会被赋值为0，而是会被设置为NULL。接下来的计算，无论是NULL - 100还是NULL / 100，结果都依然是NULL。最终，存储过程可能输出一堆空值，而排查起来却异常困难，因为语法上并没有报错。

安全的写法要求我们显式地处理这种空值情况：

• 子查询包装法：直接在查询中使用(SELECT COALESCE(SUM(amount), 0) FROM sales WHERE ...)，一步到位地完成求和与空值转换。
• 先计数后求和：先执行SELECT COUNT(*) INTO row_count FROM sales WHERE ...，根据row_count的值再决定是否进行求和操作，逻辑更清晰。
• 更优策略：一次性聚合：实际上，更推荐的做法是将当前值、同比值和环比值的计算，通过一次聚合查询全部取出。这不仅能避免多次SELECT ... INTO带来的状态依赖和性能开销，也让代码结构更紧凑、更健壮。

同比环比的分母为 0 或负数时没兜底

计算增长率的经典公式(current - previous) / previous * 100，其实隐藏着两个“杀手”：一是当previous（即上期值）为0时，除法直接崩溃；二是当previous为负数时（例如上期业绩亏损），计算出的增长率符号会完全失真，严重误导业务判断。想象一下，业绩从-50万提升到30万，本是大为好转，但公式算出来却是-160%，这显然与事实背道而驰。

因此，在生产环境的存储过程中，必须加入保护逻辑：

• 使用CASE WHEN分支：对同比/环比结果统一用CASE WHEN previousTotal = 0 THEN NULL WHEN previousTotal < 0 THEN ... ELSE ... END这样的结构进行判断和处理。
• 定义清晰策略：常见的处理策略包括：分母为0时，返回NULL或特定的标记如'N/A'；分母为负时，则改用绝对值计算差额，或者单独标记为“负基期，需单独分析”。
• 后端拦截原则：切记，这类数据完整性和逻辑正确性的问题，应该在存储过程这一数据层就解决掉，不要依赖前端Ja vaScript或其他应用层代码去补救或掩盖。

用 JOIN 替代多次独立查询更稳定

回顾一下最初的思路：分别执行三次独立的查询（本期、上年同期、上期），每次查询都伴随着全表扫描和函数计算，性能开销大。更重要的是，在事务中多次读取同一张表，可能在并发写入的场景下，导致三次查询看到的数据状态不一致，从而算出矛盾的结果。

换一种思路，采用单次聚合配合自连接，不仅更优雅，也更稳定：

SELECT
  curr.yymm AS period,
  curr.total AS current_total,
  last_yr.total AS yoy_total,
  COALESCE(ROUND((curr.total - last_yr.total) / NULLIF(last_yr.total, 0) * 100, 2), NULL) AS yoy_rate,
  last_mth.total AS mom_total,
  COALESCE(ROUND((curr.total - last_mth.total) / NULLIF(last_mth.total, 0) * 100, 2), NULL) AS mom_rate
FROM (
  SELECT DATE_FORMAT(date, '%Y-%m') yymm, SUM(amount) total
  FROM sales GROUP BY DATE_FORMAT(date, '%Y-%m')
) curr
LEFT JOIN (
  SELECT DATE_FORMAT(DATE_SUB(date, INTERVAL 1 YEAR), '%Y-%m') yymm, SUM(amount) total
  FROM sales GROUP BY DATE_FORMAT(DATE_SUB(date, INTERVAL 1 YEAR), '%Y-%m')
) last_yr ON curr.yymm = last_yr.yymm
LEFT JOIN (
  SELECT DATE_FORMAT(DATE_SUB(date, INTERVAL 1 MONTH), '%Y-%m') yymm, SUM(amount) total
  FROM sales GROUP BY DATE_FORMAT(DATE_SUB(date, INTERVAL 1 MONTH), '%Y-%m')
) last_mth ON curr.yymm = last_mth.yymm;

这种写法的优势是显而易见的：它天然规避了月份越界、空值传播和重复扫描的问题。从优化角度看，也只需要在date字段上建立一个合适的索引，就能高效支撑所有基于日期的计算和连接操作。

说到底，真正的挑战不在于写出一段能在测试环境跑通的SQL，而在于确保这套逻辑在面对数据量激增、跨年一月切换、上期数据为零、财务冲正调整等各种边缘场景时，依然能够输出稳定且可解释的结果。所以，别为了省那几行COALESCE和NULLIF的代码，而给未来埋下隐患。