GROUP BY fait passer la requête SELECT en mode agrégation, qui fonctionne comme suit :
- La clause
GROUP BYcontient 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, HAVING et ORDER BY doivent être calculées à partir d’expressions de clé ou de fonctions d’agrégation 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
SELECTcontiendra 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.
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
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 :
SELECT sum(x), y FROM t_null_big GROUP BY y renvoie :
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
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 peut affecter les résultats des sous-totaux.
Query
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 colonnedayest remplie de zéros) ;GROUP BY year(dans ce cas, les colonnesmonthetdaysont toutes deux remplies de zéros) ;- et les totaux (et les trois colonnes des expressions de clé valent zéro).
Response
WITH.
Query
- Le paramètre group_by_use_nulls pour assurer la compatibilité avec la norme SQL.
Modificateur CUBE
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 peut affecter les résultats des sous-totaux.
Query
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, dayGROUP BY year, monthGROUP BY year, dayGROUP BY yearGROUP BY month, dayGROUP BY monthGROUP BY day- ainsi que les totaux.
GROUP BY sont remplacées par des zéros.
Response
WITH.
Query
- Le paramètre group_by_use_nulls pour assurer la compatibilité avec la norme SQL.
Modificateur WITH TOTALS
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
XMLetJSON*, cette ligne est renvoyée dans un champtotalsdistinct. - Dans les formats
TabSeparated*,CSV*etVertical, 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 est présent. Le comportement dépend du paramètre totals_mode.
Configuration du traitement des totaux
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 (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 :
GROUP BY contiendront le plus grand nombre possible de champs non agrégés que nous pouvons en extraire.
Par exemple :
Exemples
GROUP BY calcule un ensemble de valeurs de fonctions d’agrégation.
Modificateur GROUPING SETS
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.
- Le paramètre group_by_use_nulls pour assurer la compatibilité avec la norme SQL.
Détails d’implémentation
Optimisation de GROUP BY en fonction de la clé de tri de la table
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. 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
GROUP BY.
Le paramètre 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, 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 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 avec un 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).