MySQL 8如何查询设备20分钟内连续超温的温度记录？

问题分析 #

原查询的核心缺陷：

• 用SUM(...) OVER()做累计求和，只能统计设备历史所有异常记录的总数，无法识别连续的异常段，属于逻辑方向错误。
• 子查询通过COUNT(*)与累计值比较的逻辑完全不成立，无法判断目标20分钟窗口内是否存在正常温度。

正确解决方案 #

针对需求分两种场景给出实现：

场景1：筛选持续时间≥20分钟的连续异常段中的所有记录

如果需要找出属于持续至少20分钟的连续异常时段内的所有记录，用窗口函数解决连续记录分组（孤岛问题）：

WITH temp_with_status AS (
    -- 标记每条记录是否超出温度范围
    SELECT 
        IDDevice,
        Data,
        Temperature,
        CASE WHEN Temperature < 2 OR Temperature > 8 THEN 1 ELSE 0 END AS is_out_of_range
    FROM temperature
    WHERE Data >= DATE_SUB(NOW(), INTERVAL 1 DAY) -- 限制最近1天数据，可按需调整
),
temp_with_groups AS (
    -- 为连续的同状态记录分配分组ID：连续异常/正常的记录会被分到同一组
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY IDDevice ORDER BY Data) 
        - ROW_NUMBER() OVER (PARTITION BY IDDevice, is_out_of_range ORDER BY Data) AS group_id
    FROM temp_with_status
),
group_summary AS (
    -- 统计每个分组的时间跨度，筛选出持续≥20分钟的异常分组
    SELECT 
        IDDevice,
        group_id,
        MIN(Data) AS group_start,
        MAX(Data) AS group_end,
        TIMESTAMPDIFF(MINUTE, MIN(Data), MAX(Data)) AS duration_min
    FROM temp_with_groups
    GROUP BY IDDevice, group_id, is_out_of_range
    HAVING is_out_of_range = 1 AND duration_min >= 20
)
-- 关联分组信息，返回符合条件的所有异常记录
SELECT t.IDDevice, t.Data, t.Temperature
FROM temp_with_groups t
JOIN group_summary gs 
    ON t.IDDevice = gs.IDDevice 
    AND t.group_id = gs.group_id
WHERE t.is_out_of_range = 1
ORDER BY t.IDDevice, t.Data;

场景2：单条记录验证：自身往前20分钟内无正常记录

如果需求是每条异常记录，其时间点往前推20分钟到自身的区间内，没有任何正常温度记录，用NOT EXISTS直接验证：

SELECT 
    t1.IDDevice,
    t1.Data,
    t1.Temperature
FROM temperature t1
WHERE 
    (t1.Temperature < 2 OR t1.Temperature > 8)
    AND t1.Data >= DATE_SUB(NOW(), INTERVAL 1 DAY)
    -- 验证目标窗口内无正常温度
    AND NOT EXISTS (
        SELECT 1
        FROM temperature t2
        WHERE 
            t2.IDDevice = t1.IDDevice
            AND t2.Data BETWEEN DATE_SUB(t1.Data, INTERVAL 20 MINUTE) AND t1.Data
            AND t2.Temperature BETWEEN 2 AND 8
    )
ORDER BY t1.IDDevice, t1.Data;

逻辑说明 #

• 场景1通过分组连续异常段，确保整个时段内没有正常记录且时长达标，适合追踪设备的持续异常时段。
• 场景2针对单条记录做窗口验证，适合找出所有“自身所在20分钟窗口全异常”的独立记录。

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