GROUP BY

COMMENTSInstructions de procédure SQL (PSQL)

`GROUP BY`

Une clause GROUP BY joint les enregistrements qui ont la même combinaison de valeurs de champs spécifiés dans sa liste en un seul enregistrement.Les fonctions d’agrégation dans la liste de sélection s’appliquent à chaque groupe individuellement, et non à l’ensemble.

Si la liste de sélection ne contient que des colonnes agrégées ou des colonnes dont les valeurs ne dépendent pas des lignes individuelles du jeu principal, la clause GROUP BY est facultative.Lorsque la clause GROUP BY est omise, le jeu de résultats se compose d’une seule ligne (à condition qu’au moins une colonne d’agrégation soit présente).

Si la liste de sélection contient à la fois des colonnes agrégées et des colonnes dont les valeurs dépendent des lignes sélectionnées, la clause GROUP BY devient obligatoire.

Syntaxe

SELECT ...
FROM ...
GROUP BY <grouping-item> [, <grouping-item> ...]
[HAVING <grouped-row-condition>] ...

<grouping-item> ::= <non-aggr-select-item> | <non-aggr-expression>

<non-aggr-select-item> ::=
    column-copy
  | column-alias
  | column-position

Table 1. Paramètres de la clause GROUP BY
Paramètre	Description
non-aggr-expression	Toute expression non agrégée qui n’est pas incluse dans la liste d’échantillons, c’est-à-dire les colonnes non sélectionnées de l’ensemble source ou les expressions qui ne dépendent pas du tout de l’ensemble de données.
column-copy	Copie mot à mot d’une expression d’une liste de sélection qui ne contient pas de fonction d’agrégation.
column-alias	Alias d’une expression (colonne) d’une liste de sélection qui ne contient pas de fonction d’agrégation.
column-position	Le numéro de position d’une expression (colonne) d’une liste de sélection qui ne contient pas de fonction d’agrégation.

La règle générale est que chaque colonne non agrégée dans une liste SELECT doit également être incluse dans une liste GROUP BY.Vous pouvez le faire de trois façons :

Copiez l’expression textuelle de la liste de sélection, par exemple “class” ou “'D:' || upper(doccode)” ;
Spécifiez un alias s’il en existe un ;
Spécifiez la position de la colonne sous forme d’un nombre entier compris entre 1 et le nombre de colonnes de la liste SELECT. Les valeurs entières dérivées d’expressions, de paramètres ou simplement d’invariants seront utilisées comme telles dans le regroupement. Ils n’auront aucun effet car leur valeur est la même pour chaque ligne.

Important

Si vous regroupez par position de colonne ou par alias, l’expression correspondant à cette position (alias) sera copiée de la liste de sélection SELECT. Ceci s’applique aussi aux sous-requêtes, donc la sous-requête sera exécutée au moins deux fois.

En plus des éléments requis, la liste de regroupement peut également contenir :

Les colonnes de la table source qui ne sont pas incluses dans la liste de sélection SELECT, ou les expressions non agrégées basées sur de telles colonnes. L’ajout de telles colonnes peut encore fragmenter les groupes. Mais comme ces colonnes ne figurent pas dans la liste de sélection SELECT, vous ne pouvez pas savoir quelle valeur de colonne correspond à quelle valeur de ligne agrégée. Donc, si vous êtes intéressé par cette information, vous devez également inclure cette colonne ou expression dans la liste de sélection SELECT, ce qui vous ramène à la règle "chaque colonne non agrégée dans la liste de sélection SELECT doit être incluse dans la liste de regroupement GROUP BY ;
Expressions qui ne dépendent pas des données de l’ensemble principal, c’est-à-dire les constantes, les variables contextuelles, les sous-requêtes non corrélées qui renvoient une seule valeur, etc. Ceci n’est mentionné que pour compléter l’image, car l’ajout de ces éléments est complètement inutile, puisqu’ils n’affecteront pas du tout le regroupement. Les éléments "anodins mais inutiles" peuvent également apparaître dans la liste de sélection SELECT sans être copiés dans la liste de regroupement GROUP BY.

Exemples

Lorsque la liste de sélection SELECT ne contient que des colonnes agrégées, la clause GROUP BY est facultative :

SELECT COUNT(*), AVG(age)
FROM students
WHERE sex = 'M'

Cette requête renvoie une seule ligne avec le nombre d’étudiants masculins et leur âge moyen. L’ajout d’une expression indépendante des lignes de la table STUDENTS ne change rien :

SELECT COUNT(*), AVG(age), current_date
FROM students
WHERE sex = 'M'

La ligne de résultat comportera désormais une colonne supplémentaire indiquant la date du jour, mais à part cela, rien de fondamental n’a changé. Le regroupement n’est toujours pas nécessaire.

Cependant, dans les deux exemples ci-dessus, cela est autorisé, ce qui est parfaitement valable pour la requête également :

SELECT COUNT(*), AVG(age)
FROM students
WHERE sex = 'M'
GROUP BY class

et retournera le résultat pour chaque classe qui a des garçons, en listant le nombre de garçons et leur âge moyen dans cette classe particulière. Si vous laissez également le champ CURRENT_DATE, cette valeur sera répétée sur chaque ligne, ce qui n’est pas intéressant.

Cette requête a un inconvénient important, bien qu’elle vous donne des informations sur les différentes classes, elle ne vous dit pas quelle ligne appartient à quelle classe. Pour obtenir cette information supplémentaire, la colonne non agrégée CLASS doit être ajoutée à la liste de sélection SELECT :

SELECT class, COUNT(*), AVG(age)
FROM students
WHERE sex = 'M'
GROUP BY class

Nous avons maintenant une requête utile. Notez que l’ajout de la colonne CLASS rend la clause GROUP BY obligatoire. Nous ne pouvons pas enlever cette clause, ni enlever la colonne CLASS de la liste des colonnes.

Le résultat de la dernière requête ressemblera à ceci :

CLASS	COUNT	AVG
2A	12	13.5
2B	9	13.9
3A	11	14.6
3B	12	14.4
…	…	…

Les rubriques “COUNT” et “AVG” ne sont pas très informatives. Dans le cas le plus simple, vous pouvez contourner ce problème, mais il est préférable de leur donner des noms significatifs en utilisant des alias :

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M'
GROUP BY class

Comme vous vous souvenez de la syntaxe formelle de la liste des colonnes, le mot clé AS est facultatif.

L’ajout d’autres colonnes non agrégées (ou plus précisément dépendantes des chaînes de caractères) nécessite de les ajouter également aux clauses GROUP BY. Par exemple, vous voulez que les informations ci-dessus concernant les filles soient les mêmes, et vous voulez voir la différence entre les étudiants internes et les étudiants à plein temps :

SELECT
    class,
    sex,
    boarding_type,
    COUNT(*) AS anumber,
    AVG(age) AS avg_age
FROM students
GROUP BY class, sex, boarding_type

CLASS	SEX	BOARDING_TYPE	ANUMBER	AVG_AGE
2A	F	BOARDING	9	13.3
2A	F	DAY	6	13.5
2A	M	BOARDING	7	13.6
2A	M	DAY	5	13.4
2B	F	BOARDING	11	13.7
2B	F	DAY	5	13.7
2B	M	BOARDING	6	13.8
…	…	…	…	…

CLASS

SEX

BOARDING_TYPE

ANUMBER

AVG_AGE

BOARDING

13.3

DAY

13.5

BOARDING

13.6

DAY

13.4

BOARDING

13.7

DAY

13.7

BOARDING

13.8

…

Chaque ligne du jeu résultant correspond à une combinaison particulière des variables CLASS, SEX et BOARDING_TYPE. Les résultats agrégés — nombre et âge moyen — sont donnés pour chacun des groupes spécifiés séparément. Vous ne pouvez pas voir les résultats agrégés pour les garçons séparément ou pour les étudiants à temps plein séparément comme résultat de la requête.Il faut donc trouver un compromis. Plus vous ajoutez de colonnes non agrégées, plus vous instanciez les groupes, et plus vous perdez de vue la vue d’ensemble. Bien sûr, vous pouvez toujours obtenir des agrégats "plus grands", en utilisant des requêtes séparées.

HAVING

Comme la clause WHERE, la clause HAVING restreint les lignes du jeu de données à celles qui satisfont la condition de recherche, à la différence que la clause HAVING impose des restrictions sur les lignes agrégées du jeu groupé.La clause HAVING est facultative et ne peut être utilisée qu’en conjonction avec la clause GROUP BY.

La (les) condition(s) dans la clause 'HAVING' peuvent se référer à :

Toute colonne agrégée dans la liste de sélection SELECT. C’est le cas le plus couramment utilisé ;
Toute expression agrégée qui ne figure pas dans la liste de sélection SELECT, mais qui est autorisée dans le contexte de la requête. C’est parfois utile ;
Toute colonne de la liste GROUP BY. Cependant, il est plus efficace de filtrer les données non agrégées plus tôt dans la clause WHERE ;
Toute expression dont la valeur est indépendante du contenu de l’ensemble de données (telle qu’une constante ou une variable contextuelle). Cela est acceptable, mais n’a aucun sens, car une telle clause, qui n’a rien à voir avec l’ensemble de données lui-même, va soit supprimer l’ensemble entier, soit le laisser intact.

Une clause HAVING ne peut pas contenir :

Expressions de colonnes non agrégées qui ne sont pas dans la liste GROUP BY ;
Position de la colonne. Un nombre entier dans une clause HAVING est simplement un nombre entier ;
Les alias de colonnes — même s’ils apparaissent dans une clause GROUP BY.

Exemples

Reconstruire nos premiers exemples. Nous pouvons utiliser la clause "HAVING" pour exclure de petits groupes d’élèves :

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M'
GROUP BY class
HAVING COUNT(*) >= 5

Ne choisissez que les groupes dont l’écart d’âge minimum est de 1,2 an :

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M'
GROUP BY class
HAVING MAX(age) - MIN(age) > 1.2

Notez que si vous êtes vraiment intéressé par cette information, c’est une bonne idée d’inclure min(age) et max(age) ou l’expression max(age) - min(age) dans la liste de sélection.

La requête suivante ne sélectionne que les élèves de 3ème année :

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M'
GROUP BY class
HAVING class STARTING WITH '3'

Cependant, il est bien mieux de déplacer cette condition dans la clause WHERE:

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M' AND class STARTING WITH '3'
GROUP BY class