查询时间线的最新值 - 云原生多模数据库 Lindorm

最新值查询是指使用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表示该时间点未写入数据。

查询最新一条数据

查询设备F07A1260和F07A1261的最新温度数据。

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条数据

查询设备F07A1260和F07A1261最新两条温度数据。

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子句，分别查询设备F07A1260和F07A1261最新两条温度数据中的最大值。

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            |
+-----------+-----------------+

分页查询

查询设备F07A1260和F07A1261的最新两条温度数据，指定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        |
+-----------+----------+-------------+