如何通过SQL在SQL Server中实现员工座位表的缓慢变化维度

现有员工座位快照数据，需将同一员工连续时间段内座位类型相同的记录合并为单条，保留该连续区间的最早开始日期、最晚结束日期及座位类型。以下是测试数据集及实现方案：

测试数据集创建SQL #

create table #test
(
    start_date date,
    end_date date,
    emp_id varchar(20),
    seating_type varchar(10)
)

insert into #test
select '01-01-2025','01-10-2025','123','abc'
union all
select '01-11-2025','01-20-2025','123','abc'
union all
select '01-21-2025','01-31-2025','123','def'
union all
select '02-01-2025','02-10-2025','123','abc'
union all
select '02-11-2025','02-20-2025','123','def'
union all
select '02-21-2025','02-28-2025','123','gih'
union all
select '03-01-2025','03-10-2025','123','def'
union all
select '02-10-2025','02-25-2025','456','def'
union all
select '02-26-2025','03-10-2025','456','abc'
union all
select '03-11-2025','03-27-2025','456','abc'
union all
select '03-28-2025','04-10-2025','456','gih'

注：将原union替换为union all避免不必要去重，同时删除重复的create table语句

实现SQL逻辑 #

这是典型的连续相同值分组合并场景，可通过窗口函数实现：

with cte as (
    select 
        *,
        -- 标记当前记录与上一条座位类型是否不同，生成分组标识
        sum(case when prev_seat = seating_type then 0 else 1 end) over(partition by emp_id order by start_date) as group_id
    from (
        select 
            *,
            -- 获取同一员工上一条记录的座位类型
            lag(seating_type) over(partition by emp_id order by start_date) as prev_seat
        from #test
    ) t
)
select 
    emp_id,
    seating_type,
    min(start_date) as start_date,
    max(end_date) as end_date
from cte
group by emp_id, seating_type, group_id
order by emp_id, start_date;

逻辑说明 #

1. 内层子查询用lag函数，按员工分组、日期排序，获取每条记录的上一条座位类型；
2. 中间CTE通过sum累计计算分组标识：当当前座位类型与上一条不同时，分组标识加1，以此将连续相同类型的记录归为同一组；
3. 最终按员工、座位类型、分组标识聚合，取每组的最早开始日期和最晚结束日期。

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