Usage and examples of Lindorm aggregate functions - Lindorm - Centro de documentación de Alibaba Cloud

Lindorm SQL admite funciones de agregación para calcular resúmenes entre filas: totales, promedios, valores extremos y cadenas concatenadas.

Motores y versiones compatibles

Motor	Versión
LindormTable	Todas las versiones
LindormTSDB	V3.4.7 y posteriores

HEAD y GROUP_CONCAT requieren LindormTable 2.7.9 o posterior y Lindorm SQL 2.8.8.0 o posterior.

Funciones

Función	Descripción	Requisito de versión
AVG	Devuelve el promedio de todos los valores de una columna	Todas las versiones
COUNT	Devuelve la cantidad de valores no vacíos en una columna o el total de filas	Todas las versiones
MAX	Devuelve el valor máximo de una columna	Todas las versiones
MIN	Devuelve el valor mínimo de una columna	Todas las versiones
SUM	Devuelve la suma de los valores de una columna	Todas las versiones
HEAD	Devuelve el primer valor no vacío de una columna o expresión, con un orden de clasificación opcional	LindormTable 2.7.9+, Lindorm SQL 2.8.8.0+
GROUP_CONCAT	Concatena los valores de una columna dentro de cada grupo en una sola cadena	LindormTable 2.7.9+, Lindorm SQL 2.8.8.0+

Tabla de ejemplo

Los ejemplos de este tema utilizan a continuación la tabla sensor:

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

Devuelve el promedio de todos los valores de la columna especificada.

Sintaxis

AVG(column_name)

Parámetros

Parámetro	Descripción
`column_name`	La columna de la que se calcula el promedio. Debe ser de un tipo de datos numérico.

Ejemplo

Calcule la temperatura promedio de todos los dispositivos:

SELECT AVG(temperature) AS temperature FROM sensor;

Salida:

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

COUNT

Devuelve la cantidad de valores no vacíos en la columna especificada. Si se invoca con *, devuelve el número total de filas.

Sintaxis

COUNT(* | column_name)

Parámetros

Parámetro	Descripción
`column_name`	La columna en la que se cuentan los valores no vacíos.
`*`	Cuenta todas las filas, incluidas aquellas con valores nulos.

Ejemplos

Cuente las lecturas de temperatura:

SELECT COUNT(temperature) AS temperature FROM sensor;

Salida:

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

Cuente las filas donde device_id es F07A1260:

SELECT COUNT(*) FROM sensor WHERE device_id = 'F07A1260';

Salida:

+--------+
| EXPR$0 |
+--------+
| 1      |
+--------+

Cuente todas las filas de la tabla:

SELECT COUNT(*) FROM sensor;

Salida:

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

MAX

Devuelve el valor máximo de la columna especificada.

Sintaxis

MAX(column_name)

Parámetros

Parámetro	Descripción
`column_name`	La columna que se evalúa.

Ejemplo

Obtenga la temperatura más alta registrada en todos los dispositivos:

SELECT MAX(temperature) FROM sensor;

Salida:

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

MIN

Devuelve el valor mínimo de la columna especificada.

Sintaxis

MIN(column_name)

Parámetros

Parámetro	Descripción
`column_name`	La columna que se evalúa.

Ejemplo

Obtenga la temperatura más baja registrada en todos los dispositivos:

SELECT MIN(temperature) FROM sensor;

Salida:

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

SUM

Devuelve la suma de los valores de la columna especificada.

Sintaxis

SUM(column_name)

Parámetros

Parámetro	Descripción
`column_name`	La columna cuyos valores se suman. Debe ser de un tipo de datos numérico.

Ejemplo

Calcule el total de todas las lecturas de temperatura:

SELECT SUM(temperature) FROM sensor;

Salida:

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

HEAD

Devuelve el primer valor no vacío de la columna o expresión indicada en expr1. Las filas se ordenan por expr2 antes de seleccionar el primer valor. Si se omite expr2, las filas se ordenan según la primera columna de clave primaria.

Importante

Se requiere LindormTable 2.7.9 o posterior y Lindorm SQL 2.8.8.0 o posterior. Compruebe sus versiones actuales en la consola: consulte las notas de la versión de LindormTable y Lindorm SQL version description. Para actualizar, consulte Actualizar la versión secundaria del motor de una instancia de LindormTable.

Sintaxis

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

Parámetros

Parámetro	Descripción
`expr1`	La columna o expresión de la que se obtiene el primer valor no vacío. Tipos de datos compatibles: INTEGER, BIGINT, SMALLINT, TINYINT, FLOAT, DOUBLE, DECIMAL, BOOLEAN, VARCHAR, CHAR, TIMESTAMP, JSON y GEOMETRY.
`expr2`	La columna o expresión que define el orden. Si se omite, las filas se ordenan según la primera columna de clave primaria. No puede ser otra función de agregación; por ejemplo, no se admite `HEAD(c1 ORDER BY COUNT(c2))`.

Ejemplos

Obtenga las temperaturas mínima y máxima mediante orden ascendente y descendente:

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

Salida:

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

Obtenga la temperatura del dispositivo cuyo device_id sea el más corto:

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

Salida:

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

GROUP_CONCAT

Agrupa las filas según las columnas indicadas en una cláusula GROUP BY, ordena los valores dentro de cada grupo y los concatena en una sola cadena.

Importante

Sintaxis

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

Importante

No se pueden usar DISTINCT y ORDER BY juntos en la misma llamada a GROUP_CONCAT.

Parámetros

Parámetro	Descripción
`expr`	La columna de proyección que se concatena.
`col_name`	La columna que define el orden.
`str_val`	El separador entre los valores concatenados.

Ejemplos

A continuación se muestran ejemplos basados en la tabla 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);

Ejemplo 1: Concatene los valores de c1 agrupados por p1:

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

Salida:

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

Ejemplo 2: Concatene los valores distintos de c1 agrupados por p1:

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

Salida:

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

Ejemplo 3: Concatene los valores de c1 agrupados por p1 y ordénelos de forma ascendente por c1:

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

Salida:

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

Ejemplo 4: Concatene los valores de c1 agrupados por p1 y ordénelos de forma descendente por c3:

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

Salida:

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

Ejemplo 5: Igual que el ejemplo 4, pero con una tubería (|) como separador:

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

Salida:

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