如何在SQL中重置员工重复汇报同一经理的RowNumber计数？

我正尝试为组织构建时间点层级结构，遇到的问题是部分员工在职业生涯中多次向同一经理汇报。我尝试对员工ID和经理ID使用row_number()窗口函数，但当员工再次向同一经理汇报时，计数会持续累加（如样本数据中，员工再次汇报经理2时，RowNumber变为4、5）。我希望此时能将连续的同一经理汇报记录合并，得到如下期望结果，认为最佳方案是使用窗口函数，但未找到简便的实现方式，恳请各位提供建议。

样本数据 #

期望结果 #

解决方案 #

这个问题属于连续相同分组的合并，核心是识别员工向同一经理连续汇报的时间段，而非所有历史汇报记录。可以通过以下窗口函数组合实现：

步骤1：生成分组标识 #

使用LAG()函数获取前一条记录的经理ID，对比当前记录的经理ID，若不同则标记为新分组的开始，最终通过累加这些标记生成唯一的分组ID：

WITH grouped_data AS (
    SELECT 
        ID,
        Manager_ID,
        EFF_DT,
        EXP_DT,
        -- 当当前经理ID与前一条不同时，生成1，否则0，累加得到分组ID
        SUM(CASE WHEN Manager_ID != LAG(Manager_ID) OVER (PARTITION BY ID ORDER BY EFF_DT) THEN 1 ELSE 0 END) 
            OVER (PARTITION BY ID ORDER BY EFF_DT) AS group_id
    FROM your_table_name
)

步骤2：按分组合并时间段 #

基于生成的分组ID，按员工ID和分组ID聚合，取每个分组的最早生效日期和最晚失效日期：

SELECT 
    ID,
    Manager_ID,
    MIN(EFF_DT) AS EFF_DT,
    MAX(EXP_DT) AS EXP_DT
FROM grouped_data
GROUP BY ID, Manager_ID, group_id
ORDER BY ID, MIN(EFF_DT);

说明 #

• LAG(Manager_ID) OVER (PARTITION BY ID ORDER BY EFF_DT)：按员工ID分组、生效日期排序，获取上一条记录的经理ID
• 累加标记生成的group_id会把连续向同一经理汇报的记录归为同一组，即使员工后来再次向该经理汇报，也会生成新的分组ID，从而实现计数重置的效果
• 最终聚合后就能得到连续时间段合并后的结果，完全符合你的期望

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