SQL中如何为时序数据的周期分组补充结束时间字段？

我有一份数据采集的时间戳列表，规定时间间隔≤100秒的时间戳归为同一周期，间隔超过100秒则创建新周期。现有Cycles表存储历史周期数据，Data表存储新采集的时间戳。

我已经写了一段SQL可以获取需要新增到Cycles表的新周期编号（Cycle）和周期开始时间（CycleStart），但没法同时拿到周期结束时间（CycleEnd），请问怎么修改查询补充这个字段？

原查询代码：

DECLARE @lastCycle int = (SELECT TOP 1 Cycle FROM Cycles ORDER BY Cycle DESC);
DECLARE @lastCycleEnd datetime = (SELECT TOP 1 CycleEnd FROM Cycles ORDER BY Cycle DESC);
WITH marks AS (
    SELECT datatimestamp, 
    CASE 
        WHEN DATEDIFF(Second, LAG(datatimestamp, 1, DATEADD(Second, -101, datatimestamp)) OVER (ORDER BY datatimestamp), datatimestamp) > 100 
        THEN 1 ELSE 0 
    END AS NextC
    FROM [Data] 
    WHERE datatimestamp > @lastCycleEnd 
)
SELECT @lastCycle + ROW_NUMBER() OVER (ORDER BY d.datatimestamp) AS Cycle, d.datatimestamp AS CycleBegin 
FROM [Data] d
INNER JOIN marks m On m.datatimestamp = d.datatimestamp
WHERE m.NextC = 1

原查询结果示例：

相关表结构及示例数据 #

Cycles表

CREATE TABLE [Cycles](
    [Cycle] [int] NOT NULL,
    [CycleStart] [datetime] NOT NULL,
    [CycleEnd] [datetime] NOT NULL,
 CONSTRAINT [PK_Cycles] PRIMARY KEY CLUSTERED 
(
    [Cycle] DESC
))
INSERT INTO [Cycles] VALUES
(10,'2023-12-04T9:00:00','2023-12-04T10:00:00'),
(11,'2023-12-04T21:00:00','2023-12-04T22:00:00'),
(12,'2023-12-04T23:00:00','2023-12-05T00:00:00')

Data表

CREATE TABLE [Data](
    [datatimestamp] [datetime] NOT NULL,
 CONSTRAINT [PK_Data] PRIMARY KEY NONCLUSTERED 
(
    [datatimestamp] ASC
))
INSERT INTO [Data] VALUES
('2023-12-05T00:05:20'),
('2023-12-05T00:05:21'),
('2023-12-05T00:05:22'),
('2023-12-05T00:10:01'),
('2023-12-05T00:10:02'),
('2023-12-05T00:10:03')

你可以通过为每个时间戳分配周期组ID，再按组聚合获取周期的开始和结束时间，修改后的SQL如下：

DECLARE @lastCycle int = (SELECT TOP 1 Cycle FROM Cycles ORDER BY Cycle DESC);
DECLARE @lastCycleEnd datetime = (SELECT TOP 1 CycleEnd FROM Cycles ORDER BY Cycle DESC);

WITH filtered_data AS (
    -- 过滤出历史周期之后的新数据
    SELECT datatimestamp 
    FROM [Data] 
    WHERE datatimestamp > @lastCycleEnd 
    ORDER BY datatimestamp
),
cycle_groups AS (
    -- 为每个时间戳分配所属的周期组ID
    SELECT 
        datatimestamp,
        SUM(CASE 
                WHEN DATEDIFF(Second, LAG(datatimestamp, 1, DATEADD(Second, -101, datatimestamp)) OVER (ORDER BY datatimestamp), datatimestamp) > 100 
                THEN 1 
                ELSE 0 
            END) OVER (ORDER BY datatimestamp) AS group_id
    FROM filtered_data
),
cycle_boundaries AS (
    -- 按组聚合得到每个周期的开始和结束时间
    SELECT 
        group_id,
        MIN(datatimestamp) AS CycleStart,
        MAX(datatimestamp) AS CycleEnd
    FROM cycle_groups
    GROUP BY group_id
)
-- 生成连续的新周期编号，输出完整周期信息
SELECT 
    @lastCycle + ROW_NUMBER() OVER (ORDER BY CycleStart) AS Cycle,
    CycleStart,
    CycleEnd
FROM cycle_boundaries;

执行结果 #

运行上述查询后，会得到包含完整周期信息的结果：

逻辑说明 #

1. filtered_data：筛选出历史周期结束时间之后的新采集数据，确保只处理未归档的时间戳；
2. cycle_groups：通过SUM() OVER()累加间隔标记值，为每个时间戳分配唯一的周期组ID——每当两个相邻时间戳间隔超过100秒时，组ID加1，实现周期分组；
3. cycle_boundaries：对每个周期组取最小时间戳作为CycleStart，最大时间戳作为CycleEnd；
4. 最后通过ROW_NUMBER()生成与历史周期连续的新周期编号，输出完整的周期信息。

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