In questo articolo, impareremo la funzione SQL Average che è conosciuta come funzione AVG() in T-SQL. La funzione AVG() è una funzione aggregata che calcola il valore medio di un set di dati numerici che ritorna dall’istruzione SELECT.
Introduzione
Supponiamo di avere una collezione di numeri. In primo luogo, sommiamo tutti i membri della collezione e poi dividiamo il numero totale dei membri della collezione. Come risultato, il numero ottenuto sarà la media. Spieghiamo questa nozione matematica con un esempio semplice.
John è uno studente universitario e decide di registrare le sue spese ogni giorno. Il grafico e la tabella seguenti rappresentano le spese di John dell’ultima settimana.
Ora, calcoleremo la spesa media dell’ultima settimana di John. All’inizio, sommeremo tutte le spese per la suddetta settimana.
($20+$60+$20+$42+$10+$15+$8) = $175 è l’importo totale delle spese per la settimana.
Nel secondo passo, divideremo l’importo totale delle spese a 7 perché questa collezione è formata da 7 membri. In altre parole, una settimana consiste di sette giorni.
$175 / 7 = $25 è la spesa media della settimana.
Dopo aver discusso il concetto matematico della media, continuiamo ad imparare le basi della funzione AVG() in SQL.
Preparazione dei dati
Con l’aiuto della seguente query, creeremo la tabella WeekExpense e poi inseriremo le 3 settimane di spesa di John. Useremo questa tabella in tutti gli esempi di questo articolo.
|
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′,’Lunedì’, 20 ),
(‘Week05′,’Martedì’, 60 ),
(‘Week05′,’Mercoledì’, 20 ),
( ‘Week05′,’Giovedì’, 42 ),
( ‘Week05′,’Venerdì’, 10 ),
( ‘Week05′,’Sabato’, 15 ) ,
(‘Week05′,’Domenica’, 8 ),
(‘Week04′,’Lunedì’, 29 ),
(‘Week04′,’Martedì’, 17 )
(‘Week04′,’Mercoledì’, 42 ),
(‘Week04′,’Giovedì’, 11 ),
(‘Week04′,’Venerdì’, 43 ),
(‘Week04′,’Sabato’, 10 ) ,
( ‘Week04′,’Domenica’, 15 ),
(‘Week03′,’Lunedì’, 10 ),
(‘Week03′,’Martedì’, 32 ),
(‘Week03′,’Mercoledì’, 35 ),
(‘Week03′,’Giovedì’, 19 ),
( ‘Week03′,’Venerdì’, 30 ),
( ‘Week03′,’Sabato’, 10 ) ,
( ‘Week03′,’Domenica’, 15 )
GO
SELECT *
FROM WeekExpense
|
SQL Sintassi della funzione Average
La sintassi della funzione AVG() sarà simile alla seguente nella sua forma semplice:
|
1
2
3
|
SELECT AVG ( columname )
FROM TABLENAME
WHERE CONDITION
|
La parola chiave ALL ci permette di calcolare una media per tutti i valori del set di risultati e viene utilizzata per default. La parola chiave DISTINCT implementa la funzione AVG() solo per i valori unici.
esempio della funzione AVG()
La seguente query calcolerà la spesa media di John con l’aiuto della funzione AVG().
|
1
2
3
|
SELECT AVG(Expense) AS
FROM WeekExpense
WHERE WeekNumber = ‘Week05’
|
L’immagine seguente illustra la metodologia di calcolo della funzione AVG() con un utilizzo predefinito.
Come possiamo vedere, AVG() considera tutti i valori dei giorni della settimana e dei fine settimana nel suo calcolo. Inoltre, possiamo ottenere lo stesso risultato quando aggiungiamo la parola chiave ALL alla sintassi.
|
1
2
3
|
SELECT AVG(ALL Expense) AS
FROM WeekExpense
WHERE WeekNumber =’Week05′
|
Se vogliamo ignorare i valori duplicati durante il calcolo della funzione AVG(), possiamo usare la parola chiave DISTINCT. Dopo aver eseguito la query qui sotto, analizziamo il risultato:
|
1
2
3
|
SELECT AVG(DISTINCT Expense) AS
FROM WeekExpense
WHERE WeekNumber =’Week05′
|
La parola chiave DISTINCT elimina i valori duplicati, quindi, prende in considerazione solo una delle spese i cui valori sono $20 nel calcolo. L’immagine seguente illustra fondamentalmente il meccanismo di funzionamento della parola chiave DISTINCT.
Funzione media SQL e valori NULL
La funzione AVG() non considera i valori NULL durante il suo calcolo. Ora studieremo un esempio di questo problema. All’inizio, aggiorneremo le spese della domenica come NULL nella tabella WeekExpense.
|
1
2
3
4
5
|
UPDATE WeekExpense
SET
Expense = NULL
WHERE WeekDayName = ‘Domenica’ AND
WeekNumber = ‘Week05’
|
Ora, eseguiremo la seguente query per calcolare il valore medio.
|
1
2
3
|
SELECT AVG(Expense) AS
FROM WeekExpense
WHERE WeekNumber =’Week05′
|
Come possiamo vedere, il valore NULL non è stato preso in considerazione dalla funzione AVG() nel calcolo. L’immagine seguente illustra il metodo di calcolo:
Se vogliamo includere i valori NULL nel calcolo, possiamo usare la funzione ISNULL. La funzione ISNULL è utilizzata per cambiare i valori NULL nei valori definiti. Quindi eseguiremo quanto segue per includere le espressioni NULL nel calcolo.
|
1
2
3
|
SELECT AVG(ISNULL(Spese,0)) AS
FROM WeekExpense
WHERE WeekNumber =’Week05′
|
L’immagine seguente illustra il metodo di calcolo della query precedente:
A questo punto, dobbiamo notare che abbiamo incluso l’espressione NULL al calcolo come 0.
Uso della funzione SQL Average con l’istruzione GROUP BY
L’istruzione GROUP BY è usata per raggruppare i dati e si usa principalmente con le funzioni aggregate.
John ha deciso di calcolare la spesa media di tutte le settimane. Per gestire il problema di John, abbiamo bisogno di usare l’istruzione GROUP BY e la funzione AVG() allo stesso tempo. La seguente query calcolerà la spesa media per ogni singola settimana.
|
1
2
3
4
|
SELECT WeekNumber, AVG(Expense) AS
FROM WeekExpense
GROUP BY WeekNumber
ORDER BY WeekNumber DESC
|
Quando abbiamo dato un’occhiata al set di risultati della query, possiamo vedere che le medie sono state calcolate per tutte le settimane separatamente.
Suggerimento bonus: Dettagli del piano di esecuzione della funzione SQL Average
Il piano di esecuzione aiuta a capire i dettagli di esecuzione di una query. Quando analizziamo il piano di esecuzione di una query, possiamo ovviamente capire cosa succede dietro le quinte.
Ora, analizzeremo il piano di esecuzione effettivo della seguente query con ApexSQL Plan in modo da poter capire chiaramente cosa succede dietro le quinte della funzione AVG().
|
1
2
|
SELECT AVG(Expense) AS
FROM WeekExpense
|
Il piano di esecuzione visivo della query è mostrato di seguito:
La scansione della tabella legge tutte le righe nelle tabelle perché non creiamo alcun indice in questa tabella. Nel passo successivo, l’operatore di aggregazione Stream calcola la somma e il valore di conteggio delle espressioni.
Compute scalar prende queste espressioni da Stream aggregate e calcola il valore medio attraverso la seguente formula.
|
1
2
3
4
5
|
CASE
WHEN = (0)
THEN NULL
ELSE / CONVERT_IMPLICIT(money, , 0)
END
|
In questa formula, dovremmo notare un punto. Quando il numero totale delle espressioni restituisce 0, il calcolo della media restituisce NULL. Se il numero totale di espressioni restituisce 0, il valore medio sarà NULL. L’intenzione principale di questo è di evitare l’errore di divisione per zero.
Conclusione
In questo articolo, abbiamo imparato la funzione SQL Average e rafforzato il nostro apprendimento con esempi e illustrazioni di base. Lungo il percorso, abbiamo discusso i dettagli del piano di esecuzione della funzione AVG().
- Autore
- Post recenti
La maggior parte della sua carriera si è concentrata sull’amministrazione e lo sviluppo di database SQL Server. I suoi interessi attuali sono nell’amministrazione di database e nella Business Intelligence. Potete trovarlo su LinkedIn.
Vedi tutti i post di Esat Erkec
- Miti di ottimizzazione delle query – 23 marzo, 2021
- Sintomi del Parameter Sniffing in SQL Server – 17 marzo 2021
- Utilizzo della correzione automatica del piano per l’ottimizzazione delle query – 4 marzo 2021