MySQL按分类按月统计销量同比上年时缺失月份数据偏移问题求解

核心问题是原有逻辑只会为存在订单的月份生成行，无销量的月份根本不会进入结果集，CASE WHEN无法处理不存在的行。需要先生成全量的商品品类+月份基准集合，再关联实际销量数据，确保无销量的月份也有对应记录，窗口函数的偏移计算才会准确。

-- 递归生成统计范围内的连续年月序列，MySQL 8.0+支持递归CTE
WITH RECURSIVE all_months AS (
    SELECT MIN(DATE_FORMAT(orderDate, '%Y-%m-01')) as month 
    FROM orders
    UNION ALL
    SELECT DATE_ADD(month, INTERVAL 1 MONTH) 
    FROM all_months
    WHERE month < (SELECT MAX(DATE_FORMAT(orderDate, '%Y-%m-01')) FROM orders)
),
-- 取所有商品品类列表
all_product_lines AS (
    SELECT DISTINCT productLine FROM products
),
-- 生成全量 品类*年月 基准集合，所有组合都会存在
base AS (
    SELECT 
        pl.productLine,
        am.month,
        EXTRACT(YEAR FROM am.month) as orderYear,
        EXTRACT(MONTH FROM am.month) as orderMonth
    FROM all_product_lines pl
    CROSS JOIN all_months am
),
-- 预统计每个品类每个月的实际销量
monthly_sales AS (
    SELECT
        p.productLine,
        EXTRACT(YEAR FROM o.orderDate) as orderYear,
        EXTRACT(MONTH FROM o.orderDate) as orderMonth,
        SUM(od.quantityOrdered) as real_quantity
    FROM orders o
    INNER JOIN orderDetails od USING(orderNumber)
    INNER JOIN products p USING(productCode)
    GROUP BY p.productLine, orderYear, orderMonth
)
-- 关联基准表和销量表，计算同比
SELECT
    b.productLine,
    b.orderYear,
    b.orderMonth,
    IFNULL(ms.real_quantity, 0) as orderQuantity,
    LAG(IFNULL(ms.real_quantity, 0), 12) OVER (PARTITION BY b.productLine ORDER BY b.orderYear, b.orderMonth) as orderQuantity_prevYear,
    -- 处理上年同期销量为0的情况，避免除以0报错
    CASE 
        WHEN LAG(IFNULL(ms.real_quantity, 0), 12) OVER (PARTITION BY b.productLine ORDER BY b.orderYear, b.orderMonth) = 0 THEN '无上年数据'
        ELSE CONCAT(ROUND((IFNULL(ms.real_quantity, 0) - LAG(IFNULL(ms.real_quantity, 0), 12) OVER (PARTITION BY b.productLine ORDER BY b.orderYear, b.orderMonth)) * 100 / LAG(IFNULL(ms.real_quantity, 0), 12) OVER (PARTITION BY b.productLine ORDER BY b.orderYear, b.orderMonth), 0), '%')
    END AS year_over_year_rate
FROM base b
LEFT JOIN monthly_sales ms 
    ON b.productLine = ms.productLine 
    AND b.orderYear = ms.orderYear 
    AND b.orderMonth = ms.orderMonth
ORDER BY b.productLine, b.orderYear, b.orderMonth;

• 用递归CTE生成连续月份，覆盖所有需要统计的时间区间，不会遗漏月份
• 通过CROSS JOIN生成每个品类对应所有月份的基准行，确保无销量的月份也会保留
• 左关联实际销量数据，无销量的月份销量自动补0
• 窗口函数增加PARTITION BY productLine，按品类单独统计同比，避免不同品类数据互相干扰
• 增加除以0的异常处理，上年同期销量为0时不会报错
• 原代码的WHERE条件YEAR(orderDate) > (YEAR(orderDate) - 1)为恒真条件，已删除，若需要限制统计时间范围可以在all_months的生成逻辑中修改

内容的提问来源于stack exchange，提问作者Exramas