W tym artykule poznamy funkcję SQL Average, która w języku T-SQL znana jest jako funkcja AVG(). Funkcja AVG() jest funkcją agregującą, która oblicza średnią wartość numerycznego zbioru danych, który zwraca się z instrukcji SELECT.
Wprowadzenie
Załóżmy, że mamy kolekcję liczb. Po pierwsze, sumujemy wszystkie elementy kolekcji, a następnie dzielimy przez całkowitą liczbę elementów kolekcji. W rezultacie, otrzymana liczba będzie średnią. Wyjaśnijmy to matematyczne pojęcie na prostym przykładzie.
Jan jest studentem na uniwersytecie i postanowił zapisywać swoje wydatki każdego dnia. Poniższy wykres i tabela przedstawiają wydatki Jana z ostatniego tygodnia.
Obliczymy teraz średnie wydatki Jana z ostatniego tygodnia. Na początku zsumujemy wszystkie wydatki za wspomniany tydzień.
($20+$60+$20+$42+$10+$15+$8) = $175 to całkowita kwota wydatków za ten tydzień.
W drugim kroku podzielimy całkowitą kwotę wydatków na 7, ponieważ ta kolekcja składa się z 7 członków. Innymi słowy, tydzień składa się z siedmiu dni.
$175 / 7 = $25 to średnia wydatków w tygodniu.
Po omówieniu matematycznej koncepcji średniej, kontynuujmy naukę podstaw funkcji AVG() w SQL.
Przygotowanie danych
Z pomocą poniższego zapytania utworzymy tabelę WeekExpense, a następnie wstawimy do niej 3-tygodniowe wydatki Johna. Będziemy używać tej tabeli we wszystkich przykładach w tym artykule.
|
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)
I INSERT INTO WeekExpense
VALUES
(’Week05′,’Monday', 20 ),
(’Week05′,’Tuesday', 60 ),
(„Week05”, „Wednesday”, 20 ),
(„Week05”, „Thurusday”, 42 ),
(„Week05”, „Friday”, 10 ),
(„Week05”, „Saturday”, 15 ) ,
(„Tydzień05”, „Niedziela”, 8 ),
(„Tydzień04”, „Poniedziałek”, 29 ),
(„Tydzień04”, „Wtorek”, 17 ),
(„Tydzień04”, „środa”, 42 ),
(„Tydzień04”, „czwartek”, 11 ),
(„Tydzień04”, „piątek”, 43 ),
(„Tydzień04”, „sobota”, 10 ),
(„Tydzień04”, „niedziela”, 15 ),
(„Tydzień03”, „poniedziałek”, 10 ),
(„Week03”, „Tuesday”, 32 ),
(„Week03”, „Wednesday”, 35 ),
(„Week03”, „Thurusday”, 19 ),
(„Tydzień03”, „Piątek”, 30 ),
(„Tydzień03”, „Sobota”, 10 ),
(„Tydzień03”, „Niedziela 15 )
GO
SELECT *
FROM WeekExpense
|
Składnia funkcjiSQL Average
Składnia funkcji AVG() w swojej prostej postaci będzie wyglądała jak poniżej:
|
1
2
3
|
SELECT AVG ( columname )
FROM TABLENAME
WHERE CONDITION
|
Słowo kluczowe ALL umożliwia nam obliczenie średniej dla wszystkich wartości zbioru wyników i jest używane domyślnie. Słowo kluczowe DISTINCT implementuje funkcję AVG() tylko dla unikalnych wartości.
Przykład funkcjiAVG()
Następujące zapytanie obliczy średni wydatek Johna za pomocą funkcji AVG().
|
1
2
3
|
SELECT AVG(Expense) AS
FROM WeekExpense
WHERE WeekNumber = 'Week05′
|
Następujący obrazek ilustruje metodologię obliczeń funkcji AVG() przy domyślnym użyciu.
Jak widzimy, funkcja AVG() uwzględnia w swoich obliczeniach wszystkie wartości dni tygodnia i weekendów. Również taki sam wynik możemy uzyskać, gdy do składni dodamy słowo kluczowe ALL.
|
1
2
3
|
SELECT AVG(ALL Expense) AS
FROM WeekExpense
WHERE WeekNumber =’Week05′
|
Jeśli chcemy zignorować zduplikowane wartości podczas obliczania funkcji AVG(), możemy użyć słowa kluczowego DISTINCT. Po wykonaniu poniższego zapytania, przeanalizujmy wynik:
|
1
2
3
|
SELECT AVG(DISTINCT Expense) AS
FROM WeekExpense
WHERE WeekNumber =’Week05′
|
Słowo kluczowe DISTINCT eliminuje zduplikowane wartości, dlatego w obliczeniach brany jest pod uwagę tylko jeden z wydatków, którego wartości wynoszą $20. Poniższy obrazek w podstawowy sposób ilustruje mechanizm działania słowa kluczowego DISTINCT.
Funkcja Average w SQL a wartości NULL
Funkcja AVG() nie uwzględnia wartości NULL podczas swoich obliczeń. Przeanalizujemy teraz przykład tego problemu. Na początku zaktualizujemy niedzielne wydatki jako NULL w tabeli WeekExpense.
|
1
2
3
4
5
|
UPDATE WeekExpense
SET
Wydatki = NULL
WHERE WeekDayName. = 'Sunday' AND
WeekNumber = 'Week05′
|
Teraz, wykonamy poniższe zapytanie w celu obliczenia średniej wartości.
|
1
2
3
|
SELECT AVG(Expense) AS
FROM WeekExpense
WHERE WeekNumber =’Week05′
|
Jak widzimy, wartość NULL nie została uwzględniona przez funkcję AVG() w obliczeniach. Poniższy obrazek ilustruje sposób obliczeń:
Jeśli chcemy uwzględnić wartości NULL w obliczeniach, możemy skorzystać z funkcji ISNULL. Funkcja ISNULL służy do zamiany wartości NULL na wartości zdefiniowane. Tak więc wykonamy następujące czynności, aby włączyć wyrażenia NULL do obliczeń.
|
1
2
3
|
SELECT AVG(ISNULL(Expense,0)) AS
FROM WeekExpense
WHERE WeekNumber =’Week05′
|
Poniższy obrazek ilustruje metodę obliczeń z poprzedniego zapytania:
W tym miejscu należy zaznaczyć, że do obliczeń włączyliśmy wyrażenie NULL jako 0.
Użycie funkcji Średnia w SQL z instrukcją GROUP BY
Konstrukcja GROUP BY służy do grupowania danych i jest używana głównie z funkcjami agregującymi.
John postanowił obliczyć średnią wydatków z wszystkich tygodni. Aby rozwiązać problem Johna, musimy użyć instrukcji GROUP BY oraz funkcji AVG() w tym samym czasie. Poniższe zapytanie obliczy średnią wydatków dla każdego tygodnia z osobna.
|
1
2
3
4
|
SELECT WeekNumber, AVG(Expense) AS
FROM WeekExpense
GROUP BY WeekNumber
ORDER BY WeekNumber DESC
|
Gdy spojrzeliśmy na zestaw wyników zapytania, możemy zauważyć, że średnie zostały obliczone dla wszystkich tygodni osobno.
Podpowiedź bonusowa: Szczegóły planu wykonania funkcji SQL Average
Plan wykonania pomaga zrozumieć szczegóły wykonania zapytania. Kiedy analizujemy plan wykonania zapytania, możemy oczywiście zrozumieć, co dzieje się za kulisami.
Przeanalizujemy teraz następujący plan wykonania zapytania za pomocą ApexSQL Plan, abyśmy mogli jasno zrozumieć, co dzieje się za kulisami funkcji AVG().
|
1
2
|
SELECT AVG(Expense) AS
FROM WeekExpense
|
Wizualny plan wykonania zapytania przedstawiono jak poniżej:
Skanowanie tabeli odczytuje wszystkie wiersze w tabelach, ponieważ nie tworzymy żadnego indeksu w tej tabeli. W kolejnym kroku operator agregacji strumienia oblicza sumę i wartość licznika wyrażeń.
Compute scalar pobiera te wyrażenia z agregacji strumienia i oblicza średnią wartość poprzez następujący wzór.
|
1
2
3
4
5
|
CASE
WHEN = (0)
THEN NULL
ELSE / CONVERT_IMPLICIT(money, , 0)
END
|
W tej formule powinniśmy zwrócić uwagę na jeden punkt. Gdy całkowita liczba wyrażeń zwróci 0, to obliczenie średniej zwróci NULL. Jeśli całkowita liczba wyrażeń zwróci 0, to wartość średnia będzie NULL. Główną intencją tego jest uniknięcie błędu dzielenia przez zero.
Podsumowanie
W tym artykule poznaliśmy funkcję SQL Average i wzmocniliśmy naszą naukę podstawowymi przykładami i ilustracjami. Po drodze omówiliśmy szczegóły planu wykonania funkcji AVG().
- Autor
- Recent Posts
Większość jego kariery koncentrowała się na administracji i rozwoju baz danych SQL Server. Jego obecne zainteresowania to administracja bazami danych i Business Intelligence. Można go znaleźć na LinkedIn.
View all posts by Esat Erkec
- Mity optymalizacji zapytań – 23 marca, 2021
- Symptoms of Parameter Sniffing in SQL Server – March 17, 2021
- Using Automatic Plan Correction for Query Tuning – March 4, 2021