MySQL 5.7无窗口函数时7日移动平均计算异常的解决办法

核心问题分析 #

你的问题出在原查询按行号取前7行计算平均，而非按日期范围取最近7天数据。当存在日期缺失时，前7行对应的实际天数会超过7天，导致平均结果异常。要解决这个问题，需先补全缺失日期的销售数据，再基于日期范围计算移动平均。

步骤1：生成连续日期序列 #

MySQL 5.7不支持递归CTE，可通过数字表交叉连接生成指定范围内的连续日期：

-- 生成2022-09-14至2022-10-04的连续日期
SELECT 
    DATE_ADD('2022-09-14', INTERVAL (a.a + 10*b.a + 100*c.a) DAY) AS date
FROM
    (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
    CROSS JOIN (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    CROSS JOIN (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
WHERE
    DATE_ADD('2022-09-14', INTERVAL (a.a + 10*b.a + 100*c.a) DAY) <= '2022-10-04'
ORDER BY date;

步骤2：补全缺失日期的销售数据 #

将连续日期表与你的销售表左连接，缺失日期的销售额用0填充：

-- 补全销售数据，确保每个日期都有记录
SELECT 
    d.date,
    COALESCE(s.sales_amount, 0) AS sales_amount
FROM
    (SELECT 
        DATE_ADD('2022-09-14', INTERVAL (a.a + 10*b.a + 100*c.a) DAY) AS date
    FROM
        (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
        CROSS JOIN (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
        CROSS JOIN (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
    WHERE
        DATE_ADD('2022-09-14', INTERVAL (a.a + 10*b.a + 100*c.a) DAY) <= '2022-10-04') d
LEFT JOIN 
    your_sales_table s ON d.date = s.sale_date
ORDER BY d.date;

步骤3：计算7日移动平均 #

通过自连接关联当前日期及往前6天的所有数据，取销售额平均值：

-- 最终计算7日移动平均
SELECT 
    main.date,
    main.sales_amount,
    AVG(prev.sales_amount) AS 7dayMovingAvg
FROM
    -- 补全后的销售数据
    (SELECT 
        d.date,
        COALESCE(s.sales_amount, 0) AS sales_amount
    FROM
        (SELECT 
            DATE_ADD('2022-09-14', INTERVAL (a.a + 10*b.a + 100*c.a) DAY) AS date
        FROM
            (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
            CROSS JOIN (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
            CROSS JOIN (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
        WHERE
            DATE_ADD('2022-09-14', INTERVAL (a.a + 10*b.a + 100*c.a) DAY) <= '2022-10-04') d
    LEFT JOIN 
        your_sales_table s ON d.date = s.sale_date) main
-- 关联当前日期往前6天的所有数据
JOIN
    (SELECT 
        d.date,
        COALESCE(s.sales_amount, 0) AS sales_amount
    FROM
        (SELECT 
            DATE_ADD('2022-09-14', INTERVAL (a.a + 10*b.a + 100*c.a) DAY) AS date
        FROM
            (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
            CROSS JOIN (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
            CROSS JOIN (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
        WHERE
            DATE_ADD('2022-09-14', INTERVAL (a.a + 10*b.a + 100*c.a) DAY) <= '2022-10-04') d
    LEFT JOIN 
        your_sales_table s ON d.date = s.sale_date) prev
ON prev.date BETWEEN DATE_SUB(main.date, INTERVAL 6 DAY) AND main.date
GROUP BY main.date, main.sales_amount
ORDER BY main.date;

优化建议 #

如果频繁需要生成连续日期，建议创建一张永久的数字表（如numbers，存储0~1000的整数），后续生成日期时直接关联该表，简化SQL语句。

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