Usage and examples of Lindorm aggregate functions - Lindorm

Lindorm provides a series of aggregate functions for data analysis, such as COUNT and SUM.

Applicable engines and versions

The functions described in this topic are applicable to the following Lindorm engines of specific versions:

LindormTable of all versions
LindormTSDB V3.4.7 and later

Functions

The following table describes the aggregate functions supported by Lindorm.

Function	Description
AVG	Returns the average of all values in a specified column.
COUNT	Returns the number of non-empty values in a specified column.
MAX	Returns the maximum value of a specified column.
MIN	Returns the minimum value of a specified column.
SUM	Returns the sum of values in a specified column.
HEAD	Returns the first non-empty value of a specified column or expression.
GROUP_CONCAT	Groups one or more projection columns based on the column specified in the `GROUP BY` clause, sorts projection columns in each group based on one or more sorting columns, concatenates the column values into a long string, and then returns the concatenated string.

Execute the following statement to create a sample table:

-- Create a table named sensor.
CREATE TABLE sensor(
    device_id VARCHAR NOT NULL,
    region VARCHAR NOT NULL,
    time TIMESTAMP NOT NULL,
    temperature DOUBLE,
    humidity BIGINT,
    PRIMARY KEY(device_id, region, time)
);

-- Write multiple rows of data at the same time.
UPSERT INTO sensor(device_id ,region ,time,temperature) VALUES('F07A1260','north-cn','2021-04-22 15:33:20',10.6), ('F07A1261','south-cn','2021-04-22 15:33:00',18.1), ('F07A1261','south-cn','2021-04-22 15:33:10',19.7);

AVG

You can call this function to obtain the average of all values in a specified column.

Syntax

AVG(column_name)

Parameters

Parameter	Description
column_name	The name of the column. Only columns of the numeric data type are supported.

Examples

Calculate the average of the temperature values collected from all devices.

SELECT AVG(temperature) AS temperature FROM sensor;

Sample output:

+--------------------+
| temperature        |
+--------------------+
| 16.133333333333333 |
+--------------------+

COUNT

You can call this function to obtain the number of non-empty values in a specified column.

Syntax

COUNT(* | column_name)

Parameters

Parameter	Description
column_name	The name of the column.

Examples

Count the number of temperature values collected from all devices.

SELECT COUNT(temperature) AS temperature FROM sensor;

Sample output:

+-------------+
| temperature |
+-------------+
| 3           |
+-------------+

Count the number of rows in which the value of the device_id column is F07A1260 in the sensor table.
```
SELECT COUNT(*) FROM sensor WHERE device_id = 'F07A1260';
```
Sample output:
```
+--------+
| EXPR$0 |
+--------+
| 1      |
+--------+
```

Count the number of rows in the sensor table.

SELECT COUNT(*) FROM sensor;

Sample output:

+--------+
| EXPR$0 |
+--------+
| 3      |
+--------+

MAX

You can call this function to obtain the maximum value of a specified column.

Syntax

MAX(column_name)

Parameters

Parameter	Description
column_name	The name of the column.

Examples

Query the maximum value of the temperature values collected from all devices.

SELECT MAX(temperature) FROM sensor;

Sample output:

+--------+
| EXPR$0 |
+--------+
| 19.7   |
+--------+

MIN

You can call this function to obtain the minimum value of a specified column.

Syntax

MIN(column_name)

Parameters

Parameter	Description
column_name	The name of the column.

Examples

Query the minimum value of the temperature values collected from all devices.

SELECT MIN(temperature) FROM sensor;

Sample output:

+--------+
| EXPR$0 |
+--------+
| 10.6   |
+--------+

SUM

You can call this function to obtain the sum of values in a specified column.

Syntax

SUM(column_name)

Parameters

Parameter	Description
column_name	The name of the column. Only columns of the numeric data type are supported.

Examples

Query the sum of the temperature values collected from all devices.

SELECT SUM(temperature) FROM sensor;

Sample output:

+--------+
| EXPR$0 |
+--------+
| 48.4   |
+--------+

HEAD

You can call this function to obtain the first non-empty value of the column or expression specified by expr1 and sort the result based on the column specified by expr2.

Important

The version of LindormTable must be 2.7.9 or later. The version of Lindorm SQL must be 2.8.8.0 or later. You can view the versions of LindormTable and Lindorm SQL in the console, and update the minor version of LindormTable to 2.7.9 or later.

Syntax

HEAD (expr1 [ ORDER BY expr2 [ DESC ] ] )

Parameters

Parameter

Description

expr1

The column or expression from which you want to obtain the first non-empty value.

This parameter supports the following data types: INTEGER, BIGINT, SMALLINT, TINYINT, FLOAT, DOUBLE, DECIMAL, BOOLEAN, VARCHAR, CHAR, TIMESTAMP, JSON, and GEOMETRY.

expr2

The sorting method. If you do not specify this parameter, the result is sorted based on the first primary key column.

Important

This parameter cannot be nested in other aggregate functions. For example, HEAD(c1 ORDER BY COUNT(c2)) is not supported.

Examples

Query the minimum and maximum values in the temperature column.

SELECT HEAD(temperature ORDER BY temperature), HEAD(temperature ORDER BY temperature DESC) FROM sensor;

Sample output:

+--------+--------+
| EXPR$0 | EXPR$1 |
+--------+--------+
| 10.6   | 19.7   |
+--------+--------+

Query the temperature value that corresponds to the minimum character length in the device_id column.

SELECT HEAD(temperature ORDER BY LENGTH(device_id)) FROM sensor;

Sample output:

+--------+
| EXPR$0 |
+--------+
| 10.6   |
+--------+

GROUP_CONCAT

You can call this function to group one or more projection columns based on the group columns specified in the GROUP BY clause, sort projection columns in each group based on one or more sorting columns, concatenate the column values into a long string, and then return the concatenated string.

Important

Syntax

GROUP_CONCAT ( [ DISTINCT ] expr (, expr ... )*
             [ ORDER BY col_name [ DESC ] (, col_name [ DESC ])* ]
             [ SEPARATOR str_val ] )

Important

DISTINCT and ORDER BY keywords cannot be used at the same time.

Parameters

Parameter	Description
expr	The projection column.
col_name	The sorting column.
str_val	The separator that is used in the concatenated string.

Examples

Execute the following statement to create a sample table named t1:

-- Create a sample table named t1.
CREATE TABLE t1 (p1 INT, p2 VARCHAR, c1 INT, c2 VARCHAR, c3 DOUBLE, PRIMARY KEY(p1, p2));

-- Insert data into the table.
UPSERT INTO t1(p1, p2, c1, c2, c3) VALUES 
(1, 'a', 10, 'java', 10.23),
(1, 'b', 10, 'c/c++', 5.76),
(1, 'c', 18, 'python', 8.21),
(2, 'a', 40, 'sql', 3.14),
(2, 'b', 100, 'c#', 4.96),
(2, 'c', 66, 'matlab', 7.01);

Example 1: Group the column c1 based on the primary key column p1.

SELECT p1, GROUP_CONCAT(c1) FROM t1 GROUP BY p1;

Sample output:

+----+-----------+
| p1 |  EXPR$1   |
+----+-----------+
| 1  | 10,10,18  |
| 2  | 40,100,66 |
+----+-----------+

Example 2: Group the column c1 based on the primary key column p1 and remove duplicate values.

SELECT p1, GROUP_CONCAT(DISTINCT c1) FROM t1 GROUP BY p1;

Sample output:

+----+-----------+
| p1 |  EXPR$1   |
+----+-----------+
| 1  | 18,10     |
| 2  | 100,40,66 |
+----+-----------+

Example 3: Group the column c1 based on the primary key column p1 and sort the result in ascending order based on the column c1.

SELECT p1, GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY p1;

Sample output:

+----+-----------+
| p1 |  EXPR$1   |
+----+-----------+
| 1  | 10,10,18  |
| 2  | 40,66,100 |
+----+-----------+

Example 4: Group the column c1 based on the primary key column p1 and sort the result in descending order based on the column c3.

SELECT p1, GROUP_CONCAT(c1 ORDER BY c3 DESC) FROM t1 GROUP BY p1;

Sample output:

+----+-----------+
| p1 |  EXPR$1   |
+----+-----------+
| 1  | 10,18,10  |
| 2  | 66,100,40 |
+----+-----------+

Example 5: Group the column c1 based on the primary key column p1, sort the result in descending order based on the column c3, and concatenate values into a string in which values are separated with vertical bars (|).
```
SELECT p1, GROUP_CONCAT(c1 ORDER BY c3 DESC SEPARATOR '|') FROM t1 GROUP BY p1;
```
Sample output:
```
+----+-----------+
| p1 |  EXPR$1   |
+----+-----------+
| 1  | 10|18|10  |
| 2  | 66|100|40 |
+----+-----------+
```