如何基于上一组StartDate为TSQL分组生成EndDate

原TSQL查询 #

WITH CTE AS 
(
    SELECT 
        [id],
        residentId,
        LoadDateTime AS StartDate,
        DENSE_RANK() OVER (PARTITION BY ID ORDER BY LoadDateTime DESC) AS Run
    FROM 
        [ResManPStage].[Residencies] WITH (NOLOCK)
    WHERE  
        residentId IN (4719)
) 
SELECT DISTINCT 
    id, residentId, StartDate, Run
FROM
    cte

当前查询结果 #

期望输出 #

需求说明 #

需要将上一组的StartDate设为当前组的EndDate，分组依据为id、Run和StartDate的组合。单独处理单行分组时可以用LAG或LEAD函数，但面对同一分组有多行数据的场景，无法直接实现。

解决方案 #

先提取分组后的唯一记录并计算对应EndDate，再与原数据关联，即可为每一行匹配到正确的EndDate：

WITH CTE AS 
(
    SELECT 
        [id],
        residentId,
        LoadDateTime AS StartDate,
        DENSE_RANK() OVER (PARTITION BY ID ORDER BY LoadDateTime DESC) AS Run
    FROM 
        [ResManPStage].[Residencies] WITH (NOLOCK)
    WHERE  
        residentId IN (4719)
),
GroupedDates AS (
    SELECT DISTINCT
        id,
        residentId,
        StartDate,
        Run,
        -- 按Run升序，获取下一个分组的StartDate作为当前组的EndDate
        LEAD(StartDate) OVER (PARTITION BY id, residentId ORDER BY Run) AS EndDate
    FROM CTE
)
SELECT 
    c.id,
    c.residentId,
    c.StartDate,
    c.Run,
    gd.EndDate
FROM CTE c
JOIN GroupedDates gd 
    ON c.id = gd.id 
    AND c.residentId = gd.residentId 
    AND c.StartDate = gd.StartDate 
    AND c.Run = gd.Run

逻辑说明 #

1. GroupedDates CTE：通过DISTINCT提取每个分组的唯一记录，使用LEAD函数获取当前组的下一个分组的StartDate，作为当前组的EndDate（最小组的EndDate为NULL）。
2. 关联查询：将原CTE与GroupedDates按分组字段关联，让原数据中的每一行都匹配到对应分组的EndDate。

内容的提问来源于stack exchange，提问作者Sai Abhiram Inapala