如何从历史表追踪用户各Profile的生效起止日期

我有一张users_table，需要追踪用户每个任职Profile的VALID_FROM（生效起始日期）和VALID_TO（生效结束日期），规则是当前生效的Profile的VALID_TO为NULL。

现有查询SQL如下：

select id,email,ROLE,PROFILE,
    min_last_modified_date as valid_from, lead(min_last_modified_date) over (partition by 
    id order by min_last_modified_date) as valid_to 
    from
    (
      select id,email,ROLE,PROFILE,
      min(last_modified_date) as min_last_modified_date,
      max(last_modified_date) as max_last_modified_date
      from users_table 
      group by 1,2,3,4 
    ) 

当前查询无法正确展现Profile变更的时间节点，期望输出能准确体现每个Profile的完整生效周期。

原SQL的问题分析 #

1. 周期衔接错误：内层按id,email,ROLE,PROFILE分组后，用lead(min_last_modified_date)取的是下一个分组的起始时间，而非当前Profile的实际结束时间。比如用户从Profile A切换到Profile B时，Profile A的VALID_TO应该是其最后修改日期，而非Profile B的起始日期。
2. 错误合并非连续周期：如果用户先使用Profile A，切换到Profile B后又切回Profile A，原SQL会把两次Profile A的记录合并成一个周期，无法区分两次不同的任职阶段。

修正后的查询方案 #

正确的思路是先识别用户Profile的连续生效阶段，再生成每个阶段的起止日期：

WITH user_profile_changes AS (
    SELECT 
        id,
        email,
        ROLE,
        PROFILE,
        last_modified_date,
        -- 标记Profile变更节点：当前行与上一行Profile不同时，生成新分组ID
        SUM(CASE WHEN LAG(PROFILE) OVER (PARTITION BY id ORDER BY last_modified_date) != PROFILE THEN 1 ELSE 0 END) 
            OVER (PARTITION BY id ORDER BY last_modified_date) AS profile_group_id
    FROM users_table
),
profile_periods AS (
    SELECT 
        id,
        email,
        ROLE,
        PROFILE,
        MIN(last_modified_date) AS valid_from,
        MAX(last_modified_date) AS period_end_date
    FROM user_profile_changes
    GROUP BY id, email, ROLE, PROFILE, profile_group_id
)
SELECT 
    id,
    email,
    ROLE,
    PROFILE,
    valid_from,
    -- 下一个周期的起始日期作为当前周期的结束日期，最后一个周期设为NULL
    LEAD(valid_from) OVER (PARTITION BY id ORDER BY valid_from) AS valid_to
FROM profile_periods
ORDER BY id, valid_from;

逻辑说明 #

1. user_profile_changes：通过LAG函数对比当前行与上一行的Profile，当Profile发生变化时，累加生成分组ID，确保同一个连续生效的Profile被分到同一组。
2. profile_periods：按用户、Profile和分组ID聚合，得到每个连续Profile周期的最早生效日期（valid_from）和该周期内的最后修改日期。
3. 外层查询：用LEAD函数获取下一个周期的起始日期，作为当前周期的VALID_TO；当前生效的Profile因无后续周期，VALID_TO自动为NULL。

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