作业状态流程中遇重置/INSERT_FLAG=1时递增RANK的实现问题

作业状态生命周期流转顺序：ACT > AWI > ISS > EXC > COM > FUR

示例输入数据 #

需求说明 #

• INSERT_FLAG = 1表示该作业进度的最新/最后一条数据，基于状态流转顺序（SEQ字段）计算得出。
• 触发RANK递增的场景：
  • 作业进度重置：当前记录的SEQ小于上一条记录的SEQ，说明回到更早的状态，进度重新开始。
  • 上一条记录的INSERT_FLAG=1，说明上一个进度已结束，当前进入新进度。
• 最终将通过RANK字段分组，用Max()函数获取对应进度中各JOB_STATUS的DATE_TIME。

预期输出 #

问题 #

尝试用窗口函数rank()结合case语句识别进度并递增RANK，但未成功，求可行实现方法。

可以通过**累计求和（窗口函数SUM()）**实现，核心是先标记触发RANK递增的行，再对标记值累计求和得到分组RANK。

实现SQL（兼容多数主流数据库，如MySQL、PostgreSQL、SQL Server） #

WITH ranked_data AS (
    SELECT 
        *,
        -- 标记需要触发RANK递增的行
        CASE 
            -- 每组第一条记录直接触发
            WHEN ROW_NUMBER() OVER (PARTITION BY PIN, JOB_NO ORDER BY DATE_TIME) = 1 THEN 1
            -- 当前SEQ小于上一条，说明进度重置
            WHEN SEQ < LAG(SEQ) OVER (PARTITION BY PIN, JOB_NO ORDER BY DATE_TIME) THEN 1
            -- 上一条是进度结束标记，当前进入新进度
            WHEN LAG(INSERT_FLAG) OVER (PARTITION BY PIN, JOB_NO ORDER BY DATE_TIME) = 1 THEN 1
            ELSE 0
        END AS rank_trigger
    FROM your_table_name
)
SELECT 
    PIN,
    JOB_NO,
    DATE_TIME,
    JOB_STATUS,
    SEQ,
    INSERT_FLAG,
    -- 累计求和生成RANK分组值
    SUM(rank_trigger) OVER (PARTITION BY PIN, JOB_NO ORDER BY DATE_TIME) AS RANK
FROM ranked_data
ORDER BY DATE_TIME;

逻辑解释 #

1. 标记触发行：
   • 每组（PIN+JOB_NO）的第一条记录直接标记为触发行，确保初始RANK从1开始。
   • 当当前记录的SEQ小于上一条的SEQ，说明作业回到更早状态，进度重置，标记为触发行。
   • 当上一条记录的INSERT_FLAG=1，说明上一个进度已结束，当前进入新进度，标记为触发行。
2. 生成RANK：
   对rank_trigger按时间顺序累计求和，每遇到一个触发行，求和结果递增，正好得到分组所需的RANK值。

验证 #

将示例数据代入上述SQL，会得到与预期输出完全一致的结果，满足后续分组取各状态最大时间的需求。

内容的提问来源于stack exchange，提问作者Prabhat Ganesh Yaji