Fonctions d’agrégation¶

Les fonctions d’agrégation agissent sur les valeurs de lignes pour effectuer des calculs mathématiques tels que la somme, la moyenne, le comptage, les valeurs minimale/maximale, l’écart type et l’estimation, ainsi que d’autres opérations non mathématiques.

Une fonction d’agrégation prend zéro, une ou plusieurs lignes en entrée et génère une sortie unique. En revanche, les fonctions scalaires prennent une ligne en entrée et produisent une ligne (une seule valeur) en sortie.

Une fonction d’agrégation retourne toujours exactement une ligne, même lorsque l’entrée ne contient aucune ligne. En règle générale, si l’entrée ne contient aucune ligne, la sortie est NULL. Toutefois, une fonction d’agrégation peut renvoyer 0, une chaîne vide ou une autre valeur lorsqu’elle est ne reçoit aucune ligne.

Dans ce chapitre :

Liste des fonctions (par sous-catégorie)¶

Nom de la fonction	Remarques
Agrégation générale
ANY_VALUE
AVG
CORR
COUNT
COUNT_IF
COVAR_POP
COVAR_SAMP
LISTAGG
MAX
MAX_BY
MEDIAN
MIN
MIN_BY
MODE
PERCENTILE_CONT	Utilise une syntaxe différente de celle des autres fonctions d’agrégation.
PERCENTILE_DISC	Utilise une syntaxe différente de celle des autres fonctions d’agrégation.
STDDEV, STDDEV_SAMP	STDDEV et STDDEV_SAMP sont des alias.
STDDEV_POP
SUM
VAR_POP
VAR_SAMP
VARIANCE_POP	Alias pour VAR_POP.
VARIANCE , VARIANCE_SAMP	Alias pour VAR_SAMP.
Agrégation Bitwise
BITAND_AGG
BITOR_AGG
BITXOR_AGG
Agrégation booléenne
BOOLAND_AGG
BOOLOR_AGG
BOOLXOR_AGG
Hachage
HASH_AGG
Agrégation de données semi-structurées
ARRAY_AGG
OBJECT_AGG
Régression linéaire
REGR_AVGX
REGR_AVGY
REGR_COUNT
REGR_INTERCEPT
REGR_R2
REGR_SLOPE
REGR_SXX
REGR_SXY
REGR_SYY
Statistiques et probabilités
KURTOSIS
SKEW
Comptage de valeurs distinctes
ARRAY_UNION_AGG
ARRAY_UNIQUE_AGG
BITMAP_BIT_POSITION
BITMAP_BUCKET_NUMBER
BITMAP_COUNT
BITMAP_CONSTRUCT_AGG
BITMAP_OR_AGG
Estimation de cardinalité . (en utilisant HyperLogLog)
APPROX_COUNT_DISTINCT	Alias pour HLL.
HLL
HLL_ACCUMULATE
HLL_COMBINE
HLL_ESTIMATE	Pas une fonction d’agrégation. Utilise une entrée scalaire à partir de HLL_ACCUMULATE ou HLL_COMBINE.
HLL_EXPORT
HLL_IMPORT
Estimation de similarité . (en utilisant MinHash)
APPROXIMATE_JACCARD_INDEX	Alias pour APPROXIMATE_SIMILARITY.
APPROXIMATE_SIMILARITY
MINHASH
MINHASH_COMBINE
Estimation de fréquence . (en utilisant Économie d’espace)
APPROX_TOP_K
APPROX_TOP_K_ACCUMULATE
APPROX_TOP_K_COMBINE
APPROX_TOP_K_ESTIMATE	Pas une fonction d’agrégation. Utilise une entrée scalaire à partir de APPROX_TOP_K_ACCUMULATE ou APPROX_TOP_K_COMBINE.
Estimation du centile . (en utilisant t-Digest)
APPROX_PERCENTILE
APPROX_PERCENTILE_ACCUMULATE
APPROX_PERCENTILE_COMBINE
APPROX_PERCENTILE_ESTIMATE	Pas une fonction d’agrégation. Utilise une entrée scalaire à partir de APPROX_PERCENTILE_ACCUMULATE ou APPROX_PERCENTILE_COMBINE.
Utilitaires d’agrégation
GROUPING	Il ne s’agit pas d’une fonction d’agrégation, mais peut être utilisé conjointement avec des fonctions d’agrégation pour déterminer le niveau d’agrégation d’une ligne produite par une requête GROUP BY.
GROUPING_ID	Alias pour GROUPING.

Exemple d’introduction¶

L’exemple suivant illustre la différence entre une fonction d’agrégation (AVG) et une fonction scalaire (COS). La fonction scalaire renvoie une ligne de sortie pour chaque ligne d’entrée, tandis que la fonction d’agrégation renvoie une ligne de sortie pour plusieurs lignes d’entrée :

La table contient les données suivantes :
SELECT x, y 
    FROM simple
    ORDER BY x,y;
+----+----+
|  X |  Y |
|----+----|
| 10 | 20 |
| 20 | 44 |
| 30 | 70 |
+----+----+
Copy
La fonction scalaire renvoie une ligne de sortie pour chaque ligne d’entrée.
SELECT COS(x)
    FROM simple
    ORDER BY x;
+---------------+
|        COS(X) |
|---------------|
| -0.8390715291 |
|  0.4080820618 |
|  0.1542514499 |
+---------------+
Copy
La fonction d’agrégation renvoie une ligne de sortie pour plusieurs lignes d’entrée :
SELECT SUM(x)
    FROM simple;
+--------+
| SUM(X) |
|--------|
|     60 |
+--------+
Copy

Fonctions d’agrégation et valeurs NULL¶

Certaines fonctions d’agrégation ignorent les valeurs NULL. Par exemple, AVG calcule la moyenne des valeurs 1, 5 et NULL sur 3, en fonction de la formule suivante :

(1 + 5) / 2 = 3

Dans le numérateur et le dénominateur, seules les deux valeurs autres que NULL sont utilisées.

Si toutes les valeurs transmises à la fonction d’agrégation sont NULL, alors la fonction d’agrégation renvoie NULL.

Certaines fonctions d’agrégation peuvent recevoir plusieurs colonnes. Par exemple :

SELECT COUNT(col1, col2) FROM table1;

Copy

Dans ces cas, la fonction d’agrégation ignore une ligne si l’une des colonnes est NULL.

Par exemple, dans la requête suivante, COUNT renvoie 1, pas 4, car trois des quatre lignes contiennent au moins une valeur NULL dans les colonnes sélectionnées :

Créer une table et la remplir avec des valeurs :

CREATE TABLE t (x INT, y INT);
INSERT INTO t (x, y) VALUES
  (1, 2),         -- No NULLs.
  (3, NULL),      -- One but not all columns are NULL.
  (NULL, 6),      -- One but not all columns are NULL.
  (NULL, NULL);   -- All columns are NULL.

Copy

Interrogez la table :

SELECT COUNT(x, y) FROM t;
+-------------+
| COUNT(X, Y) |
|-------------|
|           1 |
+-------------+

Copy

De même, si SUM est appelé avec une expression faisant référence à deux colonnes ou plus, et si l’une ou plusieurs de ces colonnes est NULL, alors l’expression donne un résultat NULL et la ligne est ignorée :

SELECT SUM(x + y) FROM t;
+------------+
| SUM(X + Y) |
|------------|
|          3 |
+------------+

Copy

Notez que ce comportement diffère du comportement de GROUP BY, qui ne supprime pas les lignes lorsque certaines colonnes sont NULL :

SELECT x AS X_COL, y AS Y_COL FROM t GROUP BY x, y;
+-------+-------+
| X_COL | Y_COL |
|-------+-------|
|     1 |     2 |
|     3 |  NULL |
|  NULL |     6 |
|  NULL |  NULL |
+-------+-------+

Copy