MySQL如何按10分钟间隔统计聚合结果 包含无数据时段0值

核心解决思路是先构造从统计起始时间到当前时间的完整10分钟刻度序列，再和你现有统计结果左关联，无匹配的时段自动补0即可。

完整实现SQL（支持MySQL 8.0+、Hive、Spark SQL等支持递归CTE的引擎） #

WITH RECURSIVE time_series AS (
    -- 自定义统计起始时间，这里按你的示例取2021-09-16 09:10:00，可根据需求调整
    SELECT '2021-09-16 09:10:00' AS time_slot
    UNION ALL
    -- 每次递增加10分钟，直到时间超过当前时间
    SELECT DATE_ADD(time_slot, INTERVAL 10 MINUTE)
    FROM time_series
    WHERE time_slot < NOW()
),
access_stats AS (
    -- 你原有的统计逻辑，可直接复用
    SELECT
        CASE 
            WHEN SUBSTR(in_datetime,15,2) < '10' THEN CONCAT(SUBSTR(in_datetime,12,2),':00')
            WHEN SUBSTR(in_datetime,15,2) >='10' AND SUBSTR(in_datetime,15,2)<'20' THEN CONCAT(SUBSTR(in_datetime,12,2),':10')
            WHEN SUBSTR(in_datetime,15,2) >='20' AND SUBSTR(in_datetime,15,2)<'30' THEN CONCAT(SUBSTR(in_datetime,12,2),':20')
            WHEN SUBSTR(in_datetime,15,2) >='30' AND SUBSTR(in_datetime,15,2)<'40' THEN CONCAT(SUBSTR(in_datetime,12,2),':30')
            WHEN SUBSTR(in_datetime,15,2) >='40' AND SUBSTR(in_datetime,15,2)<'50' THEN CONCAT(SUBSTR(in_datetime,12,2),':40')
            WHEN SUBSTR(in_datetime,15,2) >='50' AND SUBSTR(in_datetime,15,2)<'60' THEN CONCAT(SUBSTR(in_datetime,12,2),':50')
        END as hhmm,
        COUNT(access_no) as au
    FROM tbl_access
    WHERE DATE(out_datetime) = STR_TO_DATE('20210916', '%Y%m%d')
    AND in_datetime IS NOT NULL
    GROUP BY 
        SUBSTR(in_datetime,1,10),
        CASE 
            WHEN SUBSTR(in_datetime,15,2) < '10' THEN CONCAT(SUBSTR(in_datetime,12,2),':00')
            WHEN SUBSTR(in_datetime,15,2) >='10' AND SUBSTR(in_datetime,15,2)<'20' THEN CONCAT(SUBSTR(in_datetime,12,2),':10')
            WHEN SUBSTR(in_datetime,15,2) >='20' AND SUBSTR(in_datetime,15,2)<'30' THEN CONCAT(SUBSTR(in_datetime,12,2),':20')
            WHEN SUBSTR(in_datetime,15,2) >='30' AND SUBSTR(in_datetime,15,2)<'40' THEN CONCAT(SUBSTR(in_datetime,12,2),':30')
            WHEN SUBSTR(in_datetime,15,2) >='40' AND SUBSTR(in_datetime,15,2)<'50' THEN CONCAT(SUBSTR(in_datetime,12,2),':40')
            WHEN SUBSTR(in_datetime,15,2) >='50' AND SUBSTR(in_datetime,15,2)<'60' THEN CONCAT(SUBSTR(in_datetime,12,2),':50')
        END
)
-- 最终关联查询补全空时段
SELECT 
    ts.`hh:mm`,
    IFNULL(stats.au, 0) AS au
FROM (
    -- 把时间序列格式化为hh:mm格式
    SELECT DATE_FORMAT(time_slot, '%H:%i') AS `hh:mm`
    FROM time_series
) ts
LEFT JOIN access_stats stats ON ts.`hh:mm` = stats.hhmm
ORDER BY ts.`hh:mm`

优化提示 #

你原有的多层case判断截取10分钟刻度的逻辑可以简化，仅用一行代码即可实现相同效果：
DATE_FORMAT(DATE_ADD(in_datetime, INTERVAL -(MINUTE(in_datetime) % 10) MINUTE), '%H:%i')

低版本MySQL兼容方案 #

如果使用不支持递归CTE的低版本MySQL，可提前创建一张时间维度辅助表，提前录入所有可能用到的hh:mm 10分钟刻度，直接查询该维度表左关联你的统计结果即可，逻辑和上述方案一致。

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