基于动态时间间隔为SQL Server表分配GroupID的技术问题

你遇到的是典型的时间会话分组问题，单纯用LAG()/LEAD()只能对比相邻行，无法追踪整个分组的时间边界，以下是两种符合需求的实现方案，根据你的实际业务选择：

方案1：同连续时间区间的行共享同一GroupID #

适用于将同一患者内相邻间隔≤60分钟的记录归为同一分组，GroupID唯一标识分组（新分组自动递增）。

实现代码 #

WITH GroupStartFlags AS (
    SELECT
        PatientCSN,
        BPReading,
        DateTime AS RecordDateTime,
        -- 标记新分组起始行：患者首条记录，或与上一条间隔超过60分钟
        CASE
            WHEN LAG(DateTime) OVER (PARTITION BY PatientCSN ORDER BY DateTime) IS NULL
                 OR DATEDIFF(MINUTE, LAG(DateTime) OVER (PARTITION BY PatientCSN ORDER BY DateTime), DateTime) > 60
            THEN 1
            ELSE 0
        END AS IsNewGroup
    FROM PatientBloodPressure
),
AssignGroupIDs AS (
    SELECT
        PatientCSN,
        BPReading,
        RecordDateTime,
        -- 累积求和生成GroupID，同一连续区间内的行共享相同ID
        SUM(IsNewGroup) OVER (
            PARTITION BY PatientCSN
            ORDER BY RecordDateTime
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS GroupID
    FROM GroupStartFlags
)
SELECT * FROM AssignGroupIDs
ORDER BY PatientCSN, RecordDateTime;

工作原理 #

1. 标记分组起始：用LAG()窗口函数获取同一患者的上一条记录时间，判断是否需要开启新分组，符合条件则标记为IsNewGroup=1。
2. 生成分组ID：通过SUM() OVER()的累积求和逻辑，在同一患者范围内按时间顺序累加标记值，自动生成连续的分组ID——同一时间连续区间的行求和结果一致，新分组则求和值加1。

方案2：组内每行GroupID依次递增（新组重置为1） #

如果你的需求是：同一分组内的行GroupID从1开始依次递增，当某行与当前分组起始行间隔超过60分钟时，开启新分组并重置GroupID为1，用递归CTE实现：

实现代码 #

WITH OrderedRecords AS (
    SELECT
        PatientCSN,
        BPReading,
        DateTime AS RecordDateTime,
        -- 给每个患者的记录按时间排序编号
        ROW_NUMBER() OVER (PARTITION BY PatientCSN ORDER BY DateTime) AS RowNum
    FROM PatientBloodPressure
),
RecursiveGroups AS (
    -- 递归起点：每个患者的第一条记录，初始化分组信息
    SELECT
        PatientCSN,
        BPReading,
        RecordDateTime,
        RowNum,
        1 AS GroupID,
        RecordDateTime AS GroupStartDateTime
    FROM OrderedRecords
    WHERE RowNum = 1

    UNION ALL

    -- 递归处理后续行
    SELECT
        o.PatientCSN,
        o.BPReading,
        o.RecordDateTime,
        o.RowNum,
        -- 判断是否重置GroupID：间隔超60分钟则重置为1，否则递加
        CASE
            WHEN DATEDIFF(MINUTE, r.GroupStartDateTime, o.RecordDateTime) > 60
            THEN 1
            ELSE r.GroupID + 1
        END AS GroupID,
        -- 更新分组起始时间：新分组用当前行时间，否则沿用原起始
        CASE
            WHEN DATEDIFF(MINUTE, r.GroupStartDateTime, o.RecordDateTime) > 60
            THEN o.RecordDateTime
            ELSE r.GroupStartDateTime
        END AS GroupStartDateTime
    FROM OrderedRecords o
    JOIN RecursiveGroups r
        ON o.PatientCSN = r.PatientCSN
        AND o.RowNum = r.RowNum + 1
)
SELECT PatientCSN, BPReading, RecordDateTime, GroupID
FROM RecursiveGroups
ORDER BY PatientCSN, RecordDateTime
OPTION (MAXRECURSION 0); -- 处理大量数据时需设置，0表示无递归上限

工作原理 #

1. 排序编号：先给每个患者的记录按时间生成连续编号，用于递归时关联上一行。
2. 递归初始化：以每个患者的第一条记录为初始分组，GroupID设为1并记录分组起始时间。
3. 逐行迭代：对比当前行与分组起始时间的间隔，决定是否开启新分组，同时更新GroupID和分组起始时间。

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