You need to enable JavaScript to run this app. 如何编写SQL查询统计指定日期内各会议室的忙碌与空闲累计时长
优惠活动
大模型
产品
解决方案
定价
更多

如何编写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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.01 18:36:04