如何在Azure SQL中按秒递增生成日期时间列表并分组聚合

需求说明 #

基于原始数据的StatusDateTimeGMT列，每秒递增生成对应stateDuration行数的时间列表；将生成的时间列四舍五入到最近的900秒（15分钟），最终按用户、状态和15分钟间隔分组统计持续时长。

原始数据示例：

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

解决方案SQL #

以下脚本包含时区转换（GMT转UTC+2）、秒级时间生成、15分钟间隔四舍五入及分组统计：

WITH AgentActivityWithTZ AS (
    -- 转换GMT时间为UTC+2时区
    SELECT 
        userid,
        statuskey,
        stateDuration,
        DATEADD(hour, 2, StatusDateTimeGMT) AS StatusDateTimeLocal,
        DATEADD(hour, 2, EndDateTimeGMT) AS EndDateTimeLocal
    FROM AgentActivityLog
    WHERE 
        userid = 'Abanoub.Sanad'
        AND StateDuration > 0
        AND DATEADD(hour, 2, StatusDateTimeGMT) >= '2022-09-28' 
        AND DATEADD(hour, 2, StatusDateTimeGMT) < '2022-09-30'
),
Numbers AS (
    -- 生成足够覆盖最大stateDuration的数字序列（效率优于递归CTE）
    SELECT TOP (SELECT MAX(stateDuration) FROM AgentActivityWithTZ)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
    FROM sys.all_columns c1
    CROSS JOIN sys.all_columns c2
),
TimeSeries AS (
    -- 生成每秒递增的时间序列，行数与stateDuration一致
    SELECT 
        a.userid,
        a.statuskey,
        DATEADD(second, n.n, a.StatusDateTimeLocal) AS new_time
    FROM AgentActivityWithTZ a
    JOIN Numbers n ON n.n < a.stateDuration
),
RoundedTime AS (
    -- 将时间四舍五入到最近的900秒（15分钟）
    SELECT 
        userid,
        statuskey,
        new_time,
        DATEADD(second, ROUND(DATEDIFF(second, 0, new_time) / 900.0, 0) * 900, 0) AS rounded_15min
    FROM TimeSeries
)
-- 分组统计每个15分钟间隔的持续秒数
SELECT 
    userid,
    statuskey,
    rounded_15min AS interval_start,
    DATEADD(second, 900, rounded_15min) AS interval_end,
    COUNT(*) AS duration_seconds
FROM RoundedTime
GROUP BY userid, statuskey, rounded_15min
ORDER BY rounded_15min;

脚本说明 #

1. 时区转换：将原始GMT时间转换为UTC+2时区时间，与原查询逻辑保持一致；
2. 数字序列生成：利用系统表sys.all_columns交叉连接生成数字序列，避免递归CTE在大数据量下的性能瓶颈；
3. 秒级时间序列：通过数字序列作为秒数偏移量，生成对应stateDuration行数的每秒递增时间；
4. 15分钟间隔四舍五入：计算时间到纪元时间的秒数，除以900后四舍五入，再转换回datetime类型得到最近的15分钟起始点；
5. 分组统计：按用户、状态和15分钟间隔分组，统计每个间隔内的持续秒数（即行数）。

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