Verwenden von Aggregatfunktionen

  • 3 Minuten

T-SQL stellt Aggregatfunktionen wie SUM, MAX und AVG bereit, um Berechnungen durchzuführen, die mehrere Werte annehmen und ein einziges Ergebnis zurückgeben.

Arbeiten mit Aggregatfunktionen

Die meisten Abfragen, die wir untersucht haben, arbeiten mit einer Zeile gleichzeitig und verwenden eine WHERE-Klausel zum Filtern von Zeilen. Jede zurückgegebene Zeile entspricht einer Zeile im ursprünglichen Dataset.

In SQL Server werden zahlreiche Aggregatfunktionen bereitgestellt. In diesem Abschnitt untersuchen wir die gängigsten Funktionen wie SUM, MIN, MAX, AVG und COUNT.

Beim Arbeiten mit Aggregatfunktionen müssen Sie die folgenden Punkte berücksichtigen:

  • Aggregatfunktionen geben einen einzelnen (skalaren) Wert zurück und können in SELECT-Anweisungen fast überall dort verwendet werden, wo ein einzelner Wert verwendet werden kann. Diese Funktionen können beispielsweise in den SELECT-, HAVING- und ORDER BY-Klauseln verwendet werden. Sie können jedoch nicht in der WHERE-Klausel verwendet werden.
  • Aggregatfunktionen ignorieren NULL-Werte, außer bei Verwendung von COUNT(*).
  • Aggregatfunktionen in einer SELECT-Liste haben keinen Spaltenheader, es sei denn, Sie stellen einen Alias mit AS zur Verfügung.
  • Aggregatfunktionen in einer SELECT-Liste werden für alle Zeilen verwendet, die an den SELECT-Vorgang übergeben werden. Wenn keine GROUP BY-Klausel vorhanden ist, werden alle Zeilen zusammengefasst, die einen beliebigen Filter in der WHERE-Klausel erfüllen. In der nächsten Lerneinheit erfahren Sie mehr über GROUP BY.
  • Sofern Sie nicht GROUP BY verwenden, sollten Sie keine Aggregatfunktionen mit Spalten kombinieren, die nicht in Funktionen in derselben SELECT-Liste enthalten sind.

Um eine Erweiterung über die integrierten Funktionen hinaus zu ermöglichen, bietet SQL Server über die .NET Common Language Runtime (CLR) einen Mechanismus für benutzerdefinierte Aggregatfunktionen. Dieses Thema geht jedoch über den Rahmen dieses Moduls hinaus.

Integrierte Aggregatfunktionen

Wie bereits erwähnt, stellt Transact-SQL zahlreiche integrierte Aggregatfunktionen zur Verfügung. Zu den häufig verwendeten Funktionen gehören:

Funktionsname

Syntax

Beschreibung

SUM

SUM(Ausdruck)

Addiert alle numerischen Werte, die nicht NULL sind, in einer Spalte.

AVG

AVG(Ausdruck)

Bildet den Durchschnitt aller numerischen Werte, die nicht NULL sind, in einer Spalte (Summe/Anzahl).

MIN

MIN(Ausdruck)

Gibt die kleinste Zahl, das früheste Datum/die früheste Uhrzeit oder die zuerst vorkommende Zeichenfolge zurück (gemäß den Sortierregeln).

MAX

MAX(Ausdruck)

Gibt die größte Zahl, das späteste Datum/die späteste Uhrzeit oder die zuletzt vorkommende Zeichenfolge zurück (gemäß Sortierungssortierregeln).

COUNT oder COUNT_BIG

COUNT(*) oder COUNT(Ausdruck)

Mit (*) werden alle Zeilen gezählt, einschließlich Zeilen mit NULL-Werten. Wenn eine Spalte als Ausdruck angegeben wird, wird die Anzahl der Zeilen für diese Spalte zurückgegeben, die ungleich NULL sind. COUNT gibt einen int und COUNT_BIG einen big_int zurück.

Um ein integriertes Aggregat in einer SELECT-Klausel zu verwenden, betrachten Sie das folgende Beispiel in der MyStore-Beispieldatenbank:

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

Die Ergebnisse dieser Abfrage sehen ungefähr so aus:

AveragePrice

MinimumPrice

MaximumPrice

744.5952

2.2900

3578.2700

Beachten Sie, dass im Beispiel oben alle Zeilen aus der Production.Product-Tabelle zusammengefasst werden. Wir könnten die Abfrage leicht ändern, um die Durchschnitts-, Mindest- und Höchstpreise für Produkte in einer bestimmten Kategorie zurückzugeben, indem wir eine WHERE-Klausel hinzufügen, etwa so:

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product
WHERE ProductCategoryID = 15;

Bei Verwendung von Aggregaten in einer SELECT-Klausel müssen alle Spalten, auf die in der SELECT-Liste verwiesen wird, als Eingaben für eine Aggregatfunktion verwendet oder in einer GROUP BY-Klausel referenziert werden.

Betrachten Sie die folgende Abfrage, die versucht, das Feld ProductCategoryID in die aggregierten Ergebnisse aufzunehmen:

SELECT ProductCategoryID, AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

Die Ausführung dieser Abfrage führt zu folgendem Fehler:

Msg 8120, Level 16, State 1, Line 1

Column 'Production.ProductCategoryID' is invalid in the select list because it isn't contained in either an aggregate function or the GROUP BY clause (Die Spalte 'Production.ProductCategoryID' in der Auswahlliste ist ungültig, da sie nicht in einer Aggregatfunktion oder GROUP BY-Klausel enthalten ist).

Die Abfrage behandelt alle Zeilen als einzelne aggregierte Gruppe. Daher müssen alle Spalten als Eingaben zum Aggregieren von Funktionen verwendet werden.

In den vorherigen Beispielen haben wir numerische Daten aggregiert, z. B. den Preis und Mengen im vorherigen Beispiel. Einige der Aggregatfunktionen können auch verwendet werden, um Datums-, Uhrzeit- und Zeichendaten zusammenzufassen. Die folgenden Beispiele zeigen die Verwendung von Aggregaten mit Datumsangaben und Zeichen:

Diese Abfrage gibt das erste und letzte Unternehmen anhand des Namens mit MIN und MAX zurück:

SELECT MIN(CompanyName) AS MinCustomer, 
       MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;

Diese Abfrage gibt den ersten und letzten Wert für CompanyName in der Sortierungssequenz der Datenbank zurück, in diesem Fall in alphabetischer Reihenfolge:

MinCustomer

MaxCustomer

A Bike Store

Yellow Bicycle Company

Andere Funktionen können in Aggregatfunktionen geschachtelt werden.

Beispielsweise wird die YEAR-Skalarfunktion im folgenden Beispiel verwendet, um nur den Jahresteil des Bestelldatums zurückzugeben, bevor MIN und MAX ausgewertet werden:

SELECT MIN(YEAR(OrderDate)) AS Earliest,
       MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader;

Früheste

Neueste

2008

2021

Die MIN- und MAX-Funktionen können auch mit Datumsangaben verwendet werden, um die frühesten und letzten chronologischen Werte zurückzugeben. AVG und SUM können jedoch nur für numerische Daten verwendet werden, einschließlich Integer-, Währungs-, Gleitkomma- und Dezimaldatentypen.

Verwenden von DISTINCT mit Aggregatfunktionen

Beachten Sie die Verwendung von DISTINCT in einer SELECT-Klausel, um doppelt vorhandene Zeilen zu entfernen. Bei Verwendung mit einer Aggregatfunktion entfernt DISTINCT doppelt vorhandene Werte aus der Eingabespalte, bevor der Zusammenfassungswert berechnet wird. DISTINCT ist nützlich, wenn eindeutige Vorkommen von Werten zusammengefasst werden, z. B. Kunden in der Orders-Tabelle.

Im folgenden Beispiel wird die Anzahl der Kunden zurückgegeben, die Bestellungen platziert haben, und zwar unabhängig davon, wie viele Bestellungen sie aufgegeben haben:

SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Sales.SalesOrderHeader;

COUNT(<eine_Spalte>) zählt lediglich, wie viele Zeilen einen Wert in der Spalte aufweisen. Wenn keine NULL-Werte vorhanden sind, ist COUNT(<eine_Spalte>) mit COUNT(*) identisch. COUNT (DISTINCT <eine_Spalte>) zählt, wie viele verschiedene Werte in der Spalte vorhanden sind.

Verwenden von Aggregatfunktionen mit NULL

Es ist wichtig, sich des möglichen Vorhandenseins von NULL-Werten in Ihren Daten bewusst zu sein und zu wissen, wie NULL mit T-SQL-Abfragekomponenten (einschließlich Aggregatfunktionen) interagiert. Es gibt einige Aspekte, die zu beachten sind:

  • Mit Ausnahme von COUNT in Verbindung mit der Option (*) ignorieren T-SQL-Aggregatfunktionen NULL-Werte. Beispielsweise fügt eine SUM-Funktion nur Werte hinzu, die nicht NULL sind. NULL-Werte werden nicht in null ausgewertet. COUNT(*) zählt alle Zeilen, unabhängig vom Wert oder Nichtwert in einer Spalte.
  • Das Vorhandensein von NULL-Werten in einer Spalte kann zu ungenauen Berechnungen für AVG führen, da nur mit Daten aufgefüllte Zeilen addiert und diese Summe durch die Anzahl der Zeilen geteilt wird, die nicht NULL sind. Möglicherweise gibt es einen Unterschied in den Ergebnissen zwischen AVG(<Spalte>) und (SUM(<Spalte>)/COUNT(*)).

Sehen Sie sich z. B. die folgende Tabelle mit dem Namen t1 an:

C1

C2

1

NULL

2

10

3

20

4

30

5

40

6

50

Diese Abfrage veranschaulicht den Unterschied zwischen der Art, auf die AVG mit NULL umgeht, und der Art, auf die Sie einen Durchschnitt mit einer mit SUM/COUNT(*) berechneten Spalte berechnen könnten:

SELECT SUM(c2) AS sum_nonnulls, 
    COUNT(*) AS count_all_rows, 
    COUNT(c2) AS count_nonnulls, 
    AVG(c2) AS average, 
    (SUM(c2)/COUNT(*)) AS arith_average
FROM t1;

Das Ergebnis wäre:

sum_nonnulls

count_all_rows

count_nonnulls

average

arith_average

150

6

5

30

25

In diesem Resultset ist die Spalte mit dem Namen average das Aggregat, das intern die Summe von 150 erhält und durch die Anzahl der Nicht-NULL-Werte in Spalte c2 dividiert. Die Berechnung würde 150/5 oder 30 ergeben. Die Spalte arith_average dividiert die Summe explizit durch die Anzahl aller Zeilen, sodass die Berechnung 150/6 oder 25 ergibt.

Wenn Sie alle Zeilen unabhängig davon zusammenfassen müssen, ob sie NULL sind oder nicht, sollten Sie in Betracht ziehen, die NULL-Werte durch einen anderen Wert zu ersetzen, der von Ihrer Aggregatfunktion nicht ignoriert wird. Sie können dazu die COALESCE-Funktion verwenden.