文档

最新值查询

更新时间:

最新值查询是指使用LATEST函数,匹配时间线的最新时间点的值,获取时序表指定列中最新N条数据的查询方式。

引擎与版本

最新值查询仅适用于时序引擎。无版本限制。

语法

select_latest_by_statement ::=  SELECT ( select_clause )
                                FROM table_identifier
                                WHERE where_clause 
                                SAMPLE BY 0
select_clause              ::=  selector [ AS identifier ] ( ',' selector [ AS identifier ] )
selector                   ::=  tag_identifier | time | latest '(' field_name [ ',' N ] ')'
where_clause               ::=  relation ( AND relation )* (OR relation)*
relation                   ::=  ( field_identifier | tag_identifier ) operator term
operator                   ::=  '=' | '<' | '>' | '<=' | '>=' | '!=' | IN 
说明

最新值查询不能直接与offset、limit、group by field等子句一起使用,但可以通过子查询的方式配合使用。

使用说明

selector

支持将标签列(tag_identifier)、时间戳列(time)和最新值函数表达式设置为selector。

最新值函数表达式(latest '(' field_name [ ',' N] ')')

仅支持LATEST函数,且不支持LATEST函数与其他函数组合使用,例如RATE、DELTA、MAX、MIN等函数。LATEST函数的参数说明如下:

参数

描述

field_name

Field列名。

N

整型,用于指定查询最新的N条数据。默认值为1,表示获取最新的一条数据。

示例

假设示例表sensor的结构如下:

-- 创建表
CREATE TABLE sensor (
    device_id VARCHAR TAG,
    region VARCHAR TAG,
    time   TIMESTAMP,
    temperature DOUBLE,
    humidity DOUBLE,
    PRIMARY KEY(device_id));

-- 写入数据
INSERT INTO sensor (device_id, region, time, temperature, humidity) VALUES
    ('F07A1260','north-cn','2021-04-22 15:33:00',12.1,45),
    ('F07A1260','north-cn','2021-04-22 15:33:10',13.2,47),
    ('F07A1260','north-cn','2021-04-22 15:33:20',10.6,null),
    ('F07A1260','north-cn','2021-04-22 15:33:30',null,48),
    ('F07A1260','north-cn','2021-04-22 15:33:20',10.6,46),
    ('F07A1261','south-cn','2021-04-22 15:33:00',18.1,44),
    ('F07A1261','south-cn','2021-04-22 15:33:10',19.7,44);
说明

null表示该时间点未写入数据。

查询最新一条数据

查询设备F07A1260F07A1261的最新温度数据。

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 |
+-----------+----------+---------------------------+-------------+
| F07A1261  | south-cn | 2021-04-22T15:33:10+08:00 | 19.7        |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.6        |
+-----------+----------+---------------------------+-------------+

查询最新N条数据

查询设备F07A1260F07A1261最新两条温度数据。

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 |
+-----------+----------+---------------------------+-------------+
| F07A1261  | south-cn | 2021-04-22T15:33:10+08:00 | 19.7        |
| F07A1261  | south-cn | 2021-04-22T15:33:00+08:00 | 18.1        |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.6        |
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00 | 13.2        |
+-----------+----------+---------------------------+-------------+

查询多列数据的最新值

查询设备F07A1260多列的最新值。

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-04-22T15:33:30+08:00 | null        | 48       |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.6        | null     |
+-----------+----------+---------------------------+-------------+----------+
说明

如果最新值在时间上无法对齐,则返回null。

嵌套查询

结合GROUP BY子句,分别查询设备F07A1260F07A1261最新两条温度数据中的最大值。

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  | 19.7            |
| F07A1260  | 13.2            |
+-----------+-----------------+

分页查询

查询设备F07A1260F07A1261的最新两条温度数据,指定LIMIT(返回结果数量)和OFFSET(偏移量)为1,返回查询结果中的第二条数据。

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 |
+-----------+----------+-------------+
| F07A1261  | south-cn | 18.1        |
+-----------+----------+-------------+

  • 本页导读 (1)
文档反馈