Guide du débutant sur les fonctions agrégées SQL

Les fonctions d'agrégation sont de puissants outils SQL qui effectuent des calculs numériques sur les données, permettant à la requête de retourner des informations résumées sur une colonne ou un ensemble de résultats donnés. Ces fonctions peuvent être utilisées conjointement avec l'instruction GROUP BY. Nous allons voir comment elles fonctionnent à l'aide de quelques exemples simples.

Fonctions agrégées SQL

Supposons que nous ayons des utilisateurs résidant dans une ville et que nous stockions leurs informations dans deux tables. Ces tables et leur relation sont présentées ci-dessous :

Introduisons quelques données dans ce modèle :

INSERT INTO `cities` VALUES (1,'Miami'),(2,'Orlando'),
(3,'Las Vegas'),(4,'Coyote Springs');
INSERT INTO `users` VALUES (1,1,'Jhon','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 maintenant assez de matériel pour expliquer l'utilisation de base des fonctions d'agrégation SQL. Commençons par une fonction simple.

MIN

Cette fonction renvoie la plus petite valeur d'une colonne donnée. Par exemple, obtenons l'âge minimum de notre groupe d'utilisateurs :

SELECT MIN(age) FROM users;

Cette fonction renvoie un "9".

Vous pouvez également utiliser cette fonction pour trouver des informations alphabétiques. Essayons-la avec la colonne "last_name" :

SELECT MIN(last_name) FROM users;

Cela renvoie "Bennet", qui est le premier dans l'ordre alphabétique.

Remarque : la collation utilisée pour trier vos données aura un impact sur les résultats de cette requête. Par exemple, dans la collation danoise, "A" est traité comme "A" - la dernière lettre de l'alphabet. La collation latine, bien sûr, traite "A" comme la première lettre de l'alphabet.

MAX

De manière similaire mais opposée à MIN, MAX renvoie la plus grande valeur d'une colonne. Obtenons l'âge maximum de notre liste d'utilisateurs :

SELECT MAX(age) FROM users;

Cela renverra un "81".

Essayons la même chose avec la colonne du nom de famille :

SELECT MAX(last_name) FROM users;

Cela renvoie "Thomson", qui est le dernier dans l'ordre alphabétique. N'oubliez pas que cela peut changer en fonction de la collation que vous utilisez.

SOMME

Cette fonction calcule la somme de toutes les valeurs numériques d'une colonne. Utilisons-la pour récupérer la somme de tous les âges dans le tableau :

SELECT SUM(age) FROM users;

Elle retournera "265".

AVG

Cette fonction est utilisée pour calculer la valeur moyenne d'une colonne. Voyons-le en action, en récupérant l'âge moyen de nos utilisateurs :

SELECT AVG(age) FROM users;

Il renvoie un "27.75".

COUNT (colonne)

Cette fonction renvoie le nombre de valeurs non nulles dans une colonne donnée. Si nous voulions savoir combien d'utilisateurs nous ont indiqué leur âge, nous écririons :

SELECT COUNT(age) FROM users;

Il retournera un "7". Tous les enregistrements de la table "users" ont une valeur d'âge. Si un enregistrement n'avait pas de valeur d'âge, il aurait été considéré comme NULL (et non inclus dans le résultat COUNT). Si vous souhaitez compter le nombre réel de lignes de la table indépendamment de la valeur de la colonne, la fonction COUNT(*) est ce qu'il vous faut. Au lieu de spécifier un nom de colonne comme argument de fonction, nous utilisons un astérisque :

SELECT COUNT(*) FROM users;

Dans notre cas, cela renvoie toujours un "7" car la table compte sept enregistrements.

Les valeurs NULL peuvent être déroutantes, mais ne vous inquiétez pas. Nous vous montrerons plus loin dans cet article comment traiter les valeurs NULL dans les fonctions d'agrégation SQL.

Maintenant que vous savez ce que font ces fonctions d'agrégation, voyons comment les rendre encore plus utiles.

Vous voulez en savoir plus sur Les jointures en SQL? Regardez un épisode de notre série Nous apprenons le SQL sur Youtube. Vérifiez si vous savez déjà tout sur les différents types de JOINs.

Utilisation de GROUP BY avec des fonctions d'agrégation

L'instruction GROUP BY nous permet d'effectuer des agrégations sur un groupe de valeurs basées sur des colonnes données. Vous utiliserez fréquemment GROUP BY avec des fonctions d'agrégation, aussi ces exemples seront-ils un peu plus compliqués et réalistes que les exemples simples que nous avons utilisés précédemment.

Conceptuellement, "GROUP BY (column_x)"signifie "mettre tous les enregistrements qui partagent la même valeur dans "column_x" dans un groupe". Voyons comment cela fonctionne avec chacune des fonctions dont nous avons déjà parlé.

MIN + GROUP BY

Supposons que nous voulions connaître l'âge de notre plus jeune utilisateur dans chaque ville. Nous pouvons voir que la table "users" possède une colonne appelée "city_id" qui identifie la ville où vit chaque utilisateur. Nous pouvons utiliser cette colonne avec une instruction GROUP BY pour connaître l'âge le plus jeune dans chaque ville :

SELECT 
    city_id, MIN(age)
FROM
    users
GROUP BY city_id;

Pour mieux comprendre ce qui se passe, regardez les données brutes dans la table "users" :

L'utilisation de MIN() avec GROUP BY regroupera les enregistrements en fonction des valeurs de la colonne "city_id" avant de calculer l'agrégat pour chaque groupe. Si vous pouviez voir les valeurs groupées, cela ressemblerait à quelque chose comme ceci :

Ensuite, la requête retiendra les âges les plus bas dans chaque groupe. Si nous pouvions voir cette étape en action, cela ressemblerait à ceci :

Les valeurs surlignées représentent les valeurs calculées par MIN() pour chaque groupe.

Enfin, la requête affichera les résultats suivants :

MAX + GROUP BY

Comme vous l'avez peut-être déjà deviné, l'utilisation de MAX combiné à GROUP BY fonctionne de la même manière que MIN. Elle renvoie simplement la valeur la plus élevée pour chaque groupe. Nous pourrions calculer l'âge maximum pour chaque ville de manière similaire :

SELECT 
    city_id, MAX(age)
FROM
    users
GROUP BY city_id;

Cette requête va regrouper les utilisateurs en fonction de leur champ "city_id", puis récupérer la valeur d'âge maximum pour chaque groupe. Elle nous donne les résultats suivants :

SUM + GROUP BY

Nous pouvons également calculer la somme des âges des utilisateurs dans chaque ville. Pour cela, nous pouvons exécuter la requête suivante...

SELECT 
    city_id, SUM(age)
FROM
    users
GROUP BY city_id;

... qui nous donnera les résultats suivants :

COUNT + GROUP BY

Nous pouvons également vouloir calculer le nombre d'utilisateurs dans chaque ville. Il est facile de le faire en utilisant la fonction COUNT:

SELECT 
    city_id, COUNT(age)
FROM
    users
GROUP BY city_id;

Sur la base des deux derniers exemples, nous pourrions calculer l'âge moyen dans chaque ville en divisant la somme de tous les âges dans un groupe de villes par le nombre d'utilisateurs pour cette ville. Voici comment nous procédons :

SELECT 
    city_id,
    SUM(age),
    COUNT(age),
    SUM(age) / COUNT(age) as average
FROM
    users
GROUP BY city_id;

Note : Nous aurions également pu utiliser COUNT(*) ici, puisqu'il n'y a pas d'enregistrements avec des valeurs NULL dans la colonne "age". Dans ce cas, COUNT(age) fonctionne de la même manière que COUNT(*). Sinon, la valeur serait différente, comme nous l'expliquerons plus tard dans la section "Dealing with NULLs".

AVG + GROUP BY

Dans l'exemple précédent, nous avons calculé "manuellement" l'âge moyen pour chaque ville. Nous pouvons utiliser la fonction AVG() pour effectuer cette opération à notre place, comme indiqué ci-dessous :

SELECT 
    city_id,
    AVG(age)
FROM
    users
GROUP BY city_id;

La valeur renvoyée par AVG(age) est identique au résultat de l'opération mathématique que nous avons effectuée précédemment.

Filtrage des résultats groupés

Il arrive que vous ayez besoin de filtrer davantage les résultats en fonction des conditions générées par les résultats regroupés. L'ajout de toute condition dans l'adresse WHERE échouerait. Vous ne me croyez pas ? Voyez ce qui se passe lorsque nous essayons de récupérer le nombre d'utilisateurs uniquement dans les villes où l'âge moyen des utilisateurs est supérieur à 20 ans:

SELECT 
    city_id, COUNT(age), AVG(age)
FROM
    users
WHERE AVG(age) >= 20
GROUP BY city_id;

Le moteur (dans mon cas, MySQL) se plaindra. Il dira quelque chose comme ceci :

Code d'erreur : 1111. Utilisation invalide de la fonction de groupe

Pour filtrer les résultats de cette manière, nous devons utiliser la clause HAVING. HAVING filtrera les résultats résumés de GROUP BY; la clause WHERE s'applique uniquement aux enregistrements individuels. Si un groupe ne répond pas aux critères de la clause HAVING, il ne sera pas renvoyé.

Ainsi, si nous voulions obtenir le site COUNT pour chaque ville dont l'âge moyen est d'au moins 20 ans, la façon correcte de gérer le filtrage serait la suivante :

SELECT 
    city_id, COUNT(age), AVG(age)
FROM
    users
GROUP BY city_id
HAVING AVG(age) >= 20

Note : Le groupe avec city_id = 1 est écarté, car son AVG(age) est 18,5.

Enfin, WHERE et HAVING peuvent être utilisés simultanément sans aucun problème (si cela a un sens dans votre requête, bien sûr). Remarquez comment cela fonctionne dans l'exemple suivant. Nous effectuons à nouveau le calcul, mais cette fois, nous excluons tout utilisateur dont le nom de famille est "Simpson" :

SELECT 
    city_id, COUNT(age), AVG(age)
FROM
    users
WHERE last_name <> 'Simpson'
GROUP BY city_id
HAVING AVG(age) >= 20

Remarquez que le calcul de COUNT et AVG diffère pour city_id = 3. Il existe un utilisateur dont le nom de famille est "Simpson" pour city_id = 3, et cet enregistrement a été écarté en raison de la condition WHERE last_name <> 'Simpson’.

Traitement des NULL

Jusqu'à présent, toutes nos colonnes ont été remplies de données, mais ce n'est pas toujours le cas. Insérons quelques enregistrements avec des âges NULL pour expliquer certains cas limites. Les deux INSERTs suivants ajouteront deux nouveaux utilisateurs, avec un ID de 8 et 9 respectivement, qui ont un NULL dans la colonne âge :

INSERT INTO `users` (`id`, `city_id`, `first_name`, `last_name`, `age`)
VALUES ('8', '2', 'Frederic', 'Scott',NULL);

INSERT INTO `users` (`id`, `city_id`, `first_name`, `last_name`, `age`) 
VALUES ('9', '4', 'Stacy', 'Roberts',NULL);

Les enregistrements avec des valeurs nulles ne sont pas pris en compte dans MIN, MAX, SUM, AVG et COUNT(column). Ces enregistrements sont simplement ignorés dans le calcul. En règle générale, si vous ne prévoyez pas d'avoir des valeurs nulles ou si vous prévoyez de traiter les valeurs nulles comme "0", vous devez définir les colonnes comme NOT NULL et définir les colonnes NULL à "0" ou à toute autre valeur pertinente pour votre cas d'utilisation.

COUNT(colonne) vs COUNT(*)

La fonction COUNT(column) ne comptera pas les enregistrements dont les valeurs d'âge sont NULL. Voyons cela en action :

SELECT 
    COUNT(age)
FROM
    users;

Cette requête renvoie à nouveau un "7" ; les deux enregistrements que nous avons ajoutés ont des valeurs NULL dans la colonne "age", ils sont donc ignorés. Si nous voulions compter tous les enregistrements, quelle que soit leur valeur, nous utiliserions la fonction COUNT(*) function:


SELECT 
    COUNT(*)
FROM
    users;

Cette fonction renvoie le résultat attendu de "9".

Voyons comment les NULL ont un impact sur la fonction AVG().

AVG + NULL

Étant donné que AVG ignorera les enregistrements contenant des NULL dans la colonne spécifiée, la valeur résultante peut ne pas avoir de sens. En voici la raison.

La fonction AVG(age) additionnera uniquement les utilisateurs ayant une valeur non nulle dans la colonne "age" et divisera ce nombre par le COUNT des utilisateurs ayant également une valeur non nulle dans la colonne "age". Dans notre exemple, ce nombre est de 7. Si vous considérez que les deux utilisateurs que nous venons d'ajouter avec des âges NULL doivent être pris en compte pour le calcul de l'âge moyen, alors la valeur que vous obtiendrez avec AVG(age) sera fausse. La requête ci-dessous montre la différence entre les calculs :

SELECT 
    SUM(age),
    COUNT(age),
    AVG(age),
    SUM(age) / COUNT(age),
    COUNT(*),
    SUM(age) / COUNT(*)
FROM
    users;

Remarquez comment la valeur de AVG(age) correspond à la valeur calculée avec SUM(age)/COUNT(age); les deux fonctions ne prennent pas en compte les enregistrements avec des valeurs NULL. Mais regardez comment la valeur moyenne change en utilisant COUNT(*), qui inclut tous les enregistrements.

Nous avons vu que les valeurs NULL ne seront pas calculées dans les fonctions MIN, MAX, AVG, SUM et COUNT(column_name). Si vous utilisez ces fonctions et que vous prévoyez des champs NULL, assurez-vous de définir les NULL comme des valeurs spécifiques.

De même, définissez les types de colonnes en fonction de la valeur que vous stockez. Par exemple, les nombres stockés dans une colonne VARCHAR ne seront pas traités comme des nombres et peuvent entraîner des résultats indésirables dans les calculs de votre fonction agrégée.

Enfin, j'aimerais souligner l'importance de placer vos conditions de filtrage aux bons endroits dans vos requêtes. Utilisez HAVING si vous avez besoin de conditions basées sur des valeurs agrégées.

Que pensez-vous des fonctions agrégées SQL? Avez-vous des expériences ou des exemples concernant leur utilisation ? Veuillez partager vos réflexions avec notre communauté. Et ne manquez pas la deuxième partie de cet article, où j'expliquerai comment intégrer les fonctions agrégées SQL avec les JOINs. Nous la publierons bientôt sur le blog Vertabelo !