机器状态调整SQL优化：替代递归CTE的高效实现方案问询

针对机器状态变更表mch.MachineStateChanges的状态调整需求，我们可以通过窗口函数分组标记的方式替代递归CTE，实现高效计算。

需求回顾 #

新增MachineStateCodeAdjusted列，规则：

• 若前一条记录的MachineStateCodeAdjusted为3，且当前记录的MachineStateCode≠2，则当前值为3
• 否则取当前记录的MachineStateCode值

本质是：机器进入故障状态（3）后，只有遇到运行状态（2）才会退出故障，中间所有非运行状态都维持故障标记。

高效SQL实现 #

WITH RankedRecords AS (
    SELECT 
        MachineCode,
        DoCreation,
        MachineStateCode,
        -- 按时间排序，用运行状态（2）作为故障区间的分隔点，生成分区编号
        SUM(CASE WHEN MachineStateCode = 2 THEN 1 ELSE 0 END) 
            OVER (PARTITION BY MachineCode ORDER BY DoCreation) AS FaultPartition
    FROM mch.MachineStateChanges
    -- 可选：仅过滤目标机器，去掉则处理全量机器数据
    WHERE MachineCode = 'DM139'
),
FaultPartitionCheck AS (
    SELECT 
        *,
        -- 标记当前分区内是否出现过故障状态（3）
        MAX(CASE WHEN MachineStateCode = 3 THEN 1 ELSE 0 END) 
            OVER (PARTITION BY MachineCode, FaultPartition) AS HasActiveFault
    FROM RankedRecords
)
SELECT 
    MachineCode,
    DoCreation,
    MachineStateCode,
    -- 根据分区故障标记生成调整后的状态
    CASE 
        WHEN HasActiveFault = 1 AND MachineStateCode != 2 THEN 3
        ELSE MachineStateCode
    END AS MachineStateCodeAdjusted
FROM FaultPartitionCheck
ORDER BY DoCreation;

方案原理 #

1. 分区划分：通过SUM(...) OVER()窗口函数，每遇到一次运行状态（2）就递增分区编号，将数据划分为多个「运行状态间隔区间」。
2. 故障标记：在每个分区内，用MAX(...) OVER()检查是否存在故障状态（3），标记该分区是否处于「故障未退出」状态。
3. 状态计算：如果分区存在未退出的故障，且当前状态不是运行状态，则调整为3；否则保留原状态。

性能优势 #

该方案仅需两次线性扫描（窗口函数均为O(n)复杂度），避免了递归CTE的嵌套循环逻辑，即使处理一年的全量数据，也能维持毫秒级的查询速度，远优于递归CTE的分钟级耗时。

注意事项 #

• 支持多机器批量处理：去掉WHERE MachineCode = 'DM139'即可，PARTITION BY MachineCode会自动按机器分组计算。
• 兼容初始故障场景：若机器第一条记录就是故障状态，第一个分区会正确标记并维持故障状态，直到遇到第一个运行状态。

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