如何通过SQL获取时间序列中每日温度的最大变化率？

没有直接的聚合函数能一步算出这个结果，但可以用窗口函数+分组聚合的组合来实现，比程序逐行读取效率高很多，且直接在数据库端完成计算。

核心思路 #

1. 用窗口函数LAG()获取同一日内上一条记录的温度和时间戳
2. 计算相邻时间点的温度变化率（单位时间内的温度变化幅度）
3. 按日期分组，取出每日的最大变化率

不同数据库的实现示例 #

现代SQL数据库（PostgreSQL、MySQL 8.0+、SQL Server、BigQuery等）

这些数据库支持标准窗口函数LAG()，代码最简洁：

WITH daily_temp_changes AS (
    SELECT
        DATE(timestamp) AS measurement_date,
        -- 计算每秒温度变化率（绝对值），可根据需求调整时间单位
        ABS(temperature - LAG(temperature) OVER (PARTITION BY DATE(timestamp) ORDER BY timestamp)) 
        / EXTRACT(EPOCH FROM (timestamp - LAG(timestamp) OVER (PARTITION BY DATE(timestamp) ORDER BY timestamp))) AS temp_rate_per_second
    FROM
        your_table_name
)
SELECT
    measurement_date,
    MAX(temp_rate_per_second) AS max_daily_temp_rate
FROM
    daily_temp_changes
WHERE
    temp_rate_per_second IS NOT NULL  -- 跳过每组第一条记录（无前置数据）
GROUP BY
    measurement_date
ORDER BY
    measurement_date;

旧版本数据库（如MySQL 5.x）

如果不支持LAG()，可以用自关联替代，虽然效率稍低，但能实现需求：

SELECT
    DATE(a.timestamp) AS measurement_date,
    MAX(ABS(a.temperature - b.temperature) / EXTRACT(EPOCH FROM (a.timestamp - b.timestamp))) AS max_daily_temp_rate
FROM
    your_table_name a
JOIN
    your_table_name b 
    ON DATE(a.timestamp) = DATE(b.timestamp) 
    AND b.timestamp = (SELECT MAX(timestamp) FROM your_table_name WHERE DATE(timestamp) = DATE(a.timestamp) AND timestamp < a.timestamp)
GROUP BY
    measurement_date
ORDER BY
    measurement_date;

自定义调整说明 #

• 如果你的「变化率」指的是相邻时间点的温度差绝对值（不考虑时间间隔），直接去掉时间差的分母即可：

  ABS(temperature - LAG(temperature) OVER (PARTITION BY DATE(timestamp) ORDER BY timestamp)) AS temp_change
  

• 时间单位可调整：比如要算每分钟变化率，就把分母除以60；每小时则除以3600
• 若存在同一时间戳多条温度记录，建议先做聚合（如取平均、取第一条），避免计算异常

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