SQL查询：统计时间连续序列的起始点及长度（SQL Server 2012+）

针对你需要按用户分组，识别间隔1小时的连续时间序列（且新一天00:00自动断序列）的需求，以下是高效的窗口函数解决方案，替代逐行处理的低效方案：

核心思路 #

利用日期截断将跨天记录强制分组，同时通过窗口函数判断相邻记录的时间间隔，生成分组标识，最终通过分组聚合得到序列起始和长度。

完整代码示例 #

假设你的表名为UserTimeLogs，字段为Usr（用户ID）和Date（日期时间）：

WITH RankedData AS (
    SELECT
        Usr,
        Date,
        -- 生成分组标识：同一用户+同一天内，相邻记录间隔1小时则归为同组，否则开启新组
        SUM(
            CASE 
                -- 组内第一条记录，直接标记为新组
                WHEN LAG(Date) OVER (PARTITION BY Usr, CAST(Date AS DATE) ORDER BY Date) IS NULL 
                    THEN 1
                -- 与上一条间隔正好1小时，归为同组
                WHEN DATEDIFF(HOUR, LAG(Date) OVER (PARTITION BY Usr, CAST(Date AS DATE) ORDER BY Date), Date) = 1
                    THEN 0
                -- 间隔超过1小时，开启新组
                ELSE 1
            END
        ) OVER (PARTITION BY Usr, CAST(Date AS DATE) ORDER BY Date) AS GroupId
    FROM UserTimeLogs
)
SELECT
    Usr,
    MIN(Date) AS SequenceStart,
    COUNT(*) AS SequenceLength
FROM RankedData
GROUP BY Usr, CAST(Date AS DATE), GroupId
ORDER BY Usr, SequenceStart;

代码解释 #

1. CTE RankedData：

   • PARTITION BY Usr, CAST(Date AS DATE)：按用户+日期分组，确保新一天的00:00自动成为新序列起始，跨天记录不会合并。
   • LAG(Date)：获取当前用户同一天内的上一条记录时间，用于判断间隔是否符合1小时规则。
   • SUM() OVER()：累计分组标识，同一连续序列的记录会得到相同的GroupId。

2. 最终聚合：

   • 按用户、日期、组ID分组，取每组的最小时间作为序列起始点，统计记录数得到序列长度。

性能优化建议 #

为窗口函数创建联合索引，避免全表扫描：

CREATE NONCLUSTERED INDEX IX_UserTimeLogs_Usr_Date ON UserTimeLogs(Usr, Date);

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