mysql存储过程如何计算同比环比数据_复杂SQL统计逻辑封装-mysql教程-PHP中文网

mysql存储过程需避免直接用year()/month()做跨年/跨月判断，应改用date_sub对齐时间粒度；sum()须用coalesce兜底为0；select ... into前需检查数据存在性；同比环比分母为0或负数时须用case when保护。

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

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

MySQL 存储过程中不能直接在 WHERE 条件里依赖函数结果做跨年/跨月边界判断，比如 YEAR(date) = year - 1 看似合理，但一旦遇到 1 月调用，month - 1 就变成 0，导致 MONTH(date) = 0 永远不匹配——数据查不到，previousTotal 为 NULL，后续除法直接报 Division by zero 或返回 NULL。

正确做法是用日期运算函数对齐时间粒度：

同比：用 DATE_SUB(date, INTERVAL 1 YEAR) 对齐年份，再按年月分组或过滤
环比：用 DATE_SUB(date, INTERVAL 1 MONTH) 对齐上月第一天到最后一日，避免只靠 MONTH() 硬减
必须给 SUM() 查询加 COALESCE(..., 0)，否则任一字段为 NULL，整个增长率表达式结果就是 NULL

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

存储过程里写 SELECT SUM(amount) INTO currentTotal FROM sales WHERE ...，如果条件没命中任何行，currentTotal 会被设成 NULL，而不是 0。后面做减法或除法时，NULL - 100 还是 NULL，最终输出全是空值，还看不出错在哪。

安全写法是显式处理空值：

用子查询包装： (SELECT COALESCE(SUM(amount), 0) FROM sales WHERE ...)
或先查计数：SELECT COUNT(*) INTO row_count FROM sales WHERE ...，再决定是否执行求和
更推荐用聚合查询一次性拉出当前、同比、环比三组值，避免多次 SELECT ... INTO 带来的状态依赖

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

公式 (current - previous) / previous * 100 在 previous = 0 时崩溃；若 previous 是负数（比如上期亏损），增长率符号会反向误导业务判断——例如从 -50 到 30，算出来是 -160%，实际是大幅好转。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

生产环境必须加保护逻辑：

同比/环比结果统一用 CASE WHEN previousTotal = 0 THEN NULL WHEN previousTotal 分支处理
常见策略是：分母为 0 返回 NULL 或字符串 'N/A'；分母为负时改用绝对值差额或单独标记“负基期”
不要依赖前端 JS 去补救，存储过程层就要把异常值拦截住

用 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，而是让这套逻辑在数据量涨十倍、出现跨年 1 月、上期归零、财务冲正等边缘场景下依然吐出可解释的结果。别省那几行 COALESCE 和 NULLIF。