Combinateurs de fonctions d'agrégation

Le nom d’une fonction d’agrégation peut être suivi d’un suffixe. Cela modifie son fonctionnement.

-If

Le suffixe -If peut être ajouté au nom de n’importe quelle fonction d’agrégation. Dans ce cas, la fonction d’agrégation accepte un argument supplémentaire – une condition (de type Uint8). La fonction d’agrégation traite uniquement les lignes pour lesquelles la condition est remplie. Si la condition n’est jamais remplie, elle renvoie une valeur par défaut (généralement des zéros ou des chaînes vides). Exemples : sumIf(column, cond), countIf(cond), avgIf(x, cond), quantilesTimingIf(level1, level2)(x, cond), argMinIf(arg, val, cond) et ainsi de suite. Avec les fonctions d’agrégation conditionnelles, vous pouvez calculer des agrégats pour plusieurs conditions à la fois, sans utiliser de sous-requêtes ni de JOIN. Par exemple, les fonctions d’agrégation conditionnelles peuvent être utilisées pour implémenter la fonctionnalité de comparaison de segments.

-Array

Le suffixe -Array peut être appliqué à n’importe quelle fonction d’agrégation. Dans ce cas, la fonction d’agrégation prend des arguments de type ‘Array(T)’ (tableaux) au lieu d’arguments de type ‘T’. Si la fonction d’agrégation accepte plusieurs arguments, ceux-ci doivent être des tableaux de même longueur. Lors du traitement de tableaux, la fonction d’agrégation se comporte comme la fonction d’agrégation d’origine sur l’ensemble des éléments des tableaux. Exemple 1 : sumArray(arr) - Fait le total de tous les éléments de tous les tableaux ‘arr’. Dans cet exemple, on aurait pu l’écrire plus simplement : sum(arraySum(arr)). Exemple 2 : uniqArray(arr) – Compte le nombre d’éléments uniques dans tous les tableaux ‘arr’. On pourrait le faire plus simplement ainsi : uniq(arrayJoin(arr)), mais il n’est pas toujours possible d’ajouter ‘arrayJoin’ à une requête. -If et -Array peuvent être combinés. Cependant, ‘Array’ doit venir en premier, puis ‘If’. Exemples : uniqArrayIf(arr, cond), quantilesTimingArrayIf(level1, level2)(arr, cond). En raison de cet ordre, l’argument ‘cond’ ne sera pas un tableau.

-Map

Le suffixe -Map peut être ajouté à toute fonction d’agrégation. Cela crée une fonction d’agrégation qui prend un type Map en argument et agrège séparément les valeurs de chaque clé de la map à l’aide de la fonction d’agrégation spécifiée. Le résultat est lui aussi de type Map. Exemple

CREATE TABLE map_map(
    date Date,
    timeslot DateTime,
    status Map(String, UInt64)
) ENGINE = MergeTree
ORDER BY ();

INSERT INTO map_map VALUES
    ('2000-01-01', '2000-01-01 00:00:00', (['a', 'b', 'c'], [10, 10, 10])),
    ('2000-01-01', '2000-01-01 00:00:00', (['c', 'd', 'e'], [10, 10, 10])),
    ('2000-01-01', '2000-01-01 00:01:00', (['d', 'e', 'f'], [10, 10, 10])),
    ('2000-01-01', '2000-01-01 00:01:00', (['f', 'g', 'g'], [10, 10, 10]));

SELECT
    timeslot,
    sumMap(status),
    avgMap(status),
    minMap(status)
FROM map_map
GROUP BY timeslot;

┌────────────timeslot─┬─sumMap(status)───────────────────────┬─avgMap(status)───────────────────────┬─minMap(status)───────────────────────┐
│ 2000-01-01 00:00:00 │ {'a':10,'b':10,'c':20,'d':10,'e':10} │ {'a':10,'b':10,'c':10,'d':10,'e':10} │ {'a':10,'b':10,'c':10,'d':10,'e':10} │
│ 2000-01-01 00:01:00 │ {'d':10,'e':10,'f':20,'g':20}        │ {'d':10,'e':10,'f':10,'g':10}        │ {'d':10,'e':10,'f':10,'g':10}        │
└─────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┘

-SimpleState

Si vous appliquez ce combinateur, la fonction d’agrégation renvoie la même valeur, mais avec un type différent. Il s’agit d’une SimpleAggregateFunction(…) qui peut être stockée dans une table pour être utilisée avec les tables AggregatingMergeTree. Syntaxe

<aggFunction>SimpleState(x)

Arguments

x — Paramètres de la fonction d’agrégation.

Valeurs de retour Valeur d’une fonction d’agrégation du type SimpleAggregateFunction(...). Exemple

Query

WITH anySimpleState(number) AS c SELECT toTypeName(c), c FROM numbers(1);

Response

┌─toTypeName(c)────────────────────────┬─c─┐
│ SimpleAggregateFunction(any, UInt64) │ 0 │
└──────────────────────────────────────┴───┘

-State

Si vous appliquez ce combinateur, la fonction d’agrégation ne renvoie pas la valeur finale (par exemple, le nombre de valeurs uniques pour la fonction uniq), mais un état intermédiaire de l’agrégation (pour uniq, il s’agit de la table de hachage utilisée pour calculer le nombre de valeurs uniques). Il s’agit d’une AggregateFunction(...) qui peut être utilisée pour un traitement ultérieur ou stockée dans une table afin de finaliser l’agrégation plus tard.

Veuillez noter que -MapState n’est pas invariant pour un même jeu de données, car l’ordre des données dans l’état intermédiaire peut changer, même si cela n’a pas d’impact sur l’ingestion de ces données.

Pour travailler avec ces états, utilisez :

le moteur de table AggregatingMergeTree ;
la fonction finalizeAggregation ;
la fonction runningAccumulate ;
le combinateur -Merge ;
le combinateur -MergeState.

-Merge

Si vous appliquez ce combinateur, la fonction d’agrégation prend l’état d’agrégation intermédiaire en argument, fusionne les états pour achever l’agrégation et renvoie la valeur obtenue.

-MergeState

Fusionne les états d’agrégation intermédiaires de la même manière que le combinateur -Merge. Cependant, il ne renvoie pas la valeur obtenue, mais un état d’agrégation intermédiaire, à l’instar du combinateur -State.

-ForEach

Convertit une fonction d’agrégation pour les tables en une fonction d’agrégation pour les tableaux, qui agrège les éléments correspondants de chaque tableau et renvoie un tableau de résultats. Par exemple, sumForEach pour les tableaux [1, 2], [3, 4, 5] et [6, 7] renvoie le résultat [10, 13, 5] après avoir additionné les éléments correspondants.

-Tuple

Le suffixe -Tuple peut être ajouté à n’importe quelle fonction d’agrégation. La fonction combinée prend un argument de type Tuple pour chaque argument de la fonction d’agrégation sous-jacente ; tous les tuples doivent avoir le même nombre d’éléments. L’agrégation est appliquée indépendamment à chaque position d’élément : elle reçoit l’élément correspondant de chaque Tuple et renvoie un Tuple de résultats. Si le premier Tuple d’entrée comporte des noms d’éléments explicites, ils sont conservés dans le résultat. Les fonctions d’agrégation qui gèrent elles-mêmes les valeurs NULL (anyRespectNulls, anyLastRespectNulls, le modificateur RESPECT NULLS) ne prennent pas en charge le type Nullable(Tuple(...)) comme argument ; utilisez plutôt des éléments Nullable. Syntaxe

<aggFunction>Tuple(tuple1[, tuple2, ...])

Arguments

tuple1[, tuple2, ...] — Colonnes de type Tuple, une pour chaque argument de la fonction d’agrégation sous-jacente, toutes ayant le même nombre d’éléments. Chaque élément doit être d’un type pris en charge par la fonction d’agrégation sous-jacente pour cette position d’argument.

Valeur de retour

Un Tuple contenant le résultat de l’application de la fonction d’agrégation à chaque élément indépendamment.

Type : Tuple(aggFunction(element1), aggFunction(element2), ...). Exemple Requête :

SELECT sumTuple(t) FROM
(
    SELECT tuple(toInt64(1), toFloat64(2.5)) AS t
    UNION ALL
    SELECT tuple(toInt64(3), toFloat64(4.5))
    UNION ALL
    SELECT tuple(toInt64(5), toFloat64(6.5))
);

Résultat :

┌─sumTuple(t)─┐
│ (9,13.5)    │
└─────────────┘

Utilisation de GROUP BY :

SELECT
    k,
    avgTuple(t)
FROM
(
    SELECT
        number % 2 AS k,
        tuple(toInt64(number), toFloat64(number) * 1.5) AS t
    FROM numbers(6)
)
GROUP BY k
ORDER BY k;

┌─k─┬─avgTuple(t)─┐
│ 0 │ (2,3)       │
│ 1 │ (3,4.5)     │
└───┴─────────────┘

Utilisation avec une fonction d’agrégation à plusieurs arguments : chaque argument Tuple fournit un argument à la fonction sous-jacente, et les éléments sont appariés selon leur position :

corrTuple((a1, a2), (b1, b2)) = (corr(a1, b1), corr(a2, b2))

SELECT corrTuple((a1, a2), (b1, b2))
FROM
(
    SELECT
        toFloat64(number) AS a1,
        toFloat64(number * 2) AS a2,
        toFloat64(100 - number) AS b1,
        toFloat64(number * 3) AS b2
    FROM numbers(10)
);

┌─corrTuple((a1, a2), (b1, b2))─┐
│ (-1,1)                        │
└───────────────────────────────┘

a1 et b1 sont anticorrélés, tandis que a2 et b2 sont proportionnels ; le résultat est donc (-1, 1). -Tuple peut être combiné avec d’autres combinateurs, comme -If. Par exemple : sumTupleIf(tuple_column, cond).

-Distinct

Chaque combinaison distincte d’arguments n’est agrégée qu’une seule fois. Les valeurs répétées sont ignorées. Exemples : sum(DISTINCT x) (ou sumDistinct(x)), groupArray(DISTINCT x) (ou groupArrayDistinct(x)), corrStable(DISTINCT x, y) (ou corrStableDistinct(x, y)) et ainsi de suite.

-OrDefault

Modifie le comportement d’une fonction d’agrégation. Si une fonction d’agrégation ne reçoit aucune valeur en entrée, ce combinateur renvoie la valeur par défaut de son type de données de retour. S’applique aux fonctions d’agrégation qui peuvent accepter des données d’entrée vides. -OrDefault peut être utilisé avec d’autres combinateurs. Syntaxe

<aggFunction>OrDefault(x)

Arguments

x — Paramètres de la fonction d’agrégation.

Valeur de retour Renvoie la valeur par défaut du type de retour de la fonction d’agrégation s’il n’y a rien à agréger. Le type dépend de la fonction d’agrégation utilisée. Exemple

Query

SELECT avg(number), avgOrDefault(number) FROM numbers(0)

Response

┌─avg(number)─┬─avgOrDefault(number)─┐
│         nan │                    0 │
└─────────────┴──────────────────────┘

-OrDefault peut également être utilisé avec d’autres combinateurs. Cela est utile lorsque la fonction d’agrégation n’accepte pas d’entrée vide.

Query

SELECT avgOrDefaultIf(x, x > 10)
FROM
(
    SELECT toDecimal32(1.23, 2) AS x
)

Response

┌─avgOrDefaultIf(x, greater(x, 10))─┐
│                              0.00 │
└───────────────────────────────────┘

-OrNull

Modifie le comportement d’une fonction d’agrégation. Ce combinateur convertit le résultat d’une fonction d’agrégation en type de données Nullable. Si la fonction d’agrégation n’a aucune valeur à calculer, elle renvoie NULL. -OrNull peut être utilisé avec d’autres combinateurs. Syntaxe

<aggFunction>OrNull(x)

Arguments

x — Paramètres de la fonction d’agrégation.

Valeurs de retour

Le résultat de la fonction d’agrégation, converti en type de données Nullable.
NULL s’il n’y a rien à agréger.

Type : Nullable(type de retour de la fonction d’agrégation). Exemple Ajoutez -orNull à la fin de la fonction d’agrégation.

Query

SELECT sumOrNull(number), toTypeName(sumOrNull(number)) FROM numbers(10) WHERE number > 10

Response

┌─sumOrNull(number)─┬─toTypeName(sumOrNull(number))─┐
│              ᴺᵁᴸᴸ │ Nullable(UInt64)              │
└───────────────────┴───────────────────────────────┘

De plus, -OrNull peut être utilisé avec d’autres combinateurs. Cela est utile lorsque la fonction d’agrégation n’accepte pas d’entrée vide.

Query

SELECT avgOrNullIf(x, x > 10)
FROM
(
    SELECT toDecimal32(1.23, 2) AS x
)

Response

┌─avgOrNullIf(x, greater(x, 10))─┐
│                           ᴺᵁᴸᴸ │
└────────────────────────────────┘

-Resample

Permet de répartir les données en groupes, puis d’agréger séparément les données de chaque groupe. Les groupes sont créés en répartissant les valeurs d’une colonne en intervalles.

<aggFunction>Resample(start, end, step)(<aggFunction_params>, resampling_key)

Arguments

start — Valeur de début de l’intervalle complet requis pour les valeurs de resampling_key.
stop — Valeur de fin de l’intervalle complet requis pour les valeurs de resampling_key. L’intervalle complet n’inclut pas la valeur stop [start, stop). Elle doit être supérieure à start : une plage ne couvrant aucun sous-intervalle lève une exception.
step — Pas utilisé pour découper l’intervalle complet en sous-intervalles. aggFunction est exécutée indépendamment sur chacun de ces sous-intervalles.
resampling_key — Colonne dont les valeurs sont utilisées pour répartir les données en intervalles.
aggFunction_params — Paramètres de aggFunction.

Valeur de retour

Tableau des résultats de aggFunction pour chaque sous-intervalle.

Exemple Considérez la table people avec les données suivantes :

┌─name───┬─age─┬─wage─┐
│ John   │  16 │   10 │
│ Alice  │  30 │   15 │
│ Mary   │  35 │    8 │
│ Evelyn │  48 │ 11.5 │
│ David  │  62 │  9.9 │
│ Brian  │  60 │   16 │
└────────┴─────┴──────┘

Récupérons les noms des personnes dont l’âge se situe dans les intervalles [30,60) et [60,75). Comme nous utilisons une représentation entière pour l’âge, nous obtenons les âges des intervalles [30, 59] et [60,74]. Pour agréger les noms dans un tableau, nous utilisons la fonction d’agrégation groupArray. Elle prend un argument. Dans notre cas, il s’agit de la colonne name. La fonction groupArrayResample doit utiliser la colonne age pour agréger les noms par âge. Pour définir les intervalles requis, nous passons les arguments 30, 75, 30 à la fonction groupArrayResample.

SELECT groupArrayResample(30, 75, 30)(name, age) FROM people

┌─groupArrayResample(30, 75, 30)(name, age)─────┐
│ [['Alice','Mary','Evelyn'],['David','Brian']] │
└───────────────────────────────────────────────┘

Examinons les résultats. John ne figure pas dans l’échantillon parce qu’il est trop jeune. Les autres personnes sont réparties selon les tranches d’âge spécifiées. Comptons maintenant le nombre total de personnes ainsi que leur salaire moyen dans les tranches d’âge spécifiées.

SELECT
    countResample(30, 75, 30)(name, age) AS amount,
    avgResample(30, 75, 30)(wage, age) AS avg_wage
FROM people

┌─amount─┬─avg_wage──────────────────┐
│ [3,2]  │ [11.5,12.949999809265137] │
└────────┴───────────────────────────┘

-ArgMin

Le suffixe -ArgMin peut être ajouté au nom de n’importe quelle fonction d’agrégation. Dans ce cas, la fonction d’agrégation accepte un argument supplémentaire, qui doit être une expression comparable. La fonction d’agrégation traite uniquement les lignes ayant la valeur minimale pour l’expression supplémentaire spécifiée. Exemples : sumArgMin(column, expr), countArgMin(expr), avgArgMin(x, expr), etc.

-ArgMax

Similaire au suffixe -ArgMin, mais ne traite que les lignes dont la valeur de l’expression supplémentaire spécifiée est maximale.

Blog : Utiliser les combinateurs d’agrégation dans ClickHouse

Référence SQL

Types de données

Moteurs

Fonctions

Formats

Paramètres

Tables système

Lacs de données

-If

-Array

-Map

-SimpleState

-State

-Merge

-MergeState

-ForEach

-Tuple

-Distinct

-OrDefault

-OrNull

-Resample

-ArgMin

-ArgMax

​-If

​-Array

​-Map

​-SimpleState

​-State

​-Merge

​-MergeState

​-ForEach

​-Tuple

​-Distinct

​-OrDefault

​-OrNull

​-Resample

​-ArgMin

​-ArgMax

​Contenu connexe

-If

-Array

-Map

-SimpleState

-State

-Merge

-MergeState

-ForEach

-Tuple

-Distinct

-OrDefault

-OrNull

-Resample

-ArgMin

-ArgMax

Contenu connexe