Utiliser des fonctions d'agrégation
T-SQL fournit des fonctions d’agrégation telles que SUM, MAX et AVG pour effectuer des calculs qui acceptent plusieurs valeurs et retournent un résultat unique.
Travailler avec les fonctions d’agrégation
La plupart des requêtes que nous avons examinées opèrent sur une ligne à la fois, à l’aide d’une clause WHERE pour filtrer les lignes. Chaque ligne retournée correspond à une ligne dans le jeu de données d’origine.
De nombreuses fonctions d’agrégation sont fournies dans SQL Server. Dans cette section, nous allons examiner les fonctions les plus courantes, telles que SUM, MIN, MAX, AVG et COUNT.
Lorsque vous utilisez des fonctions d’agrégation, vous devez prendre en compte les points suivants :
- Les fonctions d’agrégation retournent une valeur unique (scalaire) et peuvent être utilisées dans des instructions SELECT presque partout où une seule valeur peut être utilisée. Par exemple, ces fonctions peuvent être utilisées dans les clauses SELECT, HAVING et ORDER BY. Toutefois, elles ne peuvent pas être utilisées dans la clause WHERE.
- Les fonctions d’agrégation ignorent les valeurs NULL, sauf quand vous utilisez COUNT(*).
- Les fonctions d’agrégation dans une liste SELECT n’ont pas d’en-tête de colonne, sauf si vous fournissez un alias avec AS.
- Les fonctions d’agrégation dans une liste SELECT fonctionnent sur toutes les lignes transmises à l’opération SELECT. S’il n’existe aucune clause GROUP BY, toutes les lignes répondant à un filtre dans la clause WHERE sont résumées. Vous en apprendrez davantage sur GROUP BY dans la rubrique suivante.
- À moins que vous n’utilisiez GROUP BY, vous ne devez pas combiner les fonctions d’agrégation avec des colonnes non incluses dans les fonctions de la même liste SELECT.
Pour aller au-delà des fonctions intégrées, SQL Server fournit un mécanisme pour les fonctions d’agrégation définies par l’utilisateur via le CLR (Common Language Runtime) .NET. Ce sujet dépasse la portée de ce module.
Fonctions d’agrégation intégrées
Comme évoqué plus tôt, Transact-SQL fournit de nombreuses fonctions d’agrégation intégrées. Les fonctions couramment utilisées comprennent :
Nom de la fonction
Syntaxe
Description
SUM
SUM(expression)
Totalise toutes les valeurs numériques non NULL dans une colonne.
AVG
AVG(expression)
Calcule la moyenne de toutes les valeurs numériques non NULL dans une colonne (somme/nombre).
MIN
MIN(expression)
Renvoie le plus petit nombre, la date/heure la plus ancienne ou la première chaîne qui se produit (selon les règles de tri par classement).
MAX
MAX(expression)
Retourne le nombre le plus élevé, la date/heure la plus récente ou la dernière chaîne qui se produit (selon les règles de tri de classement).
COUNT ou COUNT_BIG
COUNT(*) ou COUNT(expression)
Avec (*), compte toutes les lignes, notamment celles qui ont des valeurs NULL. Lorsqu’une colonne est spécifiée en tant qu’expression, retourne le nombre de lignes non NULL pour cette colonne. COUNT retourne une valeur int ; COUNT_BIG retourne une valeur big_int.
Pour utiliser un agrégat intégré dans une clause SELECT, examinez l’exemple suivant dans l’exemple de base de données myStore :
SELECT AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;
Les résultats de cette requête peuvent se présenter comme ceci :
AveragePrice
MinimumPrice
MaximumPrice
744.5952
2.2900
3578.2700
Notez que l’exemple ci-dessus résume toutes les lignes de la table Production.Product. Nous pourrions facilement modifier la requête pour retourner les prix moyen, minimum et maximum des produits dans une catégorie spécifique en ajoutant une clause WHERE, comme suit :
SELECT AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product
WHERE ProductCategoryID = 15;
Lors de l’utilisation d’agrégats dans une clause SELECT, toutes les colonnes référencées dans la liste SELECT doivent être utilisées comme entrées pour une fonction d’agrégation, ou être référencées dans une clause GROUP BY.
Examinez la requête suivante, qui tente d’inclure le champ ProductCategoryID dans les résultats agrégés :
SELECT ProductCategoryID, AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;
L’exécution de cette requête génère l’erreur suivante
Msg 8120, Level 16, State 1, Line 1
Column ’Production.ProductCategoryID’ is invalid in the select list because it isn’t contained in either an aggregate function or the GROUP BY clause.
La requête traite toutes les lignes comme un groupe agrégé unique. Par conséquent, toutes les colonnes doivent être utilisées comme entrées pour les fonctions d’agrégation.
Dans les exemples précédents, nous avons agrégé des données numériques telles que le prix et les quantités de l’exemple précédent. Certaines fonctions d’agrégation peuvent également être utilisées pour résumer les données de date, d’heure et de caractère. Les exemples suivants illustrent l’utilisation d’agrégats avec des dates et des caractères :
Cette requête retourne les première et dernière entreprises par nom, en utilisant MIN et MAX :
SELECT MIN(CompanyName) AS MinCustomer,
MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;
Cette requête retourne les première et dernière valeurs de CompanyName dans la séquence de classement de la base de données, dans ce cas l’ordre alphabétique :
MinCustomer
MaxCustomer
A Bike Store
Yellow Bicycle Company
D’autres fonctions peuvent être imbriquées avec des fonctions d’agrégation.
Par exemple, la fonction scalaire YEAR est utilisée dans l’exemple suivant pour retourner uniquement la partie année de la date de commande, avant que les fonctions MIN et MAX soient évaluées :
SELECT MIN(YEAR(OrderDate)) AS Earliest,
MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader;
Plus ancien
Plus récent
2008
2021
Les fonctions MIN et MAX peuvent également être utilisées avec des données de date pour retourner les valeurs chronologiques les plus anciennes et les plus récentes. Toutefois, AVG et SUM ne peuvent être utilisés que pour les données numériques, qui comprennent les types integers, money, float et decimal.
Utilisation de DISTINCT avec des fonctions d’agrégation
Vous devez être conscient de l’utilisation de DISTINCT dans une clause SELECT pour supprimer les lignes en double. Lorsque vous l’utilisez avec une fonction d’agrégation, DISTINCT supprime les valeurs dupliquées de la colonne d’entrée avant de calculer la valeur résumée. DISTINCT est utile pour résumer des occurrences uniques de valeurs, comme des clients dans la table Orders.
L’exemple suivant retourne le nombre de clients qui ont passé des commandes, quel que soit le nombre de commandes passées :
SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Sales.SalesOrderHeader;
COUNT(<une_colonne>) compte simplement le nombre de lignes ayant une valeur dans la colonne. S’il n’existe aucune valeur NULL, COUNT(<une_colonne>) est identique à COUNT(*). COUNT (DISTINCT <une_colonne>) compte le nombre de valeurs distinctes dans la colonne.
Utilisation de fonctions d’agrégation avec NULL
Il est important d’avoir conscience de la présence possible de valeurs NULL dans vos données et de la façon dont la valeur NULL interagit avec les composants de requête T-SQL, y compris la fonction d’agrégation. Il y a quelques points à prendre en compte:
- À l’exception de COUNT utilisé avec l’option (*), les fonctions d’agrégation T-SQL ignorent les valeurs NULL. Par exemple, une fonction SUM n’ajoutera que des valeurs non NULL. Les valeurs NULL ne sont pas évaluées à zéro. COUNT(*) compte toutes les lignes, quelle que soit la valeur ou la non-valeur d’une colonne.
- La présence de valeurs NULL dans une colonne peut entraîner des calculs inexacts pour AVG, qui n’additionne uniquement les lignes remplies et divise cette somme par le nombre de lignes non NULL. Il peut exister une différence de résultats entre AVG(<colonne>) et (SUM(<colonne>)/COUNT(*)).
Imaginons, par exemple, la table suivante nommée t1 :
C1
C2
1
NULL
2
10
3
20
4
30
5
40
6
50
Cette requête illustre la différence entre la façon dont AVG gère NULL et la façon dont vous pouvez calculer une moyenne avec une colonne calculée SUM/COUNT(*) :
SELECT SUM(c2) AS sum_nonnulls,
COUNT(*) AS count_all_rows,
COUNT(c2) AS count_nonnulls,
AVG(c2) AS average,
(SUM(c2)/COUNT(*)) AS arith_average
FROM t1;
Le résultat devrait être :
sum_nonnulls
count_all_rows
count_nonnulls
average
arith_average
150
6
5
30
25
Dans cet ensemble de résultats, la colonne nommée average est l’agrégat qui obtient en interne la somme de 150 et la divise par le nombre de valeurs non NULL dans la colonne c2. Le calcul équivaudrait à 150/5, ou 30. La colonne appelée arith_average divise explicitement la somme par le nombre de toutes les lignes. Le calcul est donc 150/6, ou 25.
Si vous devez résumer toutes les lignes, qu’elles soient NULL ou non, envisagez de remplacer les valeurs NULL par une autre valeur qui ne sera pas ignorée par votre fonction d’agrégation. Vous pouvez utiliser la fonction COALESCE à cet effet.