如何改写并简化使用Lead与Partition By的SQL查询

原查询核心逻辑：按CLIENT_ID分区，当当前记录与下一条记录的GROUP_NM、SNP_CD完全匹配，且当前记录END_DT与下一条记录START_DT间隔1天时，将当前记录的END_DT和DSNP_CODE替换为下一条记录的值。原查询存在重复调用窗口函数的问题，以下提供几种更简洁高效的替代方案：

方案1：用CTE预计算LEAD值（推荐） #

把重复的LEAD窗口函数计算统一放到CTE中，减少重复计算量，同时让代码结构更清晰：

WITH HR_With_Lead AS (
    SELECT 
        CLIENT_ID,
        END_DT,
        DSNP_CODE,
        GROUP_NM,
        SNP_CD,
        -- 预计算下一条记录的所有需用字段
        LEAD(GROUP_NM, 1, 0) OVER (PARTITION BY CLIENT_ID ORDER BY START_DT) AS NEXT_GROUP_NM,
        LEAD(SNP_CD, 1, 0) OVER (PARTITION BY CLIENT_ID ORDER BY START_DT) AS NEXT_SNP_CD,
        LEAD(START_DT, 1, CAST('19000101' AS DATE)) OVER (PARTITION BY CLIENT_ID ORDER BY START_DT) AS NEXT_START_DT,
        LEAD(END_DT, 1, CAST('19000101' AS DATE)) OVER (PARTITION BY CLIENT_ID ORDER BY START_DT) AS NEXT_END_DT,
        LEAD(DSNP_CODE, 1, 0) OVER (PARTITION BY CLIENT_ID ORDER BY START_DT) AS NEXT_DSNP_CODE
    FROM dbo.HR
)
SELECT 
    CLIENT_ID,
    CASE 
        WHEN GROUP_NM = NEXT_GROUP_NM 
             AND SNP_CD = NEXT_SNP_CD 
             AND DATEDIFF(DAY, END_DT, NEXT_START_DT) = 1 
        THEN NEXT_END_DT 
        ELSE END_DT 
    END AS END_DT,
    CASE 
        WHEN GROUP_NM = NEXT_GROUP_NM 
             AND SNP_CD = NEXT_SNP_CD 
             AND DATEDIFF(DAY, END_DT, NEXT_START_DT) = 1 
        THEN NEXT_DSNP_CODE 
        ELSE DSNP_CODE 
    END AS DSNP_CODE
FROM HR_With_Lead;

优势：数据库只需执行一次LEAD逻辑，避免重复计算，代码可读性大幅提升。

方案2：用OUTER APPLY关联下一条记录（SQL Server适用） #

如果使用SQL Server等支持APPLY语法的数据库，可以直接关联获取下一条记录，逻辑更贴近业务意图：

SELECT 
    h.CLIENT_ID,
    CASE 
        WHEN h.GROUP_NM = h_next.GROUP_NM 
             AND h.SNP_CD = h_next.SNP_CD 
             AND DATEDIFF(DAY, h.END_DT, h_next.START_DT) = 1 
        THEN h_next.END_DT 
        ELSE h.END_DT 
    END AS END_DT,
    CASE 
        WHEN h.GROUP_NM = h_next.GROUP_NM 
             AND h.SNP_CD = h_next.SNP_CD 
             AND DATEDIFF(DAY, h.END_DT, h_next.START_DT) = 1 
        THEN h_next.DSNP_CODE 
        ELSE h.DSNP_CODE 
    END AS DSNP_CODE
FROM dbo.HR h
OUTER APPLY (
    SELECT TOP 1 
        GROUP_NM, SNP_CD, START_DT, END_DT, DSNP_CODE
    FROM dbo.HR h2
    WHERE h2.CLIENT_ID = h.CLIENT_ID
      AND h2.START_DT > h.START_DT
    ORDER BY h2.START_DT
) h_next;

注意：若START_DT存在重复值，需调整ORDER BY或添加主键等额外条件，确保获取正确的下一条记录。

方案3：分组合并连续同类记录（业务场景适配） #

如果业务目标是将连续满足条件的记录合并为一条（而非修改每条记录的字段值），可以用分组标记的方式实现：

WITH HR_Groups AS (
    SELECT 
        *,
        -- 标记分组：当前记录与上一条不满足连续条件时，开启新分组
        SUM(CASE 
                WHEN LAG(GROUP_NM) OVER (PARTITION BY CLIENT_ID ORDER BY START_DT) = GROUP_NM
                     AND LAG(SNP_CD) OVER (PARTITION BY CLIENT_ID ORDER BY START_DT) = SNP_CD
                     AND DATEDIFF(DAY, LAG(END_DT) OVER (PARTITION BY CLIENT_ID ORDER BY START_DT), START_DT) = 1
                THEN 0 
                ELSE 1 
            END) OVER (PARTITION BY CLIENT_ID ORDER BY START_DT) AS GROUP_ID
    FROM dbo.HR
)
SELECT 
    CLIENT_ID,
    MAX(END_DT) AS END_DT,
    LAST_VALUE(DSNP_CODE) OVER (PARTITION BY CLIENT_ID, GROUP_ID ORDER BY START_DT ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS DSNP_CODE
FROM HR_Groups
GROUP BY CLIENT_ID, GROUP_ID;

优势：直接输出合并后的聚合结果，避免冗余记录，适合需要整合连续同类数据的场景。

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