4th Jul 2022 14 minutes de lecture

Que sont les fonctions agrégées en SQL, et comment les utiliser ?

Les données sont votre source de connaissances. Et grâce aux fonctions d'agrégation SQL, vous pouvez extraire efficacement de vos données les connaissances précises dont vous avez besoin. Lisez la suite pour en savoir plus.

Les principales fonctions d'agrégation SQL sont les suivantes :

COUNT(column_name | *) renvoie le nombre de lignes d'une table.
SUM(column_name) renvoie la somme des valeurs d'une colonne numérique.
AVG(column_name) renvoie la valeur moyenne d'une colonne numérique.
MIN(column_name) renvoie la valeur minimale d'une colonne sélectionnée.
MAX(column_name) renvoie la valeur maximale d'une colonne sélectionnée.

Dans cet article, nous allons aborder chacune de ces fonctions à l'aide d'exemples. Vous découvrirez ce qu'il advient de NULLs et des doublons lorsqu'ils sont soumis aux fonctions d'agrégation. En outre, nous expliquerons *, le mot-clé DISTINCT et l'instruction CASE.

C'est parti !

Comment fonctionnent les fonctions d'agrégation

Les fonctions d'agrégation SQL accumulent les données de plusieurs lignes en une seule ligne récapitulative. La valeur cumulée est basée sur les valeurs de la colonne passée en argument. Nous pouvons regrouper les lignes à l'aide d'une clause GROUP BY et les filtrer davantage à l'aide d'une clause HAVING.

Un exemple standard consiste à trouver le nombre de lignes d'un tableau. Ici, nous agrégeons toutes les lignes du tableau Livres en une seule ligne.

SELECT * FROM Books;

Id	Author	Title	Price
234	Anthony Molinaro	SQL Cookbook	20.00
235	Alan Beaulieu	Learning SQL	25.00
236	Donald Knuth	Things a Computer Scientist Rarely Talks About	25.00
237	Donald Knuth	The Art of Computer Programming	27.00

SELECT COUNT(*) AS NumberOfBooks
FROM Books;

NumberOfBooks
4

Facile, non ? En utilisant COUNT(*), vous pouvez compter le nombre de toutes les lignes.

Vous pouvez également consulter nos autres articles sur les fonctions d'agrégation SQL ici et ici.

L'argument * des fonctions d'agrégation

Si vous me demandez ce que signifie * en SQL, je vous répondrai qu'il s'agit de " tous". Il est couramment utilisé avec une instruction SELECT pour interroger toutes les colonnes d'une table donnée. Prenez, par exemple, SELECT * FROM Books, comme ci-dessus.

L'argument * peut également être utilisé avec la fonction d'agrégation COUNT(). Celle-ci compte alors toutes les lignes d'une table. Prenez, par exemple, SELECT COUNT(*) as NumberOfBooks FROM Books, comme ci-dessus.

Vous pouvez regrouper les données par une colonne ou même par plusieurs colonnes. Jetez un coup d'œil à l'exemple suivant :

SELECT Author, COUNT(*) AS NumberOfBooks
FROM Books
GROUP BY Author;

Author	NumberOfBooks
Anthony Molinaro	1
Alan Beaulieu	1
Donald Knuth	2

Il s'agit de compter le nombre de livres par auteur.

L'argument * s'applique uniquement à la fonction d'agrégation COUNT(). Pour les autres fonctions d'agrégation, une colonne spécifique, ou une combinaison de colonnes, est requise comme argument.

Le mot-clé DISTINCT

Le mot-clé DISTINCT indique à la base de données que nous ne voulons pas prendre en compte les valeurs en double.

Par exemple, COUNT(Author) nous permet d'obtenir le nombre de tous les auteurs présents dans une table. Mais si le même auteur apparaît plusieurs fois dans une colonne, il est compté plusieurs fois. Jetez un coup d'œil à ceci :

SELECT COUNT(Author) AS NumberOfAuthors
FROM books;

NumberOfAuthors
4

Vous voyez ? Il compte quatre auteurs car Donald Knuth est compté deux fois.

Que se passe-t-il si nous ajoutons le mot-clé DISTINCT?

SELECT COUNT(DISTINCT Author) AS NumberOfAuthors
FROM Books;

NumberOfAuthors
3

Cette fois, nous utilisons un mot-clé DISTINCT. Maintenant, Donald Knuth n'est compté qu'une seule fois. Lorsque vous utilisez le mot-clé DISTINCT, COUNT() doit prendre une colonne spécifique comme argument. Il renvoie le nombre de valeurs uniques stockées dans cette colonne.

De même, nous pouvons utiliser le mot-clé DISTINCT avec les arguments des fonctions d'agrégation SUM() et AVG(). Ci-dessous, nous comparons les résultats de l'exécution de la fonction SUM() avec et sans le mot-clé DISTINCT.

SELECT SUM(DISTINCT Price)
AS TotalDistinctPrice
FROM Books;

TotalDistinctPrice
72

	SELECT SUM(Price)
	AS TotalPrice
	FROM Books;

TotalPrice
97

Comme vous pouvez le constater, lorsque vous utilisez le mot-clé DISTINCT, les livres ayant le même prix ne sont pris en compte qu'une seule fois dans SUM(). Dans ce cas, il est plus logique d'utiliser la fonction SUM() sans le mot-clé DISTINCT.

De même, pour calculer un prix moyen, il est préférable de ne pas utiliser le mot-clé DISTINCT; nous devrions considérer chaque prix autant de fois qu'il apparaît dans la colonne. Voyez ce qui se passe avec AVG():

SELECT AVG(DISTINCT Price)
AS TotalDistinctAvg
FROM Books;

TotalDistinctAvg
24

	SELECT AVG(Price)
	AS TotalAvg
	FROM Books;

TotalAvg
24.25

Pour les fonctions d'agrégation MIN() et MAX(), le mot-clé DISTINCT ne fait pas de différence. Mais il ne provoque pas non plus d'erreurs. Pourquoi ? Considérons un ensemble de nombres {1, 2, 2, 3, 4, 5, 5, 6}. Ses valeurs maximale et minimale sont respectivement 6 et 1. Avec le mot-clé DISTINCT, cet ensemble devient {1, 2, 3, 4, 5, 6}, et les valeurs maximale et minimale sont toujours les mêmes.

L'instruction CASE

L'instruction CASE catégorise et filtre les données. Elle est comme un gardien de l'argument d'une fonction agrégée, qui décide des valeurs à laisser entrer. Prenons quelques exemples pour illustrer ce concept.

Dans la requête suivante, nous utilisons une instruction CASE comme argument pour la fonction COUNT(). Celle-ci ne compte que les livres dont le prix est supérieur à 20,00 $.

SELECT COUNT(CASE WHEN Price > 20 THEN Price END)
AS NumberOfExpensiveBooks
FROM Books;

NumberOfExpensiveBooks
3

Une instruction CASE peut également être utilisée comme argument pour d'autres fonctions d'agrégation. Dans la requête ci-dessous, nous additionnons les prix des livres qui coûtent exactement 25,00 $. L'instruction CASE à l'intérieur de la fonction SUM() permet d'inclure dans la somme uniquement les livres dont le prix est de 25 $.

SELECT SUM(CASE WHEN Price = 25 THEN Price END)
AS BooksSum
FROM Books;

BooksSum
50

Maintenant, nous faisons la moyenne des prix des livres dont le prix est inférieur à 26 $ dans la requête suivante. L'instruction CASE à l'intérieur de la fonction AVG() permet d'inclure dans la moyenne uniquement les livres dont le prix est inférieur à 26,00 $. La fonction AVG() étant un argument de la fonction ROUND(), le résultat de la fonction AVG() est arrondi à deux décimales.

SELECT ROUND(AVG(CASE WHEN Price < 26 THEN Price END), 2)
AS BooksAvg
FROM Books;

BooksAvg
23.33

Dans la requête suivante, nous trouvons le prix minimum des livres sur SQL dont le prix est inférieur à 26,00 $. L'instruction CASE à l'intérieur de la fonction MIN() permet d'inclure dans le jeu uniquement les livres dont le prix est inférieur à 26,00 $.

SELECT MIN(CASE WHEN Price < 26 AND Title LIKE '%SQL%' THEN Price END)
AS BooksMin
FROM Books;

BooksMin
20

Ensuite, nous trouvons le prix maximum des livres dont le prix est inférieur à 25 $. L'instruction CASE dans la fonction MAX() permet d'inclure dans l'ensemble uniquement les livres dont le prix est inférieur à 25 $.

SELECT MAX(CASE WHEN Price < 25 THEN Price END)
AS BooksMax
FROM Books;

BooksMax
20

Je suis sûr que vous pouvez déjà comprendre le résultat de ces requêtes !

Que se passe-t-il avec les NULL ?

La réponse est simple. Les fonctions d'agrégation SQL ignorent les valeurs NULL.

Considérons une table Books mis à jour. Cette fois, nous avons un prix de NULL.

SELECT * FROM Books;

Id	Author	Title	Price
234	Anthony Molinaro	SQL Cookbook	20.00
235	Alan Beaulieu	Learning SQL	25.00
236	Donald Knuth	Things a Computer Scientist Rarely Talks About	25.00
237	Donald Knuth	The Art of Computer Programming	NULL

COUNT(Price) maintenant renvoie 3 et non 4, et SUM(Price) renvoie 70,00. NULLs est ignoré dans les deux cas.

Vous pouvez également utiliser les fonctions d'agrégation avec les JOIN ! Consultez notre article sur l'utilisation des fonctions d'agrégation SQL avec les JOIN pour en savoir plus.

Le rôle de HAVING et GROUP BY avec les fonctions agrégées

Il est facile de comprendre ce que fait une clause HAVING si vous êtes familier avec la clause WHERE. Une clause HAVING filtre les valeurs de sortie des fonctions agrégées. Une clause GROUP BY vous permet de diviser vos données en groupes et de trouver une valeur agrégée pour chaque groupe.

Prenons un exemple.

SELECT Author, AVG(Price) AS AvgBookPrice
FROM Books
GROUP BY Author
HAVING AVG(Price) > 20;

Author	AvgBookPrice
Alan Beaulieu	25
Donald Knuth	26

Nous regroupons les données par la colonne Auteur à l'aide d'une clause GROUP BY. Ensuite, nous limitons les valeurs de AVG(Price) pour qu'elles soient supérieures à 20 à l'aide d'une clause HAVING.

Nous pouvons essayer d'utiliser les clauses WHERE et HAVING ensemble pour voir la différence entre elles.

SELECT Author, AVG(Price) AS AvgBookPrice
FROM Books
WHERE Author LIKE 'A%'
GROUP BY Author
HAVING AVG(Price) > 20;

Author	AvgBookPrice
Alan Beaulieu	25

La clause HAVING est souvent confondue avec la clause WHERE. N'oubliez pas que vous ne pouvez pas utiliser de fonctions agrégées dans une clause WHERE. Veillez à vous entraîner suffisamment et consultez notre tutoriel SQL HAVING. Consultez également notre article sur l'utilisation de GROUP BY en SQL pour en savoir plus sur la clause GROUP BY.

Fonctions d'agrégation SQL ou fonctions de fenêtre SQL ? Ou peut-être les deux ? Consultez notre article sur les similitudes et les différences entre les deux !

Mettons-nous en pratique !

Il ne suffit pas de lire. Le langage SQL requiert une grande quantité de pratique. Commençons par quelques exemples ici pour que vous puissiez continuer par vous-même !

Avant d'entrer dans les exemples, assurez-vous que vous avez bien compris toutes les fonctions suivantes Les Fondamentaux de SQL et Fonctions SQL standards!

Exemple de base de données

Vous trouverez ci-dessous le plan de la base de données. Nous allons utiliser cette base de données dans nos exemples ci-dessous.

Analysons le plan de la base de données, en commençant par la gauche.

La table Customers stocke les données relatives aux clients. Sa clé primaire est la colonne CustomerId. Les colonnes Customers et Orders sont liées par la colonne CustomerId. La table Orders stocke la date de la commande et l'ID du client qui a passé la commande. Sa clé primaire est la colonne OrderId. Le lien entre les tables Customers et Orders définit la relation qui existe entre elles. Un client peut avoir zéro ou plusieurs commandes, mais une commande ne peut être attribuée qu'à un seul client.

Les tables Orders et OrderDetails sont liées par la colonne OrderId. Les tables Products et OrderDetails sont liées par la colonne ProductId. La clé primaire de la table OrderDetails est constituée des colonnes OrderId et ProductId.

Une commande peut être composée d'un ou plusieurs produits. Par conséquent, une ligne de la table Orders peut être liée à une ou plusieurs lignes de la table OrderDetails table. De même, un produit peut se trouver dans zéro ou plusieurs commandes. Par conséquent, une ligne du tableau Products peut être liée à zéro ou plusieurs lignes du tableau. OrderDetails tableau.

Insérons maintenant quelques données dans nos tables.

Le site Customers tableau :

CustomerId	FirstName	LastName	Street	HouseNo	City	Country	Email	PhoneNo
1	Rachel	Hartley	Bicetown Road	602	New York	USA	rh@email.com	0123456789
2	Caitlyn	Ray	Friedrichstrasse	44	Berlin	Germany	cr@email.com	0987654321
3	Andrew	Duncan	Lairg Road	38	London	NULL	ad@email.com	0567432678
4	Taylor	Jenkins	Park Row	106	Edinburgh	UK	NULL	0876345123
5	Ben	Holland	Williams Avenue	252	Los Angeles	USA	bh@email.com	0987456789

Le tableau Orders (la colonne date est au format JJ-MM-AAAA) :

OrderId	CustomerId	OrderDate
45	1	10-10-2021
46	2	11-12-2020
47	3	05-05-2021
48	4	09-08-2021
49	5	NULL
50	1	02-06-2021
51	2	07-07-2021

Le site OrderDetails tableau :

OrderId	ProductId	Quantity
45	100	2
45	101	3
46	100	1
47	102	4
48	101	3
48	103	5
49	104	2
50	100	3
51	101	1

Le site Products tableau :

ProductId	Name	UnitPrice	AvailableInStock
100	Keyboard	30.00	300
101	USB Drive	20.00	450
102	Mouse	20.00	500
103	Screen	100.00	450
104	Laptop	600.00	200

Nous sommes maintenant prêts à commencer les exemples.

Exemples avec COUNT()

Nous commençons par le tableau Customers tableau. Cherchons à savoir combien de clients il y a par pays.

SELECT Country, COUNT(CustomerId) AS NumberOfCustomers
FROM Customers
GROUP BY Country;

Country	NumberOfCustomers
NULL	1
Germany	1
UK	1
USA	2

Nous avons sélectionné toutes les valeurs distinctes de la colonne Country, y compris la valeur NULL. La colonne NumberOfCustomers stocke le nombre de clients pour chaque valeur de la colonne Country.

Que se passe-t-il si nous utilisons la colonne Email comme argument de la fonction COUNT()?

SELECT Country, COUNT(Email) AS NumberOfCustomers
FROM Customers
GROUP BY Country;

Country	NumberOfCustomers
NULL	1
Germany	1
UK	0
USA	2

La valeur de la colonne NumberOfCustomers pour le pays "UK" devient zéro. Cela s'explique par le fait que la colonne Email de la table Customers table est NULL pour ce client.

Voyons maintenant un exemple qui utilise les clauses GROUP BY et HAVING.

SELECT Country, COUNT(Email) AS NumberOfCustomersWithEmail
FROM Customers
WHERE Country IS NOT NULL
GROUP BY Country
HAVING COUNT(Email) > 1;

Country	NumberOfCustomersWithEmail
USA	2

Comme précédemment, nous sélectionnons les valeurs de la colonne Pays et obtenons le nombre de clients ayant des e-mails par pays. Dans la clause WHERE, nous indiquons que nous ne prenons pas en compte les valeurs de NULL pour la colonne Country. Ensuite, nous regroupons nos données par Country. Enfin, nous limitons les valeurs de la colonne NumberOfCustomersWithEmail à une valeur supérieure à 1 avec une clause HAVING.

Exemples avec SUM()

Vérifions combien valent tous les produits disponibles.

SELECT SUM(UnitPrice * AvailableInStock) AS AllProductsValue
FROM Products;

AllProductsValue
193000

Ici, la fonction d'agrégation SUM() crée une valeur de UnitPrice * AvailableInStock pour chaque ligne, puis additionne toutes ces valeurs.

Disons que chaque commande d'une valeur supérieure à 100,00 $ donne droit à une remise. Nous voulons savoir quelles commandes bénéficient de cette remise.

SELECT OrderId,
       CASE WHEN OrderValue > 100 THEN 1 ELSE 0 END
       AS QualifiesForDiscount
FROM
     ( SELECT aod.OrderId AS OrderId,
              SUM(aod.Quantity * ap.UnitPrice) AS OrderValue
       FROM Products ap
       JOIN OrderDetails aod
       ON ap.ProductId = aod.ProductId
       GROUP BY aod.OrderId );

OrderId	QualifiesForDiscount
45	1
46	0
47	0
48	1
49	1
50	0
51	0

La requête interne sélectionne toutes les valeurs de la colonne OrderId et calcule la valeur de chaque commande à l'aide de la fonction SUM(). La requête externe utilise une instruction CASE pour décider si la commande peut bénéficier d'une remise (1) ou non (0).

Supposons maintenant que nous définissions tous les produits dont le prix unitaire est supérieur à 90,00 $ comme étant chers. Découvrons la valeur totale de tous les produits chers en stock.

SELECT
    SUM(CASE WHEN UnitPrice > 90 THEN UnitPrice * AvailableInStock END)
    AS ExpensiveProductsValue
FROM Products;

ExpensiveProductsValue
165000

Nous avons transmis une déclaration CASE comme argument à la fonction SUM(). Cet argument garantit que seules les lignes dont la valeur UnitPrice est supérieure à 90,00 $ sont prises en compte. À part cela, cet exemple est assez similaire au premier de cette section.

Exemples avec AVG()

Vérifions quel est le prix moyen d'une commande.

SELECT AVG(OrderValue) AS AvgOrderValue
FROM
     ( SELECT aod.OrderId AS OrderId,
              SUM(aod.Quantity * ap.UnitPrice) AS OrderValue
       FROM Products ap
       JOIN OrderDetails aod
       ON ap.ProductId = aod.ProductId
       GROUP BY aod.OrderId );

AvgOrderValue
300

La requête interne produit la valeur totale de la commande pour chaque commande. La requête externe calcule la valeur moyenne d'une commande.

Nous pouvons également connaître la quantité moyenne commandée par produit.

SELECT ROUND(AVG(Quantity), 2) AS AvgOrderQuantity
FROM OrderDetails;

AvgOrderQuantity
2.67

En moyenne, nos clients achètent entre 2 et 3 articles d'un produit donné dans une commande.

Voyons ce qui change lorsque nous ne considérons que les valeurs uniques de la colonne Quantity.

SELECT ROUND(AVG(DISTINCT Quantity), 2) AS AvgOrderQuantity
FROM OrderDetails;

AvgOrderQuantity
3

La valeur de sortie change, car nous ne tenons plus compte des valeurs en double qui apparaissent dans la colonne Quantity de la table OrderDetails de la table.

Exemples avec MAX() et MIN()

Dernier point mais non des moindres ! Les fonctions MAX() et MIN() sont assez simples. Trouvons les commandes les plus anciennes et les plus récentes.

SELECT MIN(OrderDate) AS EarliestOrder,
       MAX(OrderDate) AS LatestOrder
FROM Orders;

EarliestOrder	LatestOrder
11-12-2020	10-10-2021

La fonction MIN() renvoie la date la plus ancienne, et la fonction MAX() la date la plus récente.

Nous pouvons également identifier les produits les moins chers et les plus chers. Pour ce faire, vous pouvez effectuer une recherche dans la table Products pour ce faire.

SELECT MIN(UnitPrice) AS CheapestProductPrice,
       MAX(UnitPrice) AS MostExpensiveProductPrice
FROM Products;

CheapestProductPrice	MostExpensiveProductPrice
20	600

Déterminons combien de commandes il y a par client, puis obtenons le nombre minimum et maximum de commandes par client.

SELECT MIN(NumberOfOrders) AS MinNumberOfOrders,
       MAX(NumberOfOrders) AS MaxNumberOfOrders
FROM
     ( SELECT CustomerId,
              COUNT(OrderID) AS NumberOfOrders
       FROM Orders
       GROUP BY CustomerId );

MinNumberOfOrders	MaxNumberOfOrders
1	2

La requête interne sélectionne la colonne CustomerId et le nombre total de commandes passées par un client donné. La fonction COUNT(OrderId) compte le nombre de commandes par client. Ensuite, nous regroupons nos données par la colonne CustomerId en utilisant une clause GROUP BY. Dans cette étape, la fonction COUNT(OrderId) compte les commandes par client et non pour tous les clients ensemble. La requête externe sélectionne les valeurs minimum et maximum de la colonne NumberOfOrders dans la requête interne.

Les fonctions agrégées SQL, un outil essentiel en science des données

En utilisant les fonctions d'agrégation, nous pouvons facilement trouver des réponses à des questions spécifiques, telles que le nombre de clients ou le prix moyen d'une commande. Les fonctions d'agrégation SQL nous permettent d'analyser efficacement les données.

Ces fonctions d'agrégation SQL de base sont très utiles en science des données. Grâce à elles, vous pouvez organiser les données comme vous le souhaitez et extraire les informations dont vous avez besoin.

Nous avons examiné de nombreux exemples avec les fonctions d'agrégation COUNT(), SUM(), AVG(), MIN() et MAX(). Nous avons également couvert des exemples d'utilisation de *, du mot-clé DISTINCT et de l'instruction CASE comme arguments des fonctions d'agrégation. Maintenant, vous êtes prêt à créer votre base de données et à vous entraîner encore un peu !

Tags: