使用RANK OVER PARTITION对比历史行结果计算复聘标识isRehire

实现思路 #

你原来仅用RANK的思路无法满足需求，因为RANK只能生成固定排序编号，没法处理动态重置基准日期的逻辑，这类需要逐行判断并更新基准的场景属于「间隙与岛屿」问题，可以用递归CTE实现，步骤如下：

• 第一步：按userID分区，对startDate升序排序，给每个用户的入职记录生成唯一行号
• 第二步：从每个用户的第一条记录开始递归计算：
  • 首条记录无历史入职，isRehire固定为N，基准日期为当前startDate
  • 后续记录和上一轮的基准日期对比：日期间隔<1年则isRehire为Y，基准日期保持不变；日期间隔≥1年则isRehire为N，基准日期更新为当前startDate

可运行实现代码（SQL Server 示例，可适配所有支持CTE的SQL方言） #

WITH RankedHire AS (
    -- 第一步：给每个用户的入职记录按入职日期升序编号
    SELECT 
        userID,
        startDate,
        ROW_NUMBER() OVER (PARTITION BY userID ORDER BY startDate ASC) AS rn
    FROM MyTable
),
RecursiveCalc AS (
    -- 递归锚点：初始化每个用户的第一条入职记录
    SELECT 
        userID,
        startDate,
        rn,
        CAST('N' AS CHAR(1)) AS isRehire,
        startDate AS baseDate -- 初始基准日期为第一条入职日期
    FROM RankedHire
    WHERE rn = 1

    UNION ALL

    -- 递归逻辑：逐行计算后续记录的isRehire和基准日期
    SELECT 
        rh.userID,
        rh.startDate,
        rh.rn,
        CASE 
            WHEN DATEDIFF(DAY, rc.baseDate, rh.startDate) < 365 
            THEN 'Y' 
            ELSE 'N' 
        END AS isRehire,
        CASE 
            WHEN DATEDIFF(DAY, rc.baseDate, rh.startDate) < 365 
            THEN rc.baseDate 
            ELSE rh.startDate 
        END AS baseDate
    FROM RecursiveCalc rc
    INNER JOIN RankedHire rh 
        ON rc.userID = rh.userID 
        AND rh.rn = rc.rn + 1
)
-- 输出最终结果
SELECT userID, startDate, isRehire
FROM RecursiveCalc
ORDER BY userID, startDate;

方言适配说明 #

如果使用MySQL、PostgreSQL等其他SQL方言，仅需要替换日期差计算函数即可：

• MySQL 替换DATEDIFF(DAY, rc.baseDate, rh.startDate)为DATEDIFF(rh.startDate, rc.baseDate)
• PostgreSQL 替换为(rh.startDate - rc.baseDate)

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