MySQL温度数据模式检测：24小时内超40℃时长超2小时

核心思路 #

用MySQL窗口函数把连续的高温记录归为一组，计算每组的持续时长后，筛选出符合条件的区间。

步骤1：标记高温记录 #

先给每条数据打标签，区分是否属于温度超过40的记录：

SELECT 
    temp,
    ttime,
    CASE WHEN temp > 40 THEN 1 ELSE 0 END AS is_high_temp
FROM your_table_name;

注：如果要求包含40度，把temp > 40改成temp >= 40就行

步骤2：给连续高温记录分组 #

用LAG()窗口函数判断当前记录和上一条是否是连续的高温（时间间隔5分钟且都属于高温），再通过累积求和生成分组ID：

WITH high_temp_marks AS (
    SELECT 
        temp,
        ttime,
        CASE WHEN temp > 40 THEN 1 ELSE 0 END AS is_high_temp
    FROM your_table_name
),
grouped_records AS (
    SELECT 
        *,
        SUM(CASE WHEN is_high_temp = 1 AND prev_is_high = 1 AND TIMESTAMPDIFF(MINUTE, prev_ttime, ttime) = 5 THEN 0 ELSE 1 END) OVER (ORDER BY ttime) AS group_id
    FROM (
        SELECT 
            *,
            LAG(is_high_temp) OVER (ORDER BY ttime) AS prev_is_high,
            LAG(ttime) OVER (ORDER BY ttime) AS prev_ttime
        FROM high_temp_marks
    ) AS sub
)
SELECT * FROM grouped_records;

步骤3：计算时长并筛选目标区间 #

对每个高温分组计算开始、结束时间和持续时长，筛选出持续超过2小时且区间在24小时内的记录：

WITH high_temp_marks AS (
    SELECT 
        temp,
        ttime,
        CASE WHEN temp > 40 THEN 1 ELSE 0 END AS is_high_temp
    FROM your_table_name
),
grouped_records AS (
    SELECT 
        *,
        SUM(CASE WHEN is_high_temp = 1 AND prev_is_high = 1 AND TIMESTAMPDIFF(MINUTE, prev_ttime, ttime) = 5 THEN 0 ELSE 1 END) OVER (ORDER BY ttime) AS group_id
    FROM (
        SELECT 
            *,
            LAG(is_high_temp) OVER (ORDER BY ttime) AS prev_is_high,
            LAG(ttime) OVER (ORDER BY ttime) AS prev_ttime
        FROM high_temp_marks
    ) AS sub
),
duration_calc AS (
    SELECT 
        group_id,
        MIN(ttime) AS start_time,
        MAX(ttime) AS end_time,
        TIMESTAMPDIFF(MINUTE, MIN(ttime), MAX(ttime)) AS duration_minutes
    FROM grouped_records
    WHERE is_high_temp = 1
    GROUP BY group_id
)
SELECT 
    start_time,
    end_time,
    duration_minutes,
    CONCAT(FLOOR(duration_minutes / 60), '小时', duration_minutes % 60, '分钟') AS duration
FROM duration_calc
WHERE duration_minutes > 120 -- 2小时等于120分钟
  AND TIMESTAMPDIFF(HOUR, start_time, end_time) <= 24; -- 确保区间在24小时周期内

补充说明 #

• 因为数据是每5分钟一条，连续的高温记录时间间隔应该刚好是5分钟，所以用TIMESTAMPDIFF(MINUTE, prev_ttime, ttime) = 5判断连续性
• 如果存在数据缺失的情况，可以调整连续性判断逻辑，比如允许时间间隔不超过5分钟
• 最终结果会列出所有符合要求的高温区间，包含开始结束时间和时长

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