SQL Azure中基于时长生成秒级时间行并按15分钟间隔分组统计

需求说明 #

• 基于StatusDuration字段，从StatusDateTimeGMT开始每秒递增生成新行，新增列New_time
• 新增Interval列，将New_time取整至最近的15分钟间隔
• 按UserId、StatusKey、Interval分组，统计每个分组内New_time的数量

SQL Azure 原始数据 #

userid          statuskey   stateDuration   StatusDateTimeGMT     EndDateTimeGMT
Abanoub.Sanad   available   4656            2022-09-29 07:29:16   2022-09-29 08:46:52
Abanoub.Sanad   available   24626           2022-09-29 10:12:26   2022-09-29 17:02:52
Abanoub.Sanad   available   9030            2022-09-29 17:18:23   2022-09-29 19:48:53
Abanoub.Sanad   available   33647           2022-09-29 23:04:07   2022-09-30 08:24:54

期望输出效果 #

输出按userid、statuskey、15分钟间隔分组，统计每个间隔内的有效秒数。例如：

• 07:15-07:30区间统计从07:29:16到07:30:00的秒数
• 完整15分钟区间统计900秒，最后一个区间统计到EndDateTimeGMT的剩余秒数

逻辑对比说明 #

Excel中通过生成每秒时间序列、取整15分钟后透视统计；SQL需实现相同逻辑：先生成时间序列，再按15分钟间隔分组计算秒数总和。

当前使用的查询语句（中文注释版） #

WITH cte1 AS (
    SELECT 
        userid,
        statuskey,
        stateDuration,
        -- 转换时区（加2小时）
        DATEADD(HOUR, 2, StatusDateTimeGMT) AS StatusDateTimeGMT,
        DATEADD(HOUR, 2, EndDateTimeGMT) AS EndDateTimeGMT,
        -- 计算当前时间所属的15分钟起始间隔（向下取整）
        CAST(FLOOR(CAST(DATEADD(HOUR, 2, StatusDateTimeGMT) AS FLOAT) * 96) / 96 AS DATETIME) AS interval,
        -- 计算当前15分钟间隔的结束时间
        CAST(CEILING(CAST(DATEADD(HOUR, 2, StatusDateTimeGMT) AS FLOAT) * 96) / 96 AS DATETIME) AS interval_end_date
    FROM AgentActivityLog
    WHERE 
        -- 过滤时区转换后的时间范围
        DATEADD(HOUR, 2, StatusDateTimeGMT) >= '2022-09-28' 
        AND DATEADD(HOUR, 2, StatusDateTimeGMT) < '2022-09-30' 
        AND StateDuration > 0 
        AND userid = 'Abanoub.Sanad'
),
cte2 AS (
    -- 初始加载第一个15分钟间隔数据
    SELECT userid, statuskey, EndDateTimeGMT, StatusDateTimeGMT, interval, interval_end_date FROM cte1
    UNION ALL
    -- 递归生成后续的15分钟间隔，直到间隔结束时间接近记录结束时间
    SELECT 
        userid, 
        statuskey, 
        EndDateTimeGMT, 
        interval_end_date AS StatusDateTimeGMT,
        DATEADD(SECOND, 900, interval) AS interval,
        DATEADD(SECOND, 900, interval_end_date) AS interval_end_date
    FROM cte2 
    WHERE DATEADD(SECOND, 15, interval_end_date) < EndDateTimeGMT
)
-- 统计每个15分钟间隔内的有效秒数
SELECT 
    userid,
    statuskey,
    interval,
    [Duration] = CASE 
        -- 完整15分钟间隔，计算起始到间隔结束的秒数
        WHEN interval_end_date < EndDateTimeGMT THEN DATEDIFF(SECOND, StatusDateTimeGMT, interval_end_date)
        -- 最后一个不完整间隔，计算起始到记录结束的秒数
        ELSE DATEDIFF(SECOND, StatusDateTimeGMT, EndDateTimeGMT)
    END
FROM cte2
ORDER BY interval

内容的提问来源于stack exchange，提问作者Mahmoud Badr