AVG (Transact-SQL)
S’applique à :
point de terminaison d’analytique SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Platform System (PDW) SQL Analytics dans Microsoft Fabric Warehouse dans 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 ) ]
Remarque
Pour afficher la syntaxe Transact-SQL pour SQL Server 2014 (12.x) et versions antérieures, consultez la Documentation sur les versions antérieures.
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)
Contenu connexe
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour