Dans cet article, nous allons apprendre la fonction SQL Average qui est connue sous le nom de fonction AVG() en T-SQL. La fonction AVG() est une fonction agrégée qui calcule la valeur moyenne d’un ensemble de données numériques qui revient de l’instruction SELECT.
Introduction
Supposons que nous ayons une collection de nombres. Tout d’abord, nous additionnons tous les membres de la collection, puis nous divisons le nombre total de membres de la collection. En conséquence, le nombre obtenu sera la moyenne. Expliquons cette notion mathématique à l’aide d’un exemple simple.
John est un étudiant à l’université et décide d’enregistrer ses dépenses chaque jour. Le graphique et le tableau suivants représentent les dépenses de John la semaine dernière.
Maintenant, nous allons calculer la dépense moyenne de John la semaine dernière. Dans un premier temps, nous allons additionner toutes les dépenses de la semaine précitée.
(20$+60$+20$+42$+10$+15$+8$) = 175$ est le montant total des dépenses de la semaine.
Dans un second temps, nous allons diviser le montant total des dépenses à 7 car cette collection est formée sur 7 membres. En d’autres termes, une semaine est constituée de sept jours.
175 $ / 7 = 25 $ est la moyenne des dépenses de la semaine.
Après avoir discuté du concept mathématique de la moyenne, continuons à apprendre les bases de la fonction AVG() en SQL.
Préparation des données
A l’aide de la requête suivante, nous allons créer la table WeekExpense puis insérer les dépenses de Jean sur 3 semaines. Nous utiliserons cette table dans tous les exemples de cet article.
|
..
1
2
3
. 4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
CREATE TABLE WeekExpense
( WeekNumber VARCHAR(20),WeekDayName VARCHAR(50), Expense MONEY)
INSERT INTO WeekExpense
VALUES
(‘Week05’, ‘Monday’, 20 ),
(‘Week05’, ‘Tuesday’, 60 ),
(‘Semaine05’, ‘Mercredi’, 20 ),
(‘Semaine05’, ‘Jeudi’, 42 ),
(‘Semaine05’, ‘Vendredi’, 10 ),
(‘Semaine05’, ‘Samedi’, 15 ) ,
(‘Semaine05’, ‘Dimanche’, 8 ),
(‘Semaine04’, ‘Lundi’, 29 ),
(‘Semaine04’, ‘Mardi’, 17 ),
(‘Semaine04’, ‘Mercredi’, 42 ),
(‘Semaine04’, ‘Jeudi’, 11 ),
(‘Semaine04’, ‘Vendredi’, 43 ),
(‘Semaine04’, ‘Samedi’, 10 ) ,
(‘Semaine04’, ‘Dimanche’, 15 ),
(‘Semaine03’, ‘Lundi’, 10 ),
(‘Semaine03’, ‘Mardi’, 32 ),
(‘Semaine03’, ‘Mercredi’, 35 ),
(‘Semaine03’, ‘Jeudi’, 19 ),
(‘Semaine03’, ‘Vendredi’, 30 ),
(‘Semaine03’, ‘Samedi’, 10 ) ,
(‘Semaine03’, ‘Dimanche’, 15 )
GO
Sélectionner *
de WeekExpense
|
SQL Average function syntax
La fonction de syntaxe AVG() ressemblera à ce qui suit dans sa forme simple :
|
1
. 2
. 3
|
SELECT AVG ( columname )
FROM TABLENAME
WHERE CONDITION
|
Le mot-clé ALL nous permet de calculer une moyenne pour toutes les valeurs du jeu de résultats et il est utilisé par défaut. Le mot-clé DISTINCT met en œuvre la fonction AVG() uniquement pour les valeurs uniques.
Exemple de la fonction AVG()
La requête suivante permet de calculer la dépense moyenne de Jean à l’aide de la fonction AVG().
|
1
2
3
… |
Select AVG(Expense) AS
FROM WeekExpense
WHERE WeekNumber = ‘Week05’
|
L’image suivante illustre la méthodologie de calcul de la fonction AVG() par utilisation par défaut.
Comme nous pouvons le voir, AVG() prend en compte toutes les valeurs des jours de la semaine et du week-end dans son calcul. De même, nous pouvons obtenir le même résultat lorsque nous ajoutons le mot-clé ALL à la syntaxe.
|
1
2
3
|
Select AVG(ALL Expense) AS
FROM WeekExpense
WHERE WeekNumber = ‘Week05’
|
Un exemple simple de la fonction AVG() avec le mot clé ALLSi nous voulons ignorer les valeurs en double lors du calcul de la fonction AVG(), nous pouvons utiliser le mot-clé DISTINCT. Après avoir exécuté la requête ci-dessous, analysons le résultat :
|
1
2
3
|
Select AVG(DISTINCT Expense) AS
FROM WeekExpense
WHERE WeekNumber = ‘Week05’
|
.
Le mot clé DISTINCT élimine les valeurs en double, par conséquent, il ne prend en compte qu’une seule des dépenses dont les valeurs sont de 20 $ dans le calcul. L’image suivante illustre essentiellement le mécanisme de fonctionnement du mot-clé DISTINCT.
Fonction moyenne SQL et valeurs NULL
La fonction AVG() ne tient pas compte des valeurs NULL lors de son calcul. Maintenant, nous allons étudier un exemple de ce problème. Dans un premier temps, nous allons mettre à jour les dépenses du dimanche comme NULL dans la table WeekExpense.
|
1
2
3
4
5
|
UPDATE WeekExpense
SET
Expense = NULL
WHERE WeekDayName = ‘Sunday’ AND
WeekNumber = ‘Week05’
|
Maintenant, nous allons exécuter la requête suivante afin de calculer la valeur moyenne.
|
1
2
3
|
Select AVG(Expense) AS
FROM WeekExpense
WHERE WeekNumber = ‘Week05’
|
Comme on peut le constater, la valeur NULL n’a pas été prise en compte par la fonction AVG() dans le calcul. L’image suivante illustre la méthode de calcul :
Si nous voulons inclure les valeurs NULL dans le calcul, nous pouvons utiliser la fonction ISNULL. La fonction ISNULL est utilisée pour changer les valeurs NULL en valeurs définies. Nous allons donc exécuter ce qui suit afin d’inclure les expressions NULL dans le calcul.
|
1
2
3
|
SELECT AVG(ISNULL(Expense,0)) AS
FROM WeekExpense
WHERE WeekNumber = ‘Week05’
|
Utilisation de la fonction AVG() avec ISNULL
L’image suivante illustre la méthode de calcul de la requête précédente :
À ce stade, nous devons remarquer ici est que nous avons inclus l’expression NULL au calcul comme 0.
Utilisation de la fonction moyenne SQL avec l’instruction GROUP BY
L’instruction GROUP BY est utilisée pour regrouper les données et elle s’utilise principalement avec les fonctions d’agrégation.
John a décidé de calculer la dépense moyenne de toutes les semaines. Pour traiter la question de John, nous devons utiliser l’instruction GROUP BY et la fonction AVG() en même temps. La requête suivante calculera la dépense moyenne par chaque semaine individuelle.
|
1
2
3
4
|
SELECT WeekNumber, AVG(Expense) AS
FROM WeekExpense
GROUPE PAR WeekNumber
ORDER BY WeekNumber DESC
|
Lorsque nous avons jeté un coup d’œil à l’ensemble des résultats de la requête, nous pouvons voir que les moyennes ont été calculées pour toutes les semaines séparément.
Introduction gratuite : Détails du plan d’exécution de la fonction Moyenne SQL
Le plan d’exécution permet de comprendre les détails d’exécution d’une requête. Lorsque nous analysons le plan d’exécution d’une requête, nous pouvons évidemment comprendre ce qui se passe dans les coulisses.
Maintenant, nous allons analyser le plan d’exécution réel de la requête suivante avec ApexSQL Plan afin de bien comprendre ce qui se passe dans les coulisses de la fonction AVG().
|
1
2
|
.
SELECT AVG(Expense) AS
FROM WeekExpense
|
Le plan d’exécution visuel de la requête est présenté ci-dessous :
Le balayage de la table lit toutes les lignes dans les tables car nous ne créons pas d’index dans cette table. Dans l’étape suivante, l’opérateur Stream aggregate calcule la somme et la valeur de comptage des expressions.
Compute scalar prend ces expressions de Stream aggregate et calcule la valeur moyenne grâce à la formule suivante.
|
1
2
3
4
5
|
CASE
WHEN = (0)
THEN NULL
ELSE / CONVERT_IMPLICIT(money, , 0)
END
|
Dans cette formule, il faut remarquer un point. Lorsque le nombre total des expressions retournera 0, le calcul de la moyenne retournera NULL. Si le nombre total d’expressions renvoie 0, la valeur moyenne sera NULL. L’intention principale de ceci est d’éviter l’erreur de division par zéro.
Conclusion
Dans cet article, nous avons appris la fonction Moyenne de SQL et renforcé notre apprentissage avec des exemples et des illustrations de base. En cours de route, nous avons discuté des détails du plan d’exécution de la fonction AVG().
- Auteur
- Messages récents
La majeure partie de sa carrière a été consacrée à l’administration et au développement de bases de données SQL Server. Il s’intéresse actuellement à l’administration des bases de données et à la Business Intelligence. Vous pouvez le trouver sur LinkedIn.
Voir tous les messages de Esat Erkec
- Mythes d’optimisation des requêtes – 23 mars, 2021
- Symptômes du reniflage des paramètres dans SQL Server – 17 mars 2021
- Utilisation de la correction automatique du plan pour l’optimisation des requêtes – 4 mars 2021
.