Fonctions d’agrégation

Documentation des fonctions d’agrégation

Les fonctions d’agrégation se comportent de manière classique, comme s’y attendent les experts en bases de données.

ClickHouse prend également en charge :

• les fonctions d’agrégation paramétriques, qui acceptent d’autres paramètres en plus des colonnes.
• les combinateurs, qui modifient le comportement des fonctions d’agrégation.

​

Traitement des NULL

Lors de l’agrégation, tous les arguments NULL sont ignorés. Si l’agrégation comporte plusieurs arguments, elle ignore toute ligne dans laquelle au moins l’un d’eux est NULL.

Il existe une exception à cette règle : les fonctions first_value, last_value et leurs alias (any et anyLast, respectivement), lorsqu’elles sont suivies du modifier RESPECT NULLS. Par exemple, FIRST_VALUE(b) RESPECT NULLS.

Exemples :

Considérez la table suivante :

┌─x─┬────y─┐
│ 1 │    2 │
│ 2 │ ᴺᵁᴸᴸ │
│ 3 │    2 │
│ 3 │    3 │
│ 3 │ ᴺᵁᴸᴸ │
└───┴──────┘

Supposons que vous deviez faire la somme des valeurs de la colonne y :

SELECT sum(y) FROM t_null_big

┌─sum(y)─┐
│      7 │
└────────┘

Vous pouvez maintenant utiliser la fonction groupArray pour créer un tableau à partir de la colonne y :

SELECT groupArray(y) FROM t_null_big

┌─groupArray(y)─┐
│ [2,2,3]       │
└───────────────┘

groupArray n’inclut pas NULL dans le tableau résultant.

Vous pouvez utiliser COALESCE pour remplacer NULL par une valeur adaptée à votre cas d’usage. Par exemple : avg(COALESCE(column, 0)) utilisera la valeur de la colonne dans l’agrégation, ou zéro si elle vaut NULL :

SELECT
    avg(y),
    avg(coalesce(y, 0))
FROM t_null_big

┌─────────────avg(y)─┬─avg(coalesce(y, 0))─┐
│ 2.3333333333333335 │                 1.4 │
└────────────────────┴─────────────────────┘

Vous pouvez également utiliser Tuple pour contourner le comportement d’omission de NULL. Un Tuple qui contient uniquement une valeur NULL n’est pas NULL ; les fonctions d’agrégation n’ignoreront donc pas cette ligne à cause de cette valeur NULL.

SELECT
    groupArray(y),
    groupArray(tuple(y)).1
FROM t_null_big;

┌─groupArray(y)─┬─tupleElement(groupArray(tuple(y)), 1)─┐
│ [2,2,3]       │ [2,NULL,2,3,NULL]                     │
└───────────────┴───────────────────────────────────────┘

Notez que les agrégations sont ignorées lorsque les colonnes sont utilisées comme arguments d’une fonction d’agrégation. Par exemple, count sans paramètres (count()) ou avec des paramètres constants (count(1)) comptera toutes les lignes du bloc (indépendamment de la valeur de la colonne du GROUP BY, puisqu’elle n’est pas un argument), tandis que count(column) ne renverra que le nombre de lignes pour lesquelles column n’est pas NULL.

SELECT
    v,
    count(1),
    count(v)
FROM
(
    SELECT if(number < 10, NULL, number % 3) AS v
    FROM numbers(15)
)
GROUP BY v

┌────v─┬─count()─┬─count(v)─┐
│ ᴺᵁᴸᴸ │      10 │        0 │
│    0 │       1 │        1 │
│    1 │       2 │        2 │
│    2 │       2 │        2 │
└──────┴─────────┴──────────┘

Et voici un exemple de first_value avec RESPECT NULLS, où l’on peut voir que les valeurs d’entrée NULL sont prises en compte et que la première valeur lue sera renvoyée, qu’elle soit NULL ou non :

SELECT
    col || '_' || ((col + 1) * 5 - 1) AS range,
    first_value(odd_or_null) AS first,
    first_value(odd_or_null) IGNORE NULLS as first_ignore_null,
    first_value(odd_or_null) RESPECT NULLS as first_respect_nulls
FROM
(
    SELECT
        intDiv(number, 5) AS col,
        if(number % 2 == 0, NULL, number) AS odd_or_null
    FROM numbers(15)
)
GROUP BY col
ORDER BY col

┌─range─┬─first─┬─first_ignore_null─┬─first_respect_nulls─┐
│ 0_4   │     1 │                 1 │                ᴺᵁᴸᴸ │
│ 1_9   │     5 │                 5 │                   5 │
│ 2_14  │    11 │                11 │                ᴺᵁᴸᴸ │
└───────┴───────┴───────────────────┴─────────────────────┘