MySQL实现从现有表创建按小时汇总SUM值的新表

前提说明 #

假设你原数据表的表名为original_table，如果实际名称不同请自行替换，且原表Time字段为MySQL支持的TIME类型或可正常解析的HH:MM:SS格式字符串。

注：Time、Values属于MySQL接近关键字的保留字段，代码中全部用反引号包裹避免语法报错。

方案1：仅统计有数据的小时（无数据的小时不展示） #

如果你可以接受无原始数据的小时不出现在结果里，直接用分组聚合即可快速实现：

CREATE TABLE new_hour_sum AS
SELECT
    DATE_FORMAT(`Time`, '%H:00') AS `Time`,
    SUM(`Values`) AS `Sum`
FROM `original_table`
GROUP BY DATE_FORMAT(`Time`, '%H:00')
ORDER BY `Time`;

方案2：生成完整24行数据（无数据的小时Sum显示为0） #

如果需要严格输出24行，覆盖00:00到23:00所有整点，即使对应小时没有原始数据Sum也显示0，用以下写法：

CREATE TABLE new_hour_sum AS
WITH RECURSIVE hours AS (
    SELECT 0 AS h
    UNION ALL
    SELECT h + 1 FROM hours WHERE h < 23
)
SELECT
    CONCAT(LPAD(hours.h, 2, '0'), ':00') AS `Time`,
    IFNULL(SUM(ot.`Values`), 0) AS `Sum`
FROM hours
LEFT JOIN `original_table` ot ON HOUR(ot.`Time`) = hours.h
GROUP BY hours.h
ORDER BY hours.h;

注：如果你的MySQL版本低于8.0不支持CTE语法，可手动枚举24个小时的临时表替换上述CTE部分即可实现相同效果。

逻辑说明 #

• 通过DATE_FORMAT或HOUR函数提取原始时间的小时维度，统一格式化为整点字符串
• 用SUM()聚合函数计算对应小时下所有Values的总和，不受Date字段影响
• 左连接自动生成的24小时序列，保证所有整点都出现在结果中，IFNULL函数处理无数据时Sum为0的需求

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