使用SQL按30分钟间隔计算呼叫等待时长(Hold Time)

需求说明 #

按30分钟时间间隔统计呼叫等待时长（Hold Time，单位为秒），最终需按ID、StartDate、30分钟区间的起止时间汇总holdtime。

输入数据 #

输入表包含Date、ID、StartDate、StartTime、holdtime字段，示例数据如下：

期望输出 #

按ID、StartDate、30分钟区间起止时间汇总holdtime，示例输出如下：

现有尝试问题 #

曾使用WHILE循环实现，但逻辑复杂且未得到预期结果，循环代码如下：

while (@eventDurationMins>0)
    begin
        set @eventDurationInIntervalMins = cast(@intervalEndTime-@eventStartTime as float)*24*60 ;
        if @eventDurationMins<@eventDurationInIntervalMins
                set @eventDurationInIntervalMins = @eventDurationMins ;
        insert into @retTable
        select @intervalStartTime,@intervalEndTime,@eventDurationInIntervalMins
        set @eventDurationMins = @eventDurationMins - @eventDurationInIntervalMins ;
        set @eventStartTime = @intervalEndTime;
        set @intervalStartTime = @intervalEndTime;
        set @intervalEndTime = dateadd(minute,@intervalMins,@intervalEndTime);
    end;

优化SQL解决方案 #

无需循环，通过日期截断的方式将每条记录映射到对应的30分钟区间，再分组求和即可。以下是适用于SQL Server的实现代码：

SELECT 
    ID,
    StartDate,
    -- 生成30分钟区间的起始时间，格式匹配示例输出
    FORMAT(DATEADD(MINUTE, DATEDIFF(MINUTE, 0, StartTime) / 30 * 30, 0), 'HH:mm:ss.fff') AS intervalStartTime,
    -- 生成30分钟区间的结束时间，格式匹配示例输出
    FORMAT(DATEADD(MINUTE, (DATEDIFF(MINUTE, 0, StartTime) / 30 + 1) * 30, 0), 'HH:mm:ss.fffffff') AS intervalStoptTime,
    SUM(holdtime) AS holdtime
FROM 
    YourTableName -- 替换为实际表名
GROUP BY 
    ID,
    StartDate,
    -- 按30分钟区间分组
    DATEDIFF(MINUTE, 0, StartTime) / 30
ORDER BY 
    ID,
    StartDate,
    intervalStartTime;

方案说明 #

1. 区间计算逻辑：
   • 用DATEDIFF(MINUTE, 0, StartTime)计算从SQL Server基准时间（1900-01-01 00:00:00）到当前StartTime的总分钟数。
   • 除以30取整后再乘以30，得到该时间所属30分钟区间的起始分钟数，再通过DATEADD转换为时间格式。
   • 区间结束时间为起始时间加30分钟。
2. 分组求和：按ID、StartDate和计算出的区间分组，对holdtime求和得到每个区间的总等待时长。
3. 优势：基于集合操作，执行效率远高于循环，逻辑简洁易维护，直接匹配需求输出格式。

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