如何在SQL中按连续日期周期分组员工职位记录

我有一张员工雇佣历史表 [t104_employment_history]，包含字段：

• t104f005_employee_no：员工编号
• t104f040_position_no：职位编号
• t104f025_date_effective：生效日期
• t104f030_date_to：结束日期

需求是识别同一员工同一职位的连续日期周期，输出每个周期的最小生效日期和最大结束日期。我尝试了两种SQL方法但都没得到正确结果，求可行的实现方案。

尝试的方法1 #

WITH RankedPositions AS (
  SELECT
    [t104f005_employee_no],
    [t104f040_position_no],
    [t104f025_date_effective],
    [t104f030_date_to],
    ROW_NUMBER() OVER (PARTITION BY [t104f005_employee_no] ORDER BY [t104f025_date_effective]) -
    ROW_NUMBER() OVER (PARTITION BY [t104f005_employee_no], [t104f040_position_no] ORDER BY [t104f025_date_effective]) AS grp
  FROM [AUR11PROD].[dbo].[t104_employment_history] with (nolock)
  WHERE [t104f005_employee_no] = '11354'
)    
SELECT
  [t104f005_employee_no],
  [t104f040_position_no],
  MIN([t104f025_date_effective]) AS min_startdate,
  MAX([t104f030_date_to]) AS max_enddate
FROM RankedPositions
GROUP BY [t104f005_employee_no], [t104f040_position_no], grp
ORDER BY [t104f005_employee_no], min_startdate;

尝试的方法2 #

WITH RECURSIVE ContinuousPositions AS (
  SELECT
    employee_no,
    position_no,
    startdate,
    enddate
  FROM your_table_name
  WHERE NOT EXISTS (
    SELECT 1
    FROM your_table_name AS t2
    WHERE t2.employee_no = your_table_name.employee_no
      AND t2.position_no = your_table_name.position_no
      AND t2.startdate < your_table_name.startdate
  )
  
  UNION ALL
  
  SELECT
    cp.employee_no,
    cp.position_no,
    cp.startdate,
    t.enddate
  FROM ContinuousPositions AS cp
  JOIN your_table_name AS t ON (
    cp.employee_no = t.employee_no
    AND cp.position_no = t.position_no
    AND cp.enddate = DATEADD(day, -1, t.startdate)
  )
)    
SELECT
  employee_no,
  position_no,
  MIN(startdate) AS min_startdate,
  MAX(enddate) AS max_enddate
FROM ContinuousPositions
GROUP BY employee_no, position_no
ORDER BY employee_no, min_startdate;

可行的SQL实现方案 #

核心思路是先标记连续周期的起始点，再通过累加标记生成分组ID，最后聚合得到每个周期的起止日期（以SQL Server为例）：

WITH EmpPositionHistory AS (
    SELECT
        t104f005_employee_no,
        t104f040_position_no,
        t104f025_date_effective,
        t104f030_date_to,
        -- 标记新周期：当前记录生效日期不是上一条同职位记录结束日期的次日
        CASE 
            WHEN LAG(t104f030_date_to) OVER (
                PARTITION BY t104f005_employee_no, t104f040_position_no 
                ORDER BY t104f025_date_effective
            ) = DATEADD(DAY, -1, t104f025_date_effective)
            THEN 0
            ELSE 1
        END AS is_new_period
    FROM [AUR11PROD].[dbo].[t104_employment_history] WITH (NOLOCK)
    -- 可移除WHERE条件处理所有员工
    -- WHERE t104f005_employee_no = '11354'
),
PeriodGroups AS (
    SELECT
        t104f005_employee_no,
        t104f040_position_no,
        t104f025_date_effective,
        t104f030_date_to,
        -- 累加起始标记，生成连续周期的分组ID
        SUM(is_new_period) OVER (
            PARTITION BY t104f005_employee_no, t104f040_position_no 
            ORDER BY t104f025_date_effective
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS period_group_id
    FROM EmpPositionHistory
)
SELECT
    t104f005_employee_no AS employee_no,
    t104f040_position_no AS position_no,
    MIN(t104f025_date_effective) AS min_startdate,
    MAX(t104f030_date_to) AS max_enddate
FROM PeriodGroups
GROUP BY t104f005_employee_no, t104f040_position_no, period_group_id
ORDER BY t104f005_employee_no, min_startdate;

方案说明 #

1. 标记周期起始：用LAG()函数获取同员工同职位的上一条记录结束日期，判断当前记录生效日期是否为上一条结束日期的次日，非连续则标记为新周期起点。
2. 生成分组ID：通过累加起始标记，将同一连续周期的记录归为同一个分组ID。
3. 聚合结果：按员工编号、职位编号和分组ID聚合，取最小生效日期和最大结束日期，得到合并后的连续周期。

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