如何用SQL检测同一Batch内BATCH_ACTIVE值从1切换到0再回到1的记录

我在一家制帽厂工作，纺纱机的每批生产信息会被录入MySQL数据库，存储在RS_SPINNER_MACHINESTATUS表中。该表每分钟（00秒时）记录一次数据，一天共1440条记录。即使机器完全停机，也会向表中插入内容为"No Batch Active"的记录。

表结构与字段说明 #

正常表数据示例： #

字段含义： #

• BATCH：批次编号，无运行批次时为null
• BATCH_ACTIVE：二进制类型，仅取0或1
  • 0：机器无批次运行（空闲）
  • 1：机器正在运行对应批次
• RUN_TIMESTAMP：记录时间戳，精确到分钟

正常情况下，批次启动后BATCH_ACTIVE从0变为1，保持1直到批次结束后变回0。但存在异常情况：同一批次内BATCH_ACTIVE出现从1切换到0再变回1的情况，例如：

解决方案：SQL查询语句 #

要检测这类异常，可通过窗口函数对比当前记录与前一条记录的状态，找出同一批次内的异常切换：

WITH batch_status_changes AS (
    SELECT
        BATCH,
        RUN_TIMESTAMP,
        BATCH_ACTIVE,
        LAG(BATCH_ACTIVE) OVER (PARTITION BY BATCH ORDER BY RUN_TIMESTAMP) AS prev_active,
        LAG(BATCH) OVER (ORDER BY RUN_TIMESTAMP) AS prev_batch
    FROM RS_SPINNER_MACHINESTATUS
    WHERE BATCH IS NOT NULL
),
abnormal_switches AS (
    SELECT
        BATCH,
        RUN_TIMESTAMP AS switch_to_0_time,
        (SELECT MIN(RUN_TIMESTAMP)
         FROM batch_status_changes bsc2
         WHERE bsc2.BATCH = bsc1.BATCH
           AND bsc2.RUN_TIMESTAMP > bsc1.RUN_TIMESTAMP
           AND bsc2.BATCH_ACTIVE = 1
           AND bsc2.prev_active = 0) AS switch_back_to_1_time
    FROM batch_status_changes bsc1
    WHERE BATCH_ACTIVE = 0
      AND prev_active = 1
      AND prev_batch = BATCH
)
SELECT
    BATCH,
    switch_to_0_time,
    switch_back_to_1_time
FROM abnormal_switches
WHERE switch_back_to_1_time IS NOT NULL;

逻辑说明： #

1. batch_status_changes：用LAG窗口函数获取每条记录的前一条状态和批次号，仅保留有批次的记录。
2. abnormal_switches：筛选出同一批次内状态从1变为0的记录，并查找该批次后续是否存在从0变回1的时间戳。
3. 最终查询：返回存在异常切换的批次及对应的切换时间点。

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