Redshift SQL员工状态变更记录行压缩异常问题求助

我有一张记录员工随时间变更信息的表，每行包含start_date和end_date字段，目标是压缩表中数据，仅保留状态实际发生变更的记录。目前需求已基本实现，但存在特殊场景问题：当员工状态恢复为之前的状态时，起始日期无法正确取到变更后的起始日期（示例仅用status字段演示，实际表中还有更多会发生变更的字段）。

现有数据 #

期望压缩后的数据 #

尝试的Redshift SQL #

with all_rows as (
    select 
        my_emp_id,
        cast(start_date as date),
        cast(end_date as date),
        status,
        lead(status) over (partition by my_emp_id order by cast(start_date as date), cast(end_date as date)) as new_status
        
    from my_emp_table
   where  my_emp_id = 'abcde'
      order by cast(start_date as date), cast(end_date as date)),
      condensed as ( 
    select 
        my_emp_id,
       status,
       first_value(cast(start_date as DATE)) over (partition by my_emp_id order by cast(start_date as date) rows between unbounded preceding and unbounded following)  as FINAL_START_DATE,
             cast(end_date as date) as final_end_date
     from all_rows)
  
select Start_date, 
       end_date, 
       status, 
       final_start_date, 
       Final_end_date
from condensed
where  (status <> new_status or new_status is null)
order by start_date;

但上述语句无法得到期望结果，以下是正确的解决方案：

解决方案 #

核心思路是将连续相同状态的记录归为同一分组，然后对每个分组取最小的start_date和最大的end_date。具体通过LAG函数标记分组边界，再累加生成分组ID，最后聚合计算：

WITH grouped_rows AS (
    SELECT
        my_emp_id,
        status,
        start_date::DATE,
        end_date::DATE,
        -- 当当前状态与上一行不同时，标记为新分组起点
        CASE 
            WHEN LAG(status) OVER (PARTITION BY my_emp_id ORDER BY start_date, end_date) <> status 
            THEN 1 
            ELSE 0 
        END AS is_new_group
    FROM my_emp_table
    WHERE my_emp_id = 'abcde'
),
group_ids AS (
    SELECT
        my_emp_id,
        status,
        start_date,
        end_date,
        -- 累加分组标记，生成唯一分组ID
        SUM(is_new_group) OVER (PARTITION BY my_emp_id ORDER BY start_date, end_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM grouped_rows
)
SELECT
    my_emp_id,
    status,
    MIN(start_date) AS start_date,
    MAX(end_date) AS end_date
FROM group_ids
GROUP BY my_emp_id, status, group_id
ORDER BY start_date;

说明 #

1. grouped_rows CTE：用LAG函数对比当前行与上一行的status，如果不同则标记为新分组（is_new_group=1），否则为0。
2. group_ids CTE：通过累加is_new_group的值，为连续相同状态的记录生成唯一的group_id，这样即使后续状态恢复为之前的值，也会被分到新的分组。
3. 最终聚合：按my_emp_id、status和group_id分组，取每组的最小start_date和最大end_date，得到压缩后的结果。

如果实际表中有多个变更字段（比如department、position等），只需将这些字段都加入LAG的对比条件和GROUP BY中即可，例如：

-- 多字段变更的分组标记
CASE 
    WHEN LAG(status, 1) OVER (...) <> status 
         OR LAG(department, 1) OVER (...) <> department
         OR LAG(position, 1) OVER (...) <> position
    THEN 1 
    ELSE 0 
END AS is_new_group

-- 最终GROUP BY
GROUP BY my_emp_id, status, department, position, group_id

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