11 Aggregatfunktionen

In den vorherigen Kapiteln haben wir uns angesehen, wie man mit SQL Daten aus einer oder mehreren Tabellen abfragt und filtert. Oft reicht es aber nicht aus, nur einzelne Datensätze zu betrachten. Für viele Analysen benötigen wir zusammenfassende Statistiken und Kennzahlen über Gruppen von Datensätzen. Hierfür bietet SQL leistungsstarke Aggregatfunktionen und die Möglichkeit, Datensätze flexibel zu gruppieren.

11.1 Statistisches mit Aggregatfunktionen

Aggregatfunktionen führen Berechnungen über mehrere Datensätze durch. Sie fassen Werte aus mehreren Zeilen zu einem einzigen Ergebniswert zusammen.

-- Anzahl von Datensätzen
SELECT COUNT(*) FROM mitarbeiter;
SELECT COUNT(telefon) FROM mitarbeiter;  -- Ohne NULL-Werte

-- Summe
SELECT SUM(gehalt) FROM mitarbeiter;
SELECT SUM(menge * preis) AS gesamtumsatz FROM bestellungen;

-- Durchschnitt
SELECT AVG(gehalt) FROM mitarbeiter;
SELECT AVG(DATEDIFF(lieferdatum, bestelldatum)) AS lieferzeit FROM bestellungen;

-- Minimum und Maximum
SELECT 
    MIN(gehalt) AS niedrigstes_gehalt,
    MAX(gehalt) AS hoechstes_gehalt
FROM mitarbeiter;

-- Kombination mehrerer Funktionen
SELECT 
    COUNT(*) AS anzahl,
    SUM(betrag) AS gesamtumsatz,
    AVG(betrag) AS durchschnitt,
    MIN(betrag) AS minimum,
    MAX(betrag) AS maximum
FROM bestellungen;

Die wichtigsten Aggregatfunktionen sind:

• COUNT: Zählt die Anzahl der Datensätze. Mit COUNT(*) werden alle Zeilen gezählt, mit COUNT(spalte) nur Zeilen, in denen spalte nicht NULL ist.
• SUM: Berechnet die Summe aller Werte einer Spalte. Kann auch mit Ausdrücken wie SUM(menge * preis) verwendet werden.
• AVG: Berechnet den Durchschnitt (das arithmetische Mittel) aller Werte einer Spalte.
• MIN und MAX: Finden den kleinsten bzw. größten Wert einer Spalte.

Aggregatfunktionen ignorieren NULL-Werte. Sie können in einem SELECT mehrere Aggregatfunktionen kombinieren, um verschiedene Kennzahlen gleichzeitig zu berechnen.

11.2 Tabelle in Gruppen zerlegen

Oft möchte man Statistiken nicht für die gesamte Tabelle, sondern für Gruppen von Datensätzen berechnen. Beispielsweise den Umsatz pro Kunde oder die Anzahl der Mitarbeiter pro Abteilung. Hierfür verwendet man die GROUP BY-Klausel.

-- Grundlegende Gruppierung
SELECT abteilung, COUNT(*) AS anzahl
FROM mitarbeiter
GROUP BY abteilung;

-- Mehrere Aggregatfunktionen
SELECT 
    abteilung,
    COUNT(*) AS anzahl_mitarbeiter,
    AVG(gehalt) AS durchschnittsgehalt,
    SUM(gehalt) AS gehaltskosten
FROM mitarbeiter
GROUP BY abteilung;

-- Mehrere Gruppierungsspalten
SELECT 
    land,
    stadt,
    COUNT(*) AS anzahl_kunden,
    SUM(umsatz) AS gesamtumsatz
FROM kunden
GROUP BY land, stadt;

-- Mit Sortierung
SELECT 
    abteilung,
    COUNT(*) AS anzahl
FROM mitarbeiter
GROUP BY abteilung
ORDER BY anzahl DESC;

GROUP BY teilt die Datensätze anhand der angegebenen Spalten in Gruppen ein. Für jede entstehende Gruppe wird eine Zeile im Ergebnis erzeugt. Aggregatfunktionen in der SELECT-Klausel werden dann für jede Gruppe separat berechnet.

Wichtige Punkte zu GROUP BY:

• In der SELECT-Klausel dürfen nur Spalten stehen, die auch in der GROUP BY-Klausel vorkommen, sowie Aggregatfunktionen.
• Mit GROUP BY spalte1, spalte2, ... kann nach mehreren Spalten gruppiert werden. Dadurch entstehen Untergruppen.
• Die Ergebnismenge kann mit ORDER BY nach den gruppierten Spalten oder Aggregatfunktionen sortiert werden.

11.3 Gruppenergebnisse filtern

Mit GROUP BY erhält man Ergebniszeilen für alle Gruppen - auch solche, die vielleicht nicht interessant sind, weil sie zu wenige Datensätze enthalten oder bestimmte Bedingungen nicht erfüllen. Mit der HAVING-Klausel kann man Gruppen anhand der Ergebnisse der Aggregatfunktionen filtern.

-- Grundlegende Filterung
SELECT 
    abteilung,
    COUNT(*) AS anzahl
FROM mitarbeiter
GROUP BY abteilung
HAVING anzahl > 10;

-- Komplexe Bedingungen
SELECT 
    kunde_id,
    COUNT(*) AS bestellungen,
    SUM(betrag) AS gesamtumsatz,
    AVG(betrag) AS durchschnitt
FROM bestellungen
GROUP BY kunde_id
HAVING 
    bestellungen >= 5 
    AND gesamtumsatz > 1000;

-- Kombination mit WHERE
SELECT 
    abteilung,
    COUNT(*) AS anzahl,
    AVG(gehalt) AS durchschnittsgehalt
FROM mitarbeiter
WHERE aktiv = 1
GROUP BY abteilung
HAVING 
    anzahl >= 5 
    AND durchschnittsgehalt > 50000;

HAVING verhält sich ähnlich wie WHERE, mit dem Unterschied, dass es auf die Ergebnisse der Aggregatfunktionen angewendet wird, nachdem die Gruppierung durchgeführt wurde. In der HAVING-Klausel können Aliase verwendet werden, die in der SELECT-Klausel definiert wurden.

Beachten Sie den Unterschied zwischen WHERE und HAVING:

• WHERE filtert einzelne Datensätze vor der Gruppierung.
• HAVING filtert Gruppen nach der Gruppierung anhand von Aggregatfunktionen.

Beide Klauseln können kombiniert werden, um sowohl Datensätze als auch Gruppen zu filtern.

11.3.1 Kann ich nach Ausdrücken gruppieren?

Ja, die Gruppierung muss nicht auf einzelne Spalten beschränkt sein. Es können auch Ausdrücke und Funktionen verwendet werden, solange sie für jeden Datensatz einen definierten Wert liefern.

-- Nach Monat gruppieren
SELECT 
    MONTH(bestelldatum) AS monat,
    COUNT(*) AS anzahl,
    SUM(betrag) AS umsatz
FROM bestellungen
GROUP BY MONTH(bestelldatum);

-- Nach Kategorien gruppieren
SELECT 
    CASE 
        WHEN alter < 30 THEN 'Jung'
        WHEN alter < 50 THEN 'Mittel'
        ELSE 'Senior'
    END AS altersgruppe,
    COUNT(*) AS anzahl
FROM mitarbeiter
GROUP BY altersgruppe;

Im ersten Beispiel wird die MONTH-Funktion verwendet, um aus dem Bestelldatum den Monat zu extrahieren. Die Gruppierung erfolgt dann nach diesen Monatswerten.

Im zweiten Beispiel wird mit CASE eine Alterskategorie berechnet. Die Gruppierung erfolgt nach diesen Kategorien.

Beachten Sie, dass Ausdrücke und Funktionen, die in der GROUP BY-Klausel verwendet werden, auch in der SELECT-Klausel stehen müssen.

11.3.2 Kann ich nach mehr als einer Spalte gruppieren?

Ja, Sie können nach beliebig vielen Spalten gruppieren. Jede zusätzliche Spalte in der GROUP BY-Klausel erzeugt eine weitere Ebene von Untergruppen.

-- Zweistufige Gruppierung
SELECT 
    land,
    stadt,
    COUNT(*) AS anzahl
FROM kunden
GROUP BY land, stadt;

-- Mehrstufige Analyse
SELECT 
    YEAR(bestelldatum) AS jahr,
    MONTH(bestelldatum) AS monat,
    produkt_kategorie,
    COUNT(*) AS anzahl,
    SUM(betrag) AS umsatz
FROM bestellungen b
JOIN produkte p ON b.produkt_id = p.id
GROUP BY 
    YEAR(bestelldatum),
    MONTH(bestelldatum),
    produkt_kategorie
ORDER BY 
    jahr, 
    monat;

Im ersten Beispiel wird zunächst nach Land gruppiert. Innerhalb jedes Landes wird dann nach Stadt untergruppiert. So erhält man für jede Kombination aus Land und Stadt eine Ergebniszeile mit der Anzahl der Kunden.

Das zweite Beispiel zeigt eine komplexe Analyse mit Gruppierung nach Jahr, Monat und Produktkategorie. Für jede Kombination dieser Werte werden Anzahl und Umsatz der Bestellungen berechnet. Die Ergebnisse werden hierarchisch nach Jahr und Monat sortiert.

Beachten Sie, dass die Anzahl der Ergebniszeilen mit jeder zusätzlichen Gruppierungsebene steigt. Große Gruppierungen können zu langen Laufzeiten führen.

11.3.3 Wie kann ich GROUP BY beschleunigen?

Gruppierungen können bei großen Datenmengen ressourcenintensiv sein. Hier sind einige Tipps zur Optimierung:

-- Index für Gruppierungsspalten
CREATE INDEX idx_abteilung_gehalt 
ON mitarbeiter(abteilung, gehalt);

-- Temporäre Tabelle für Zwischenergebnisse
CREATE TEMPORARY TABLE temp_stats AS
SELECT 
    abteilung,
    COUNT(*) AS anzahl,
    AVG(gehalt) AS durchschnittsgehalt
FROM mitarbeiter
GROUP BY abteilung;

• Erstellen Sie Indizes für die Spalten, die in der GROUP BY-Klausel verwendet werden. Dies beschleunigt die Gruppierung, da die Datenbank die Datensätze nicht mehr vollständig durchsuchen muss.
• Verwenden Sie temporäre Tabellen, um Zwischenergebnisse zu speichern, insbesondere bei komplexen Gruppierungen oder großen Datenmengen. Dadurch müssen die Berechnungen nicht mehrfach durchgeführt werden.

Praktische Tipps für Auswertungen:

1. WHERE vor GROUP BY:

-- Effizienter
SELECT abteilung, COUNT(*) 
FROM mitarbeiter
WHERE gehalt > 50000
GROUP BY abteilung;

-- Weniger effizient
SELECT abteilung, COUNT(*) 
FROM mitarbeiter
GROUP BY abteilung
HAVING MIN(gehalt) > 50000;

Wenden Sie Filterungen mit WHERE möglichst vor der Gruppierung an. Das reduziert die Anzahl der Datensätze, die gruppiert werden müssen und beschleunigt die Abfrage.

2. Vermeiden Sie unnötige Gruppierungen:

-- Besser: Direkte Aggregation
SELECT COUNT(*) 
FROM mitarbeiter
WHERE abteilung = 'Vertrieb';

-- Statt
SELECT COUNT(*) 
FROM (
    SELECT abteilung, COUNT(*)
    FROM mitarbeiter
    GROUP BY abteilung
) t
WHERE abteilung = 'Vertrieb';

Wenn Sie nur an einem aggregierten Wert für eine bestimmte Gruppe interessiert sind, ist es oft effizienter, direkt nach dieser Gruppe zu filtern, statt erst die gesamte Tabelle zu gruppieren.

3. Nutzen Sie EXPLAIN zur Analyse:

EXPLAIN SELECT 
    abteilung, 
    COUNT(*) 
FROM mitarbeiter
GROUP BY abteilung;

Mit EXPLAIN können Sie sich den Ausführungsplan einer Abfrage anzeigen lassen. So sehen Sie, wie die Gruppierung durchgeführt wird und ob Indizes verwendet werden. Dies hilft bei der Optimierung.

4. Überprüfen Sie Zwischenergebnisse:

-- Gruppierung testen
SELECT 
    abteilung,
    COUNT(*) AS anzahl,
    MIN(gehalt) AS min_gehalt,
    MAX(gehalt) AS max_gehalt
FROM mitarbeiter
GROUP BY abteilung
ORDER BY abteilung;

Bei komplexen Gruppierungen ist es hilfreich, die Ergebnisse schrittweise zu überprüfen. Beginnen Sie mit einer einfachen Gruppierung und fügen Sie nach und nach weitere Spalten und Aggregatfunktionen hinzu. So können Sie sicherstellen, dass die Gruppierung korrekt ist und die gewünschten Ergebnisse liefert.

5. Dokumentieren Sie komplexe Auswertungen:

-- Kommentare für Berechnungslogik
SELECT 
    /* Gruppierung nach Quartal */
    QUARTER(bestelldatum) AS quartal,
    /* Nur erfolgreiche Bestellungen */
    COUNT(*) AS anzahl
FROM bestellungen
WHERE status = 'erfolgreich'
GROUP BY QUARTER(bestelldatum);

Verwenden Sie Kommentare, um die Logik komplexer Auswertungen zu erklären. Dies erleichtert die Wartung und Wiederverwendung der Abfragen.

Aggregatfunktionen und Gruppierungen sind mächtige Werkzeuge für die Datenanalyse mit SQL. Sie ermöglichen es, schnell zusammenfassende Statistiken und Kennzahlen zu berechnen, ohne die Daten in eine andere Anwendung exportieren zu müssen.

Durch geschicktes Kombinieren von GROUP BY, Aggregatfunktionen, HAVING und WHERE lassen sich komplexe Analysen durchführen. Achten Sie jedoch auf die Performanz bei großen Datenmengen und optimieren Sie Ihre Abfragen durch Indizes und temporäre Tabellen.

Üben Sie die Verwendung von Aggregatfunktionen und Gruppierungen anhand praktischer Beispiele aus Ihrem Arbeitsalltag. Experimentieren Sie mit verschiedenen Kombinationen und analysieren Sie die Ergebnisse. Mit etwas Übung werden Sie schnell in der Lage sein, aussagekräftige Berichte und Statistiken aus Ihren Datenbanken zu erstellen.