IT-Fundus

Was sind Aggregatfunktionen?

Aggregatfunktionen sind spezielle SQL-Funktionen, die auf eine Gruppe von Werten angewendet werden und einen einzelnen Wert zurückgeben. Diese Funktionen sind äußerst nützlich für Berechnungen und Analysen auf deinen Daten. Die gängigsten Aggregatfunktionen in MSSQL sind:

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()
• STRING_AGG()
• COUNT_BIG()
• GROUPING()
• VARP() und STDEVP()
• CHECKSUM_AGG()
• BITWISE_AGG()
• PERCENTILE_CONT() und PERCENTILE_DISC()
• MODE()
• FIRST_VALUE() und LAST_VALUE()
• LAG() und LEAD()
• CUME_DIST() und PERCENT_RANK()
• NTILE()
• MEDIAN()
• APPROX_COUNT_DISTINCT()
• REGR_SLOPE() und REGR_INTERCEPT()
• VARIANCE() und STDDEV()
• JSON_AGG() und JSON_OBJECTAGG()

COUNT()

Die COUNT()-Funktion zählt die Anzahl der Zeilen in einem Datensatz, die ein bestimmtes Kriterium erfüllen. Hier ein einfaches Beispiel:

ELECT COUNT(*) AS AnzahlDerMitarbeiter
FROM Mitarbeiter;

Dieser Befehl gibt die Gesamtzahl der Mitarbeiter in der Tabelle Mitarbeiter zurück. Du kannst auch die Anzahl der Zeilen zählen, die nicht-NULL-Werte in einer bestimmten Spalte enthalten:

SELECT COUNT(Gehalt) AS AnzahlMitGehalt
FROM Mitarbeiter;

SUM()

Die SUM()-Funktion berechnet die Gesamtsumme einer numerischen Spalte. Angenommen, du möchtest die Gesamtgehälter aller Mitarbeiter berechnen:


SELECT SUM(Gehalt) AS GesamtGehalt
FROM Mitarbeiter;

AVG()

Die AVG()-Funktion berechnet den Durchschnittswert einer numerischen Spalte. Um den durchschnittlichen Gehalt aller Mitarbeiter zu berechnen, verwendest du:

SELECT AVG(Gehalt) AS DurchschnittsGehalt
FROM Mitarbeiter;

MIN() und MAX()

Die MIN()– und MAX()-Funktionen geben den kleinsten bzw. größten Wert in einer Spalte zurück. Hier ein Beispiel, um das niedrigste und höchste Gehalt zu ermitteln:

SELECT MIN(Gehalt) AS MindestGehalt,
       MAX(Gehalt) AS HöchstGehalt
FROM Mitarbeiter;

Aggregatfunktionen mit GROUP BY

Aggregatfunktionen sind oft noch nützlicher, wenn sie mit der GROUP BY-Klausel kombiniert werden. Diese Klausel gruppiert die Datensätze nach einer oder mehreren Spalten und führt dann die Aggregatfunktion auf jede Gruppe aus. Hier ist ein Beispiel, wie du die Gesamtgehälter nach Abteilung berechnest:


SELECT Abteilung, SUM(Gehalt) AS GesamtGehalt
FROM Mitarbeiter
GROUP BY Abteilung;

In diesem Fall werden die Gehälter für jede Abteilung separat summiert.

HAVING-Klausel

Die HAVING-Klausel wird verwendet, um Gruppen von Datensätzen zu filtern, nachdem die GROUP BY-Klausel angewendet wurde. Hier ist ein Beispiel, das nur die Abteilungen anzeigt, deren Gesamtgehalt über 100.000 liegt:

SELECT Abteilung, SUM(Gehalt) AS GesamtGehalt
FROM Mitarbeiter
GROUP BY Abteilung
HAVING SUM(Gehalt) > 100000;

STRING_AGG()

Die STRING_AGG()-Funktion ist nützlich, um Zeichenfolgen zu kombinieren. Sie verbindet Werte aus mehreren Zeilen zu einer einzigen Zeichenfolge, getrennt durch ein angegebenes Trennzeichen. Hier ein Beispiel:

SELECT STRING_AGG(Name, ', ') AS MitarbeiterNamen
FROM Mitarbeiter;

Dies würde alle Namen aus der Mitarbeiter-Tabelle zu einer einzigen, durch Kommas getrennten Zeichenfolge kombinieren.

COUNT_BIG()

Die COUNT_BIG()-Funktion funktioniert ähnlich wie COUNT(), gibt jedoch den Ergebniswert als BIGINT zurück. Dies ist besonders nützlich bei sehr großen Datenmengen:

SELECT COUNT_BIG(*) AS GesamtZeilen
FROM SehrGrosseTabelle;

GROUPING()

Die GROUPING()-Funktion wird oft in Verbindung mit GROUP BY und ROLLUP verwendet, um zu bestimmen, ob eine Zeile aggregiert wurde:

SELECT Abteilung, COUNT(*), GROUPING(Abteilung) AS IstGruppiert
FROM Mitarbeiter
GROUP BY ROLLUP(Abteilung);

Hierbei gibt GROUPING(Abteilung) 1 zurück, wenn die Zeile durch ROLLUP aggregiert wurde, und 0, wenn nicht.

VARP() und STDEVP()

Neben VAR() und STDEV(), die die Stichprobenvarianz und -standardabweichung berechnen, berechnen VARP() und STDEVP() die Populationsvarianz und -standardabweichung:

SELECT VARP(Gehalt) AS GehaltVarianz, STDEVP(Gehalt) AS GehaltStdAbweichung
FROM Mitarbeiter;

CHECKSUM_AGG()

Die CHECKSUM_AGG()-Funktion berechnet eine Prüfsumme über eine Gruppe von Werten. Diese Funktion ist nützlich, um zu überprüfen, ob sich Daten in einer Tabelle geändert haben:

SELECT CHECKSUM_AGG(CHECKSUM(*)) AS TabellenPruefsumme
FROM Mitarbeiter;

BITWISE_AGG()

Die BITWISE_AGG()-Funktion führt eine bitweise Aggregation durch. Diese Funktion ist in speziellen Szenarien nützlich, beispielsweise bei Berechnungen über Bitfelder:

SELECT BITWISE_AGG(DISTINCT Rechte) AS RechteAggregation
FROM BenutzerRechte;

PERCENTILE_CONT() und PERCENTILE_DISC()

Diese Funktionen berechnen das Perzentil einer Verteilung. PERCENTILE_CONT() liefert kontinuierliche Werte, während PERCENTILE_DISC() diskrete Werte liefert:

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Gehalt) AS MedianGehalt
FROM Mitarbeiter;

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Gehalt) AS DiskreterMedian
FROM Mitarbeiter;

MODE()

Die MODE()-Funktion gibt den häufigsten Wert in einer Gruppe zurück:

SELECT MODE() WITHIN GROUP (ORDER BY Abteilung) AS HäufigsteAbteilung
FROM Mitarbeiter;

FIRST_VALUE() und LAST_VALUE()

Diese Funktionen geben den ersten bzw. letzten Wert einer Gruppe zurück:

SELECT FIRST_VALUE(Gehalt) OVER (ORDER BY Einstellungsdatum) AS ErstesGehalt,
       LAST_VALUE(Gehalt) OVER (ORDER BY Einstellungsdatum) AS LetztesGehalt
FROM Mitarbeiter;

LAG() und LEAD()

Diese Funktionen ermöglichen es dir, auf vorherige oder nachfolgende Zeilen innerhalb einer Partition zuzugreifen, ohne dass eine selbstverknüpfende Abfrage erforderlich ist:

SELECT Name, Gehalt,
       LAG(Gehalt, 1) OVER (ORDER BY Einstellungsdatum) AS VorherigesGehalt,
       LEAD(Gehalt, 1) OVER (ORDER BY Einstellungsdatum) AS NachfolgendesGehalt
FROM Mitarbeiter;

CUME_DIST() und PERCENT_RANK()

Diese Funktionen berechnen die kumulative Verteilungsfunktion und den prozentualen Rang einer Zeile innerhalb einer Gruppe:

SELECT Name, Gehalt,
       CUME_DIST() OVER (ORDER BY Gehalt) AS KumulativeVerteilung,
       PERCENT_RANK() OVER (ORDER BY Gehalt) AS ProzentRang
FROM Mitarbeiter;

NTILE()

Die NTILE()-Funktion teilt die Zeilen in eine definierte Anzahl von Gruppen ein und nummeriert sie. Dies ist nützlich, um Quartile, Quintile usw. zu berechnen:

SELECT Name, Gehalt,
       NTILE(4) OVER (ORDER BY Gehalt) AS Quartil
FROM Mitarbeiter;

MEDIAN()

Die MEDIAN()-Funktion berechnet den Medianwert einer Gruppe von Werten. Diese Funktion ist ab SQL Server 2016 verfügbar:

SELECT MEDIAN(Gehalt) AS MedianGehalt
FROM Mitarbeiter;

APPROX_COUNT_DISTINCT()

Die APPROX_COUNT_DISTINCT()-Funktion gibt eine ungefähre Anzahl unterschiedlicher Werte zurück. Diese Funktion ist schneller und ressourcenschonender als COUNT(DISTINCT Spalte), besonders bei großen Datenmengen:

SELECT APPROX_COUNT_DISTINCT(Gehalt) AS UngefaehreAnzahlUnterschiedlicherGehalt
FROM Mitarbeiter;

REGR_SLOPE() und REGR_INTERCEPT()

Diese Funktionen sind Teil der Regressionsanalyse und geben die Steigung und den Achsenabschnitt der linearen Regression zurück:

SELECT REGR_SLOPE(Gehalt, Dienstjahre) AS RegressionsSteigung,
       REGR_INTERCEPT(Gehalt, Dienstjahre) AS RegressionsAchsenabschnitt
FROM Mitarbeiter;

VARIANCE() und STDDEV()

VARIANCE() und STDDEV() sind alternative Namen für VAR() und STDEV() und bieten die gleiche Funktionalität:

SELECT VARIANCE(Gehalt) AS GehaltsVarianz,
       STDDEV(Gehalt) AS GehaltsStandardabweichung
FROM Mitarbeiter;

JSON_AGG() und JSON_OBJECTAGG()

Diese Funktionen ermöglichen es dir, JSON-Objekte und Arrays zu aggregieren:

SELECT JSON_AGG(Name) AS NamenAlsJSON
FROM Mitarbeiter;

SELECT JSON_OBJECTAGG(Name, Gehalt) AS GehaltNachNameAlsJSON
FROM Mitarbeiter;

Fazit

Aggregatfunktionen sind ein mächtiges Werkzeug in MSSQL, das dir hilft, wertvolle Einblicke aus deinen Daten zu gewinnen. Ob du Summen, Durchschnitte, Minima, Maxima oder Zählungen benötigst – mit diesen Funktionen kannst du deine Daten auf eine neue Ebene heben. Die Kombination mit GROUP BY und HAVING macht sie noch flexibler und leistungsfähiger. Experimentiere mit diesen Funktionen und entdecke, wie sie deine SQL-Abfragen bereichern können.