MySQL按小时统计满足处理时长条件的记录数及平均时长

问题背景 #

现有一张包含updated_date和upload_date列的表auk_feed_queue，示例数据如下：

需求：

• 按upload_date的1小时时间段（如2023-04-03 09:00:00至10:00:00）分组
• 统计每个时间段内处理时长（updated_date与upload_date的时间差）超过1小时的记录数
• 返回该组的平均处理时长，格式为hh:mm:ss
• 每个时间段对应一行结果

尝试的查询及问题 #

尝试了以下SQL：

SELECT COUNT(*) AS number_of_feeds,
       AVG(TIMEDIFF(updated_date, upload_date)) AS average_processing_time
FROM   auk_feed_queue  
WHERE  TIMEDIFF(updated_date, upload_date) > '00:59:59'

但存在两个问题：

1. 所有符合条件的记录被合并成一行返回，未按小时段分组
2. 平均处理时长格式不正确，返回数值（如90274.3861）而非hh:mm:ss格式

解决方案 #

核心思路 #

1. 对upload_date进行小时级截断，生成每个分组的时间段标识
2. 条件统计每个分组内处理时长超1小时的记录数
3. 将时间差转换为秒数计算平均值，再转回hh:mm:ss格式

完整SQL（MySQL） #

SELECT
    DATE_FORMAT(upload_date, '%Y-%m-%d %H:00:00') AS hour_interval,
    COUNT(CASE WHEN TIMEDIFF(updated_date, upload_date) > '00:59:59' THEN 1 END) AS number_of_feeds,
    SEC_TO_TIME(AVG(TIME_TO_SEC(TIMEDIFF(updated_date, upload_date)))) AS average_processing_time
FROM auk_feed_queue
GROUP BY hour_interval
ORDER BY hour_interval;

关键说明 #

• DATE_FORMAT(upload_date, '%Y-%m-%d %H:00:00')：将upload_date统一到当前小时的0分0秒，作为分组的时间段标识
• COUNT(CASE ...)：仅统计处理时长超过1小时的记录，不符合条件的会被视为NULL，COUNT不会统计NULL值
• TIME_TO_SEC(TIMEDIFF(...))：将时间差转换为秒数，方便计算平均值；SEC_TO_TIME()再将平均秒数转换回hh:mm:ss格式
• GROUP BY hour_interval：按小时段分组，确保每个时间段对应一行结果

其他数据库适配（PostgreSQL） #

SELECT
    DATE_TRUNC('hour', upload_date) AS hour_interval,
    COUNT(CASE WHEN (updated_date - upload_date) > INTERVAL '1 hour' THEN 1 END) AS number_of_feeds,
    TO_CHAR(AVG(updated_date - upload_date), 'HH24:MI:SS') AS average_processing_time
FROM auk_feed_queue
GROUP BY hour_interval
ORDER BY hour_interval;

内容的提问来源于stack exchange，提问作者Márton Molnár