如何在SQL Server中快速生成相同ES值对应的时间间隔？

嘿，这个需求我之前处理过类似的，咱们可以用SQL Server的窗口函数来高效解决，这是最适合这类连续分组场景的方案，性能也很靠谱。

思路拆解 #

核心是先把连续相同ES值的记录归为同一个组，然后对每个组计算时间范围和间隔：

1. 用LAG()函数判断当前记录和上一条的ES是否一致，标记新组的起始点
2. 通过累积求和生成唯一的组ID，把连续相同ES的记录绑定在一起
3. 按组聚合，计算每组的起始/结束时间，再算出时间间隔

完整SQL代码 #

-- 替换成你的实际表名
WITH GroupedEvents AS (
    SELECT 
        ES,
        TimeStamp,
        -- 标记当前行是否是新组的开始：和上一行ES不同则为1，否则0
        CASE 
            WHEN LAG(ES) OVER (ORDER BY TimeStamp) != ES THEN 1 
            ELSE 0 
        END AS GroupStartFlag
    FROM YourEventTable
),
EventGroups AS (
    SELECT 
        ES,
        TimeStamp,
        -- 累积求和生成组ID，连续相同ES的记录会共享同一个ID
        SUM(GroupStartFlag) OVER (
            ORDER BY TimeStamp 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS GroupID
    FROM GroupedEvents
)
-- 最终聚合计算时间间隔
SELECT 
    ES,
    MIN(TimeStamp) AS IntervalStart,
    MAX(TimeStamp) AS IntervalEnd,
    -- 这里用秒为单位，可根据需求换成MILLISECOND、MINUTE等
    DATEDIFF(SECOND, MIN(TimeStamp), MAX(TimeStamp)) AS DurationInSeconds
FROM EventGroups
GROUP BY ES, GroupID
ORDER BY IntervalStart;

代码说明 #

• LAG(ES) OVER (ORDER BY TimeStamp)：获取当前记录的前一条ES值，和当前值对比，判断是否开启新组
• 累积求和的窗口函数：把所有之前的GroupStartFlag加起来，这样每个连续相同ES的组会得到唯一的GroupID
• DATEDIFF：根据你的需求调整时间单位，比如要毫秒就用MILLISECOND，分钟用MINUTE

性能优化提示 #

如果你的表数据量很大，记得给TimeStamp列创建非聚集索引，这样窗口函数的排序操作会直接利用索引，避免额外的排序开销，大幅提升查询速度。

用你提供的示例数据测试，会得到如下结果（截取部分）：

完全符合你要的连续记录时间间隔需求~

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