Blog
Monday, 11. July 2022

Aggregatfunktionen in SQL Server

Michael
IT-Consultant

Einleitung

Aggregatfunktionen gehören zu den am häufigsten verwendeten Funktionen in allen RDBMS (Relational Database Management System). Sie sind äußerst nützlich für Berichte, Berechnungen und allgemein zum Gruppieren und Analysieren von Daten.

In diesem SQL-Tutorial werden wir die häufig verwendeten Aggregatfunktionen in SQL Server untersuchen und Unterschiede der verschiedenen Verwendungsmöglichkeiten dieser SQL-Anweisungen näher beleuchten. Da dieses Thema sehr umfangreich ist, wird dieser Artikel zweigeteilt sein – in eine Einführung und einen fortgeschrittenen Teil.

Wir werden im folgenden Artikel das auf GitHub frei verfügbare Datenbankbeispiel Chinook verwenden, das in mehreren RDBMS-Formaten verfügbar ist. Es handelt sich um eine Simulation eines digitalen Medienspeichers mit einigen Beispieldaten. Sie müssen nur die von Ihnen benötigte Version herunterladen und schon haben Sie alle Skripte für die Datenstruktur sowie alle Inserts zur Erstellung von Testdaten.

SQL Aggregatfunktionen - Einführung

Zunächst einmal: Was ist eine Aggregatfunktion in einer SQL-Datenbank? Die Definition in der offiziellen Microsoft-Dokumentation lautet wie folgt:

Eine Aggregatfunktion führt eine Berechnung auf einer Menge von Werten durch und gibt einen einzigen Wert zurück.

Schauen wir uns ein einfaches Beispiel für eine SELECT-Anweisung mit der Funktion COUNT an, um festzustellen, wie viele Rechnungen in der Rechnungstabelle enthalten sind:

SELECT COUNT(invoiceid) AS num_invoices
FROM Invoice
Ergebnis num_invoices

Hierbei handelt es sich um einen sehr einfachen Anwendungsfall, bei der wir die Anzahl der Invoice-ID zählen, die den PK (primary key) der Tabelle Invoice darstellt.

Der PRIMARY KEY identifiziert jeden Datensatz in einer Tabelle eindeutig. Primärschlüssel müssen UNIQUE-Werte enthalten und dürfen keine NULL-Werte enthalten.

Wir hätten das gleiche Ergebnis auch mit COUNT(*) erzielen können. Es gibt unzählige Blog-Artikel darüber, welche der beiden Methoden für die Leistung am besten geeignet ist: für den Rahmen dieses Artikels sind sie fast gleichwertig.

In einem zweiten Anwendungsfall wollen wir neben der Anzahl der Rechnungen auch die Summe der jeweiligen Beträge ermitteln und das jeweilige Ergebnis auf Ebene einzelner Monate darstellen. Hierbei müssen wir zusätzlich das Schlüsselwort GROUP BY hinzufügen, um die Abfrageergebnisse in Gruppen von Zeilen zu unterteilen:

SELECT CAST(MONTH(InvoiceDate) AS VARCHAR) + ' - ' + CAST(YEAR(InvoiceDate) AS VARCHAR) AS month_year, 
       COUNT(Invoiceid) AS num_invoices, 
       SUM(Total) AS Total_amount
FROM Invoice
GROUP BY CAST(MONTH(InvoiceDate) AS VARCHAR) + ' - ' + CAST(YEAR(InvoiceDate) AS VARCHAR)
Ergebnis month_year + num_invoices + Total_amount

Wir haben hier sowohl eine GROUP BY als auch eine andere sehr häufig verwendete Aggregatfunktion hinzugefügt: SUM( ). Die Gruppierung erfolgt über eine Kombination von zwei Datumsoperationen auf der Spalte InvoiceDate, die Monat und Jahr des Datums extrahiert.

So weit, so einfach: beide Aggregatfunktionen COUNT( ) und SUM( ) werden für jede Monats-Jahres-Kombination durchgeführt, so dass wir die Anzahl der Rechnungen und den gesamten Rechnungsbetrag für jede Monats-Jahres-Kombination haben.

Nun wollen wir eine weitere wichtige Option bei Aggregationen einführen: das Argument DISTINCT( ). Das heißt, die Möglichkeit, nur auf der Grundlage eindeutiger Werte zu aggregieren:

SELECT COUNT(DISTINCT invoiceid) AS Number_invoices,
       COUNT(invoiceid) AS number_lines_invoices,
       COUNT(invoicelineid) AS number_invoice_lines
FROM invoiceline
Ergebnis Number_invoices + Number_invoices_lines

Wie Sie sehen können, wendet die Verwendung des DISTINCT-Arguments die Aggregatfunktion nur auf eindeutige Werte an. Selbst wenn wir also insgesamt 2240 Rechnungsnummern in der Tabelle InvoiceLine haben, sind nur 412 davon einzigartig. Hierbei handelt es sich um einen äußerst praktischen Ansatz um doppelte Einträge in Ihrer Datenbank zu managen.

Eine weitere viel genutzte Funktion ist AVG(), die den mathematischen Durchschnitt einer Gruppe von Werten zurückgibt. Lassen Sie uns das an einem kurzen Beispiel veranschaulichen, indem wir den durchschnittlichen Rechnungsbetrag pro Jahr berechnen:

SELECT YEAR(invoicedate) AS Year_invoice, 
       COUNT(invoiceid) AS num_invoices, 
       SUM(total) AS Total_amount,
       AVG(total) AS average_amount
FROM Invoice
GROUP BY YEAR(invoicedate)
Year_invoice

Zu guter Letzt in der Liste der nützlichsten und am häufigsten verwendeten Aggregatfunktionen sind MIN( ) und MAX( ). Sehen wir uns ein Beispiel an, in dem diese beiden zur vorherigen Abfrage hinzugefügt werden:

SELECT YEAR(invoicedate) AS Year_invoice, 
       COUNT(invoiceid) AS num_invoices, 
       SUM(total) AS Total_amount, -- sum function
       AVG(total) AS average_amount, -- average value 
       MIN(total) AS minimum_invoice_amount, -- minimum value
       MAX(total) AS maximum_invoice_amount -- maximum value
FROM Invoice
GROUP BY year(invoicedate)
Year_invoice max+min

Wie der Name vermuten lässt, geben die beiden Funktionen den minimalen bzw. den maximalen Wert innerhalb eines Datensets zurück. Ihr Einsatzzweck beschränkt sich dabei nicht nur auf Zahlen, sondern sie lassen sich auch auf Datumswerte (oder sogar Strings) anwenden, so dass wir das Minimum und Maximum in einer Gruppe von Datums-/ Zeitdaten zurückgeben können:

SELECT MIN(InvoiceDate) AS Min_InvoiceDate, 
       MAX(invoicedate) AS Max_InvoiceDate
FROM Invoice
max_invoice + min_invoice

In diesem ersten Teil des Artikels, der den Aggregatfunktionen gewidmet ist, haben wir einige der am häufigsten verwendeten und nützlichsten Funktionen sowie GROUP BY und DISTINCT kennen gelernt.

Für Fortgeschrittene

In diesem zweiten Teil werden wir etwas tiefer in das Thema eindringen und HAVING sowie die Verwendung von Aggregaten mit OVER vorstellen.

Wie Sie vielleicht wissen, ist es möglich, der Aggregatfunktion mit Hilfe der Klausel HAVING einen Filter hinzuzufügen. HAVING wird in der Regel mit einer GROUP BY-Klausel verwendet. Wenn GROUP BY nicht verwendet wird, gibt es eine implizite einzelne, aggregierte Gruppe. Nehmen wir zum Beispiel an, dass wir mit dieser SELECT-Anweisung nur die Kunden extrahieren müssen, denen mindestens 40 Dollar in Rechnung gestellt wurden, sowie die Gesamtzahl der Rechnungen:

SELECT invoice.CustomerId,
       firstname + ' ' + lastname AS Customer, 
       COUNT(invoiceid) AS num_invoices, -- COUNT Function 
       SUM(total) AS Total_amount
FROM Invoice
INNER JOIN Customer 
  ON invoice.CustomerId = Customer.CustomerId
GROUP BY invoice.CustomerId, firstname + ' ' + LastName
HAVING SUM(total) >= 40;
Sehr einfache Möglichkeit, nur die höchsten Umsätze in einer SQL-Anweisung zu filtern.

Natürlich können HAVING-clauses mit AND/OR um zusätzliche Suchbedingungen erweitert werden und es ist möglich, sie in CTEs (Common Table Expression) zu verwenden, wie wir es in diesem Beispiel im UPDATE getan haben.

WITH discount AS
( SELECT Invoice.CustomerId, SUM(invoiceline.UnitPrice * Quantity) AS genretotal
  FROM invoice
  INNER JOIN InvoiceLine ON Invoice.InvoiceId = InvoiceLine.InvoiceId
  INNER JOIN Track ON Track.TrackId = InvoiceLine.TrackId
  INNER JOIN customer ON customer.CustomerId = Invoice.CustomerId
  WHERE country = 'Austria' AND GenreId IN (1,3) -- WHERE Clause
  GROUP BY Invoice.CustomerId
  HAVING SUM(invoiceline.UnitPrice * Quantity) > 20
)

UPDATE Invoice
SET total = total * 0.8
FROM Invoice
INNER JOIN discount ON Invoice.CustomerId = discount.CustomerId

In diesem Fall handelt es sich um ein Beispiel für die Anwendung eines 20%igen Rabatts mit Hilfe einer CTE, um die spezifischen Rechnungen zu extrahieren, bei denen mehr als 20 USD für bestimmte Songs bestimmter Musik-Genres für österreichische Kunden ausgegeben wurden, wobei die HAVING-Klausel zu diesem Zweck verwendet wird.

Nun wollen wir unser Spiel etwas aufpeppen und uns ein weiteres Thema ansehen, das wir in einem früheren Tipp vorgestellt haben: die OVER-clause.

Die OVER-Klausel wird zusammen mit Aggregatfunktionen oder statistischen Funktionen verwendet, um nicht auf der Grundlage eines GROUP BY zu aggregieren, sondern über eine Anzahl von Zeilen, eine gleitende Teilmenge von Daten wie ein Fenster auf einem Datensatz.

Wir möchten dies anhand eines weiteren Beispiels für eine SELECT-Anweisung veranschaulichen. Nehmen wir an, dass wir die Aufschlüsselung der Rechnungen nach Rechnungsnummer und Genre aggregiert haben möchten:

SELECT DISTINCT
   Invoiceid, genre.[Name], 
   SUM(Quantity * invoiceline.[UnitPrice]) OVER (PARTITION BY invoiceid) AS TotalInvoice, 
   SUM(Quantity * invoiceline.[UnitPrice]) OVER (PARTITION BY invoiceid, genre.[Name]) AS TotalInvoice_genre,
   COUNT(invoicelineid) OVER (PARTITION BY invoiceid) AS TotalRows,
   COUNT(invoicelineid) OVER (PARTITION BY invoiceid, genre.[Name]) AS TotalRows_genre,
   MAX(quantity) OVER (PARTITION BY invoiceid) AS MaxQuantity,
   MIN(quantity) OVER (PARTITION BY invoiceid) AS MinQuantity
FROM InvoiceLine
INNER JOIN Track ON InvoiceLine.[TrackId] = Track.[TrackId]
INNER JOIN Genre ON Track.[GenreId] = Genre.[GenreId]
Ergebnis InvoiceID +Name+ TotalInvoice

Auf diese Weise erhalten wir in ein und derselben Abfrage sowohl die Gesamtsummen pro Rechnung als auch die nach Rechnung/Gattung aggregierte Aufschlüsselung, wobei wir auch eine Höchst- und Mindestmenge auf der Rechnung hinzufügen, indem wir einfach verschiedene OVER-clause (PARTITION BY) verwenden. Das ist viel einfacher und effizienter als die Verwendung von Unterabfragen oder CTEs! Beachten Sie, dass die Aggregationsfunktionen auf alle von der Abfrage zurückgegebenen Zeilen angewendet werden, wenn PARTITION BY weggelassen wird.

Mit OVER können wir auch ORDER BY verwenden – in Kombination mit PARTITION BY oder allein. Lassen Sie uns ein weiteres Beispiel machen:

SELECT DISTINCT invoiceline.[InvoiceId], -- Distinct Values
   invoicedate,
   SUM(Quantity * invoiceline.[UnitPrice]) OVER (ORDER BY invoiceline.[invoiceid]) AS RollingTotal, -- SUM Function
   SUM(Quantity * invoiceline.[UnitPrice]) OVER (PARTITION BY invoiceline.[invoiceid] ORDER BY invoicedate) AS TotalInvoice, -- SUM Function
   COUNT(invoicelineid) OVER (PARTITION BY invoiceline.[invoiceid]) AS TotalRows
FROM InvoiceLine
INNER JOIN Track ON InvoiceLine.[TrackId] = Track.[TrackId]
INNER JOIN Invoice ON Invoiceline.[InvoiceId] = invoice.[InvoiceId]
Ergebnis invoiceID + invoicedate

Wie Sie sehen können, haben wir mit der ersten Aggregation die gleitende Gesamtsumme erhalten, also die Summe aller vorangegangenen Rechnungen inklusive der aktuellen Rechnungsnummer. Die zweite ist die Gesamtsumme der Rechnung, die wir nach der Rechnungsnummer partitioniert und nach dem Rechnungsdatum geordnet haben (die spätere Reihenfolge ist überflüssig, da die Rechnungsnummern bereits in derselben Reihenfolge sind). Zuletzt haben wir die Anzahl der Rechnungszeilen, die wie in der vorherigen Abfrage nach invoiceid aufgeteilt sind. Einfach aber sehr mächtig!

Zusammen mit Aggregationsfunktionen in OVER( )-clauses werden häufig analytische und Rangfunktionen verwendet. Die gebräuchlichsten sind LAG, LEAD, FIRST_VALUE und LAST_VALUE für die analytische Funktion und RANK und ROW_NUMBER für die Rangfunktion. Diese haben in SQL Server eine andere Klassifizierung, so dass sie nicht als Aggregatfunktionen gelten, was in Oracle anders ist.

Aber lassen Sie uns zunächst ein weiteres Beispiel mit Aggregat-, Analyse- und Rangfunktionen durchführen:

; WITH total_genre 
AS
(SELECT 
   genre.[Name] AS genre, 
   SUM(Quantity * invoiceline.[UnitPrice]) AS TotalInvoice_genre
 FROM InvoiceLine
INNER JOIN Track ON InvoiceLine.[TrackId] = Track.[TrackId]
INNER JOIN Genre ON Track.[GenreId] = Genre.[GenreId]
GROUP BY genre.[Name])

SELECT genre, 
    RANK() OVER (ORDER BY totalinvoice_genre DESC) AS ranking,
	FIRST_VALUE(genre) OVER(ORDER BY totalinvoice_genre DESC) AS First_Place
FROM total_genre
genre + ranking

Hier ist ein sehr einfaches Beispiel für die Funktionen RANK( ) und FIRST_VALUE( ), die zusammen verwendet werden, um die Rangfolge der Verkäufe nach Genre abzurufen und auch daran zu erinnern, welches Genre an erster Stelle steht, wobei eine CTE verwendet wird, um die Gesamtsumme der Rechnungen pro Genre zu erhalten.

Es gibt noch ein weiteres Beispiel mit der OVER()-Klausel unter Verwendung von Aggregatfunktionen, das ich vorstellen möchte: die Angabe von ROWS oder RANGE, um das Fenster der ausgewerteten Zeilen innerhalb einer Partition zu begrenzen.

SELECT DISTINCT genre.[Name] as Genre,
   SUM(Quantity * Invoiceline.[UnitPrice]) OVER (PARTITION BY genre.[Name] ORDER BY Invoicedate ROWS UNBOUNDED PRECEDING) AS RollingTotal_Genre,
   SUM(Quantity * Invoiceline.[UnitPrice]) OVER (PARTITION BY genre.[Name] ORDER BY Invoicelineid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS Opposite_RollingTotal_Genre
FROM InvoiceLine
INNER JOIN Track ON InvoiceLine.[TrackId] = Track.[TrackId]
INNER JOIN Genre ON Track.[GenreId] = Genre.[GenreId]
INNER JOIN Invoice ON Invoiceline.[InvoiceId] = Invoice.[InvoiceId]
genre + rolling_total_genre

In dieser Abfrage haben wir mit ROWS UNBOUNDED PRECEDING und FOLLOWING zwei verschiedene Bereiche von Zeilen definiert. Die erste gibt die fortlaufende Gesamtsumme nach Gattung zurück, die zweite tut dasselbe, aber als eine Art Countdown. Die Angabe von UNBOUNDED PRECEDING bedeutet, dass der Bereich zwischen der aktuellen Zeile und allen vorangehenden Zeilen liegt, so dass wir die fortlaufende Gesamtsumme erhalten, während die zweite Berechnung den Bereich zwischen der aktuellen Zeile und allen folgenden definiert.

Im zweiten Abschnitt des Artikels, der den Aggregatfunktionen gewidmet ist, haben wir nun die Verwendung von HAVING- und OVER-clauses und die unterschiedlichen Ergebnisse gesehen, die durch die Verwendung von ORDER BY und DISTINCT bestimmt werden. Ich hoffe, dass ich Ihnen mittels diesen Artikels die grundlegenden sowie fortgeschrittenen Aggregatfunktionen näher bringen konnte. An dieser Stelle wird zusätzlich empfohlen experimentell einige selbst definierten Abfragen zu erstellen, um ein Gefühl für das neu Erlernte zu entwickeln.

Bei Fragen stehen wir Ihnen als erfahrene SQL Server Spezialist:innen gerne im Rahmen einer kompetenten Beratung zur Verfügung. Wir freuen uns auf Ihre Kontaktaufnahme!

Interesse geweckt?

Unsere Expert:innen stehen Ihnen bei allen Fragen rund um Ihre IT Infrastruktur zur Seite.

Kontaktieren Sie uns gerne über das Kontaktformular und vereinbaren ein unverbindliches Beratungsgespräch mit unseren Berater:innen zur Bedarfsevaluierung. Gemeinsam optimieren wir Ihre Umgebung und steigern Ihre Performance!
Wir freuen uns auf Ihre Kontaktaufnahme!

Taunusstraße 72
55118 Mainz
info@madafa.de
+49 6131 3331612
Bürozeiten
Montag bis Donnerstag:
9:00 - 17:00 Uhr MEZ

Freitags:
9:30 - 14:00 Uhr MEZ