如何编写SQL查询统计指定日期内各会议室的忙碌与空闲累计时长
可实现性说明
该统计需求完全可以通过SQL完成,核心逻辑是把零散的状态变更记录,按时间顺序配对相邻两次变更的时间差,匹配对应状态做时长累加,同时自动补全天区间首尾的边界时长,避免漏算。
核心实现逻辑
- 用窗口函数提取每条状态记录的下一次变更时间戳,计算单段状态的持续时长
- 自动兼容查询时间范围的起点(当天00:00:00)和终点(当天23:59:59):如果某会议室当天的第一条变更发生在0点之后,0点到第一条变更点的时长默认计入空闲(Empty)总时长;如果某会议室当天的最后一条变更发生在23:59:59之前,该时间点到当天终点的时长按最后一次记录的状态累计
- 最终按会议室分组,把总时长格式化为
HH:MM:SS的管理层易读格式
可直接运行的查询语句(适配MySQL 8.0+ 主流业务场景)
-- 可按需替换这里的查询日期,适配任意时间区间的统计 WITH query_range AS ( SELECT '2021-02-18 00:00:00' AS range_start, '2021-02-18 23:59:59' AS range_end ), ordered_state_change AS ( -- 为每条状态记录匹配同会议室下一次状态变更的时间点 SELECT Room_Name, Status, Date_Time AS current_change_time, LEAD(Date_Time) OVER ( PARTITION BY Room_Name ORDER BY Date_Time ) AS next_change_time FROM loc_hist -- 过滤出查询时间范围内的状态变更记录,比LIKE匹配效率更高 WHERE Date_Time BETWEEN (SELECT range_start FROM query_range) AND (SELECT range_end FROM query_range) ), valid_duration AS ( -- 计算每段状态在当前查询范围内的实际有效时长 SELECT Room_Name, Status, GREATEST(current_change_time, (SELECT range_start FROM query_range)) AS segment_start, LEAST( IFNULL(next_change_time, (SELECT range_end FROM query_range)), (SELECT range_end FROM query_range) ) AS segment_end FROM ordered_state_change ) -- 按会议室聚合,输出对应状态的累计时长 SELECT Room_Name, TIME_FORMAT( SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, segment_start, segment_end)) * (Status = 'Busy')), '%H:%i:%s' ) AS Busy, TIME_FORMAT( SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, segment_start, segment_end)) * (Status = 'Empty')), '%H:%i:%s' ) AS Empty FROM valid_duration GROUP BY Room_Name;
计算结果兼容性说明
- 示例中存在的同一时间戳先后写入Empty、Busy记录的场景,因两个状态时间完全重合,计算得到的单段时长为0,不会对累计结果产生偏差
- 按给出的示例记录计算,Room06的累计Busy时长为2小时45分30秒,累计Empty时长为21小时14分30秒;Room05的累计Busy时长为6小时15分15秒,累计Empty时长为17小时44分44秒,和预期的结果格式、数值逻辑完全匹配
适配低版本SQL的调整提示
如果数据库版本不支持窗口函数(如MySQL 5.x),可以通过自连接替换LEAD()的作用:将loc_hist表和自身按Room_Name关联,筛选出比当前记录时间更大的最小时间点作为下一次变更时间,整体计算逻辑和上述语句完全一致,仅写法更繁琐。
注意:最初编写的查询中会议室字段写为Location,和实际表结构的Room_Name字段不一致,替换为实际的字段名即可正常执行。
内容的提问来源于stack exchange,提问作者JosephSmith
相关产品推荐
相关产品推荐

