SQL Server CTE中PARTITION BY取分组最大值结果异常问题求解

问题原因 #

C1阶段计算isstart标记时，已经按Item和User分区，仅判断同一个物品、同一个用户下相邻两条记录的时间间隔。但C2阶段计算累计和生成grp分组ID时，没有添加PARTITION BY Item, User约束，导致所有用户、所有物品的isstart标记被放在全局范围内累加，生成的grp是跨用户、跨物品的全局分组。C3阶段按grp分区求最大值时，会把完全不相关的行分到同一个分组，最终结果自然不符合预期。

解决方法 #

修改C2阶段的窗口函数，补充PARTITION BY Item, User即可，保证每个Item+User组合的分组ID独立生成，不会和其他组合的记录混用分组。修改后完整代码如下：

WITH C1 AS
(
    SELECT 
        Item,
        User,
        TimeStart,
        CASE 
            WHEN DATEDIFF(MINUTE, LAG(TimeStart) OVER (PARTITION BY Item, User ORDER BY TimeStart), TimeStart) < 5
                THEN 0
                ELSE 1
        END AS isstart
    FROM
        Log 
),
C2 AS 
(
    SELECT 
        *,
        -- 补充PARTITION BY Item, User，每个物品+用户的分组独立累加
        SUM(isstart) OVER (PARTITION BY Item, User ORDER BY TimeStart ROWS UNBOUNDED PRECEDING) AS grp
    FROM 
        C1
),
C3 AS
(
    SELECT 
        *, 
        MAX(TimeStart) OVER (PARTITION BY Item, User, grp) AS TimeEnd 
    FROM 
        C2
)
SELECT * 
FROM C3

修改后C3阶段的分区内仅包含同一个用户、同一个物品下属于同一个时间区间的记录，求出的TimeEnd为对应区间的最大时间，同时所有原始列都完整保留，不需要使用GROUP BY子句。

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