> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-fbfa8bee.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > Documentation de la clause GROUP BY # Clause GROUP BY La clause `GROUP BY` fait passer la requête `SELECT` en mode agrégation, qui fonctionne comme suit : * La clause `GROUP BY` contient une liste d’expressions (ou une seule expression, considérée comme une liste d’un seul élément). Cette liste sert de « clé de regroupement », tandis que chaque expression individuelle est appelée « expression de clé ». * Toutes les expressions des clauses [SELECT](/fr/reference/statements/select/index), [HAVING](/fr/reference/statements/select/having) et [ORDER BY](/fr/reference/statements/select/order-by) **doivent** être calculées à partir d’expressions de clé **ou** de [fonctions d’agrégation](/fr/reference/functions/aggregate-functions/index) appliquées à des expressions qui ne font pas partie de la clé (y compris de simples colonnes). En d’autres termes, chaque colonne sélectionnée dans la table doit être utilisée soit dans une expression de clé, soit dans une fonction d’agrégation, mais pas les deux. * Le résultat de l’agrégation d’une requête `SELECT` contiendra autant de lignes qu’il y a de valeurs uniques de « clé de regroupement » dans la table source. En général, cela réduit considérablement le nombre de lignes, souvent de plusieurs ordres de grandeur, mais pas systématiquement : le nombre de lignes reste identique si toutes les valeurs de la « clé de regroupement » sont distinctes. Si vous souhaitez regrouper les données de la table par numéros de colonnes plutôt que par noms de colonnes, activez le paramètre [enable\_positional\_arguments](/fr/reference/settings/session-settings#enable_positional_arguments). Il existe une autre manière d’effectuer une agrégation sur une table. Si une requête contient des colonnes de table uniquement à l’intérieur de fonctions d’agrégation, la clause `GROUP BY` peut être omise, et une agrégation sur un ensemble vide de clés est alors supposée. De telles requêtes renvoient toujours exactement une ligne.
## Traitement de NULL
Lors du regroupement, ClickHouse interprète [NULL](/fr/reference/syntax#null) comme une valeur et considère que `NULL==NULL`. Cela diffère du traitement de `NULL` dans la plupart des autres contextes. Voici un exemple pour illustrer ce que cela signifie. Supposons que vous ayez cette table : ```text theme={null} ┌─x─┬────y─┐ │ 1 │ 2 │ │ 2 │ ᴺᵁᴸᴸ │ │ 3 │ 2 │ │ 3 │ 3 │ │ 3 │ ᴺᵁᴸᴸ │ └───┴──────┘ ``` La requête `SELECT sum(x), y FROM t_null_big GROUP BY y` renvoie : ```text theme={null} ┌─sum(x)─┬────y─┐ │ 4 │ 2 │ │ 3 │ 3 │ │ 5 │ ᴺᵁᴸᴸ │ └────────┴──────┘ ``` Vous pouvez voir que `GROUP BY` pour `y = NULL` a additionné `x`, comme si `NULL` était une valeur à part entière. Si vous passez plusieurs expressions à `GROUP BY`, le résultat vous donnera toutes les combinaisons de la sélection, comme si `NULL` était une valeur spécifique.
## Modificateur ROLLUP
Le modificateur `ROLLUP` est utilisé pour calculer des sous-totaux pour les expressions de clé, en fonction de leur ordre dans la liste `GROUP BY`. Les lignes de sous-totaux sont ajoutées après la table de résultats. Les sous-totaux sont calculés dans l'ordre inverse : d'abord pour la dernière expression de clé de la liste, puis pour la précédente, et ainsi de suite jusqu'à la première expression de clé. Dans les lignes de sous-totaux, les valeurs des expressions de clé déjà regroupées sont définies sur `0` ou sur une chaîne vide. Gardez à l'esprit que la clause [HAVING](/fr/reference/statements/select/having) peut affecter les résultats des sous-totaux. **Exemple** Considérez la table t : ```text theme={null} ┌─year─┬─month─┬─day─┐ │ 2019 │ 1 │ 5 │ │ 2019 │ 1 │ 15 │ │ 2020 │ 1 │ 5 │ │ 2020 │ 1 │ 15 │ │ 2020 │ 10 │ 5 │ │ 2020 │ 10 │ 15 │ └──────┴───────┴─────┘ ``` ```sql title="Query" theme={null} SELECT year, month, day, count(*) FROM t GROUP BY ROLLUP(year, month, day); ``` Comme la section `GROUP BY` comporte trois expressions de clé, le résultat contient quatre tables avec des sous-totaux « cumulés » de droite à gauche : * `GROUP BY year, month, day` ; * `GROUP BY year, month` (et la colonne `day` est remplie de zéros) ; * `GROUP BY year` (dans ce cas, les colonnes `month` et `day` sont toutes deux remplies de zéros) ; * et les totaux (et les trois colonnes des expressions de clé valent zéro). ```text title="Response" theme={null} ┌─year─┬─month─┬─day─┬─count()─┐ │ 2020 │ 10 │ 15 │ 1 │ │ 2020 │ 1 │ 5 │ 1 │ │ 2019 │ 1 │ 5 │ 1 │ │ 2020 │ 1 │ 15 │ 1 │ │ 2019 │ 1 │ 15 │ 1 │ │ 2020 │ 10 │ 5 │ 1 │ └──────┴───────┴─────┴─────────┘ ┌─year─┬─month─┬─day─┬─count()─┐ │ 2019 │ 1 │ 0 │ 2 │ │ 2020 │ 1 │ 0 │ 2 │ │ 2020 │ 10 │ 0 │ 2 │ └──────┴───────┴─────┴─────────┘ ┌─year─┬─month─┬─day─┬─count()─┐ │ 2019 │ 0 │ 0 │ 2 │ │ 2020 │ 0 │ 0 │ 4 │ └──────┴───────┴─────┴─────────┘ ┌─year─┬─month─┬─day─┬─count()─┐ │ 0 │ 0 │ 0 │ 6 │ └──────┴───────┴─────┴─────────┘ ``` La même requête peut également être écrite à l’aide du mot-clé `WITH`. ```sql title="Query" theme={null} SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP; ``` **Voir aussi** * Le paramètre [group\_by\_use\_nulls](/fr/reference/settings/session-settings#group_by_use_nulls) pour assurer la compatibilité avec la norme SQL.
## Modificateur CUBE
Le modificateur `CUBE` est utilisé pour calculer des sous-totaux pour chaque combinaison des expressions de clé de la liste `GROUP BY`. Les lignes de sous-totaux sont ajoutées après la table de résultats. Dans les lignes de sous-totaux, les valeurs de toutes les expressions de clé « groupées » sont définies à `0` ou à une chaîne vide. Notez que la clause [HAVING](/fr/reference/statements/select/having) peut affecter les résultats des sous-totaux. **Exemple** Considérez la table t : ```text theme={null} ┌─year─┬─month─┬─day─┐ │ 2019 │ 1 │ 5 │ │ 2019 │ 1 │ 15 │ │ 2020 │ 1 │ 5 │ │ 2020 │ 1 │ 15 │ │ 2020 │ 10 │ 5 │ │ 2020 │ 10 │ 15 │ └──────┴───────┴─────┘ ``` ```sql title="Query" theme={null} SELECT year, month, day, count(*) FROM t GROUP BY CUBE(year, month, day); ``` Comme la clause `GROUP BY` comporte trois expressions de clé, le résultat contient huit tableaux avec des sous-totaux pour toutes les combinaisons d’expressions de clé : * `GROUP BY year, month, day` * `GROUP BY year, month` * `GROUP BY year, day` * `GROUP BY year` * `GROUP BY month, day` * `GROUP BY month` * `GROUP BY day` * ainsi que les totaux. Les colonnes exclues de `GROUP BY` sont remplacées par des zéros. ```text title="Response" theme={null} ┌─year─┬─month─┬─day─┬─count()─┐ │ 2020 │ 10 │ 15 │ 1 │ │ 2020 │ 1 │ 5 │ 1 │ │ 2019 │ 1 │ 5 │ 1 │ │ 2020 │ 1 │ 15 │ 1 │ │ 2019 │ 1 │ 15 │ 1 │ │ 2020 │ 10 │ 5 │ 1 │ └──────┴───────┴─────┴─────────┘ ┌─year─┬─month─┬─day─┬─count()─┐ │ 2019 │ 1 │ 0 │ 2 │ │ 2020 │ 1 │ 0 │ 2 │ │ 2020 │ 10 │ 0 │ 2 │ └──────┴───────┴─────┴─────────┘ ┌─year─┬─month─┬─day─┬─count()─┐ │ 2020 │ 0 │ 5 │ 2 │ │ 2019 │ 0 │ 5 │ 1 │ │ 2020 │ 0 │ 15 │ 2 │ │ 2019 │ 0 │ 15 │ 1 │ └──────┴───────┴─────┴─────────┘ ┌─year─┬─month─┬─day─┬─count()─┐ │ 2019 │ 0 │ 0 │ 2 │ │ 2020 │ 0 │ 0 │ 4 │ └──────┴───────┴─────┴─────────┘ ┌─year─┬─month─┬─day─┬─count()─┐ │ 0 │ 1 │ 5 │ 2 │ │ 0 │ 10 │ 15 │ 1 │ │ 0 │ 10 │ 5 │ 1 │ │ 0 │ 1 │ 15 │ 2 │ └──────┴───────┴─────┴─────────┘ ┌─year─┬─month─┬─day─┬─count()─┐ │ 0 │ 1 │ 0 │ 4 │ │ 0 │ 10 │ 0 │ 2 │ └──────┴───────┴─────┴─────────┘ ┌─year─┬─month─┬─day─┬─count()─┐ │ 0 │ 0 │ 5 │ 3 │ │ 0 │ 0 │ 15 │ 3 │ └──────┴───────┴─────┴─────────┘ ┌─year─┬─month─┬─day─┬─count()─┐ │ 0 │ 0 │ 0 │ 6 │ └──────┴───────┴─────┴─────────┘ ``` La même requête peut aussi s’écrire avec le mot-clé `WITH`. ```sql title="Query" theme={null} SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH CUBE; ``` **Voir aussi** * Le paramètre [group\_by\_use\_nulls](/fr/reference/settings/session-settings#group_by_use_nulls) pour assurer la compatibilité avec la norme SQL.
## Modificateur WITH TOTALS
Si le modificateur `WITH TOTALS` est spécifié, une ligne supplémentaire est calculée. Cette ligne contient des colonnes clés avec des valeurs par défaut (des zéros ou des chaînes vides), ainsi que des colonnes de fonctions d’agrégation dont les valeurs sont calculées sur l’ensemble des lignes (les valeurs « totales »). Cette ligne supplémentaire n’est produite que dans les formats `JSON*`, `TabSeparated*` et `Pretty*`, séparément des autres lignes : * Dans les formats `XML` et `JSON*`, cette ligne est renvoyée dans un champ `totals` distinct. * Dans les formats `TabSeparated*`, `CSV*` et `Vertical`, la ligne apparaît après le résultat principal, précédée d’une ligne vide (après les autres données). * Dans les formats `Pretty*`, la ligne est affichée sous forme d’un tableau distinct après le résultat principal. * Dans le format `Template`, la ligne est affichée selon le modèle spécifié. * Dans les autres formats, elle n’est pas disponible. totals est renvoyé dans les résultats des requêtes `SELECT`, mais pas dans `INSERT INTO ... SELECT`. `WITH TOTALS` peut se comporter de différentes manières lorsque [HAVING](/fr/reference/statements/select/having) est présent. Le comportement dépend du paramètre `totals_mode`.
### Configuration du traitement des totaux
Par défaut, `totals_mode = 'before_having'`. Dans ce cas, 'totals' est calculé sur l’ensemble des lignes, y compris celles qui ne satisfont pas la clause HAVING ni `max_rows_to_group_by`. Les autres options n’incluent dans 'totals' que les lignes qui satisfont la clause HAVING, et se comportent différemment avec le paramètre `max_rows_to_group_by` et `group_by_overflow_mode = 'any'`. `after_having_exclusive` – N’inclut pas les lignes qui n’ont pas satisfait `max_rows_to_group_by`. Autrement dit, 'totals' contiendra un nombre de lignes inférieur ou égal à celui obtenu si `max_rows_to_group_by` était omis. `after_having_inclusive` – Inclut dans 'totals' toutes les lignes qui n’ont pas satisfait `max_rows_to_group_by`. Autrement dit, 'totals' contiendra un nombre de lignes supérieur ou égal à celui obtenu si `max_rows_to_group_by` était omis. `after_having_auto` – Compte le nombre de lignes qui satisfont la clause HAVING. S’il dépasse un certain seuil (50 % par défaut), inclut dans 'totals' toutes les lignes qui n’ont pas satisfait `max_rows_to_group_by`. Sinon, ne les inclut pas. `totals_auto_threshold` – Par défaut, 0.5. Le coefficient pour `after_having_auto`. Si `max_rows_to_group_by` et `group_by_overflow_mode = 'any'` ne sont pas utilisés, toutes les variantes de `after_having` sont identiques, et vous pouvez utiliser n’importe laquelle d’entre elles (par exemple, `after_having_auto`). Vous pouvez utiliser `WITH TOTALS` dans les sous-requêtes, y compris les sous-requêtes de la clause [JOIN](/fr/reference/statements/select/join) (dans ce cas, les valeurs totales correspondantes sont combinées).
## GROUP BY ALL
`GROUP BY ALL` revient à lister toutes les expressions du SELECT qui ne sont pas des fonctions d’agrégation. Par exemple : ```sql theme={null} SELECT a * 2, b, count(c), FROM t GROUP BY ALL ``` est identique à ```sql theme={null} SELECT a * 2, b, count(c), FROM t GROUP BY a * 2, b ``` Dans le cas particulier où une fonction prend à la fois des fonctions d’agrégation et d’autres champs comme arguments, les clés `GROUP BY` contiendront le plus grand nombre possible de champs non agrégés que nous pouvons en extraire. Par exemple : ```sql theme={null} SELECT substring(a, 4, 2), substring(substring(a, 1, 2), 1, count(b)) FROM t GROUP BY ALL ``` est identique à ```sql theme={null} SELECT substring(a, 4, 2), substring(substring(a, 1, 2), 1, count(b)) FROM t GROUP BY substring(a, 4, 2), substring(a, 1, 2) ```
## Exemples
Exemple : ```sql theme={null} SELECT count(), median(FetchTiming > 60 ? 60 : FetchTiming), count() - sum(Refresh) FROM hits ``` Contrairement à MySQL (et conformément au SQL standard), vous ne pouvez pas récupérer la valeur d'une colonne qui ne figure pas dans une clé ni dans une fonction d'agrégation (à l'exception des expressions constantes). Pour contourner cette limitation, vous pouvez utiliser la fonction d'agrégation 'any' (qui renvoie la première valeur rencontrée) ou 'min/max'. Exemple : ```sql theme={null} SELECT domainWithoutWWW(URL) AS domain, count(), any(Title) AS title -- getting the first occurred page header for each domain. FROM hits GROUP BY domain ``` Pour chaque valeur de clé distincte rencontrée, `GROUP BY` calcule un ensemble de valeurs de fonctions d’agrégation.
## Modificateur `GROUPING SETS`
Il s’agit du modificateur le plus générique. Ce modificateur permet de spécifier manuellement plusieurs ensembles de clés d’agrégation (grouping sets). L’agrégation est effectuée séparément pour chaque ensemble de regroupement, puis tous les résultats sont combinés. Si une colonne n’est pas incluse dans un ensemble de regroupement, elle est remplacée par une valeur par défaut. En d’autres termes, les modificateurs décrits ci-dessus peuvent être représentés au moyen de `GROUPING SETS`. Bien que les requêtes utilisant les modificateurs `ROLLUP`, `CUBE` et `GROUPING SETS` soient syntaxiquement équivalentes, elles peuvent s’exécuter différemment. Alors que `GROUPING SETS` tente de tout exécuter en parallèle, `ROLLUP` et `CUBE` effectuent la fusion finale des agrégats dans un seul thread. Lorsque les colonnes source contiennent des valeurs par défaut, il peut être difficile de distinguer si une ligne fait partie de l’agrégation qui utilise ces colonnes comme clés ou non. Pour résoudre ce problème, il faut utiliser la fonction `GROUPING`. **Exemple** Les deux requêtes suivantes sont équivalentes. ```sql theme={null} -- Query 1 SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP; -- Query 2 SELECT year, month, day, count(*) FROM t GROUP BY GROUPING SETS ( (year, month, day), (year, month), (year), () ); ``` **Voir aussi** * Le paramètre [group\_by\_use\_nulls](/fr/reference/settings/session-settings#group_by_use_nulls) pour assurer la compatibilité avec la norme SQL.
## Détails d’implémentation
L’agrégation est l’une des fonctionnalités les plus importantes d’un SGBD orienté colonnes ; son implémentation est donc l’une des parties les plus optimisées de ClickHouse. Par défaut, l’agrégation s’effectue en mémoire à l’aide d’une table de hachage. Cette implémentation comporte plus de 40 spécialisations, choisies automatiquement en fonction des types de données de la « clé de regroupement ».
### Optimisation de GROUP BY en fonction de la clé de tri de la table
L’agrégation peut être effectuée plus efficacement si une table est triée selon une certaine clé et que l’expression `GROUP BY` contient au moins le préfixe de la clé de tri ou des fonctions injectives. Dans ce cas, lorsqu’une nouvelle clé est lue dans la table, le résultat intermédiaire de l’agrégation peut être finalisé et envoyé au client. Ce comportement est activé par le paramètre [optimize\_aggregation\_in\_order](/fr/reference/settings/session-settings#optimize_aggregation_in_order). Cette optimisation réduit l’utilisation de la mémoire pendant l’agrégation, mais peut, dans certains cas, ralentir l’exécution de la requête.
### GROUP BY en mémoire externe
Vous pouvez activer l’écriture des données temporaires sur le disque afin de limiter l’utilisation de la mémoire pendant `GROUP BY`. Le paramètre [max\_bytes\_before\_external\_group\_by](/fr/reference/settings/session-settings#max_bytes_before_external_group_by) détermine le seuil de consommation de RAM à partir duquel les données temporaires de `GROUP BY` sont écrites dans le système de fichiers. S’il est défini sur 0 (valeur par défaut), il est désactivé. Vous pouvez également définir [max\_bytes\_ratio\_before\_external\_group\_by](/fr/reference/settings/session-settings#max_bytes_ratio_before_external_group_by), ce qui permet d’utiliser `GROUP BY` en mémoire externe uniquement lorsque la requête atteint un certain seuil de mémoire utilisée. Lorsque vous utilisez `max_bytes_before_external_group_by`, nous recommandons de définir `max_memory_usage` à une valeur environ deux fois plus élevée (ou `max_bytes_ratio_before_external_group_by=0.5`). Cela est nécessaire, car l’agrégation comporte deux étapes : la lecture des données et la formation des données intermédiaires (1), puis la fusion des données intermédiaires (2). L’écriture des données dans le système de fichiers ne peut se produire que durant l’étape 1. Si les données temporaires n’ont pas été écrites sur disque, l’étape 2 peut alors nécessiter jusqu’à la même quantité de mémoire que l’étape 1. Par exemple, si [max\_memory\_usage](/fr/reference/settings/session-settings#max_memory_usage) est défini sur 10000000000 et que vous souhaitez utiliser l’agrégation externe, il est logique de définir `max_bytes_before_external_group_by` sur 10000000000 et `max_memory_usage` sur 20000000000. Lorsque l’agrégation externe est déclenchée (s’il y a eu au moins une écriture de données temporaires sur disque), la consommation maximale de RAM n’est que légèrement supérieure à `max_bytes_before_external_group_by`. Avec le traitement distribué des requêtes, l’agrégation externe est effectuée sur des serveurs distants. Pour que le serveur demandeur n’utilise qu’une faible quantité de RAM, définissez `distributed_aggregation_memory_efficient` sur 1. La fusion des données écrites sur le disque, ainsi que la fusion des résultats provenant de serveurs distants lorsque le paramètre `distributed_aggregation_memory_efficient` est activé, consomme jusqu’à `1/256 * the_number_of_threads` de la quantité totale de RAM. Lorsque l’agrégation externe est activée, s’il y avait moins de `max_bytes_before_external_group_by` de données (c.-à-d. que les données n’ont pas été écrites sur disque), la requête s’exécute aussi vite que sans agrégation externe. Si des données temporaires ont été écrites sur disque, le temps d’exécution sera plusieurs fois plus long (environ trois fois). Si vous avez un [ORDER BY](/fr/reference/statements/select/order-by) avec un [LIMIT](/fr/reference/statements/select/limit) après `GROUP BY`, alors la quantité de RAM utilisée dépend de la quantité de données dans `LIMIT`, et non dans la table entière. Mais si `ORDER BY` n’a pas de `LIMIT`, n’oubliez pas d’activer le tri externe (`max_bytes_before_external_sort`).