MySQL按活动名称与小时分组统计，如何拆分跨小时活动记录？

现有表结构 #

CREATE TABLE `prova` (
 `date` date NOT NULL,
  `time` varchar(8) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `activity` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

表中数据 #

需求 #

按活动名称和小时分组统计总时长，跨小时的活动需拆分为对应时段的记录。例如test2的120秒活动，要拆分为2023-06-19 17:00:00时段60秒、2023-06-19 18:00:00时段60秒。

当前查询的问题 #

原查询仅按活动、日期和开始小时分组，未处理跨小时的情况，导致test2的时长全部统计在17点时段：

SELECT DATE_FORMAT(TIMESTAMP(date, time), '%Y-%m-%d %H:00:00') as date, 
       SUM(duration) AS total_duration, activity
FROM prova
GROUP BY activity, date, hour(time)
ORDER BY time

解决方案（适配MySQL 5.6） #

由于MySQL 5.6不支持递归CTE，我们通过生成数字辅助表来展开跨小时的时段，计算每个时段的实际贡献时长：

SELECT
    DATE_FORMAT(hour_start, '%Y-%m-%d %H:00:00') AS hour_segment,
    activity,
    SUM(
        TIMESTAMPDIFF(SECOND,
            GREATEST(start_ts, hour_start),
            LEAST(end_ts, hour_start + INTERVAL 1 HOUR)
        )
    ) AS total_duration
FROM (
    -- 转换原始数据为时间戳，计算活动的开始和结束时间
    SELECT
        TIMESTAMP(`date`, `time`) AS start_ts,
        TIMESTAMP(`date`, `time`) + INTERVAL duration SECOND AS end_ts,
        activity
    FROM prova
) AS activities
-- 生成0-23的数字序列，覆盖一天内可能的跨小时偏移
JOIN (
    SELECT 0 AS n 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 UNION ALL
    SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
    SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
    SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
) AS hours_offset
-- 计算当前偏移对应的小时起始时间，筛选出与活动时间范围重叠的时段
ON start_ts <= hour_start + INTERVAL 1 HOUR
AND end_ts >= hour_start
WHERE hour_start = DATE_FORMAT(start_ts, '%Y-%m-%d %H:00:00') + INTERVAL hours_offset.n HOUR
GROUP BY hour_segment, activity
ORDER BY hour_segment, activity;

逻辑说明 #

1. 转换时间戳：将date和time字段合并为活动开始时间戳start_ts，加上duration秒得到结束时间戳end_ts。
2. 生成小时偏移：通过UNION生成0到23的数字，用来表示活动可能跨越的小时数（比如跨1小时会用到0和1两个偏移）。
3. 匹配重叠时段：计算每个偏移对应的小时起始时间，只保留与活动时间范围有重叠的时段。
4. 计算时段时长：用GREATEST取活动开始与小时段开始的较晚时间，LEAST取活动结束与小时段结束的较早时间，两者的秒差就是该活动在当前小时的贡献时长。
5. 分组统计：按小时段和活动名称分组求和，得到最终的拆分统计结果。

查询结果 #

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