制造业设备状态监控与生产优化实战：基于SQL的序列分析与状态机建模

CREATE TABLE equipment_state_history (
    equipment_id   INT,          -- 设备编号（如产线A-机床1）
    state          VARCHAR(50),  -- 状态（运行、待机、故障、维护）
    timestamp      TIMESTAMP     -- 状态变更时间
);


-- 示例数据
INSERT INTO equipment_state_history VALUES
(101, '运行',  '2023-10-10 08:00:00'),
(101, '故障',  '2023-10-10 10:30:00'),
(101, '维护',  '2023-10-10 11:15:00'),
(101, '运行',  '2023-10-10 12:00:00'),
(102, '待机',  '2023-10-10 09:00:00'),
(102, '运行',  '2023-10-10 09:30:00');

CREATE TABLE production_batch (
    batch_id       INT,          -- 批次编号
    equipment_id   INT,          -- 生产设备
    start_time     TIMESTAMP,    -- 批次开始时间
    end_time       TIMESTAMP,    -- 批次结束时间
    temperature    FLOAT,        -- 加工温度
    pressure       FLOAT,        -- 加工压力
    defect_count   INT           -- 缺陷数量
);


-- 示例数据
INSERT INTO production_batch VALUES
(5001, 101, '2023-10-10 08:00:00', '2023-10-10 08:30:00', 150.5, 2.4, 2),
(5002, 101, '2023-10-10 12:30:00', '2023-10-10 13:00:00', 162.0, 2.6, 15);  -- 温度异常

WITH state_transitions AS (
    SELECT 
        equipment_id,
        state,
        LAG(state) OVER (PARTITION BY equipment_id ORDER BY timestamp) AS prev_state,
        timestamp
    FROM equipment_state_history
)
SELECT 
    equipment_id,
    prev_state,
    state AS current_state,
    timestamp AS transition_time
FROM state_transitions
WHERE (prev_state, state) IN (
    -- 定义非法转换规则（如运行直接跳维护需人工确认）
    ('运行', '维护'), 
    ('故障', '运行')  -- 未经过维修直接重启
);

WITH state_durations AS (
    SELECT 
        equipment_id,
        state,
        EXTRACT(EPOCH FROM (
            LEAD(timestamp) OVER (PARTITION BY equipment_id ORDER BY timestamp) - timestamp
        )) AS duration_seconds
    FROM equipment_state_history
    WHERE DATE(timestamp) = '2023-10-10'
)
SELECT 
    equipment_id,
    ROUND(
        SUM(CASE WHEN state = '运行' THEN duration_seconds ELSE 0 END) 
        / SUM(duration_seconds) * 100, 2
    ) AS utilization_rate
FROM state_durations
GROUP BY equipment_id;

SELECT 
    batch_id,
    equipment_id,
    temperature,
    pressure,
    defect_count
FROM production_batch
WHERE 
    temperature NOT BETWEEN 145 AND 155  -- 标准温度范围
    OR pressure NOT BETWEEN 2.0 AND 2.5; -- 标准压力范围

WITH RECURSIVE alert_sequence AS (
    SELECT 
        equipment_id,
        timestamp,
        1 AS alert_count,
        ARRAY[state] AS sequence
    FROM equipment_state_history
    WHERE state = '振动告警'
    UNION ALL
    SELECT 
        a.equipment_id,
        h.timestamp,
        a.alert_count + 1,
        a.sequence || h.state
    FROM alert_sequence a
    JOIN equipment_state_history h 
        ON a.equipment_id = h.equipment_id 
        AND h.timestamp > a.timestamp 
        AND h.timestamp <= a.timestamp + INTERVAL '1 hour'  -- 1小时内连续告警
    WHERE h.state = '振动告警'
)
SELECT 
    equipment_id,
    MAX(alert_count) AS max_consecutive_alerts
FROM alert_sequence
GROUP BY equipment_id
HAVING MAX(alert_count) >= 3;  -- 连续3次告警触发维护

-- 计算各状态累计时长
SELECT 
    state,
    SUM(duration_seconds)  3600 AS duration_hours
FROM (
    SELECT 
        state,
        EXTRACT(EPOCH FROM (
            LEAD(timestamp) OVER (PARTITION BY equipment_id ORDER BY timestamp) - timestamp
        )) AS duration_seconds
    FROM equipment_state_history
    WHERE equipment_id = 101
) tmp
GROUP BY state;

-- 检查故障时间段内的生产批次质量
SELECT 
    b.batch_id,
    b.defect_count,
    b.temperature
FROM production_batch b
JOIN equipment_state_history h 
    ON b.equipment_id = h.equipment_id
    AND b.start_time BETWEEN h.timestamp 
        AND LEAD(h.timestamp) OVER (PARTITION BY h.equipment_id ORDER BY h.timestamp)
WHERE h.equipment_id = 101
    AND h.state = '故障';