基于有序SQL表的序列分配递增ID：面试候选人attempt字段计算及分组实现问询

当然可以！你这个思路完全站得住脚——通过按时间排序后，对比相邻行的面试阶段值来划分独立的面试尝试，是这类分组问题的经典解法。下面我会一步步拆解实现逻辑，再附上可直接运行的SQL示例，最后满足你按候选人+面试尝试分组的需求。

核心逻辑拆解 #

你的判断规则非常准确：对于同一个候选人，当某一行的interview_stage小于上一行的阶段值时，说明这是一次全新的面试流程的开始。我们可以通过以下步骤把这个规则转化为可计算的字段：

1. 先按candidate_id分组，在组内按stage_reached_at升序排序，确保面试阶段的时间顺序正确。
2. 用窗口函数LAG()获取当前行的上一行面试阶段值，用来做对比。
3. 把“新尝试开始”的行标记为1，其他行标记为0，最后对这些标记值做累加，就能得到每个行对应的attempt编号。

分步实现SQL #

第一步：获取上一行的面试阶段值 #

先运行这个查询，看看每个行的上一阶段是什么：

SELECT 
    candidate_id,
    interview_stage,
    stage_reached_at,
    -- 仅在同一候选人范围内，获取上一行的面试阶段
    LAG(interview_stage) OVER (PARTITION BY candidate_id ORDER BY stage_reached_at) AS prev_stage
FROM your_table_name;

针对你提供的示例数据，结果会是这样：

你可以看到：第4行和第6行的interview_stage小于prev_stage，这正是新面试尝试的起点。

第二步：计算attempt字段 #

基于上面的子查询，我们添加标记和累加逻辑，得到最终的attempt值：

SELECT 
    candidate_id,
    interview_stage,
    stage_reached_at,
    -- 累加标记值：第一行/阶段回退时记1，否则记0，累加后得到attempt编号
    SUM(CASE 
            WHEN prev_stage IS NULL THEN 1  -- 第一行默认是第一次尝试的开始
            WHEN interview_stage < prev_stage THEN 1
            ELSE 0
        END) OVER (PARTITION BY candidate_id ORDER BY stage_reached_at) AS attempt
FROM (
    SELECT 
        candidate_id,
        interview_stage,
        stage_reached_at,
        LAG(interview_stage) OVER (PARTITION BY candidate_id ORDER BY stage_reached_at) AS prev_stage
    FROM your_table_name
) AS stage_with_prev;

运行这个查询后，得到的attempt字段就和你提供的示例完全一致了！

第三步：按candidate_id和attempt分组汇总 #

如果要对每个候选人的每次面试尝试做分组统计（比如查看每次尝试到达的最高阶段、起止时间），可以基于上面的结果再做聚合：

SELECT 
    candidate_id,
    attempt,
    MAX(interview_stage) AS max_stage_reached,  -- 本次尝试到达的最高阶段
    MIN(stage_reached_at) AS attempt_start_date, -- 本次尝试开始时间
    MAX(stage_reached_at) AS attempt_end_date    -- 本次尝试结束时间
FROM (
    -- 这里嵌入第二步的完整查询
    SELECT 
        candidate_id,
        interview_stage,
        stage_reached_at,
        SUM(CASE 
                WHEN prev_stage IS NULL THEN 1
                WHEN interview_stage < prev_stage THEN 1
                ELSE 0
            END) OVER (PARTITION BY candidate_id ORDER BY stage_reached_at) AS attempt
    FROM (
        SELECT 
            candidate_id,
            interview_stage,
            stage_reached_at,
            LAG(interview_stage) OVER (PARTITION BY candidate_id ORDER BY stage_reached_at) AS prev_stage
        FROM your_table_name
    ) AS stage_with_prev
) AS stage_with_attempt
GROUP BY candidate_id, attempt
ORDER BY candidate_id, attempt;

这个查询的结果会清晰展示每个候选人的每次面试尝试的核心信息，比如你的示例数据会输出：

注意事项 #

• 这个逻辑依赖正常面试流程是阶段递增的前提（比如1→2→3是正常流程），如果存在跳级（比如直接从1到3），只要没有阶段回退，就不会误判为新尝试，完全适配实际场景。
• 主流数据库（MySQL 8.0+、PostgreSQL、SQL Server等）都支持LAG()和窗口式SUM()，如果是非常老的数据库版本，可能需要用变量来实现，但这种情况已经很少见了。
• 如果有多个候选人，PARTITION BY candidate_id会确保每个候选人的attempt编号是独立计算的，不会互相干扰。

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