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

Utilise une syntaxe différente de celle des autres fonctions d’agrégation.

Utilise une syntaxe différente de celle des autres fonctions d’agrégation.

Alias pour STDDEV_SAMP.

Alias pour VAR_POP.

Alias pour VAR_SAMP.

Agrégation Bitwise

Agrégation booléenne

Hachage

Agrégation de données semi-structurées

Régression linéaire

Statistiques et probabilités

Comptage de valeurs distinctes

Estimation de cardinalité . (en utilisant HyperLogLog)

Alias pour HLL.

Pas une fonction d’agrégation. Utilise une entrée scalaire à partir de HLL_ACCUMULATE ou HLL_COMBINE.

Estimation de similarité . (en utilisant MinHash)

Alias pour APPROXIMATE_SIMILARITY.

Estimation de fréquence . (en utilisant Économie d’espace)

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)

Pas une fonction d’agrégation. Utilise une entrée scalaire à partir de APPROX_PERCENTILE_ACCUMULATE ou APPROX_PERCENTILE_COMBINE.

Utilitaires d’agrégation

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.

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