如何编写SQL查询计算Temperature1过去1小时超45的时间占比？

嘿，这个需求我挺熟悉的——因为传感器在数值稳定时不会每秒都生成记录，直接数符合条件的记录数除以总记录数肯定不准，得换个思路计算状态持续的时间。咱们一步步来搞定它：

核心思路 #

因为记录不是连续每秒生成的，所以关键是计算每个数值状态的持续时长，再累加阈值以上的总时长，最后除以过去一小时的总时长（3600秒）得到占比。

具体SQL代码（适配PostgreSQL/MySQL 8.0+） #

WITH sensor_time_series AS (
    -- 第一步：筛选过去一小时的Temperature1数据，同时获取每条记录的下一条时间
    SELECT
        timestamp,
        Temperature1,
        -- 最后一条记录用当前时间作为状态结束时间
        LEAD(timestamp, 1, NOW()) OVER (ORDER BY timestamp) AS next_record_time
    FROM
        your_sensor_table
    WHERE
        timestamp >= NOW() - INTERVAL '1 hour' -- MySQL中写法为 INTERVAL 1 HOUR
        AND sensor_name = 'Temperature1' -- 如果表中有多传感器，需指定目标传感器
),
state_durations AS (
    -- 第二步：计算每条记录对应的状态持续秒数
    SELECT
        Temperature1,
        -- 时间差转秒（不同数据库语法略有差异）
        -- PostgreSQL用下面这行，MySQL替换为 TIMESTAMPDIFF(SECOND, timestamp, next_record_time)
        EXTRACT(EPOCH FROM (next_record_time - timestamp)) AS duration_sec
    FROM
        sensor_time_series
)
-- 第三步：统计阈值以上时间占比
SELECT
    ROUND(
        COALESCE(SUM(CASE WHEN Temperature1 > 45 THEN duration_sec ELSE 0 END), 0) / 3600,
        2
    ) AS above_threshold_ratio
FROM
    state_durations;

关键细节说明 #

• 窗口函数LEAD()：用来获取下一条记录的时间，完美解决了“数值不变时无记录”导致的时长缺失问题
• COALESCE()：处理极端情况——如果过去一小时内没有符合条件的记录，SUM会返回NULL，用0替代保证结果合法
• 时间差适配：不同数据库转秒的语法不一样，上面代码标注了PostgreSQL和MySQL的差异
• 边界处理：最后一条记录的结束时间用NOW()，确保覆盖到当前时刻的传感器状态

如果你的数据库版本不支持窗口函数（比如MySQL 5.x），可以用自连接的方式实现类似逻辑，需要的话我可以再补充代码~

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