如何用SQL计算每辆车各自最近24小时的平均速度？

原SQL的问题分析 #

你之前的语句存在两个核心问题：

• 子查询中定义的别名s属于子查询内部作用域，无法被外部WHERE子句访问，导致语法报错。
• 逻辑错误：IN条件仅匹配每辆车的最新一条记录，而非最新时间倒推24小时内的所有数据，根本无法计算时间范围内的平均值。

可行方案 #

方案1：使用JOIN关联最新时间（兼容多数数据库）

先通过子查询获取每辆车的最新上报时间，再与原表关联筛选出符合时间范围的记录，最后分组计算平均速度：

SELECT d.name, AVG(d.speed) AS avg_speed_last_24h
FROM data d
JOIN (
    -- 先获取每辆车的最新上报时间
    SELECT name, MAX(timestamp) AS latest_ts
    FROM data
    GROUP BY name
) t ON d.name = t.name
-- 筛选当前记录时间在最新时间倒推24小时内的条目
WHERE d.timestamp >= t.latest_ts - 86400
GROUP BY d.name;

方案2：使用窗口函数（适用于支持窗口函数的数据库，如MySQL 8+、PostgreSQL）

通过窗口函数MAX() OVER (PARTITION BY name)直接为每条记录标记所属车辆的最新上报时间，再筛选计算：

SELECT name, AVG(speed) AS avg_speed_last_24h
FROM (
    SELECT 
        name, 
        speed, 
        timestamp,
        -- 为每条记录添加所属车辆的最新时间标记
        MAX(timestamp) OVER (PARTITION BY name) AS latest_ts
    FROM data
) sub
WHERE timestamp >= latest_ts - 86400
GROUP BY name;

说明 #

两种方案都能准确筛选出每辆车基于自身最新上报时间倒推24小时内的所有速度数据，并计算出对应平均值，适配你描述的20辆车、每车约3000条数据的场景。

内容的提问来源于stack exchange，提问作者Døner M.