如何获取指定设备组中各传感器的最新值？（附表结构及部分SQL）

我通常会把这类需求拆成两个核心步骤：先锁定最新版本对应的设备范围，再在该范围内为每个sensorID筛选出最新timestamp的记录。下面提供两种实用的实现方式，你可以根据自己数据库的版本和支持情况选择：

方法1：使用窗口函数（推荐，适用于PostgreSQL、MySQL 8.0+、SQL Server等） #

窗口函数处理「分组取最新记录」的需求逻辑清晰，性能也更稳定：

WITH latest_version_scope AS (
    -- 第一步：锁定目标网关下的最新版本及对应设备
    SELECT 
        gatewayMAC, 
        deviceMAC,
        (SELECT MAX(meshSetVersion) FROM test WHERE gatewayMAC='XXX') AS last_version
    FROM test 
    WHERE gatewayMAC='XXX' 
      AND meshSetVersion = (SELECT MAX(meshSetVersion) FROM test WHERE gatewayMAC='XXX')
    GROUP BY gatewayMAC, deviceMAC
),
sensor_rankings AS (
    -- 第二步：对每个sensorID的记录按时间戳倒序排名
    SELECT 
        t.sensorID,
        t.sensorValue,
        t.timestamp,
        ROW_NUMBER() OVER (PARTITION BY t.sensorID ORDER BY t.timestamp DESC) AS rank_num
    FROM test t
    JOIN latest_version_scope lvs 
        ON t.gatewayMAC = lvs.gatewayMAC 
        AND t.deviceMAC = lvs.deviceMAC
        AND t.meshSetVersion = lvs.last_version
)
-- 第三步：取每个sensorID排名第一的记录（即最新的那条）
SELECT sensorID, sensorValue, timestamp
FROM sensor_rankings
WHERE rank_num = 1;

代码说明： #

• latest_version_scope 公共表表达式：一次性筛选出目标网关下最新版本的所有设备，避免后续重复计算最新版本号
• sensor_rankings 公共表表达式：用ROW_NUMBER()按sensorID分组，每组内按timestamp降序排序，最新的记录会被标记为排名1
• 最后只保留排名为1的记录，就是每个传感器的最新值

方法2：使用关联子查询（兼容低版本数据库，比如MySQL 5.x） #

如果你的数据库不支持窗口函数，可以用子查询先找到每个sensorID的最大时间戳，再关联原表获取对应值：

SELECT 
    t.sensorID,
    t.sensorValue,
    t.timestamp
FROM test t
INNER JOIN (
    -- 先获取最新版本下每个sensorID的最大时间戳
    SELECT 
        sensorID,
        MAX(timestamp) AS latest_timestamp
    FROM test
    WHERE 
        gatewayMAC='XXX'
        AND meshSetVersion = (SELECT MAX(meshSetVersion) FROM test WHERE gatewayMAC='XXX')
    GROUP BY sensorID
) latest_sensor_times 
    ON t.sensorID = latest_sensor_times.sensorID 
    AND t.timestamp = latest_sensor_times.latest_timestamp
WHERE 
    t.gatewayMAC='XXX'
    AND t.meshSetVersion = (SELECT MAX(meshSetVersion) FROM test WHERE gatewayMAC='XXX');

代码说明： #

• 内层子查询先计算出最新版本下每个sensorID的最大时间戳
• 再将原表与这个子查询结果关联，匹配到对应时间戳的记录，就是每个传感器的最新值

小优化建议： #

• 如果gatewayMAC和meshSetVersion的组合查询频率很高，可以给这两个字段建立联合索引，能大幅提升查询速度
• 如果同一个sensorID在同一时间戳下有多条记录，方法1会随机返回一条，方法2会返回所有同时间戳的记录，你可以根据实际需求调整（比如用RANK()代替ROW_NUMBER()，或者在子查询中添加额外筛选条件）

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