Comme la plupart des autres produits de bases de données relationnelles,
PostgreSQL supporte les fonctions
d'agrégat. Une fonction d'agrégat calcule un seul résultat à
partir de plusieurs lignes en entrée. Par exemple, il y a des agrégats pour
calculer le nombre (count
), la somme
(sum
), la moyenne (avg
), le
maximum (max
) et le minimum (min
)
d'un ensemble de lignes.
Comme exemple, nous pouvons trouver la température la plus haute parmi les températures basses avec :
SELECT max(t_basse) FROM temps;
max ----- 46 (1 row)
Si nous voulons connaître dans quelle ville (ou villes) ces lectures se sont produites, nous pouvons essayer :
SELECT ville FROM temps WHERE t_basse = max(t_basse);
FAUX
mais cela ne marchera pas puisque l'agrégat max
ne
peut pas être utilisé dans une clause WHERE
(cette
restriction existe parce que la clause WHERE
détermine
les lignes qui seront traitées par l'agrégat ; donc les lignes
doivent être évaluées avant que les fonctions d'agrégat ne calculent
leur résultat). Cependant, comme cela est souvent le cas, la requête peut
être répétée pour arriver au résultat attendu, ici en utilisant une
sous-requête :
SELECT ville FROM temps WHERE t_basse = (SELECT max(t_basse) FROM temps);
ville --------------- San Francisco (1 row)
Ceci est correct, car la sous-requête est un calcul indépendant qui traite son propre agrégat séparément à partir de ce qui se passe dans la requête externe.
Les agrégats sont également très utiles s'ils sont combinés avec les clauses
GROUP BY
. Par exemple, nous pouvons obtenir la
température la plus haute parmi les températures basses observées dans
chaque ville avec :
SELECT ville, max(t_basse) FROM temps GROUP BY ville;
ville | max ---------------+----- Hayward | 37 San Francisco | 46 (2 rows)
ce qui nous donne une ligne par ville dans le résultat. Chaque résultat
d'agrégat est calculé avec les lignes de la table correspondant à la ville.
Nous pouvons filtrer ces lignes groupées en utilisant
HAVING
et le décompte en sortie en utilisant
FILTER
:
SELECT ville, max(t_basse), count(*) FILTER (WHERE t_basse < 30) FROM temps GROUP BY ville HAVING max(t_basse) < 40;
ville | max | count ---------+-----+------- Hayward | 37 | 5
ce qui nous donne le même résultat uniquement pour les villes qui ont
toutes leurs valeurs de t_basse
en dessous de
40. Pour finir, si nous nous préoccupons seulement des villes dont le nom
commence par « S
», nous pouvons faire :
SELECT ville, max(t_basse), count(*) FILTER (WHERE t_basse < 30) FROM temps WHERE ville LIKE 'S%' -- (1) GROUP BY ville HAVING max(t_basse) < 40;
L'opérateur |
Il est important de comprendre l'interaction entre les agrégats et les
clauses SQL WHERE
et
HAVING
. La différence fondamentale entre
WHERE
et HAVING
est que
WHERE
sélectionne les lignes en entrée avant que les
groupes et les agrégats ne soient traités (donc, cette clause contrôle les
lignes qui se retrouvent dans le calcul de l'agrégat), tandis que
HAVING
sélectionne les lignes groupées après que les
groupes et les agrégats ont été traités. Donc, la clause
WHERE
ne doit pas contenir de fonctions d'agrégat ;
cela n'a aucun sens d'essayer d'utiliser un agrégat pour déterminer les
lignes en entrée des agrégats. D'un autre côté, la clause
HAVING
contient toujours des fonctions d'agrégat (pour
être précis, vous êtes autorisés à écrire une clause
HAVING
qui n'utilise pas d'agrégat, mais c'est rarement
utilisé. La même condition pourra être utilisée plus efficacement par un
WHERE
).
Dans l'exemple précédent, nous pouvons appliquer la restriction sur le nom
de la ville dans la clause WHERE
puisque cela ne
nécessite aucun agrégat. C'est plus efficace que d'ajouter la restriction
dans HAVING
parce que nous évitons le groupement et les
calculs d'agrégat pour toutes les lignes qui ont échoué lors du contrôle
fait par WHERE
.