查詢時間軸的最新值 - 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        |
+-----------+----------+-------------+