如何在SQL Server表中对连续时间戳进行分组？

假设你的表名为TimeRecords，包含字段Timestamps（datetime类型），原始数据如下：

│ Timestamps
──┼────────────────────
1 │ 2022-09-23 15:01:00
2 │ 2022-09-23 15:02:00
3 │ 2022-10-03 14:52:00
4 │ 2022-10-03 14:53:00
5 │ 2022-10-03 14:54:00
6 │ 2022-10-03 14:56:00
7 │ 2022-10-03 14:57:00
8 │ 2022-10-03 14:58:00
9 │ 2022-10-03 14:59:00

需要将所有间隔为1分钟的连续时间戳合并为时间范围，得到如下结果：

│ From                │ To
──┼─────────────────────┼─────────────────────
1 │ 2022-09-23 15:01:00 │ 2022-09-23 15:02:00
2 │ 2022-10-03 14:52:00 │ 2022-10-03 14:54:00
3 │ 2022-10-03 14:56:00 │ 2022-10-03 14:59:00

实现SQL语句 #

WITH GroupedTimes AS (
    SELECT 
        Timestamps,
        -- 标记当前时间与前一个时间的间隔是否超过1分钟，超过则生成新组的起始标记
        SUM(CASE WHEN DATEDIFF(MINUTE, LAG(Timestamps) OVER (ORDER BY Timestamps), Timestamps) = 1 THEN 0 ELSE 1 END) 
        OVER (ORDER BY Timestamps) AS GroupId
    FROM TimeRecords
)
SELECT 
    MIN(Timestamps) AS [From],
    MAX(Timestamps) AS [To]
FROM GroupedTimes
GROUP BY GroupId
ORDER BY [From];

关键逻辑说明 #

1. LAG函数：获取当前行的前一行时间戳，用于计算时间间隔。
2. DATEDIFF函数：计算当前时间与前一个时间的分钟差，若差为1则属于同一组，否则标记为新组。
3. 累计求和生成GroupId：通过SUM() OVER (ORDER BY Timestamps)将连续的时间戳分配到同一个组ID中，每遇到间隔超过1分钟的情况，组ID递增。
4. 分组聚合：按GroupId分组，取每组的最小时间作为范围起始，最大时间作为范围结束。

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