You can use the LATEST function to query the latest N rows of data in the specified column of a time series table based on the latest time point in the time series.
Applicable engines and versions
Latest-value queries are applicable only to all versions of LindormTSDB.
Syntax
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 Latest-value queries cannot be directly used with clauses such as OFFSET, LIMIT, or GROUP BY. You can use a latest-value query as a subquery in a SQL statement that contains these clauses.
Parameters
selector
You can specify the tag column (tag_identifier), timestamp column (time), and the expression of the LATEST function as the selector.
The expression of the LATEST function is in the following format: latest '(' field_name [ ',' N] ')'
Only the LATEST function is supported in the expression. The LATEST function cannot be used together with other functions, such as RATE, DELTA, MAX, and MIN. The following table describes the parameters that can be configured for the LATEST function.
Parameter | Description |
field_name | The name of the field column. |
N | An integer that specifies the number of latest values that you want to query in the field column. The default value of this parameter is 1, which indicates that only latest value in the field column is queried |
Examples
In the following examples, the sample table sensor is created by executing the following statement:
-- Create the table.
CREATE TABLE sensor (
device_id VARCHAR TAG,
region VARCHAR TAG,
time TIMESTAMP,
temperature DOUBLE,
humidity DOUBLE,
PRIMARY KEY(device_id));
-- Insert data into the table.
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);The value of null in the preceding statement indicates that no data is written to the table at the corresponding point in time.
Query the latest row of data
Query the latest temperature value of the F07A1260 and F07A1261 devices.
SELECT device_id,region,time,latest(temperature) AS temperature FROM sensor WHERE device_id in ('F07A1260', 'F07A1261') SAMPLE BY 0;The following result is returned:
+-----------+----------+---------------------------+-------------+
| 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 |
+-----------+----------+---------------------------+-------------+Query the latest N rows of data
Query the latest two temperature values of the F07A1260 and F07A1261 devices.
SELECT device_id,region,time,latest(temperature, 2) AS temperature FROM sensor WHERE device_id in ('F07A1260', 'F07A1261') SAMPLE BY 0;The following result is returned:
+-----------+----------+---------------------------+-------------+
| 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 |
+-----------+----------+---------------------------+-------------+Query the latest value in multiple columns
Query the latest value of the F07A1260 device in multiple columns.
SELECT device_id,region,time,latest(temperature) AS temperature, latest(humidity) AS humidity FROM sensor WHERE device_id in ('F07A1260') SAMPLE BY 0;The following result is returned:
+-----------+----------+---------------------------+-------------+----------+
| 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 |
+-----------+----------+---------------------------+-------------+----------+The latest values in multiple columns may be located in different rows. Other values in the rows are filled with the value null for alignment.
Perform a nested query
Use the GROUP BY clause to query the maximum value of the latest two temperature values of the F07A1260 and F07A1261 devices.
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;The following result is returned:
+-----------+-----------------+
| device_id | max_temperature |
+-----------+-----------------+
| F07A1261 | 19.7 |
| F07A1260 | 13.2 |
+-----------+-----------------+Perform a paged query
Query the latest two temperature values of the F07A1260 and F07A1261 devices, and set LIMIT and OFFSET to 1 to return the second row of data in the query result.
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;The following result is returned:
+-----------+----------+-------------+
| device_id | region | temperature |
+-----------+----------+-------------+
| F07A1261 | south-cn | 18.1 |
+-----------+----------+-------------+