T-SQL如何获取最后一组IsActive=1值的首次出现记录

需求背景 #

SQL Server环境下存在TableA、TableB_S两张表，通过UserId字段关联，需要提取每个用户最后一段连续IsActive = 1记录的最早条目。
以TableB_S的示例数据为例，目标返回记录为：

示例表TableB_S完整数据 #

原有查询逻辑仅能返回IsActive=1的最新一条记录（即2022-03-23的条目），不符合需求，原代码如下：

select a.*, ca.UserId, ca.Date
from TableA a
cross apply (select top 1 s.UserId, s.Date 
            from TableB_S s where s.UserId = a.UserId
        order by s.Date desc) ca  

实现代码 #

通过窗口函数实现连续区间分组，定位最后一段活跃区间后取区间首条记录，代码兼容多用户场景：

WITH ContinuousGroup AS (
    SELECT
        UserId,
        IsActive,
        Date,
        -- 连续相同IsActive值的记录会得到相同的分组标记
        ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date)
        - ROW_NUMBER() OVER (PARTITION BY UserId, IsActive ORDER BY Date) AS GroupTag
    FROM TableB_S
),
LastActiveGroup AS (
    SELECT
        UserId,
        -- 标记每个用户最后一段IsActive=1的分组值
        MAX(CASE WHEN IsActive = 1 THEN GroupTag END) OVER (PARTITION BY UserId) AS TargetGroupTag
    FROM ContinuousGroup
)
SELECT a.*, res.UserId, res.IsActive, res.Date
FROM TableA a
CROSS APPLY (
    SELECT TOP 1 cg.UserId, cg.IsActive, cg.Date
    FROM ContinuousGroup cg
    INNER JOIN LastActiveGroup lag
        ON cg.UserId = lag.UserId
        AND cg.GroupTag = lag.TargetGroupTag
    WHERE cg.UserId = a.UserId
    ORDER BY cg.Date ASC
) res

核心逻辑说明 #

• 利用两个ROW_NUMBER()的差值做连续值分群：同一用户下按时间排序的全局行号，减去同用户、同IsActive值下按时间排序的行号，连续相同IsActive的记录会获得相同的GroupTag，自动切分不同的连续状态段
• 提取每个用户所有IsActive=1段中最靠后的分组标记，即为最后一段连续活跃的分组
• 关联TableA后取目标分组内时间最早的记录，就是最后一段连续活跃状态的起始条目

上述代码针对示例数据可准确返回目标结果，若用户最后一段状态为IsActive=0则不会返回对应活跃记录，符合业务逻辑中“最后一组连续IsActive=1”的筛选要求。

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