如何查询历史表中跳过指定步骤的Item Code？已用PARTITION BY遇阻

核心思路 #

利用窗口函数LEAD()获取每个步骤的下一个流程步骤，结合分组筛选出不符合预期流程的Item Code。由于Id字段自增，可代表流程步骤的先后顺序，以此排序能保证步骤逻辑的正确性。

基础SQL查询 #

SELECT DISTINCT ht.ItemCode
FROM (
    SELECT 
        ItemCode,
        Step,
        LEAD(Step) OVER (PARTITION BY ItemCode ORDER BY Id) AS NextStep
    FROM history_table
) ht
WHERE ht.Step = 'Approved' 
  AND (ht.NextStep != 'Completed' OR ht.NextStep IS NULL);

代码解释 #

1. 子查询逻辑：

   • PARTITION BY ItemCode：按Item Code分组，确保仅在同一Item的流程步骤内计算下一个步骤
   • ORDER BY Id：按Id排序，保证步骤顺序匹配实际流程的先后
   • LEAD(Step)：获取当前步骤的下一个步骤值，若为最后一步则返回NULL

2. 外层筛选规则：

   • 筛选当前步骤为Approved的记录
   • 判断下一个步骤不是预期的Completed，或当前步骤已是最后一步（无后续步骤）
   • DISTINCT：避免同一个Item Code被重复返回

扩展：支持多步骤规则 #

如果需要处理多组步骤的自然流转规则（比如Created的下一步应为Approved，Completed的下一步应为Closed），可以创建步骤映射表统一管理规则，让查询更灵活：

步骤映射表示例

CREATE TABLE step_transitions (
    current_step VARCHAR(50) PRIMARY KEY,
    expected_next_step VARCHAR(50)
);

INSERT INTO step_transitions VALUES
('Created', 'Approved'),
('Approved', 'Completed'),
('Completed', 'Closed');

关联映射表的查询

SELECT DISTINCT ht.ItemCode
FROM (
    SELECT 
        ItemCode,
        Step,
        LEAD(Step) OVER (PARTITION BY ItemCode ORDER BY Id) AS NextStep
    FROM history_table
) ht
JOIN step_transitions st ON ht.Step = st.current_step
WHERE (ht.NextStep != st.expected_next_step OR ht.NextStep IS NULL);

这种方式无需修改主查询，仅维护step_transitions表即可更新流程规则，适配复杂流程场景。

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