如何用SQL遍历时序数据，识别事件关联的连续服务集群

不需要用循环，SQL的窗口函数就能高效实现这个需求，核心是通过分组排序+累计条件判断来识别连续的服务集群。

实现逻辑 #

1. 按Event_Number分组，每个组内按Sequel_Service_Start_Date排序（确保服务按时间顺序处理）
2. 对每个服务，计算两个关键间隔：
   • 与所属事件结束日期的间隔（DATEDIFF(day, Event_End, Sequel_Service_Start_Date)）
   • 与前一个服务结束日期的间隔（用LAG窗口函数获取上一个服务的结束日期，再计算间隔）
3. 用累计标记判断关联范围：只要当前服务满足「和事件间隔≤3天」或者「和前一个在范围内的服务间隔≤3天」，就标记为1；一旦某个服务不符合条件，后续所有服务都标记为0。

完整SQL代码 #

WITH RankedServices AS (
    SELECT 
        *,
        -- 获取前一个服务的结束日期
        LAG(Sequel_Service_End_Date) OVER (PARTITION BY Event_Number ORDER BY Sequel_Service_Start_Date) AS Prev_Service_End,
        -- 计算与事件结束的间隔天数
        DATEDIFF(day, Event_End, Sequel_Service_Start_Date) AS Days_From_Event,
        -- 计算与前一个服务结束的间隔天数
        DATEDIFF(day, LAG(Sequel_Service_End_Date) OVER (PARTITION BY Event_Number ORDER BY Sequel_Service_Start_Date), Sequel_Service_Start_Date) AS Days_From_Prev_Service
    FROM #Table
),
ScopeMarkers AS (
    SELECT 
        *,
        -- 逐行判断是否属于关联范围
        CASE 
            WHEN ROW_NUMBER() OVER (PARTITION BY Event_Number ORDER BY Sequel_Service_Start_Date) = 1 
                THEN IIF(Days_From_Event <= 3, 1, 0)
            ELSE IIF(Days_From_Prev_Service <= 3 AND LAG(In_Scope_Flag) OVER (PARTITION BY Event_Number ORDER BY Sequel_Service_Start_Date) = 1, 1, 0)
        END AS In_Scope_Flag
    FROM RankedServices
)
-- 方案1：更新原表的In_Scope列
UPDATE #Table
SET In_Scope = sm.In_Scope_Flag
FROM #Table t
JOIN ScopeMarkers sm ON t.Event_Number = sm.Event_Number AND t.Sequel_Service_Number = sm.Sequel_Service_Number;

-- 方案2：生成仅包含关联服务的新表
SELECT * INTO New_Event_Services FROM ScopeMarkers WHERE In_Scope_Flag = 1;

结果验证 #

执行后，#Table的In_Scope列会和示例完全匹配：

• Event 1的前3个服务标记为1，第4个为0
• Event 2的前2个服务标记为1，后续全部为0

为什么不用循环？ #

循环在SQL中属于逐行处理，效率远低于集合式操作，尤其是数据量较大时，窗口函数的方案性能优势明显，且代码更简洁易维护。

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