MySQL中如何强制AVG函数包含0值 补全完整小时统计序列

现在的查询逻辑完全是从有询盘的订单数据里聚合出的小时值，近7天里完全没有任何询盘的小时，从最内层的订单筛选环节就被过滤掉了，根本不会进入后续的聚合、计算环节，最终结果自然会跳过这类无数据的小时。

核心做法是先搭一套覆盖0-23点的完整小时基准表，再把原有的聚合结果和这套基准表左连接，没有对应聚合数据的小时直接补0询盘量，最后按小时从小到大排序后计算累计值，完全不改动原有“每日每小时单独去重o.phone计算询盘数、再跨天算小时平均值”的统计逻辑。

适配代码（以MySQL 8.0+为例） #

-- 初始化累计变量
SET @cumulativeSum := 0;

-- 递归生成覆盖0-23点的全量小时基准
WITH RECURSIVE all_hours AS (
    SELECT 0 AS hour
    UNION ALL
    SELECT hour + 1 FROM all_hours WHERE hour < 23
),
-- 保留原有逻辑的每小时平均询盘聚合
hourly_avg_enquiry AS (
    SELECT
        y.hour as hour,
        IFNULL(ROUND(AVG(y.enquiries), 0), 0) as enquiries
    FROM (
        SELECT 
            DATE(o.date_created) AS day_part,
            HOUR(o.date_created) as hour,
            COUNT(DISTINCT o.phone) as enquiries
        FROM orders o
        WHERE phone IS NOT NULL
            AND name NOT LIKE 'test%'
            AND o.email NOT LIKE 'jawor%'
            AND o.email NOT LIKE 'test%'
            AND o.email NOT LIKE '%moneyshake%'
            AND o.date_created < CURRENT_DATE()
            AND o.date_created >= DATE(NOW()) + INTERVAL -7 DAY
        GROUP BY HOUR(o.date_created), DATE(o.date_created)
    ) y
    GROUP BY hour
)
SELECT
    a.hour + 1 AS time,
    @cumulativeSum := @cumulativeSum + IFNULL(h.enquiries, 0) AS target
FROM all_hours a
-- 左连接原有聚合结果，补全缺失小时
LEFT JOIN hourly_avg_enquiry h ON a.hour = h.hour
-- 必须按小时升序排列，保证累计值计算顺序正确
ORDER BY a.hour;

注意事项 #

• 使用MySQL 5.x的话没有递归CTE功能，可以拿information_schema的系统表凑数生成0-23的连续小时序列，不用额外建临时表
• 左连接的时候一定要给缺失的询盘值补0，不然NULL值会把累计计算带崩
• 最后一定要按小时升序排序，不然用户变量算累计值的时候顺序是乱的，结果会错
• 原查询内层分组用DAY(o.date_created)做日维度拆分的话，如果统计周期跨月，会把不同月的同一天数据合并，建议改成DATE(o.date_created)做日维度标识，避免统计偏差

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