语法

select_sample_by_statement ::=  SELECT ( select_clause | '*' )
                                FROM table_name
                                WHERE where_clause
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 | CONTAINS | CONTAINS KEY

说明

最新值查询用于查询匹配时间线的最新时间点的值,支持查询最新的N条数据:latest(field_name, N)

示例

查询设备1234ABC1234ABB的最新温度和湿度:

> select device_id,region,time,latest(temperature) as temperature,latest(humidity) as humidity from sensor where device_id in ('1234ABC', '1234ABB') and  time >= 1619076780000 and time <= 1619076800000;
+------------+-----------+----------------+--------------+-----------+
| device_id  |  region   |      time      | temperature  | humidity  |
+------------+-----------+----------------+--------------+-----------+
| 1234ABC   | north-cn  | 1619076800000  | 10.6         | 46        |
| 1234ABB   | south-cn  | 1619076790000  | 19.7         | 44        |
+------------+-----------+----------------+--------------+-----------+
2 rows selected (0.113 seconds)

查询设备1234ABC1234ABB的最新的两条温度和湿度测点:

> select device_id,region,time,latest(temperature,2) as temperature,latest(humidity) as humidity from sensor where device_id in ('1234ABC', '1234ABB') and  time >= 1619076780000 and time <= 1619076800000;
+------------+-----------+----------------+--------------+-----------+
| device_id  |  region   |      time      | temperature  | humidity  |
+------------+-----------+----------------+--------------+-----------+
| 1234ABC   | north-cn  | 1619076790000  | 13.2         | 47        |
| 1234ABC   | north-cn  | 1619076800000  | 10.6         | 46        |
| 1234ABB   | south-cn  | 1619076780000  | 18.1         | 44        |
| 1234ABB   | south-cn  | 1619076790000  | 19.7         | 44        |
+------------+-----------+----------------+--------------+-----------+
4 rows selected (0.078 seconds)
说明
  • 最新值查询不能与GROUP BY、LIMIT、OFFSET使用,但可以使用子查询的方式配合使用。
  • 最新值查询不能与降采样一起使用。

当查询多个Field时,如果某一个时间点、某一个Field没有值,则默认为null,如:

> select device_id,region,time,latest(temperature,2) as temperature,latest(humidity) as humidity from sensor where device_id in ('1234ABC', '1234ABB') and  time >= 1619076780000 and time <= 1619076800000;
+------------+-----------+----------------+--------------+-----------+
| device_id  |  region   |      time      | temperature  | humidity  |
+------------+-----------+----------------+--------------+-----------+
| 1234ABC   | north-cn  | 1619076790000  | 13.2         | 47        |
| 1234ABC   | north-cn  | 1619076800000  |              | 46        | *
| 1234ABB   | south-cn  | 1619076780000  | 18.1         |           | *
| 1234ABB   | south-cn  | 1619076790000  | 19.7         | 44        |
+------------+-----------+----------------+--------------+-----------+
4 rows selected (0.078 seconds)