LindormTSDB SQL Time Series Functions for Real-Time Analytics - Lindorm

LindormTSDB SQL supports a set of built-in functions for time series data, covering aggregation, selection, conversion, and AI-powered analysis. This page describes each function's syntax, parameters, and usage with runnable examples.

Applicable engines and versions

These functions apply only to LindormTSDB.
LindormTSDB 3.4.7 or later is required. For upgrade instructions, see Release notes of LindormTSDB and Upgrade the minor engine version of a Lindorm instance.

Function list

Category	Function	Description
Timestamp	CURRENT_TIMESTAMP	Returns the current system time.
Aggregate	SUM	Calculates the sum of values in a field column.
Aggregate	AVG	Calculates the average of values in a field column.
Aggregate	COUNT	Counts the number of values in a field column.
Select	MIN	Returns the minimum value in a field column.
Select	MAX	Returns the maximum value in a field column.
Select	FIRST	Returns the earliest value in a field column.
Select	LAST	Returns the latest value in a field column.
Select	PERCENTILE	Returns the value at a specified percentile in a field column.
Conversion	RATE	Calculates the rate of change (slope) between consecutive values in a field column.
Conversion	DELTA	Calculates the difference between each value and the preceding value in a field column.
Conversion	LATEST	Returns the N most recent values in a field column.
AI	ANOMALY_DETECT	Detects anomalies in a field column.
AI	BINS	Groups values in a field column into bins (feature binning).

Data preparation

All examples in this page use a table named sensor with the following schema:

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

The table contains these rows:

+-----------+----------+---------------------------+-------------+-----------+
| 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 table has two time series:

Time series 1: device_id=F07A1260, region=north-cn, with temperature and humidity fields.
Time series 2: device_id=F07A1261, region=south-cn, with temperature and humidity fields.

For more information about time series concepts, see Terms.

Query patterns

The examples in this page use two SAMPLE BY patterns, both of which are LindormTSDB-specific SQL syntax:

SAMPLE BY <interval> — downsamples data by grouping rows into fixed time windows. For example, SAMPLE BY 20s groups rows into 20-second intervals and applies the aggregate function to each interval.
SAMPLE BY 0 — aggregates across the full time range for each time series without downsampling.

Timestamp function

CURRENT_TIMESTAMP

Returns the current system time from the server running the Lindorm instance.

Syntax

CURRENT_TIMESTAMP

Important

CURRENT_TIMESTAMP takes no parameters.
The returned timestamp is accurate to milliseconds. Example: 2023-04-23T21:13:15.819+08:00.
Do not use CURRENT_TIMESTAMP to generate timestamps for production writes. Use it for generating test data only.

Example

Insert a row into the sensor table using the current system time as the timestamp:

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

Aggregate functions

SUM

Calculates the sum of values in a field column. Returns DOUBLE or BIGINT.

Syntax

SUM(field_name)

Parameters

Parameter	Description
`field_name`	The name of the field column. The column cannot be of type VARCHAR or BOOLEAN.

Examples

Example 1: Downsample at 20-second intervals, then calculate the sum of temperature per interval per device.

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;

Result:

+-----------+----------+---------------------------+-------------+
| 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: Calculate the total temperature sum per device across the full time range.

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;

Result:

+-----------+----------+---------------------------+-------------+
| 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 total temperature sum across 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';

Result:

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

AVG

Calculates the average of values in a field column. Returns DOUBLE.

Syntax

AVG(field_name)

Parameters

Parameter	Description
`field_name`	The name of the field column. The column cannot be of type VARCHAR or BOOLEAN.

Examples

Example 1: Downsample at 20-second intervals, then calculate the average temperature per interval per device.

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;

Result:

+-----------+----------+---------------------------+-------------+
| 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: Calculate the average temperature per device across the full time range.

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;

Result:

+-----------+----------+---------------------------+-------------+
| 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 temperature across 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';

Result:

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

COUNT

Counts the number of values in a field column. Returns BIGINT.

Syntax

COUNT(field_name)

Parameters

Parameter	Description
`field_name`	The name of the field column.

Examples

Example 1: Downsample at 20-second intervals, then count temperature values per interval per device.

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;

Result:

+-----------+----------+---------------------------+-------------+
| 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: Count total temperature values per device across the full time range.

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;

Result:

+-----------+----------+---------------------------+-------------+
| 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 total temperature values across 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';

Result:

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

Select functions

Select functions return actual data rows rather than computed scalars. Unlike aggregate functions such as SUM and AVG, select functions preserve the original value — and in some cases the associated timestamp — from the underlying rows.

MIN

Returns the minimum value in a field column. Returns DOUBLE or BIGINT.

Syntax

MIN(field_name)

Parameters

Parameter	Description
`field_name`	The name of the field column. The column cannot be of type VARCHAR or BOOLEAN.

Examples

Example 1: Downsample at 20-second intervals, then find the minimum temperature per interval per device.

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;

Result:

+-----------+----------+---------------------------+-------------+
| 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: Find the minimum temperature per device across the full time range.

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;

Result:

+-----------+----------+---------------------------+-------------+
| 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: Find the minimum temperature across 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';

Result:

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

MAX

Returns the maximum value in a field column. Returns DOUBLE or BIGINT.

Syntax

MAX(field_name)

Parameters

Parameter	Description
`field_name`	The name of the field column. The column cannot be of type VARCHAR or BOOLEAN.

Examples

Example 1: Downsample at 20-second intervals, then find the maximum temperature per interval per device.

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;

Result:

+-----------+----------+---------------------------+-------------+
| 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: Find the maximum temperature per device across the full time range.

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;

Result:

+-----------+----------+---------------------------+-------------+
| 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: Find the maximum temperature across 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';

Result:

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

FIRST

Returns the earliest value in a field column within the query window. The return type matches the field column type.

Syntax

FIRST(field_name)

Parameters

Parameter	Description
`field_name`	The name of the field column.

Examples

Example 1: Downsample at 20-second intervals, then get the first temperature value per interval per device.

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;

Result:

+-----------+----------+---------------------------+-------------+
| 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: Get the first temperature value per device across the full time range.

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;

Result:

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

Returns the most recent value in a field column within the query window. The return type matches the field column type.

Syntax

LAST(field_name)

Parameters

Parameter	Description
`field_name`	The name of the field column.

Examples

Example 1: Downsample at 20-second intervals, then get the last temperature value per interval per device.

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;

Result:

+-----------+----------+---------------------------+-------------+
| 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: Get the last temperature value per device across the full time range.

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;

Result:

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

Returns the value at a specified percentile in a field column. Returns DOUBLE.

Syntax

PERCENTILE(field_name, P)

Parameters

Parameter	Description
`field_name`	The name of the field column. The column cannot be of type VARCHAR or BOOLEAN.
`P`	An integer or floating-point number specifying the percentile. Valid range: 0 to 100. Default: 50.

Examples

Example 1: Downsample at 20-second intervals, then calculate the 90th percentile of temperature per interval per device.

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;

Result:

+-----------+----------+---------------------------+-------------+
| 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: Calculate the 90th percentile of temperature per device across the full time range.

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;

Result:

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

Calculates the rate of change (slope) between consecutive values in a field column. Returns DOUBLE.

Given data points (t1,v1), (t2,v2) ... (tN,vN), RATE produces N−1 output points. Each output timestamp and value are calculated as:

where interval is the specified time interval in seconds.

Important

RATE must be the only aggregator in a SQL statement. It cannot be combined with other aggregators such as AVG, DELTA, or LATEST in the same query.

Syntax

RATE(field_name, 'interval units')

Parameters

Parameter	Description
`field_name`	The name of the field column. The column cannot be of type VARCHAR or BOOLEAN.
`interval units`	The time interval for slope calculation. Default: `1s`. Valid units: `s` (seconds), `m` (minutes), `h` (hours), `d` (days).

Examples

Example 1: Calculate the per-second rate of change of temperature for each device.

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;

Result:

+-----------+----------+---------------------------+-------------+
| 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: Calculate the per-minute rate of change of temperature for each device.

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;

Result:

+-----------+----------+---------------------------+-------------+
| 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: Downsample at 20-second intervals, compute the average temperature per interval, then calculate the per-minute rate of change of those averages.

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;

Result:

+-----------+----------+---------------------------+-------------+
| 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 following queries fail because RATE cannot be combined with other aggregators.

-- Error: multiple aggregators in one statement
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;

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

-- Error: mixing RATE with LATEST
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;

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

DELTA

Calculates the difference between each value and the preceding value in a field column. Returns DOUBLE.

Given data points (t1,v1), (t2,v2) ... (tN,vN), DELTA produces N−1 output points: (t2, v2−v1), (t3, v3−v2) ... (tN, vN−vN-1).

Important

DELTA must be the only aggregator in a SQL statement. It cannot be combined with other aggregators such as AVG, RATE, or LATEST in the same query.

Syntax

DELTA(field_name)

Parameters

Parameter	Description
`field_name`	The name of the field column. The column cannot be of type VARCHAR or BOOLEAN.

Examples

Example 1: Calculate row-to-row temperature differences for each device.

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;

Result:

+-----------+----------+---------------------------+-------------+
| 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: Downsample at 20-second intervals, compute average temperature per interval, then calculate row-to-row differences of those averages.

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;

Result:

+-----------+----------+---------------------------+-------------+
| 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 following query fails because DELTA cannot be combined with other aggregators.

-- Error: mixing DELTA with RATE
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;

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

LATEST

Returns the N most recent values in a field column, ordered by timestamp. The return type matches the field column type.

LATEST must be the only aggregator in a SQL statement.

Syntax

LATEST(field_name, N)

Parameters

Parameter	Description
`field_name`	The name of the field column.
`N`	An integer specifying the number of most recent values to return. Default: 1.

Examples

Example 1: Get the latest temperature value for each device.

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;

Result:

+-----------+----------+---------------------------+-------------+
| 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: Get the two most recent temperature values for each device.

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;

Result:

+-----------+----------+---------------------------+-------------+
| 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 following query fails because LATEST cannot be combined with other aggregators.

-- Error: mixing LATEST with RATE
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;

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

AI functions

FORECAST

Forecasts future field values in a time series table using a trained model. Returns DOUBLE.

Prerequisites

FORECAST depends on Lindorm AI. Activate Lindorm AI for your instance before using this function.

Syntax

FORECAST(field_name, model_name, options)

Parameters

Parameter	Required	Description
`field_name`	Yes	The name of the field column. The column cannot be of type VARCHAR or BOOLEAN.
`model_name`	Yes	The name of the trained model. Must be of type VARCHAR.
`options`	No	Forecast options in `key1=value1[, key2=value2]` format. See the options below.

Options

Option	Type	Default	Description
`batch_size`	INTEGER	Number of time series in the training	Number of time series per inference batch. When cached time series reach this count, a batch forecast is triggered.
`step`	INTEGER	Value of `PREDICTION_LENGTH` set during training	Step length at which forecast results are output.
`quantile_output`	DOUBLE	0.5	The percentile of the output forecast result. The in-database machine learning provides an array of predicted values per data point; `quantile_output` selects one value from that array. Set this based on the algorithm used: for DeepAR, use a value between 0 and 1; for TFT, use 0.1, 0.5, or 0.9.

Examples

Example 1: Forecast temperature values within a specified time range.

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;

Result:

+-----------+----------+---------------------------+------------------+
| 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 with step=2 and quantile_output=0.9 to get the 90th percentile forecast at every other step.

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;

Result:

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

Detects anomalies in a field column using either a built-in algorithm or a trained model. Returns BOOLEAN.

Syntax

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

Parameters

Parameter	Required	Description
`field_name`	Yes	The name of the field column. The column cannot be of type VARCHAR or BOOLEAN.
`algo_name`	Conditional	The name of a built-in algorithm from DAMO Academy. Use this parameter when Lindorm AI is not enabled. Supported values: `esd` (spike detection for monitoring curves), `nsigma` (simple statistical anomaly analysis), `ttest` (detects anomalies caused by mean-value shifts), `istl-esd` (anomaly detection for periodic data).
`model_name`	Conditional	The name of a trained anomaly detection model. Must be of type VARCHAR. Use this parameter when Lindorm AI is enabled. For more information, see Activate Lindorm AI.
`options`	No	Detection options in `key1=value1[, key2=value2]` format. For available options, see Time series anomaly detection.

Examples

Example 1: Use the ESD algorithm to detect anomalies in temperature data across all devices.

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;

Result:

+-----------+----------+---------------------------+---------------+
| 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 ESD to detect anomalies in temperature data for device F07A1260 only.

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;

Result:

+-----------+----------+---------------------------+---------------+
| 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 ESD with custom options — set lenHistoryWindow to 30 and maxAnomalyRatio 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;

Result:

+-----------+----------+---------------------------+---------------+
| 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 a trained model from Lindorm AI to detect anomalies.

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;

Result:

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

Groups values in a field column into bins (feature binning). Feature binning — also known as discrete binning or bucketing — is a data preprocessing technique. Returns VARCHAR.

Syntax

BINS(field_name, options)

Parameters

Parameter	Required	Description
`field_name`	Yes	The name of the field column. The column cannot be of type VARCHAR or BOOLEAN.
`options`	No	The binning policy and output format in `key1=value1, key2=value2` format.

Examples

Example 1: Group all temperature values in the specified time range into bins.

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;

Result:

+-----------+----------+--------------------------------+--------------------+
| 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: Group temperature values for device F07A1260 into bins.

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;

Result:

+-----------+----------+--------------------------------+--------------------+
| 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: Group temperature values for device F07A1260 into 2 uniform bins and output ordinal labels.

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;

Result:

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