Partager via


AVG (Transact-SQL)

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Cette fonction retourne la moyenne des valeurs dans un groupe. Elle ignore les valeurs null.

Conventions de la syntaxe Transact-SQL

Syntaxe

AVG ( [ ALL | DISTINCT ] expression )
   [ OVER ( [ partition_by_clause ] order_by_clause ) ]

Arguments

ALL

Applique la fonction d'agrégation à toutes les valeurs. ALL est l'argument par défaut.

DISTINCT

Spécifie que la fonction AVG est appliquée à une seule instance de chaque valeur, quel que soit le nombre d'occurrences de la valeur.

expression

Expression de la catégorie de type de données numérique exacte ou approximative, à l’exception du type de données bit. Les fonctions d’agrégation et les sous-requêtes ne sont pas autorisées.

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause divise le jeu de résultats généré par la clause FROM en partitions auxquelles la fonction est appliquée. S'il n'est pas spécifié, la fonction gère toutes les lignes du jeu de résultats de la requête en un seul groupe. order_by_clause détermine l’ordre logique dans lequel l’opération est effectuée. order_by_clause est requis. Pour plus d’informations, consultez Clause OVER (Transact-SQL).

Types de retour

Le résultat évalué de l’expression détermine le type de retour.

Résultat de l'expression Type de retour
tinyint int
smallint int
int int
bigint bigint
Catégorie decimal (p, s) decimal(38, max(s,6))
Catégorie money et smallmoney money
Catégorie float et real float

Remarques

Si le type de données d’expression est un type de données alias, le type de retour est également du type de données alias. Toutefois, si le type de données de base du type de données alias est promu, par exemple de tinyint à int, la valeur de retour prend le type de données promu et non le type de données alias.

AVG () calcule la moyenne d’un ensemble de valeurs en divisant la somme de ces valeurs par le nombre de valeurs non null. Si la somme dépasse la valeur maximale pour le type de données de la valeur de retour, AVG() retourne une erreur.

AVG est une fonction déterministe lorsqu'elle est utilisée sans les clauses OVER et ORDER BY. Il n’est pas déterministe lorsqu’il est spécifié avec les clauses OVER et ORDER BY. Pour plus d’informations, consultez Fonctions déterministes et non déterministes. En outre, AVG peut sembler se comporter comme une fonction non déterministe lorsque vous l’utilisez avec des types de données float et réels . Mais la raison sous-jacente est la nature approximative de ces types de données.

Exemples

R. Utilisation des fonctions SUM et AVG pour des calculs

Cet exemple calcule la moyenne des heures de congés, ainsi que la somme des heures de congés maladie utilisées par les vice-présidents d'Adventure Works Cycles. Chacune de ces fonctions d'agrégation produit une valeur de résumé unique pour toutes les lignes récupérées. L’exemple utilise la base de données AdventureWorks2022.

SELECT AVG(VacationHours)AS 'Average vacation hours',
    SUM(SickLeaveHours) AS 'Total sick leave hours'
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Vice President%';

Voici le jeu de résultats obtenu.

Average vacation hours       Total sick leave hours
 ----------------------       ----------------------
25                           97

(1 row(s) affected)

B. Utilisation des fonctions SUM et AVG avec une clause GROUP BY

Lorsqu'elle est utilisée avec une clause GROUP BY, chaque fonction d'agrégation produit une valeur unique couvrant chaque groupe, au lieu d’une valeur unique couvrant la totalité de la table. L’exemple suivant produit des valeurs de synthèse pour chaque secteur géographique de ventes dans la base de données AdventureWorks2022. Le résumé répertorie la moyenne des bonus reçus par les vendeurs dans chaque secteur, ainsi que la somme des ventes annuelles à ce jour pour chaque secteur.

SELECT TerritoryID, AVG(Bonus)as 'Average bonus', SUM(SalesYTD) as 'YTD sales'
FROM Sales.SalesPerson
GROUP BY TerritoryID;
GO

Voici le jeu de résultats obtenu.

TerritoryID Average Bonus         YTD Sales
----------- --------------------- ---------------------
NULL        0.00                  1252127.9471
1           4133.3333             4502152.2674
2           4100.00               3763178.1787
3           2500.00               3189418.3662
4           2775.00               6709904.1666
5           6700.00               2315185.611
6           2750.00               4058260.1825
7           985.00                3121616.3202
8           75.00                 1827066.7118
9           5650.00               1421810.9242
10          5150.00               4116871.2277

(11 row(s) affected)

C. Utilisation de la fonction AVG avec DISTINCT

Cette instruction retourne les prix moyens des produits dans la base de données AdventureWorks2022. En utilisant DISTINCT, le calcul considère uniquement les valeurs uniques.

SELECT AVG(DISTINCT ListPrice)
FROM Production.Product;

Voici le jeu de résultats obtenu.

------------------------------
437.4042

(1 row(s) affected)

D. Utilisation de la fonction AVG sans DISTINCT

Sans DISTINCT, la fonction AVG recherche le prix moyen de tous les produits dans la table Product de la base de données AdventureWorks2022, y compris les valeurs en doublon.

SELECT AVG(ListPrice)
FROM Production.Product;

Voici le jeu de résultats obtenu.

------------------------------
438.6662

(1 row(s) affected)

E. Utilisation de la clause OVER

L’exemple suivant utilise la fonction AVG avec la clause OVER pour fournir une moyenne mobile des ventes annuelles pour chaque secteur dans la table Sales.SalesPerson de la base de données AdventureWorks2022. Les données sont partitionnées par TerritoryID et classées logiquement par SalesYTD. Cela signifie que la fonction AVG est calculée pour chaque secteur selon l'année de vente. Pour TerritoryID 1, il y a deux lignes pour l’année de vente 2005, qui représentent les deux vendeurs avec des ventes cette année. Les ventes moyennes pour ces deux lignes sont calculées, puis la troisième ligne représentant les ventes de l'année 2006 est incluse dans le calcul.

SELECT BusinessEntityID, TerritoryID
   ,DATEPART(yy,ModifiedDate) AS SalesYear
   ,CONVERT(VARCHAR(20),SalesYTD,1) AS  SalesYTD
   ,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID
                                            ORDER BY DATEPART(yy,ModifiedDate)
                                           ),1) AS MovingAvg
   ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID
                                            ORDER BY DATEPART(yy,ModifiedDate)
                                            ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY TerritoryID,SalesYear;

Voici le jeu de résultats obtenu.

BusinessEntityID TerritoryID SalesYear   SalesYTD             MovingAvg            CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274              NULL        2005        559,697.56           559,697.56           559,697.56
287              NULL        2006        519,905.93           539,801.75           1,079,603.50
285              NULL        2007        172,524.45           417,375.98           1,252,127.95
283              1           2005        1,573,012.94         1,462,795.04         2,925,590.07
280              1           2005        1,352,577.13         1,462,795.04         2,925,590.07
284              1           2006        1,576,562.20         1,500,717.42         4,502,152.27
275              2           2005        3,763,178.18         3,763,178.18         3,763,178.18
277              3           2005        3,189,418.37         3,189,418.37         3,189,418.37
276              4           2005        4,251,368.55         3,354,952.08         6,709,904.17
281              4           2005        2,458,535.62         3,354,952.08         6,709,904.17

(10 row(s) affected)

Dans cet exemple, la clause OVER n’inclut pas PARTITION BY. Cela signifie que la fonction s’applique à toutes les lignes retournées par la requête. La clause ORDER BY spécifiée dans la clause OVER détermine l'ordre logique dans lequel la fonction AVG s’applique. La requête retourne une moyenne mobile des ventes par année, pour tous les secteurs de vente spécifiés dans la clause WHERE. La clause ORDER BY spécifiée dans l'instruction SELECT détermine l'ordre dans lequel cette instruction affiche les lignes de la requête.

SELECT BusinessEntityID, TerritoryID
   ,DATEPART(yy,ModifiedDate) AS SalesYear
   ,CONVERT(VARCHAR(20),SalesYTD,1) AS  SalesYTD
   ,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)
                                            ),1) AS MovingAvg
   ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)
                                            ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY SalesYear;

Voici le jeu de résultats obtenu.

BusinessEntityID TerritoryID SalesYear   SalesYTD             MovingAvg            CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274              NULL        2005        559,697.56           2,449,684.05         17,147,788.35
275              2           2005        3,763,178.18         2,449,684.05         17,147,788.35
276              4           2005        4,251,368.55         2,449,684.05         17,147,788.35
277              3           2005        3,189,418.37         2,449,684.05         17,147,788.35
280              1           2005        1,352,577.13         2,449,684.05         17,147,788.35
281              4           2005        2,458,535.62         2,449,684.05         17,147,788.35
283              1           2005        1,573,012.94         2,449,684.05         17,147,788.35
284              1           2006        1,576,562.20         2,138,250.72         19,244,256.47
287              NULL        2006        519,905.93           2,138,250.72         19,244,256.47
285              NULL        2007        172,524.45           1,941,678.09         19,416,780.93
(10 row(s) affected)