MySQL 5.5中如何在延迟统计查询中包含计数为0的分组？

环境与表结构 #

当前使用MySQL 5.5，临时表结构及数据如下：

create temporary table test (
    t_source float, -- generating time; seconds from midnight
    t_received float, -- receiving time; seconds from midnight
    delay float -- t_received - t_source
);

insert into test (t_source, t_received, delay) values
(713.27, 714.61, 1.34),
(2791.81, 2797.09, 5.28),
(94.98, 98.01, 3.03),
(3577.35, 3580.01, 2.66),
(3202.63, 3207.82, 5.19),
(1696.04, 1701.94, 5.9),
(1827.46, 1833.33, 5.87),
(395.78, 400.97, 5.19),
(1102.89, 1105.91, 3.02),
(1598.06, 1602.77, 4.71),
(1662.32, 1667.06, 4.74),
(1532.28, 1533.99, 1.71),
(2488.77, 2489.82, 1.05),
(186.59, 190.34, 3.75),
(2875.11, 2879.48, 4.37),
(1796.25, 1801.35, 5.1),
(2989.3, 2994.22, 4.92),
(1084.44, 1085.45, 1.01),
(2668.18, 2669.23, 1.05),
(2632.22, 2636.42, 4.2);

原查询及结果 #

执行以下分组查询：

select t_received, @chid := floor(t_received / 300) chunk_id, count(*) as number, max(delay) as max_delay
from test
group by chunk_id;

得到结果：

问题描述 #

原查询会缺失无记录的chunk_id分组，需要输出从0到最大chunk_id的所有分组，无记录的分组number为0、max_delay为0。

期望输出 #

解决方案 #

由于MySQL 5.5不支持递归CTE（WITH RECURSIVE），需要手动生成完整的chunk_id序列，再通过左连接关联原分组结果，将空值替换为0：

SELECT 
    t.t_received,
    s.chunk_id,
    COALESCE(t.number, 0) AS number,
    COALESCE(t.max_delay, 0) AS max_delay
FROM (
    -- 生成0到最大chunk_id的完整序列（当前最大为11）
    SELECT 0 AS chunk_id 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 UNION ALL
    SELECT 10 UNION ALL
    SELECT 11
) s
LEFT JOIN (
    -- 修正原分组查询，确保语法合规（用聚合函数获取t_received）
    SELECT 
        MAX(t_received) AS t_received,
        FLOOR(t_received / 300) AS chunk_id,
        COUNT(*) AS number,
        MAX(delay) AS max_delay
    FROM test
    GROUP BY chunk_id
) t ON s.chunk_id = t.chunk_id
ORDER BY s.chunk_id;

说明 #

1. 生成完整chunk序列：通过UNION ALL手动列出0到最大chunk_id的所有值（若后续数据的最大chunk_id变化，需对应扩展序列）；
2. 左连接分组结果：将完整序列与原分组查询结果左连接，保留所有chunk_id；
3. 空值替换：用COALESCE函数将无匹配分组的number和max_delay从NULL替换为0；
4. 语法修正：原查询中直接选择非聚合列t_received且未包含在GROUP BY中，属于非标准SQL，这里改用MAX(t_received)获取分组内的接收时间，确保语法合规。

内容的提问来源于stack exchange，提问作者Jose Ángel de Pascual Viciana