AWS Timestream多测量值关联查询：如何筛选满足多指标条件的记录

Timestream的窄表存储结构下，多测量值联合查询可以通过两种常见方式实现，以下是对应实现逻辑和示例代码： #

方法1：子查询关联法 #

分别筛选出符合单条件的两个测量值数据集，以truck_id和采集时间作为关联键做内连接，即可匹配到同时满足两个条件的记录。
参考SQL如下：

WITH speed_records AS (
    SELECT 
        time, 
        truck_id, 
        measure_value::double AS speed
    FROM <你的数据库名>.<你的表名>
    WHERE 
        measure_name = 'speed'
        AND truck_id = '<指定卡车ID>'
        AND time BETWEEN '<起始时间>' AND '<结束时间>'
        AND measure_value::double > 40
),
load_records AS (
    SELECT 
        time, 
        truck_id, 
        measure_value::double AS load
    FROM <你的数据库名>.<你的表名>
    WHERE 
        measure_name = 'load'
        AND truck_id = '<指定卡车ID>'
        AND time BETWEEN '<起始时间>' AND '<结束时间>'
        AND measure_value::double < 100
)
SELECT 
    s.time,
    s.truck_id,
    s.speed,
    l.load
FROM speed_records s
INNER JOIN load_records l
    ON s.truck_id = l.truck_id 
    AND s.time = l.time
ORDER BY s.time ASC

该方法逻辑清晰，适合测量值关联数量较少的场景，查询性能稳定。 #

方法2：PIVOT行转列法 #

如果需要关联的测量值更多，可以先用PIVOT语法将行式存储的测量值转换为列，再统一做条件过滤。
参考SQL如下：

SELECT *
FROM (
    SELECT 
        time, 
        truck_id, 
        measure_name, 
        measure_value::double AS value
    FROM <你的数据库名>.<你的表名>
    WHERE 
        measure_name IN ('speed', 'load')
        AND truck_id = '<指定卡车ID>'
        AND time BETWEEN '<起始时间>' AND '<结束时间>'
)
PIVOT (
    MAX(value) FOR measure_name IN ('speed' AS speed, 'load' AS load)
)
WHERE 
    speed > 40 
    AND load < 100
ORDER BY time ASC

注意事项 #

• 如果你的测量值不是浮点类型，需要把代码中的measure_value::double替换为对应类型，比如measure_value::bigint、measure_value::boolean等
• 如果两个测量值的采集时间不完全对齐，可以将关联条件中的s.time = l.time调整为时间窗口匹配，例如ABS(to_unixtime(s.time) - to_unixtime(l.time)) < 1代表允许1秒以内的时间误差
• 无论使用哪种方法，都建议在子查询阶段就限定好设备ID、时间范围和测量名称，避免扫描全量数据导致查询性能下降。

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