SQL Server按10分钟时段统计用户连接数的实现方案

我有一个名为UserConnections的SQL Server表，结构包含ID、User、From、To字段，具体数据如下：

需要生成前一天的汇总表，统计每个10分钟时段内的在线用户数，时段需覆盖全天（含用户未连接的时段，计数为0）。统计逻辑为：当连接记录的[From] ≤ 时段开始时间且[To] ≥ 时段结束时间时，计入统计。我能处理统计逻辑，但不清楚如何生成10分钟时段序列，恳请指导。

期望生成的汇总表示例如下：

方法1：递归CTE生成时段序列 #

递归CTE是SQL Server生成连续时间序列的常用方式，以下代码可生成前一天从00:00:00到23:50:00的所有10分钟时段：

WITH TimePeriods AS (
    -- 起始时段：前一天的00:00:00
    SELECT CAST(DATEADD(DAY, -1, GETDATE()) AS DATE) AS PeriodStart
    UNION ALL
    -- 递归生成后续时段，每次递增10分钟
    SELECT DATEADD(MINUTE, 10, PeriodStart)
    FROM TimePeriods
    -- 终止条件：时段不超过前一天的23:50:00
    WHERE PeriodStart < DATEADD(MINUTE, -10, DATEADD(DAY, 0, CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)))
)
SELECT 
    PeriodStart,
    DATEADD(MINUTE, 10, PeriodStart) AS PeriodEnd
FROM TimePeriods
ORDER BY PeriodStart
OPTION (MAXRECURSION 144); -- 全天共144个10分钟时段（24*6），设置递归上限足够

方法2：数字表生成时段序列 #

如果有预定义的数字表（包含0到143的连续数字），可以更高效生成序列：

假设存在Numbers表，含Number字段（值0-143）：

SELECT 
    DATEADD(MINUTE, Number * 10, CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)) AS PeriodStart,
    DATEADD(MINUTE, (Number + 1) * 10, CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)) AS PeriodEnd
FROM Numbers
WHERE Number < 144
ORDER BY PeriodStart

无现成数字表时，可临时生成：

WITH Numbers AS (
    SELECT TOP 144 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS Number
    FROM sys.all_columns
)
SELECT 
    DATEADD(MINUTE, Number * 10, CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)) AS PeriodStart,
    DATEADD(MINUTE, (Number + 1) * 10, CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)) AS PeriodEnd
FROM Numbers
ORDER BY PeriodStart

结合统计逻辑的完整查询 #

将时段序列与UserConnections表关联，完成在线用户数统计：

WITH TimePeriods AS (
    SELECT CAST(DATEADD(DAY, -1, GETDATE()) AS DATE) AS PeriodStart
    UNION ALL
    SELECT DATEADD(MINUTE, 10, PeriodStart)
    FROM TimePeriods
    WHERE PeriodStart < DATEADD(MINUTE, -10, DATEADD(DAY, 0, CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)))
)
SELECT 
    tp.PeriodStart,
    COUNT(DISTINCT uc.[User]) AS [User Count]
FROM TimePeriods tp
LEFT JOIN UserConnections uc
    ON uc.[From] <= tp.PeriodStart
    AND uc.[To] >= DATEADD(MINUTE, 10, tp.PeriodStart)
GROUP BY tp.PeriodStart
ORDER BY tp.PeriodStart
OPTION (MAXRECURSION 144);

内容的提问来源于stack exchange，提问作者Alistair Frith