Introduction à l'utilisation des fonctions agrégées SQL avec les JOINs

Précédemment, nous avons discuté de la méthode utilisation des fonctions d'agrégation SQL avec l'instruction GROUP BY. Les lecteurs assidus de notre blog se souviendront également de notre récent article sur l'utilisation de . tutoriel sur les JOINs. Si vous êtes un peu rouillé sur l'un ou l'autre sujet, je vous encourage à les revoir avant de poursuivre cet article. En effet, nous allons approfondir le sujet. fonctions d'agrégation en les associant à des JOIN. Ce duo libère toutes les possibilités des fonctions agrégées SQL et nous permet d'effectuer des calculs sur plusieurs tables dans une seule requête.

Que font les fonctions d'agrégation SQL ?

Voici une présentation rapide des fonctions agrégées SQL les plus courantes :

Il est également important de se rappeler que l'instruction GROUP BY, lorsqu'elle est utilisée avec des agrégats, calcule des valeurs qui ont été regroupées par colonne. (Pour plus d'informations, voir Guide des fonctions d'agrégation SQL pour les débutants.) Nous pouvons utiliser GROUP BY avec l'une des fonctions ci-dessus. Par exemple, nous utilisons la fonction MIN() dans l'exemple ci-dessous :

SELECT MIN(column_name)
FROM table_name 
GROUP BY group_column

Cela permettrait de récupérer la valeur minimale trouvée dans column_name pour chaque ensemble de valeurs dans un groupe basé sur la colonne group_column. La même idée s'applique aux fonctions MAX, SUM, AVG et COUNT.

JOINs parent-enfant

Examinons maintenant quelques situations courantes dans lesquelles vous utiliserez des JOIN de groupe avec des fonctions d'agrégation. Si vous avez lu A Beginner's Guide to SQL Aggregate Functions, le schéma suivant vous sera déjà familier :

Si vous avez déjà utilisé ce modèle auparavant (par exemple en faisant les exemples de l'article précédent), veillez à effacer tous les enregistrements existants de votre table. Vous pouvez le faire en exécutant les commandes suivantes :

TRUNCATE cities;
TRUNCATE users;

Entrons de nouvelles données dans les tables :

INSERT INTO `cities` VALUES
    (1,'Miami'),
    (2,'Orlando'),
    (3,'Las Vegas'),
    (4,'Coyote Springs');
INSERT INTO `users` VALUES
    (1,1,'John','Doe',22),
    (2,1,'Albert','Thomson',15),
    (3,2,'Robert','Ford',65),
    (4,3,'Samantha','Simpson',9),
    (5,2,'Carlos','Bennet',42),
    (6,2,'Mirtha','Lebrand',81),
    (7,3,'Alex','Gomez',31);

Nous avons donc une table appelée users et une autre table appelée cities. Ces deux tables ont quelque chose en commun : une valeur numérique d'identification de la ville. Cette valeur est stockée dans la colonne id de la table cities et dans la colonne city_id de la table users de la table . La colonne city_id contient une référence (aussi appelée clé étrangère) qui relie un enregistrement d'utilisateur à une ville. Ces enregistrements correspondants nous permettent d'associer les deux tables à JOIN.

En d'autres termes, nous connaissons la ville d'un utilisateur lorsque nous récupérons l'enregistrement de la table cities qui a une valeur id égale à la valeur dans users.city_id. Dans la requête suivante, nous pouvons voir cela en action :

SELECT  cities.*, users.*
FROM cities
JOIN users
  ON cities.id = users.city_id;

Puisque la table users est connectée à une ville via la clé étrangère city_id, nous pouvons dire qu'un utilisateur appartient à une ville et que la ville a donc de nombreux utilisateurs. Il s'agit d'une relation parent-enfant (villes-utilisateurs) ; la table users partage un lien avec la table cities table.

Avec cette relation en tête, passons à autre chose et voyons comment nous pouvons calculer des données résumées intéressantes qui relient les deux tables.

Agrégat + GROUP BY + JOIN

Commençons maintenant à aborder des situations pratiques où nous devrons GROUPdes valeurs provenant de JOINdes tables éditées.

MIN + GROUP BY + JOIN

Le calcul de valeurs basées sur des enregistrements enfants qui sont regroupés par une colonne parent est assez courant. Construisons une requête qui récupérera le plus petit users.age (enregistrement enfant) pour chaque cityname (enregistrement parent) :

SELECT cities.cityname, MIN(users.age)
FROM cities
JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Cette requête renverra :

Il y a quelque chose de très important à souligner sur la façon dont le JOIN fonctionne. Ce sera plus évident si nous examinons toutes les villes :

SELECT cities.cityname
FROM cities

Comme vous pouvez le voir, "Coyote Springs" n'était pas listée avant parce qu'elle n'a pas d'utilisateurs. Si vous vouliez que cette ville soit listée dans les résultats résumés, vous devriez utiliser la commande LEFT JOIN à la place :

SELECT cities.cityname, MIN(users.age)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Ceci retournera :

Que cela ait du sens ou non dépendra de votre cas d'utilisation, mais il est important que vous gardiez cette situation à l'esprit lorsque vous joignez des tables.

MAX + GROUP BY + JOINTURES

Nous pouvons trouver l'âge le plus élevé pour chaque ville en utilisant la fonction MAX():

SELECT cities.cityname, MAX(users.age)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

La requête ci-dessus permettra de récupérer :

Notez que j'ai utilisé LEFT JOIN. Je veux une liste de toutes les villes, pas seulement celles auxquelles sont associés des enregistrements d'utilisateurs.

SOMME + GROUP BY + JOIN

Voyons maintenant comment totaliser les âges pour chaque ville. Nous pouvons utiliser la fonction SUM() pour ce faire :

SELECT cities.cityname, SUM(users.age)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Qui renvoie :

COUNT + GROUP BY + JOIN

Supposons que nous voulions voir le nombre d'utilisateurs dans chaque ville. Nous utiliserions la fonction COUNT(), comme ceci :

SELECT cities.cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Qui renvoie :

MOYENNE + GROUP BY + JOIN

En utilisant le nombre d'utilisateurs dans chaque ville (COUNT) et le SUM des âges combinés des utilisateurs de chaque ville, nous pouvons calculer l'âge moyen de chaque ville. Il suffit de diviser la somme des âges par le nombre d'utilisateurs de chaque ville :

SELECT 
    cities.cityname,
    SUM(users.age) AS sum,
    COUNT(users.id) AS count,
    SUM(users.age) / COUNT(users.id) AS average
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Retourner :

Remarquez que la somme et la moyenne calculée donnent une valeur NULL pour Coyote Springs. Cela est dû au fait que Coyote Springs n'a pas d'utilisateurs et que la colonne résumée ne peut donc pas calculer de valeur numérique.

MOYENNE + GROUPE PAR + JOINTURES

L'exemple précédent utilisait un calcul que nous avons saisi pour trouver un âge moyen pour chaque ville. Nous aurions pu utiliser la fonction AVG() à la place, comme indiqué ci-dessous :

SELECT cities.cityname, AVG(users.age)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

On obtient ainsi les mêmes valeurs que dans l'exemple précédent :

Filtrage des résultats

Vous aurez parfois besoin de filtrer les lignes en fonction de certaines conditions. Dans ce type de requête, il y a trois étapes où vous pouvez le faire : WHERE, HAVING, et JOIN.

Selon la situation, chacune de ces options peut avoir un résultat différent. Il est important de comprendre laquelle utiliser lorsque vous souhaitez obtenir un résultat spécifique. Prenons quelques exemples pour illustrer ce point.

Utilisation du prédicat JOIN

Obtenons le nombre d'utilisateurs de moins de 30 ans dans chaque ville. Nous allons utiliser LEFT JOIN pour récupérer les villes sans aucun enregistrement d'utilisateur :

SELECT cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
  AND users.age < 30
GROUP BY cities.cityname
ORDER BY cities.cityname;

La condition permettant d'inclure uniquement les utilisateurs dont l'âge est inférieur à 30 ans est définie dans le prédicat JOIN. Le résultat est le suivant :

Toutes les villes sont listées, et seuls les utilisateurs dont l'âge est compris dans la fourchette renvoient un nombre non nul. Les villes dont aucun utilisateur ne correspond à nos critères renvoient un zéro.

Que se serait-il passé si nous avions placé la même condition de filtrage dans la clause WHERE?

Utilisation des conditions WHERE

Si vous placez les mêmes conditions dans la clause WHERE, vous obtiendrez le résultat suivant :

SELECT cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id 
WHERE users.age < 30
GROUP BY cities.cityname
ORDER BY cities.cityname;

Le résultat est le suivant :

Ce n'est pas ce que j'attendais ; je voulais obtenir TOUTES les villes et le nombre de leurs utilisateurs respectifs âgés de moins de 30 ans. Même si une ville n'avait aucun utilisateur, elle aurait dû être répertoriée avec un nombre nul, comme le renvoie l'exemple de prédicat JOIN.

La raison pour laquelle cela ne renvoie pas ces enregistrements est que WHERE sont appliquées après la condition JOIN. Puisque la condition users.age < 30 supprime tous les enregistrements "Coyote Springs" et "Orlando", le calcul résumé ne peut pas inclure ces valeurs. Seuls "Las Vegas" et "Miami" remplissent les conditions de WHERE, donc seuls "Las Vegas" et "Miami" sont renvoyés.

En revanche, lorsque la condition est appliquée dans le prédicat JOIN, les enregistrements d'utilisateurs dont l'âge ne correspond pas sont supprimés avant que les deux tables ne soient jointes. Ensuite, toutes les villes sont mises en correspondance par les colonnes des utilisateurs, comme on peut s'y attendre lorsqu'on utilise un prédicat . LEFT JOIN. Cela signifie que toutes les villes feront partie des résultats ; seuls les enregistrements d'utilisateurs qui ne remplissent pas la condition users.age < 30 sont filtrés. Dans ce cas, le prédicat JOIN renvoie le résultat souhaité.

Utilisation des conditions HAVING

Nous l'avons mentionné dans le premier article, mais nous allons le répéter ici : l'utilisation de la clause WHERE pour filtrer des colonnes résumées ne fonctionne pas. Regardez l'exemple ci-dessous.

SELECT cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
WHERE COUNT(users.id) > 2
GROUP BY cities.cityname
ORDER BY cities.cityname;

La base de données émet alors une plainte comme celle-ci de la part de MySQL :

Error Code: 1111. Invalid use of group function

Utilisez plutôt la clause HAVING:

SELECT cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname
HAVING COUNT(users.id) > 2
ORDER BY cities.cityname;

Cela renvoie les enregistrements voulus (uniquement les villes ayant plus de deux utilisateurs) :

Gérer les NULLs

Outre les cas limites déjà présentés, il est important de prendre en compte un élément qui n'est pas si évident. Revenons à l'exemple de COUNT():

SELECT cities.cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Il renvoie :

Si j'avais utilisé COUNT(*) au lieu de COUNT(users.id), le nombre total de lignes aurait été généré. Cela nous aurait donné une valeur non intentionnelle - dans ce cas, un faux "1" pour "Coyote Springs". Ce résultat est dû à la nature de LEFT JOIN, dont voici un exemple:

SELECT cities.cityname, COUNT(*)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Cela renverrait :

Donc COUNT(*) compte un "1" pour Coyote Springs parce que LEFT JOIN renvoie une ligne avec des valeurs NULL. Rappelez-vous que dans COUNT(*), une ligne avec des valeurs NULL compte toujours.

Pour la même raison, COUNT(users.id) renvoie le compte attendu de "0" ; la valeur de la colonne users.id est nulle pour Coyote Springs.

En d'autres termes, utilisez toujours Count(column) avec ce type de requête.

Un dernier conseil pour travailler avec les fonctions agrégées SQL

Enfin, j'aimerais ajouter que l'utilisation des fonctions d'agrégation SQL- en particulier lorsqu'on utilise JOIN- exige que vous compreniez le langage SQL et les données avec lesquelles vous travaillez. Essayez d'abord les requêtes dans un sous-ensemble plus petit de vos données pour confirmer que tous les calculs fonctionnent comme prévu. Si possible, comparez certaines sorties à une valeur de référence pour valider les résultats de vos requêtes.

N'oubliez pas que l'utilisation de conditions dans le prédicat JOIN (après ON) n'est pas la même chose que le filtrage dans WHERE (ou l'utilisation de HAVING). Cela peut créer des différences subtiles (ou pas si subtiles) dans vos données résumées, ce qui pourrait entraîner des erreurs difficiles à détecter. Portez une attention particulière à vos choix de filtrage.

Comme toujours, merci de votre lecture et n'hésitez pas à partager vos propres expériences dans la section des commentaires.