Uso e exemplos de funções de agregação do Lindorm - Lindorm: Central de Documentação do Alibaba Cloud

Engines e versões compatíveis

Engine	Versão
LindormTable	Todas as versões
LindormTSDB	V3.4.7 e posteriores

As funções HEAD e GROUP_CONCAT exigem LindormTable 2.7.9 ou posterior e Lindorm SQL 2.8.8.0 ou posterior.

Funções

Função	Descrição	Requisito de versão
AVG	Retorna a média de todos os valores em uma coluna	Todas as versões
COUNT	Retorna o número de valores não vazios em uma coluna ou a contagem total de linhas	Todas as versões
MAX	Retorna o valor máximo em uma coluna	Todas as versões
MIN	Retorna o valor mínimo em uma coluna	Todas as versões
SUM	Retorna a soma dos valores em uma coluna	Todas as versões
HEAD	Retorna o primeiro valor não vazio em uma coluna ou expressão, com ordem de classificação opcional	LindormTable 2.7.9+, Lindorm SQL 2.8.8.0+
GROUP_CONCAT	Concatena valores de coluna dentro de cada grupo em uma única string	LindormTable 2.7.9+, Lindorm SQL 2.8.8.0+

Tabela de exemplo

Os exemplos neste tópico utilizam a tabela sensor a seguir:

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

Retorna a média de todos os valores na coluna especificada.

Sintaxe

AVG(column_name)

Parâmetros

Parâmetro	Descrição
`column_name`	Coluna para calcular a média. Deve ser um tipo de dados numérico.

Exemplo

Calcule a temperatura média em todos os dispositivos:

SELECT AVG(temperature) AS temperature FROM sensor;

Saída:

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

COUNT

Retorna o número de valores não vazios na coluna especificada ou o número total de linhas quando chamado com *.

Sintaxe

COUNT(* | column_name)

Parâmetros

Parâmetro	Descrição
`column_name`	Coluna para contar valores não vazios.
`*`	Conta todas as linhas, incluindo aquelas com valores nulos.

Exemplos

Conte o número de leituras de temperatura:

SELECT COUNT(temperature) AS temperature FROM sensor;

Saída:

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

Conte as linhas em que device_id é F07A1260:

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

Saída:

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

Conte todas as linhas da tabela:

SELECT COUNT(*) FROM sensor;

Saída:

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

MAX

Retorna o valor máximo na coluna especificada.

Sintaxe

MAX(column_name)

Parâmetros

Parâmetro	Descrição
`column_name`	Coluna a ser avaliada.

Exemplo

Obtenha a temperatura mais alta registrada em todos os dispositivos:

SELECT MAX(temperature) FROM sensor;

Saída:

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

MIN

Retorna o valor mínimo na coluna especificada.

Sintaxe

MIN(column_name)

Parâmetros

Parâmetro	Descrição
`column_name`	Coluna a ser avaliada.

Exemplo

Obtenha a temperatura mais baixa registrada em todos os dispositivos:

SELECT MIN(temperature) FROM sensor;

Saída:

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

SUM

Retorna a soma dos valores na coluna especificada.

Sintaxe

SUM(column_name)

Parâmetros

Parâmetro	Descrição
`column_name`	Coluna para somar. Deve ser um tipo de dados numérico.

Exemplo

Calcule o total de todas as leituras de temperatura:

SELECT SUM(temperature) FROM sensor;

Saída:

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

HEAD

Retorna o primeiro valor não vazio na coluna ou expressão especificada por expr1. As linhas são classificadas por expr2 antes da seleção do primeiro valor. Se expr2 for omitido, a classificação ocorrerá pela primeira coluna da chave primária.

Importante

São necessários o LindormTable 2.7.9 ou posterior e o Lindorm SQL 2.8.8.0 ou posterior. Verifique as versões atuais no console: Notas de versão do LindormTable e Descrição da versão do Lindorm SQL. Para atualizar, consulte Atualizar a versão secundária do engine de uma instância LindormTable.

Sintaxe

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

Parâmetros

Parâmetro	Descrição
`expr1`	Coluna ou expressão da qual recuperar o primeiro valor não vazio. Tipos de dados suportados: INTEGER, BIGINT, SMALLINT, TINYINT, FLOAT, DOUBLE, DECIMAL, BOOLEAN, VARCHAR, CHAR, TIMESTAMP, JSON e GEOMETRY.
`expr2`	Coluna ou expressão usada para classificação. Se omitida, as linhas serão classificadas pela primeira coluna da chave primária. Não pode ser outra função de agregação. Por exemplo, `HEAD(c1 ORDER BY COUNT(c2))` não é suportado.

Exemplos

Obtenha as temperaturas mínima e máxima usando classificação ascendente e descendente:

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

Saída:

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

Obtenha a temperatura do dispositivo com o device_id mais curto:

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

Saída:

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

GROUP_CONCAT

Agrupa linhas pelas colunas especificadas em uma cláusula GROUP BY, classifica os valores dentro de cada grupo e os concatena em uma única string.

Importante

São necessários o LindormTable 2.7.9 ou posterior e o Lindorm SQL 2.8.8.0 ou posterior. Verifique as versões atuais no console: Notas de versão do LindormTable e Descrição da versão do Lindorm SQL. Para atualizar, consulte Atualizar a versão secundária do engine de uma instância LindormTable.

Sintaxe

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

Importante

Não é possível usar DISTINCT e ORDER BY juntos na mesma chamada GROUP_CONCAT.

Parâmetros

Parâmetro	Descrição
`expr`	Coluna de projeção a ser concatenada.
`col_name`	Coluna de classificação.
`str_val`	Separador entre os valores concatenados.

Exemplos

Os exemplos a seguir usam a tabela 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);

Exemplo 1: Concatenar valores de c1 agrupados por p1:

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

Saída:

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

Exemplo 2: Concatenar valores distintos de c1 agrupados por p1:

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

Saída:

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

Exemplo 3: Concatenar valores de c1 agrupados por p1, classificados em ordem ascendente por c1:

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

Saída:

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

Exemplo 4: Concatenar valores de c1 agrupados por p1, classificados em ordem descendente por c3:

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

Saída:

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

Exemplo 5: Igual ao Exemplo 4, mas com separador de barra vertical (|):

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

Saída:

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