What are the common functions used by LindormTSDB SQL - Lindorm

This topic describes the common functions supported by LindormTSDB SQL and provides examples.

Applicable engines and versions

The common time series functions described in this topic are applicable only to LindormTSDB.
These functions are supported by LindormTSDB 3.4.7 and later versions. For more information about how to view or upgrade the version of LindormTSDB, see Release notes of LindormTSDB and Upgrade the minor engine version of a Lindorm instance.

Common time series function list

Function	Description
CURRENT_TIMESTAMP	You can call this function to obtain the current system time.
SUM	You can call this function to calculate the sum of values in a field column.
AVG	You can call this function to calculate the average value of values in a field column.
COUNT	You can call this function to count the number of values in a field column.
MIN	You can call this function to query the minimum value of values in a field column.
MAX	You can call this function to query the maximum value of values in a field column.
FIRST	You can call this function to query the first value of values in a field column.
LAST	You can call this function to query the last value of values in a field column.
PERCENTILE	You can call this function to query the specified percentile of values in a field column.
RATE	You can call this function to calculate the slope of values in a field column.
DELTA	You can call this function to calculate the difference between each value and the value in the previous row within the same field column.
LATEST	You can call this function to query the latest N values in a field column.
FORECAST	You can call this function to forecast the Field values in a column of a time series table.
ANOMALY_DETECT	You can call this function to detect anomalies in a specified field column.
BINS	You can call this function to perform feature binning on the data in the specified field column of a time series table.

Data preparation

In this topic, a sample table named sensor with the following schema is used:

+-------------+-----------+------------+
| columnName  | typeName  | columnKind |
+-------------+-----------+------------+
| device_id   | VARCHAR   | TAG        |
| region      | VARCHAR   | TAG        |
| time        | TIMESTAMP | TIMESTAMP  |
| temperature | DOUBLE    | FIELD      |
| humidity    | DOUBLE    | FIELD      |
+-------------+-----------+------------+

This table is populated with the following data:

+-----------+----------+---------------------------+-------------+-----------+
| device_id |  region  |           time            | temperature | humidity  |
+-----------+----------+---------------------------+-------------+-----------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 12.100000   | 45.000000 |
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00 | 13.200000   | 47.000000 |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   | 46.000000 |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00 | 12.100000   | 51.000000 |
| F07A1261  | south-cn | 2021-04-22T15:51:21+08:00 | 13.200000   | 52.000000 |
| F07A1261  | south-cn | 2021-04-22T15:51:27+08:00 | 10.600000   | 53.000000 |
| F07A1261  | south-cn | 2021-04-22T15:51:47+08:00 | 20.600000   | 55.000000 |
+-----------+----------+---------------------------+-------------+-----------+

The sensor table contains the following two time series:

Time series 1 that has the device_id=F07A1260, region=north-cn tag and contains the temperature and humidity fields.
Time series 2 that has the device_id=F07A1261, region=south-cn tag and contains the temperature and humidity fields.

Note

For more information about time series, see Terms.

Timestamp function

CURRENT_TIMESTAMP

Overview

You can call this function to obtain the current system time.

Syntax

CURRENT_TIMESTAMP

Important

You do not need to specify parameters when you call the CURRENT_TIMESTAMP function.
The timestamp returned by the CURRENT_TIMESTAMP function is accurate to millisecond. Example: 2023-04-23T21:13:15.819+08:00.
The CURRENT_TIMESTAMP function returns the system time of the server on which the Lindorm instance is running. Therefore, make sure that the time data generated by this function can meet your requirements in your business.
Do not use the timestamps generated by this function when you write data in your business. We recommend that you use this function to generate large amounts of test data for testing and development. We recommend that you use this function to generate large amounts of test data for testing and development.

Examples

Add a row of data to the sensor table. The tag of the data is device_id=F07A1262, region=north-cn and the timestamp of the data is the current system time when the data is written to the table.

INSERT INTO sensor(device_id,region,time,temperature,humidity) VALUES ('F07A1262','north-cn',CURRENT_TIMESTAMP,19.9,42);

Aggregate functions

SUM

Overview

You can call this function to calculate the sum of values in a field column.

Syntax

SUM(field_name)

Note

The SUM function returns DOUBLE or BIGINT values.

Parameters

Parameter	Description
field_name	The name of the field column. Note Data in the specified field column cannot be of the VARCHAR or BOOLEAN type.

Examples

Example 1: Perform downsampling at a 20-second interval on the two time series that individually contain data generated by two devices whose IDs are F07A1260 and F07A1261. Then, calculate the sum of values in the temperature column.

SELECT device_id, region, time, sum(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 20s;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 25.300000   |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00 | 35.900000   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:40+08:00 | 20.600000   |
+-----------+----------+---------------------------+-------------+

Example 2: Individually calculate the sum of temperature values generated by two devices whose IDs are F07A1260 and F07A1261.

SELECT device_id, region, time, sum(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 1970-01-01T08:00:00+08:00 | 35.900000   |
| F07A1261  | south-cn | 1970-01-01T08:00:00+08:00 | 56.500000   |
+-----------+----------+---------------------------+-------------+

Example 3: Calculate the sum of temperature values generated by all devices.

SELECT sum(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00';

The following result is returned:

+-------------+
| temperature |
+-------------+
| 92.400000   |
+-------------+

AVG

Overview

You can call this function to calculate the average value of values in a field column.

Syntax

AVG(field_name)

Note

The AVG function returns DOUBLE values.

Parameters

Parameter	Description
field_name	The name of the field column. Note Data in the specified field column cannot be of the VARCHAR or BOOLEAN type.

Examples

SELECT device_id, region, time, avg(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 20s;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 12.650000   |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00 | 11.966667   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:40+08:00 | 20.600000   |
+-----------+----------+---------------------------+-------------+

Example 2: Individually calculate the average value of temperature values generated by two devices whose IDs are F07A1260 and F07A1261.

SELECT device_id, region, time, avg(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 1970-01-01T08:00:00+08:00 | 11.966667   |
| F07A1261  | south-cn | 1970-01-01T08:00:00+08:00 | 14.125000   |
+-----------+----------+---------------------------+-------------+

Example 3: Calculate the average value of temperature values generated by all devices.

SELECT avg(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00';

The following result is returned:

+-------------+
| temperature |
+-------------+
| 13.200000   |
+-------------+

COUNT

Overview

You can call this function to count the number of values in a field column.

Syntax

COUNT(field_name)

Note

The COUNT function returns BIGINT values.

Parameters

Parameter	Description
field_name	The name of the field column.

Examples

Example 1: Perform downsampling at a 20-second interval on the two time series that individually contain data generated by two devices whose IDs are F07A1260 and F07A1261. Then, count the number of values in the temperature column within each interval.

SELECT device_id, region, time, count(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 20s;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 2           |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00 | 3           |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 1           |
| F07A1261  | south-cn | 2021-04-22T15:51:40+08:00 | 1           |
+-----------+----------+---------------------------+-------------+

Example 2: Individually count the number of temperature values generated by two devices whose IDs are F07A1260 and F07A1261.

SELECT device_id, region, time, count(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 1970-01-01T08:00:00+08:00 | 3           |
| F07A1261  | south-cn | 1970-01-01T08:00:00+08:00 | 4           |
+-----------+----------+---------------------------+-------------+

Example 3: Count the number of temperature values generated by all devices.

SELECT count(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00';

The following result is returned:

+-------------+
| temperature |
+-------------+
| 7           |
+-------------+

Select functions

MIN

Overview

You can call this function to query the minimum value of values in a field column.

Syntax

MIN(field_name)

Note

The MIN function returns DOUBLE or BIGINT values.

Parameters

Parameter	Description
field_name	The name of the field column. Note Data in the specified field column cannot be of the VARCHAR or BOOLEAN type.

Examples

Example 1: Perform downsampling at a 20-second interval on the two time series that individually contain data generated by two devices whose IDs are F07A1260 and F07A1261. Then, query the minimum value of values in the temperature column within each interval.

SELECT device_id, region, time, min(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 20s;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 12.100000   |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00 | 10.600000   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:40+08:00 | 20.600000   |
+-----------+----------+---------------------------+-------------+

Example 2: Individually query the minimum value of temperature values generated by two devices whose IDs are F07A1260 and F07A1261.

SELECT device_id, region, time, min(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:27+08:00 | 10.600000   |
+-----------+----------+---------------------------+-------------+

Example 3: Query the minimum value of temperature values generated by all devices.

SELECT min(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00';

The following result is returned:

+-------------+
| temperature |
+-------------+
| 10.600000   |
+-------------+

MAX

Overview

You can call this function to query the maximum value of values in a field column.

Syntax

MAX(field_name)

Note

The MAX function returns DOUBLE or BIGINT values.

Parameters

Parameter	Description
field_name	The name of the field column. Note Data in the specified field column cannot be of the VARCHAR or BOOLEAN type.

Examples

Example 1: Perform downsampling at a 20-second interval on the two time series that individually contain data generated by two devices whose IDs are F07A1260 and F07A1261. Then, query the maximum value of values in the temperature column within each interval.

SELECT device_id, region, time, max(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 20s;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 13.200000   |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00 | 13.200000   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:40+08:00 | 20.600000   |
+-----------+----------+---------------------------+-------------+

Example 2: Individually query the maximum value of temperature values generated by two devices whose IDs are F07A1260 and F07A1261.

SELECT device_id, region, time, max(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00 | 13.200000   |
| F07A1261  | south-cn | 2021-04-22T15:51:47+08:00 | 20.600000   |
+-----------+----------+---------------------------+-------------+

Example 3: Query the maximum value of temperature values generated by all devices.

SELECT max(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00';

The following result is returned:

+-------------+
| temperature |
+-------------+
| 20.600000   |
+-------------+

FIRST

Overview

You can call this function to query the first value of values in a field column.

Syntax

FIRST(field_name)

Note

The FIRST function returns data whose type is the same as that of data in the specified field column.

Parameters

Parameter	Description
field_name	The name of the field column.

Examples

Example 1: Perform downsampling at a 20-second interval on the two time series that individually contain data generated by two devices whose IDs are F07A1260 and F07A1261. Then, query the first value of values in the temperature column within each interval.

SELECT device_id, region, time, first(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 20s;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 12.100000   |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00 | 12.100000   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:40+08:00 | 20.600000   |
+-----------+----------+---------------------------+-------------+

Example 2: Individually query the first value of temperature values generated by two devices whose IDs are F07A1260 and F07A1261.

SELECT device_id, region, time, first(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 12.100000   |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00 | 12.100000   |
+-----------+----------+---------------------------+-------------+

LAST

Overview

You can call this function to query the last value of values in a field column.

Syntax

LAST(field_name)

Note

The LAST function returns data whose type is the same as that of data in the specified field column.

Parameters

Parameter	Description
field_name	The name of the field column.

Examples

Example 1: Perform downsampling at a 20-second interval on the two time series that individually contain data generated by two devices whose IDs are F07A1260 and F07A1261. Then, query the last value of values in the temperature column within each interval.

SELECT device_id, region, time, last(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 20s;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 13.200000   |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00 | 10.600000   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:40+08:00 | 20.600000   |
+-----------+----------+---------------------------+-------------+

Example 2: Individually query the last value of temperature values generated by two devices whose IDs are F07A1260 and F07A1261.

SELECT device_id, region, time, last(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:47+08:00 | 20.600000   |
+-----------+----------+---------------------------+-------------+

PERCENTILE

Overview

You can call this function to query the specified percentile of values in a field column.

Syntax

PERCENTILE(field_name,P)

Note

The PERCENTILE function returns DOUBLE values.

Parameters

Parameter	Description
field_name	The name of the field column. Note Data in the specified field column cannot be of the VARCHAR or BOOLEAN type.
P	An integer or a floating-point number. Valid values: 0 to 100. Default value: 50.

Examples

Example 1: Perform downsampling at a 20-second interval on the two time series that individually contain data generated by two devices whose IDs are F07A1260 and F07A1261. Then, query the 90th percentile of values in the temperature column within each interval.

SELECT device_id, region, time, percentile(temperature, 90) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 20s;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 13.200000   |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00 | 13.200000   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:40+08:00 | 20.600000   |
+-----------+----------+---------------------------+-------------+

Example 2: Individually query the 90th percentile of temperature values generated by two devices whose IDs are F07A1260 and F07A1261.

SELECT device_id, region, time, percentile(temperature, 90) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 1970-01-01T08:00:00+08:00 | 13.200000   |
| F07A1261  | south-cn | 1970-01-01T08:00:00+08:00 | 20.600000   |
+-----------+----------+---------------------------+-------------+

Conversion functions

RATE

Overview

You can call this function to calculate the slope of values in a field column.

Assume that the following data points are stored in a time series: (t₁,v₁), (t₂,v₂) ... (t_N,v_N). If you use the RATE function to calculate the slope of the data points, N-1 data points are returned, whose timestamps and values are calculated by using the following formula:

2023-03-20_10-29-38

Note

The interval parameter in the preceding formula indicates the specified interval used to calculate the slope. The value of this parameter is measured in seconds.

Syntax

RATE(field_name, 'interval units')

Note

The RATE function returns DOUBLE values.
The RATE operator must be separately used in an SQL statement.

Parameters

Parameter	Description
field_name	The name of the field column. Note Data in the specified field column cannot be of the VARCHAR or BOOLEAN type.
interval units	The interval based on which the slope is calculated. Default value: 1s. Valid units: s (seconds), m (minutes), h (hours), and d (days).

Examples

Example 1: Individually calculate the slope of temperature values generated by two devices whose IDs are F07A1260 and F07A1261 within the one-second interval.

SELECT device_id, region, time, rate(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00 | 0.110000    |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | -0.260000   |
| F07A1261  | south-cn | 2021-04-22T15:51:21+08:00 | 1.100000    |
| F07A1261  | south-cn | 2021-04-22T15:51:27+08:00 | -0.433333   |
| F07A1261  | south-cn | 2021-04-22T15:51:47+08:00 | 0.500000    |
+-----------+----------+---------------------------+-------------+

Example 2: Individually calculate the slope of temperature values generated by two devices whose IDs are F07A1260 and F07A1261 within the one-minute interval.

SELECT device_id, region, time, rate(temperature, '1m') AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00 | 6.600000    |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | -15.600000  |
| F07A1261  | south-cn | 2021-04-22T15:51:21+08:00 | 66.000000   |
| F07A1261  | south-cn | 2021-04-22T15:51:27+08:00 | -26.000000  |
| F07A1261  | south-cn | 2021-04-22T15:51:47+08:00 | 30.000000   |
+-----------+----------+---------------------------+-------------+

Example 3: Perform downsampling at a 20-second interval on the two time series that individually contain data generated by two devices whose IDs are F07A1260 and F07A1261. Then, calculate the average values of values in the temperature column within each 20-second interval. At last, calculate the slope of the average values within the one-minute interval.

SELECT device_id, region, time, rate(avg(temperature), '1m') AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 20s;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | -6.150000   |
| F07A1261  | south-cn | 2021-04-22T15:51:40+08:00 | 25.900000   |
+-----------+----------+---------------------------+-------------+

Example 4: The RATE operator must be separately used in an SQL statement. Do not use the RATE operator in the methods shown in the following examples:

Error example 1:

SELECT region, time, rate(temperature), rate(humidity) FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

The following result is returned:

ERROR 9001 (0A000): UNSUPPORTED: latest/rate/delta must be the only aggregator in a single SQL statement

Error example 2:

SELECT region, time, rate(temperature), latest(humidity) FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

The following result is returned:

ERROR 9001 (0A000): UNSUPPORTED: latest/rate/delta must be the only aggregator in a single SQL statement

DELTA

Overview

You can call this function to calculate the difference between each value and the value in the previous row within the same field column.

Assume that the following data points are stored in a time series: (t₁,v₁), (t₂,v₂) ... (t_N,v_N). If you use the DELTA function, the following N-1 data points are returned: (t₂,v₂-v₁), (t₃,v₃-v₂) ... (t_N,v_N-,v_N-1).

Syntax

DELTA(field_name)

Note

The DELTA function returns DOUBLE values.
The DELTA operator must be separately used in an SQL statement.

Parameters

Parameter	Description
field_name	The name of the field column. Note Data in the specified field column cannot be of the VARCHAR or BOOLEAN type.

Examples

Example 1: Individually calculate the difference between each value and the value in the previous row within the same column of temperature values generated by two devices whose IDs are F07A1260 and F07A1261.

SELECT device_id, region, time, delta(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00 | 1.100000    |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | -2.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:21+08:00 | 1.100000    |
| F07A1261  | south-cn | 2021-04-22T15:51:27+08:00 | -2.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:47+08:00 | 10.000000   |
+-----------+----------+---------------------------+-------------+

Example 2: Perform downsampling at a 20-second interval on the two time series that individually contain data generated by two devices whose IDs are F07A1260 and F07A1261. Then, calculate the average values of values in the temperature column within each 20-second interval. At last, calculate the difference between each value and the value in the previous row within the column of average values.

SELECT device_id, region, time, delta(avg(temperature)) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 20s;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | -2.050000   |
| F07A1261  | south-cn | 2021-04-22T15:51:40+08:00 | 8.633333    |
+-----------+----------+---------------------------+-------------+

Example 3: The DELTA operator must be separately used in an SQL statement. Do not use the DELTA operator in the method shown in the following example.

Error example:

Error example: SELECT region, time, delta(temperature), rate(humidity) FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

The following result is returned:

ERROR 9001 (0A000): UNSUPPORTED: latest/rate/delta must be the only aggregator in a single SQL statement

LATEST

Overview

You can call this function to query the latest N values in a field column.

Syntax

LATEST(field_name, N)

Note

The LATEST function returns data whose type is the same as that of data in the specified field column.
The LATEST operator must be separately used in an SQL statement.

Parameters

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

Example 1: Individually query the latest temperature values generated by the devices whose IDs are F07A1260 and F07A1261.

SELECT device_id, region, time, latest(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1261  | south-cn | 2021-04-22T15:51:47+08:00 | 20.600000   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
+-----------+----------+---------------------------+-------------+

Example 2: Individually query the latest two temperature values generated by the devices whose IDs are F07A1260 and F07A1261.

SELECT device_id, region, time, latest(temperature, 2) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

The following result is returned:

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1261  | south-cn | 2021-04-22T15:51:27+08:00 | 10.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:47+08:00 | 20.600000   |
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00 | 13.200000   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
+-----------+----------+---------------------------+-------------+

Example 3: The LATEST operator must be separately used in an SQL statement. Do not use the LATEST operator in the method shown in the following example.

Error example:

SELECT region, time, latest(temperature), rate(humidity) FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

The following result is returned:

ERROR 9001 (0A000): UNSUPPORTED: latest/rate/delta must be the only aggregator in a single SQL statement

FORECAST

Prerequisites

The FORECAST function depends on Lindorm AI. To use the FORECAST function, you must first activate Lindorm AI for the instance.

Overview

You can call this function to forecast the Field values in a column of a time series table.

Syntax

FORECAST(field_name, model_name, options)

Note

The FORECAST function returns DOUBLE values.

Parameters

Parameter	Description
field_name	The name of the field column. Note Data in the specified field column cannot be of the VARCHAR or BOOLEAN type.
model_name	The name of the model used to forecast the values. Note The value of the model_name parameter is of the VARCHAR type.
options	The options that you can configure to adjust the forecast results. This parameter is optional. The value of this parameter is in the `key1=value1[, key2=value2]` format. batch_size: The number of time series based on which an inference is performed. This option is optional. When the number of time series in the cache reach the value of this option, a batch forecast is performed. The default value of this parameter is the number of time series in the training. The value of this parameter is of the INTEGER type. step: The step length at which the forecast result is output. This option is optional. The default value of this parameter is the value of the PREDICTION_LENGTH parameter that you specified in the training. The value of this parameter is of the INTEGER type. quantile_output: The percentile of the output forecast result. This option is optional. The in-database machine learning provides an array as the forecast result for a data point in a time series based on the specified model. You can specify the quantile_output option to select a data point from the array as the final result. The value of this parameter is of the DOUBLE type. Default value: 0.5. You must set the value of this parameter based on the algorithm that is used to generate the result. If the DeepARalgorithm is used, set this parameter to a value that ranges from 0 to 1. If the TFT algorithm is used, set this parameter to one of the following values: 0.1, 0.5, or 0.9.

Examples

Example 1: Forecast the temperature values within the specified time range in a time series table named sensor.

SELECT device_id, region, `time`, forecast(temperature, forecast_model) AS forecast_result FROM sensor WHERE `time` >= '2022-01-01T00:00:00+08:00' and `time` < '2022-01-01T00:01:00+08:00' sample by 0;

The following result is returned:

+-----------+----------+---------------------------+------------------+
| device_id |  region  |           time            |  forecast_result |
+-----------+----------+---------------------------+------------------+
| F07A1260  | north-cn | 2022-01-01T00:00:00+08:00 |  12.40307807     |
| F07A1260  | north-cn | 2022-01-01T00:00:20+08:00 |  11.36715841     |
| F07A1260  | north-cn | 2022-01-01T00:00:40+08:00 |  10.12969923     |
| F07A1261  | south-cn | 2022-01-01T00:00:00+08:00 |  26.51369649     |
| F07A1261  | south-cn | 2022-01-01T00:00:20+08:00 |  25.54403301     |
| F07A1261  | south-cn | 2022-01-01T00:00:40+08:00 |  24.46405267     |
+-----------+----------+---------------------------+------------------+

Example 2: Forecast the temperature values within the specified time range in the sensor table with the step option set to 2 and the quantile_output option set to 0.9.

SELECT device_id, region, `time`, forecast(temperature, forecast_model, 'step=2,quantile_output=0.9') AS forecast_result FROM sensor WHERE `time` >= '2022-01-01T00:00:00+08:00' and `time` < '2022-01-01T00:01:00+08:00' sample by 0;

The following result is returned:

+-----------+----------+---------------------------+------------------+
| device_id |  region  |           time            |  forecast_result |
+-----------+----------+---------------------------+------------------+
| F07A1260  | north-cn | 2022-01-01T00:00:00+08:00 |  13.12353792     |
| F07A1260  | north-cn | 2022-01-01T00:00:20+08:00 |  12.14833554     |
| F07A1261  | south-cn | 2022-01-01T00:00:00+08:00 |  26.73869304     |
| F07A1261  | south-cn | 2022-01-01T00:00:20+08:00 |  24.92990853     |
+-----------+----------+---------------------------+------------------+

ANOMALY_DETECT

Overview

You can call this function to detect anomalies in a specified field column.

Syntax

ANOMALY_DETECT(field_name, [algo_name | model_name], options)

Note

The ANOMALY_DETECT function returns BOOLEAN values.

Parameters

Parameter	Description
field_name	The name of the field column. Note Data in the specified field column cannot be of the VARCHAR or BOOLEAN type.
algo_name	The name of the algorithm used to detect anomalies. The online anomaly detection algorithms developed by DAMO Academy are supported. esd: an algorithm that is applicable to spiked anomalies, such as spikes in monitoring curves and scenarios in which a small number of data points are significantly different from other data points. nsigma: an algorithm that is simple and easy to analyze the causes of anomalies. ttest: an algorithm that is used to identify whether the metrics related to time series data are abnormal because of a change in the average value. istl-esd: an algorithm that is applicable to detect anomalies in periodic data. Note The algo_identifier parameter is applicable to scenarios in which in-database machine learning is not enabled and anomalies related to time series data must be detected.
model_name	The name of the model used to forecast the values. Note The value of the model_name parameter is of the VARCHAR type. The model_name parameter is applicable to scenarios in which in-database machine learning is enabled and anomalies related to time series data must be detected. For more information, see Activate Lindorm AI.
options	The options used to adjust the detection effect. This parameter is optional. The value of this parameter is in the `key1=value1[, key2=value2]` format. For more information, see Time series anomaly detection.

Examples

Example 1: Use the ESD algorithm to detect anomalies in the temperature data within a specific time range in a time series table named sensor.

SELECT device_id, region, time, anomaly_detect(temperature, 'esd') AS detect_result FROM sensor WHERE time >= '2022-01-01T00:00:00+08:00' and time < '2022-01-01T00:01:00+08:00' sample by 0;

The following result is returned:

+-----------+----------+---------------------------+---------------+
| device_id |  region  |           time            | detect_result |
+-----------+----------+---------------------------+---------------+
| F07A1260  | north-cn | 2022-01-01T00:00:00+08:00 | true          |
| F07A1260  | north-cn | 2022-01-01T00:00:20+08:00 | false         |
| F07A1260  | north-cn | 2022-01-01T00:00:40+08:00 | true          |
| F07A1261  | south-cn | 2022-01-01T00:00:00+08:00 | false         |
| F07A1261  | south-cn | 2022-01-01T00:00:20+08:00 | false         |
| F07A1261  | south-cn | 2022-01-01T00:00:40+08:00 | false         |
+-----------+----------+---------------------------+---------------+

Example 2: Use the ESD algorithm to detect anomalies in the temperature data of the F07A1260 device within a specific time range in the sensor table.

SELECT device_id, region, time, anomaly_detect(temperature, 'esd') AS detect_result FROM sensor WHERE device_id in ('F07A1260') and time >= '2022-01-01T00:00:00+08:00' and time < '2022-01-01T00:01:00+08:00' sample by 0;

The following result is returned:

+-----------+----------+---------------------------+---------------+
| device_id |  region  |           time            | detect_result |
+-----------+----------+---------------------------+---------------+
| F07A1260  | north-cn | 2022-01-01T00:00:00+08:00 | true          |
| F07A1260  | north-cn | 2022-01-01T00:00:20+08:00 | false         |
| F07A1260  | north-cn | 2022-01-01T00:00:40+08:00 | true          |
+-----------+----------+---------------------------+---------------+

Example 3: Use the ESD algorithm to detect anomalies in the temperature data of the F07A1260 device within a specific time range in the sensor table. In addition, set the lenHistoryWindow option to 30 and the maxAnomalyRatio option to 0.1.

SELECT device_id, region, time, anomaly_detect(temperature, 'esd', 'lenHistoryWindow=30,maxAnomalyRatio=0.1') AS detect_result FROM sensor where device_id in ('F07A1260') and time >= '2022-01-01T00:00:00+08:00' and time < '2022-01-01T00:01:00+08:00' sample by 0;

The following result is returned:

+-----------+----------+---------------------------+---------------+
| device_id |  region  |           time            | detect_result |
+-----------+----------+---------------------------+---------------+
| F07A1260  | north-cn | 2022-01-01T00:00:00+08:00 | false         |
| F07A1260  | north-cn | 2022-01-01T00:00:20+08:00 | false         |
| F07A1260  | north-cn | 2022-01-01T00:00:40+08:00 | true          |
+-----------+----------+---------------------------+---------------+

Example 4: Use the anomaly detection model created in the in-database machine learning feature to detect anomalies in the temperature data within a specific time range in the sensor table.

SELECT device_id, region, `time`, raw(temperature) as temperature, anomaly_detect(temperature, ad_model) AS detect_result FROM sensor WHERE time >= '2022-01-01T00:00:00+08:00' and time < '2022-01-01T00:01:00+08:00' sample by 0;

The following result is returned:

+-----------+----------+---------------------------+-------------+---------------+
| device_id |  region  |           time            | temperature | detect_result |
+-----------+----------+---------------------------+-------------+---------------+
| F07A1260  | north-cn | 2022-01-01T00:00:00+08:00 | 59.100000   | true          |
| F07A1260  | north-cn | 2022-01-01T00:00:20+08:00 | 13.200000   | false         |
| F07A1260  | north-cn | 2022-01-01T00:00:40+08:00 | 64.600000   | true          |
| F07A1261  | south-cn | 2022-01-01T00:00:00+08:00 | 12.100000   | false         |
| F07A1261  | south-cn | 2022-01-01T00:00:20+08:00 | 13.200000   | false         |
| F07A1261  | south-cn | 2022-01-01T00:00:40+08:00 | 10.600000   | false         |
+-----------+----------+---------------------------+-------------+---------------+

BINS

Overview

You can call this function to perform feature binning on the data in the specified field column of a time series table. Feature binning is also known as discrete binning or discrete bucketing and is a technique used to preprocess data.

Syntax

BINS(field_name, options)

Note

The BINS function returns VARCHAR values.

Parameters

Parameter	Required	Description
field_name	Yes	The name of the field column. Note Data in the specified field column cannot be of the VARCHAR or BOOLEAN type.
options	No	The feature binning policy and output mode. Specify the value of this parameter in the `key1=value1, key2=value2` format.

Examples

Example 1: Perform feature binning on the temperature data within the specified time range in a time series table named sensor.

SELECT device_id, region, time, bins(temperature) AS temperature_bin FROM sensor WHERE time >= '2021-04-22 00:00:00' AND time < '2022-04-23 00:01:00' sample by 0;

The following result is returned:

+-----------+----------+--------------------------------+--------------------+
| device_id |  region  |           time                 |  temperature_bin   |
+-----------+----------+--------------------------------+--------------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00      |    [10.60,12.10)   |
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00      |    [12.10,13.20]   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00      |    [10.60,10.60)   |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00      |    [10.60,12.10)   |
| F07A1261  | south-cn | 2021-04-22T15:51:21+08:00      |    [12.10,13.20)   |
| F07A1261  | south-cn | 2021-04-22T15:51:27+08:00      |    [10.60,10.60)   |
| F07A1261  | south-cn | 2021-04-22T15:51:47+08:00      |    [13.20,20.60]   |
+-----------+-----------+-------------------------------+--------------------+

Example 2: Perform feature binning on the temperature data of a device whose ID is F07A1260 within the specified time range in a time series table named sensor.

SELECT device_id, region, time, bins(temperature) AS temperature_bin FROM sensor WHERE device_id in ('F07A1260') AND time >= '2021-04-22 00:00:00' AND time < '2022-04-23 00:01:00' sample by 0;

The following result is returned:

+-----------+----------+--------------------------------+--------------------+
| device_id |  region  |           time                 |  temperature_bin   |
+-----------+----------+--------------------------------+--------------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00      |    [10.60,12.10)   |
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00      |    [12.10,13.20]   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00      |    [10.60,10.60)   |
+-----------+----------+--------------------------------+--------------------+

Example 3: Perform feature binning on the temperature data of a device whose ID is F07A1260 within the specified time range in a time series table named sensor. In addition, specify the n_bins options to 2, the output_type option to ordinal, and the strategy option to uniform.

SELECT device_id, region, time, bins(temperature, 'n_bins=2, output_type=ordinal, strategy=uniform') AS temperature_bin FROM sensor WHERE device_id in ('F07A1260') AND time >= '2021-04-22 00:00:00' AND time < '2022-04-23 00:01:00' sample by 0;

The following result is returned:

+-----------+----------+--------------------------------+--------------------+
| device_id |  region  |           time                 |  temperature_bin   |
+-----------+----------+--------------------------------+--------------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00      |  1                 |
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00      |  1                 |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00      |  0                 |
+-----------+----------+--------------------------------+--------------------+