本文介绍时序引擎中查询每条时间线最新值的语法。
语法
select_latest_by_statement ::= SELECT ( select_clause )
FROM table_name
WHERE where_clause
SAMPLE BY 0
select_clause ::= selector [ AS identifier ] ( ',' selector [ AS identifier ] )
selector ::= tag_name | time | latest '(' field_name [ ',' integer ] ')'
where_clause ::= relation ( AND relation )* (OR relation)*
relation ::= ( field_name | tag_name ) operator term
operator ::= '=' | '<' | '>' | '<=' | '>=' | '!=' | IN 说明
- 最新值查询用于查询匹配时间线的最新时间点的值，支持查询最新的N条数据：
latest(field_name, N)。 - 最新值查询需要与
SAMPLE BY 0配合使用，表示查询每条时间线各自的最新值。 - 最新值查询不支持与其他聚合函数：rate/delta/max/min等联合使用，例如：
select time, latest(f1), max(f2) from sensor sample by 0。 latest函数说明，请参见LATEST最新值。
示例
查询设备F07A1260和F07A1261的最新温度：
lindorm> select device_id,region,time,latest(temperature) as temperature from sensor where device_id in ('F07A1260', 'F07A1261') sample by 0;
+-----------+----------+---------------------------+-------------+
| device_id | region | time | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260 | north-cn | 2021-01-01T20:00:00+08:00 | 10.000000 |
| F07A1261 | north-cn | 2021-05-01T13:00:00+08:00 | 6.000000 |
+-----------+----------+---------------------------+-------------+查询设备F07A1260和F07A1261的最新温度和最新湿度：
lindorm> select device_id,region,time,latest(temperature) as temperature, latest(humidity) as humidity from sensor where device_id in ('F07A1260', 'F07A1261') sample by 0;
+-----------+----------+---------------------------+-------------+-----------+
| device_id | region | time | temperature | humidity |
+-----------+----------+---------------------------+-------------+-----------+
| F07A1260 | north-cn | 2021-01-01T20:00:00+08:00 | 10.000000 | 47.000000 |
| F07A1261 | north-cn | 2021-05-01T13:00:00+08:00 | 6.000000 | 43.000000 |
+-----------+----------+---------------------------+-------------+-----------+latest查询多列的最新值时，如果多个列最新值在时间上无法对齐，则对应填写null，如下示例：
lindorm> select device_id,region,time,latest(temperature) as temperature, latest(humidity) as humidity from sensor where device_id in ('F07A1260') sample by 0;
+-----------+----------+---------------------------+-------------+-----------+
| device_id | region | time | temperature | humidity |
+-----------+----------+---------------------------+-------------+-----------+
| F07A1260 | north-cn | 2021-01-01T12:00:00+08:00 | 15.200000 | null |
| F07A1260 | north-cn | 2021-01-01T13:00:00+08:00 | null | 47.000000 |
+-----------+----------+---------------------------+-------------+-----------+查询设备F07A1260和F07A1261最新的两条温度测点：
lindorm> select device_id,region,time,latest(temperature, 2) as temperature from sensor where device_id in ('F07A1260', 'F07A1261') sample by 0;
+-----------+----------+---------------------------+-------------+
| device_id | region | time | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260 | north-cn | 2021-01-01T14:03:00+08:00 | 2.000000 |
| F07A1260 | north-cn | 2021-01-01T20:00:00+08:00 | 10.000000 |
| F07A1261 | north-cn | 2021-03-08T12:08:00+08:00 | 5.000000 |
| F07A1261 | north-cn | 2021-05-01T13:00:00+08:00 | 6.000000 |
+-----------+----------+---------------------------+-------------+说明 最新值查询不能直接与offset、limit、group by field等子句一起使用，但可以通过子查询的方式配合使用。
latest配合group by进行分组：
lindorm> select device_id, max(temperature) as max_temperature from (select device_id, region,time, latest(temperature,2) as temperature from sensor sample by 0) group by device_id;
+-----------+-----------------+
| device_id | max_temperature |
+-----------+-----------------+
| F07A1261 | 6.000000 |
| F07A1260 | 10.000000 |
+-----------+-----------------+latest配合limit、offset限制结果条数：
lindorm> select device_id, region, temperature from (select device_id, region, time, latest(temperature,2) as temperature from sensor sample by 0) limit 1 offset 1;
+-----------+----------+-------------+
| device_id | region | temperature |
+-----------+----------+-------------+
| F07A1260 | north-cn | 10.000000 |
+-----------+----------+-------------+