在慢变化维度表中统计最新记录为指定值的用户连续值次数

场景与原始数据 #

现有一张包含User、InDate、Flag、Type字段的表，数据如下：

User InDate   Flag  Type
1   2023-06-01  E   A
2   2023-06-01  E   AS
3   2023-06-01  E   A
4   2023-06-01  I   NULL
1   2023-03-01  E   A
2   2023-03-01  E   AS
3   2023-03-01  E   A
4   2023-03-01  I   AS
1   2022-12-01  I   NULL
2   2022-12-01  E   AS
3   2022-12-01  E   A
4   2022-12-01  E   AS

需求 #

统计截至2023-06-30，**最新记录Type为"AS"**的用户，从最新记录往前连续的"AS"次数。

尝试方案及问题 #

使用LAG函数对比上一条记录的Type，标记连续项后求和，代码如下：

WITH Lagged AS (
    SELECT [User],
           Indate,
           [Type],
           Flag,
           LAG([Type]) OVER (PARTITION BY [User] ORDER BY Indate) AS prev_val
    FROM #MYtest
    WHERE InDate >= '20220601'
    and InDate < getdate())

SELECT [User], COUNT(*)  as consecutive_count
FROM (
    SELECT [User],
           CASE WHEN [Type] = prev_val and [Type] like '%AS%' THEN 1 ELSE 0 END AS consecutive_indicator
    FROM Lagged
) AS T
WHERE consecutive_indicator = 1
GROUP BY [User];

得到结果：

User consecutive_count
2   2
4   1

但期望结果应为：

User consecutive_count
2    3

问题在于：

1. 用户4的最新记录Type是NULL，不符合"最新记录为AS"的筛选条件，却被统计进来；
2. 统计的连续次数少了1（用户2有3条连续AS，结果只统计了2次）。

尝试过将计数加1并关联原表筛选最新状态为AS的用户，但遇到边缘案例：

User,InDate,Flag,Type
5,2023-06-01,E,AS
5,2023-03-01,E,A
5,2022-12-01,E,AS
5,2022-09-01,E,AS

该用户最新记录是AS，但上一条是A，实际连续次数应为1，但之前的方法会得到2（错误统计了历史的连续AS段）。

正确解决方案 #

通过分组连续段的方式，精准定位最新的连续AS序列，代码如下：

WITH UserRecords AS (
    SELECT 
        [User],
        InDate,
        [Type],
        -- 标记每个用户的最新记录
        ROW_NUMBER() OVER(PARTITION BY [User] ORDER BY InDate DESC) AS rn,
        -- 按日期倒序累加，遇到非AS或NULL则分组ID+1，最新的连续AS段分组ID为0
        SUM(CASE WHEN [Type] <> 'AS' OR [Type] IS NULL THEN 1 ELSE 0 END) 
            OVER(PARTITION BY [User] ORDER BY InDate DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM #MYTest
    WHERE InDate <= '2023-06-30' -- 截至指定日期
),
LatestASUsers AS (
    -- 筛选出最新记录为AS的用户
    SELECT [User]
    FROM UserRecords
    WHERE rn = 1 AND [Type] = 'AS'
)
SELECT 
    ur.[User],
    COUNT(*) AS consecutive_count
FROM UserRecords ur
JOIN LatestASUsers lau ON ur.[User] = lau.[User]
WHERE ur.group_id = 0 -- 仅统计最新的连续AS段
GROUP BY ur.[User];

逻辑说明 #

1. UserRecords中：
   • rn标记每个用户的最新记录（倒序第1条）；
   • group_id按日期倒序累加，每次遇到非AS或NULL时分组ID加1，这样最新的连续AS序列的group_id始终为0，历史非连续的AS段group_id会大于0。
2. LatestASUsers筛选出最新记录为AS的用户，确保只统计符合条件的用户。
3. 最后关联两个CTE，统计group_id=0的记录数，即为该用户从最新记录往前连续的AS次数。

测试验证 #

• 对用户2：3条连续AS的group_id均为0，统计结果为3，符合预期；
• 对用户4：最新记录是NULL，不会被LatestASUsers筛选，排除在结果外；
• 对边缘案例用户5：仅最新的1条AS的group_id为0，统计结果为1，符合实际需求。

数据重现代码 #

CREATE TABLE #MYTest([User] int, InDate date,Flag varchar(1),Type  varchar(2))
INSERT INTO #MYTest
values(1,'2023-06-01','E','A'),    
(2,'2023-06-01','E','AS'),    
(3,'2023-06-01','E','A'),    
(4,'2023-06-01','I',NULL),    
(1,'2023-03-01','E','A'),    
(2,'2023-03-01','E','AS'),    
(3,'2023-03-01','E','A'),    
(4,'2023-03-01','I','AS'),
(1,'2022-12-01','I',NULL),    
(2,'2022-12-01','E','AS'),    
(3,'2022-12-01','E','A'),    
(4,'2022-12-01','E','AS')

-- 可选：添加边缘案例用户5的数据
INSERT INTO #MYTest
values(5,'2023-06-01','E','AS'),
(5,'2023-03-01','E','A'),
(5,'2022-12-01','E','AS'),
(5,'2022-09-01','E','AS')

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