如何从温度计存储数据中计算温度持续高于85℉的时长？

嘿，我完全懂你现在的困扰——这种连续状态的时间统计确实容易卡壳！结合你提到的“找高温时间+后续首次低温时间”的思路，我给你整理了两种实用的SQL方案，刚好匹配你的需求，甚至能处理连续5分钟以上的高温区间统计。

方法一：用窗口函数分组统计连续高温区间（推荐） #

这种方法更高效，尤其适合数据量大的场景，核心是把连续的高温记录归为同一组，再计算每组的持续时长。假设你的表名为readings，包含reading_time（时间戳）和temp_f（华氏温度）两个字段：

WITH high_temp_readings AS (
    SELECT 
        reading_time,
        temp_f,
        -- 标记新的高温区间：当前记录和上一条高温记录间隔超过5分钟，视为新起点
        CASE 
            WHEN TIMESTAMPDIFF(MINUTE, LAG(reading_time) OVER (ORDER BY reading_time), reading_time) > 5 THEN 1 
            ELSE 0 
        END AS is_new_interval
    FROM readings
    WHERE temp_f > 85
),
interval_groups AS (
    SELECT 
        reading_time,
        temp_f,
        -- 累加标记值，为每个连续高温区间分配唯一ID
        SUM(is_new_interval) OVER (ORDER BY reading_time) AS interval_id
    FROM high_temp_readings
)
-- 计算每个区间的持续时长，可筛选≥5分钟的区间
SELECT 
    interval_id,
    MIN(reading_time) AS interval_start,
    MAX(reading_time) AS interval_end,
    TIMESTAMPDIFF(MINUTE, MIN(reading_time), MAX(reading_time)) AS duration_minutes
FROM interval_groups
GROUP BY interval_id
-- 按需开启：只保留持续5分钟及以上的高温区间
-- HAVING duration_minutes >= 5;

逻辑拆解 #

1. 过滤高温记录：high_temp_readings先筛选出所有温度超过85℉的记录，同时用LAG窗口函数对比当前记录与上一条的时间差，超过5分钟就标记为新区间的起点。
2. 分组连续区间：interval_groups通过累加标记值，把同一段连续高温的记录归为同一个interval_id。
3. 计算时长：最后按interval_id分组，取每组的最早/最晚时间，计算时间差就是该段高温的持续时长。

方法二：贴近你的思路——找每个高温点的后续首次低温点 #

如果更倾向于你最初的想法，我们可以用自连接+MIN函数找到每个高温点之后第一个低于/等于85℉的时间，再计算时间差：

WITH high_temp_with_end AS (
    SELECT 
        r.reading_time AS high_start,
        -- 找到当前高温记录之后，第一个温度≤85的时间点
        MIN(l.reading_time) AS high_end
    FROM readings r
    LEFT JOIN readings l 
        ON l.reading_time > r.reading_time 
        AND l.temp_f <= 85
    WHERE r.temp_f > 85
    GROUP BY r.reading_time
)
SELECT 
    high_start,
    -- 如果没有后续低温点（比如最后一条记录还是高温），用当前时间替代
    COALESCE(high_end, NOW()) AS high_end,
    TIMESTAMPDIFF(MINUTE, high_start, COALESCE(high_end, NOW())) AS duration_minutes
FROM high_temp_with_end
-- 按需筛选持续≥5分钟的记录
WHERE TIMESTAMPDIFF(MINUTE, high_start, COALESCE(high_end, NOW())) >= 5;

逻辑拆解 #

1. 匹配后续低温点：通过自连接，给每个高温记录找到之后第一个温度达标（≤85℉）的时间。
2. 处理无后续低温的情况：用COALESCE函数，如果没有找到后续低温点，就用当前时间作为结束时间。
3. 计算时长并筛选：直接计算时间差，还可以过滤出持续5分钟以上的记录。

两种方法各有侧重：方法一更适合批量统计所有连续高温区间，效率更高；方法二更贴近你的初始思路，适合关注单个高温点的后续时长。你可以根据实际数据量和需求选择～

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