如何使用LEAD函数在Type变更时取后续记录值计算分区结束日期

要实现同一Person下，相同Type的所有行共享下一个不同Type的起始日期作为结束日期，最后一个Type的结束日期为NULL，可以通过以下两种方式修正SQL：

方法一：基于连续Type分组的关联查询 #

这种方法先将连续相同的Type划分为组，再为每组分配下一个组的起始日期作为结束日期，逻辑清晰，兼容性强：

WITH ranked_data AS (
    SELECT 
        Person,
        Type,
        dt_eff,
        -- 生成连续相同Type的组ID：Type变化时组ID+1
        SUM(CASE WHEN LAG(Type) OVER (PARTITION BY Person ORDER BY dt_eff) = Type THEN 0 ELSE 1 END) 
            OVER (PARTITION BY Person ORDER BY dt_eff) AS type_group_id
    FROM Person
),
group_boundaries AS (
    SELECT 
        Person,
        type_group_id,
        -- 取组内最早的日期（组起始）
        MIN(dt_eff) AS group_start,
        -- 取下一个组的起始日期作为当前组的结束日期
        LEAD(MIN(dt_eff)) OVER (PARTITION BY Person ORDER BY type_group_id) AS group_end
    FROM ranked_data
    GROUP BY Person, type_group_id
)
SELECT 
    rd.Person,
    rd.Type,
    rd.dt_eff AS Start_date,
    gb.group_end AS End_Date
FROM ranked_data rd
JOIN group_boundaries gb 
    ON rd.Person = gb.Person 
    AND rd.type_group_id = gb.type_group_id
ORDER BY rd.Person, rd.dt_eff;

逻辑说明 #

1. ranked_data：通过LAG函数对比当前行与上一行的Type，生成连续相同Type的组ID，确保同一Type的连续行属于同一组。
2. group_boundaries：按Person和组ID分组，提取每组的起始日期，再用LEAD获取下一组的起始日期作为当前组的结束日期。
3. 最后将原数据与分组边界关联，把组的结束日期赋值给组内所有行。

方法二：基于窗口函数的直接计算 #

这种方法通过嵌套窗口函数，直接筛选出当前行之后第一个不同Type的日期，代码更简洁：

SELECT 
    Person,
    Type,
    dt_eff AS Start_date,
    -- 在当前行之后的所有行中，找到第一个Type不同的日期
    MIN(CASE WHEN next_type != Type THEN next_dt END) 
        OVER (PARTITION BY Person ORDER BY dt_eff ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS End_Date
FROM (
    -- 提前获取下一行的Type和日期
    SELECT 
        Person,
        Type,
        dt_eff,
        LEAD(Type) OVER (PARTITION BY Person ORDER BY dt_eff) AS next_type,
        LEAD(dt_eff) OVER (PARTITION BY Person ORDER BY dt_eff) AS next_dt
    FROM Person
) t;

逻辑说明 #

1. 内层查询用LEAD获取每一行的下一行Type和dt_eff。
2. 外层用MIN窗口函数，在当前行之后的所有行中，筛选出第一个Type与当前行不同的日期（MIN会自动取最早的那个，即下一个Type的起始日期）。

两种方法都能得到你期望的输出结果，可根据使用的SQL方言和性能需求选择。

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