Use AnalyticDB for MySQL aggregate functions - AnalyticDB - Centre de documentation Alibaba Cloud

Les fonctions d'agrégation calculent un résultat unique à partir d'un ensemble de valeurs d'entrée. Cette rubrique présente les fonctions d'agrégation prises en charge par AnalyticDB for MySQL, notamment leur syntaxe, leurs types d'entrée et de retour, ainsi que des exemples exécutables.

Fonctions abordées : ARBITRARY, AVG, BIT_AND, BIT_OR, BIT_XOR, COUNT, MAX, MIN, STD / STDDEV / STDDEV_POP, STDDEV_SAMP, SUM, VARIANCE / VAR_POP, VAR_SAMP, GROUP_CONCAT

Remarques sur l'utilisation

Gestion des valeurs NULL

La plupart des fonctions d'agrégation ignorent les valeurs NULL. Pour MAX et MIN, les lignes dont la colonne d'entrée est NULL sont exclues du calcul. Pour VARIANCE, VAR_POP et leurs alias, si toutes les valeurs de l'ensemble d'entrée sont NULL, la fonction renvoie NULL.

Modificateur DISTINCT

Utilisez DISTINCT pour compter ou agréger uniquement les valeurs uniques. Par exemple, COUNT(DISTINCT a) compte les valeurs distinctes non NULL de la colonne a.

Table de test utilisée dans les exemples

Tous les exemples, à l'exception de GROUP_CONCAT, utilisent une table nommée testtable, créée et renseignée comme suit :

CREATE TABLE testtable(a INT) DISTRIBUTED BY HASH(a);
INSERT INTO testtable VALUES (1),(2),(3);

Fonctions d'agrégation générales

ARBITRARY

arbitrary(x)

Renvoie une seule valeur issue de l'ensemble d'entrée. La valeur renvoyée n'est pas déterministe.

Types d'entrée	Tous les types de données
Type de retour	Identique à l'entrée

Exemple

SELECT arbitrary(a) FROM testtable;

+--------------+
| arbitrary(a) |
+--------------+
|            2 |
+--------------+

AVG

avg(x)

Renvoie la moyenne arithmétique des valeurs d'entrée.

Types d'entrée	BIGINT, DOUBLE, FLOAT
Type de retour	DOUBLE

Exemple

SELECT avg(a) FROM testtable;

+--------+
| avg(a) |
+--------+
|    2.0 |
+--------+

COUNT

count([DISTINCT | ALL] x)

Compte le nombre de lignes dans l'ensemble de résultats.

ALL (par défaut) : compte toutes les lignes, y compris les doublons.
DISTINCT : compte uniquement les lignes ayant des valeurs uniques.

Types d'entrée	NUMERIC, STRING, BOOLEAN
Type de retour	BIGINT

Exemples

Comptez les valeurs distinctes de la colonne a :

SELECT count(DISTINCT a) FROM testtable;

+-------------------+
| count(distinct a) |
+-------------------+
|                 3 |
+-------------------+

Comptez toutes les lignes de la colonne a :

SELECT count(ALL a) FROM testtable;

+--------------+
| count(all a) |
+--------------+
|            3 |
+--------------+

MAX

max(x)

Renvoie la valeur maximale de l'ensemble d'entrée. Les valeurs BOOLEAN et les lignes NULL sont exclues.

Types d'entrée	Tous les types de données (sauf BOOLEAN)
Type de retour	Identique à l'entrée

Exemple

SELECT max(a) FROM testtable;

+--------+
| max(a) |
+--------+
|      3 |
+--------+

MIN

min(x)

Renvoie la valeur minimale de l'ensemble d'entrée. Les valeurs BOOLEAN et les lignes NULL sont exclues.

Types d'entrée	Tous les types de données (sauf BOOLEAN)
Type de retour	Identique à l'entrée

Exemple

SELECT min(a) FROM testtable;

+--------+
| min(a) |
+--------+
|      1 |
+--------+

SUM

sum(x)

Renvoie la somme de toutes les valeurs d'entrée.

Types d'entrée	BIGINT, DOUBLE, FLOAT
Type de retour	BIGINT

Exemple

SELECT sum(a) FROM testtable;

+--------+
| sum(a) |
+--------+
|      6 |
+--------+

GROUP_CONCAT

GROUP_CONCAT([DISTINCT] col_name
             [ORDER BY col_name [ASC | DESC]]
             [SEPARATOR str_val])

Concatène les valeurs d'un groupe en une seule chaîne, en utilisant les résultats d'une clause GROUP BY. Renvoie NULL uniquement si toutes les valeurs concaténées sont NULL.

Clause	Obligatoire	Description
`DISTINCT`	Non	Supprime les valeurs en double avant la concaténation
`ORDER BY`		Trie les valeurs au sein du groupe avant la concaténation. L'ordre croissant est utilisé par défaut
`SEPARATOR`		Définit le délimiteur entre les valeurs. La virgule (`,`) est utilisée par défaut

Type d'entrée	STRING
Type de retour	STRING

Exemple

Créez et renseignez la table person :

CREATE TABLE person(id INT, name VARCHAR, age INT) DISTRIBUTED BY HASH(id);
INSERT INTO person VALUES (1,'mary',13),(2,'eva',14),(2,'adam',13),(3,'eva',13),(3,null,13),(3,null,null),(4,null,13),(4,null,null);

Regroupez par id, concaténez les noms distincts dans l'ordre décroissant, séparés par # :

SELECT
  id,
  GROUP_CONCAT(DISTINCT name ORDER BY name DESC SEPARATOR '#')
FROM person
GROUP BY id;

+------+--------------------------------------------------------------+
| id   | GROUP_CONCAT(DISTINCT name ORDER BY name DESC SEPARATOR '#') |
+------+--------------------------------------------------------------+
|    2 | eva#adam                                                     |
|    1 | mary                                                         |
|    4 | NULL                                                         |
|    3 | eva                                                          |
+------+--------------------------------------------------------------+

Fonctions d'agrégation bit à bit

BIT_AND

bit_and(x)

Renvoie le résultat d'une opération ET bit à bit appliquée à toutes les valeurs d'entrée.

Types d'entrée	BIGINT, DOUBLE, FLOAT
Type de retour	BIGINT

Exemple

SELECT bit_and(a) FROM testtable;

+------------+
| bit_and(a) |
+------------+
|          0 |
+------------+

BIT_OR

bit_or(x)

Renvoie le résultat d'une opération OU bit à bit appliquée à toutes les valeurs d'entrée.

Types d'entrée	BIGINT, DOUBLE, FLOAT
Type de retour	BIGINT

Exemple

SELECT bit_or(a) FROM testtable;

+-----------+
| bit_or(a) |
+-----------+
|         3 |
+-----------+

BIT_XOR

bit_xor(x)

Renvoie le résultat d'une opération OU exclusif (XOR) bit à bit appliquée à toutes les valeurs d'entrée.

Types d'entrée	BIGINT, DOUBLE, FLOAT
Type de retour	BIGINT

Exemple

SELECT bit_xor(a) FROM testtable;

+------------+
| bit_xor(a) |
+------------+
|          0 |
+------------+

Fonctions d'agrégation statistiques

STD / STDDEV / STDDEV_POP

std(x)
stddev(x)
stddev_pop(x)

Renvoie l'écart type de population des valeurs d'entrée. STD, STDDEV et STDDEV_POP sont des alias de la même fonction.

Types d'entrée	BIGINT, DOUBLE
Type de retour	DOUBLE
Alias	`std`, `stddev`, `stddev_pop`

Exemples

SELECT std(a) FROM testtable;

+-------------------+
| std(a)            |
+-------------------+
| 0.816496580927726 |
+-------------------+

SELECT stddev_pop(a) FROM testtable;

+-------------------+
| stddev_pop(a)     |
+-------------------+
| 0.816496580927726 |
+-------------------+

STDDEV_SAMP

stddev_samp(x)

Renvoie l'écart type d'échantillon des valeurs d'entrée.

Types d'entrée	BIGINT, DOUBLE
Type de retour	DOUBLE

Exemple

SELECT stddev_samp(a) FROM testtable;

+----------------+
| stddev_samp(a) |
+----------------+
|            1.0 |
+----------------+

VARIANCE / VAR_POP

variance(x)
var_pop(x)

Renvoie la variance standard de population des valeurs d'entrée. Les lignes contenant des valeurs NULL sont ignorées ; si toutes les valeurs sont NULL, la fonction renvoie NULL.

VARIANCE est une extension MySQL du SQL standard. VAR_POP est l'équivalent en SQL standard. Ces deux fonctions produisent des résultats identiques.

Types d'entrée	BIGINT, DOUBLE
Type de retour	DOUBLE
Alias	`variance`, `var_pop`

Exemples

SELECT variance(a) FROM testtable;

+--------------------+
| variance(a)        |
+--------------------+
| 0.6666666666666666 |
+--------------------+

SELECT var_pop(a) FROM testtable;

+--------------------+
| var_pop(a)         |
+--------------------+
| 0.6666666666666666 |
+--------------------+

VAR_SAMP

var_samp(x)

Renvoie la variance d'échantillon des valeurs d'entrée.

Types d'entrée	BIGINT, DOUBLE
Type de retour	DOUBLE

Exemple

SELECT var_samp(a) FROM testtable;

+-------------+
| var_samp(a) |
+-------------+
|         1.0 |
+-------------+