SQL逐行获取规则Entry/Exit时间 优化Gaps and Islands查询性能

• 需识别数据中各规则的进入时间（Entry time）与退出时间（Exit time）
• 退出时间计算逻辑：若某规则未出现在下一个活动中，则对应时间即为该规则的退出时间

测试表结构、样例数据、期望结果的SQL定义如下：

DECLARE @T AS TABLE
(
    Visit_ID    INT,
    Line        INT,
    Rule_ID     INT,
    Activity_DTTM datetime
)
        

insert into @T VALUES
(123, 1, 100072, '2022-07-01 03:05:00.000' ),
(123, 1, 100173, '2022-07-01 03:05:00.000' ),
(123, 1, 718719, '2022-07-01 03:05:00.000' ),
(123, 2, 100072, '2022-07-01 04:05:00.000' ),
(123, 2, 718719, '2022-07-01 04:05:00.000' ),
(123, 3, 100072, '2022-07-01 06:00:00.000' ),
(123, 4, 100072, '2022-07-02 02:10:00.000' ),
(123, 4, 100173, '2022-07-02 02:10:00.000' )


DECLARE @Desiredresult AS TABLE
(
    Visit   INT,
    Rule_ID INT,
    Entry_Time datetime,
    Exit_Time datetime
)

insert into @Desiredresult VALUES
(123,100072,'2022-07-01 03:05:00.000', null ),
(123,100173,'2022-07-01 03:05:00.000', '2022-07-01 04:05:00.000'),
(123,718719,'2022-07-01 03:05:00.000', '2022-07-01 06:00:00.000'),
(123,100173,'2022-07-02 02:10:00.000', null)


select * from @Desiredresult

• 该需求是否属于Gaps and Islands问题？
• 之前编写的SQL执行效率极低，处理1000万行数据需要耗时2小时以上，当前使用的查询语句如下：

select me.Visit_ID
,me.Rule_ID
, MIN(CASE WHEN prev_split.line is null then me.ACTIVITY_DTTM else null end) as ENTRY_DTTM
, MAX(CASE WHEN next_split.line is null
        then next_flat.Activity_DTTM                
        else null end
        ) as EXIT_DTTM

from @T me
left join @T prev_flat on me.Visit_ID = prev_flat.Visit_ID and me.LINE-1 = prev_flat.line -- minus 1    
left join @T next_flat on me.Visit_ID = next_flat.Visit_ID and me.LINE+1 = prev_flat.line -- plus 1

left join @T prev_split on me.Visit_ID = prev_split.Visit_ID  and me.RULE_ID = prev_split.RULE_ID and me.LINE-1 = prev_split.line -- minus 1
left join @T next_split on me.Visit_ID = next_split.Visit_ID  and me.RULE_ID = next_split.RULE_ID and me.LINE+1 = next_split.line -- plus 1

group by me.Visit_ID
,me.Rule_ID

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