In diesem Artikel lernen wir die SQL-Durchschnittsfunktion kennen, die in T-SQL als AVG() Funktion bekannt ist. Die AVG()-Funktion ist eine Aggregatfunktion, die den Durchschnittswert eines numerischen Datensatzes berechnet, der von der SELECT-Anweisung zurückgegeben wird.
Einführung
Angenommen, wir haben eine Sammlung von Zahlen. Zunächst addieren wir alle Mitglieder der Sammlung und dividieren dann die Gesamtzahl der Mitglieder der Sammlung. Das Ergebnis ist der Durchschnitt der erhaltenen Zahl. Lassen Sie uns diesen mathematischen Begriff anhand eines einfachen Beispiels erklären.
John ist Student an der Universität und beschließt, jeden Tag seine Ausgaben aufzuzeichnen. Das folgende Diagramm und die Tabelle stellen Johns Ausgaben der letzten Woche dar.
Nun berechnen wir die durchschnittlichen Ausgaben von John der letzten Woche. Zuerst addieren wir alle Ausgaben für die oben genannte Woche.
($20+$60+$20+$42+$10+$15+$8) = $175 ist der Gesamtbetrag der Ausgaben für die Woche.
Im zweiten Schritt teilen wir den Gesamtbetrag der Ausgaben durch 7, da diese Sammlung aus 7 Mitgliedern besteht. Mit anderen Worten, eine Woche besteht aus sieben Tagen.
$175 / 7 = $25 ist der Durchschnittsbetrag der Ausgaben der Woche.
Nachdem wir das mathematische Konzept des Durchschnitts besprochen haben, fahren wir damit fort, die Grundlagen der Funktion AVG() in SQL zu lernen.
Datenvorbereitung
Mit Hilfe der folgenden Abfrage erstellen wir die Tabelle WeekExpense und fügen dann die Ausgaben von John für 3 Wochen ein. Diese Tabelle werden wir in allen Beispielen dieses Artikels verwenden.
|
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 ),
( ‚Woche05′,’Mittwoch‘, 20 ),
( ‚Woche05′,’Donnerstag‘, 42 ),
( ‚Woche05′,’Freitag‘, 10 ),
( ‚Woche05′,’Samstag‘, 15 ) ,
( ‚Woche05′,’Sonntag‘, 8 ),
( ‚Woche04′,’Montag‘, 29 ),
( ‚Woche04′,’Dienstag‘, 17 ),
( ‚Woche04′,’Mittwoch‘, 42 ),
( ‚Woche04′,’Donnerstag‘, 11 ),
( ‚Woche04′,’Freitag‘, 43 ),
( ‚Woche04′,’Samstag‘, 10 ),
( ‚Woche04′,’Sonntag‘, 15 ),
(‚Woche03′,’Montag‘, 10 ),
( ‚Woche03′,’Dienstag‘, 32 ),
( ‚Woche03′,’Mittwoch‘, 35 ),
( ‚Woche03′,’Donnerstag‘, 19 ),
( ‚Woche03′,’Freitag‘, 30 ),
( ‚Woche03′,’Samstag‘, 10 ) ,
( ‚Woche03′,’Sonntag‘, 15 )
GO
SELECT *
FROM WeekExpense
|
SQL Average function syntax
Die AVG()-Syntax-Funktion sieht in ihrer einfachen Form wie folgt aus:
|
1
2
3
|
SELECT AVG ( Spaltenname )
FROM TABLENAME
WHERE CONDITION
|
Das Schlüsselwort ALL ermöglicht die Berechnung eines Durchschnitts für alle Werte der Ergebnismenge und wird standardmäßig verwendet. Das Schlüsselwort DISTINCT implementiert die Funktion AVG() nur für eindeutige Werte.
AVG() Funktionsbeispiel
Die folgende Abfrage berechnet die durchschnittlichen Ausgaben von John mit Hilfe der Funktion AVG().
|
1
2
3
|
SELECT AVG(Aufwand) AS
FROM WeekExpense
WHERE WeekNumber = ‚Week05‘
|
Das folgende Bild veranschaulicht die Berechnungsmethodik der Funktion AVG() bei standardmäßiger Verwendung.
Wie wir sehen können, berücksichtigt AVG() in seiner Berechnung alle Wochentags- und Wochenendwerte. Das gleiche Ergebnis erhalten wir auch, wenn wir das Schlüsselwort ALL zur Syntax hinzufügen.
|
1
2
3
|
SELECT AVG(ALLE Ausgaben) AS
FROM WeekExpense
WHERE WeekNumber =’Week05′
|
Wenn wir doppelte Werte bei der Berechnung der Funktion AVG() ignorieren wollen, können wir das Schlüsselwort DISTINCT verwenden. Nachdem wir die folgende Abfrage ausgeführt haben, wollen wir das Ergebnis analysieren:
|
1
2
3
|
SELECT AVG(DISTINCT Expense) AS
FROM WeekExpense
WHERE WeekNumber =’Week05′
|
Das Schlüsselwort DISTINCT eliminiert doppelte Werte, daher wird nur eine der Ausgaben, deren Werte $20 sind, in der Berechnung berücksichtigt. Das folgende Bild veranschaulicht im Wesentlichen die Funktionsweise des Schlüsselworts DISTINCT.
SQL-Durchschnittsfunktion und NULL-Werte
Die Funktion AVG() berücksichtigt die NULL-Werte bei ihrer Berechnung nicht. Wir werden nun ein Beispiel für dieses Problem untersuchen. Zuerst werden wir die Sonntagsausgaben als NULL in der Tabelle WeekExpense aktualisieren.
|
1
2
3
4
5
|
UPDATE WeekExpense
SET
Expense = NULL
WHERE WeekDayName = ‚Sunday‘ AND
WeekNumber = ‚Week05‘
|
Jetzt, führen wir die folgende Abfrage aus, um den Durchschnittswert zu berechnen.
|
1
2
3
|
SELECT AVG(Aufwand) AS
FROM WeekExpense
WHERE Wochennummer =’Woche05′
|
Wie wir sehen können, wurde der NULL-Wert von der Funktion AVG() bei der Berechnung nicht berücksichtigt. Das folgende Bild veranschaulicht die Berechnungsmethode:
Wenn wir die NULL-Werte in die Berechnung einbeziehen wollen, können wir die ISNULL-Funktion verwenden. Die ISNULL-Funktion wird verwendet, um die NULL-Werte in die definierten Werte zu ändern. Wir werden also folgendes ausführen, um NULL-Ausdrücke in die Berechnung einzubeziehen.
|
1
2
3
|
SELECT AVG(ISNULL(Expense,0)) AS
FROM WochenAusgaben
WHERE Wochennummer =’Woche05′
|
Das folgende Bild verdeutlicht die Berechnungsmethode der vorherigen Abfrage:
An dieser Stelle sei angemerkt, dass wir den NULL-Ausdruck als 0 in die Berechnung einbezogen haben.
Verwendung der SQL-Durchschnittsfunktion mit der GROUP BY-Anweisung
Die GROUP BY-Anweisung wird zum Gruppieren der Daten verwendet und wird hauptsächlich mit Aggregatfunktionen verwendet.
John hat sich entschieden, die durchschnittlichen Ausgaben aller Wochen zu berechnen. Um Johns Problem zu lösen, müssen wir die GROUP BY-Anweisung und die Funktion AVG() gleichzeitig verwenden. Die folgende Abfrage wird die durchschnittlichen Ausgaben für jede einzelne Woche berechnen.
|
1
2
3
4
|
SELECT WeekNumber, AVG(Aufwand) AS
FROM WochenAufwand
GROUP BY Wochennummer
ORDER BY Wochennummer DESC
|
Wenn wir einen Blick auf die Ergebnismenge der Abfrage geworfen haben, können wir sehen, dass die Durchschnitte für alle Wochen separat berechnet wurden.
Bonustipp: Ausführungsplan-Details der SQL-Durchschnittsfunktion
Der Ausführungsplan hilft, die Ausführungsdetails einer Abfrage zu verstehen. Wenn wir den Ausführungsplan einer Abfrage analysieren, können wir offensichtlich verstehen, was hinter den Kulissen passiert.
Nun werden wir die folgende Abfrage tatsächlichen Ausführungsplan mit ApexSQL Plan analysieren, so dass wir klar verstehen können, was hinter den Kulissen der Funktion AVG() passiert.
|
1
2
|
SELECT AVG(Ausgaben) AS
FROM WochenAusgaben
|
Der visuelle Ausführungsplan der Abfrage ist wie folgt dargestellt:
Der Tabellenscan liest alle Zeilen in den Tabellen, da wir keinen Index in dieser Tabelle erstellen. Im nächsten Schritt berechnet der Stream-Aggregat-Operator die Summe und den Zählwert der Ausdrücke.
Skalar berechnen nimmt diese Ausdrücke von Stream-Aggregat und berechnet den Durchschnittswert durch die folgende Formel.
|
1
2
3
4
5
|
CASE
WHEN = (0)
THEN NULL
ELSE / CONVERT_IMPLICIT(Geld, , 0)
END
|
In dieser Formel sollten wir einen Punkt anmerken. Wenn die Gesamtzahl der Ausdrücke 0 ergibt, wird die Durchschnittsberechnung NULL zurückgeben. Wenn die Gesamtzahl der Ausdrücke 0 zurückgibt, wird der Durchschnittswert NULL sein. Der Hauptzweck dieser Funktion ist es, den Fehler „Teilen durch Null“ zu vermeiden.
Abschluss
In diesem Artikel haben wir die SQL-Durchschnittsfunktion kennengelernt und das Gelernte mit einfachen Beispielen und Illustrationen gefestigt. Nebenbei haben wir die Details des Ausführungsplans der Funktion AVG() besprochen.
- Autor
- Recent Posts
Die meiste Zeit seiner Karriere konzentrierte er sich auf SQL Server Datenbankadministration und -entwicklung. Seine aktuellen Interessen liegen in der Datenbankadministration und Business Intelligence. Sie können ihn auf LinkedIn finden.
Alle Beiträge von Esat Erkec anzeigen
- Mythen zur Abfrageoptimierung – 23. März, 2021
- Symptome von Parameter-Sniffing in SQL Server – 17. März 2021
- Verwendung der automatischen Plankorrektur für Abfrageoptimierung – 4. März 2021