Window Functions richtig einsetzen – Ranking, Moving Average, CumSum

Du willst wissen, welcher Mitarbeiter den zweitbesten Umsatz in einem Monat erzielt hat. Mit einer klassischen GROUP BY-Abfrage stößt du schnell an Grenzen: Du kannst zwar die Umsätze summieren oder den Durchschnitt berechnen, aber sobald du die Daten gruppierst, gehen die Detailzeilen verloren. Du siehst nur noch aggregierte Werte, nicht mehr die einzelnen Umsätze pro Mitarbeiter oder Tag.

Hier kommen Window Functions ins Spiel. Sie erlauben es dir, Berechnungen über Zeilen hinweg durchzuführen, ohne die einzelnen Datensätze zusammenzufassen. Das bedeutet: Du kannst sowohl die Detailinformationen behalten als auch erweiterte Analysen durchführen – wie Ranglisten, gleitende Durchschnitte oder kumulierte Summen.

In diesem Artikel zeige ich dir:

  • Wie du Ranking-Funktionen nutzt, um z. B. den Top-Umsatz pro Verkäufer zu ermitteln.
  • Wie du gleitende Durchschnitte berechnest, um Trends in deinen Daten zu erkennen.
  • Wie du kumulierte Summen erzeugst, um laufende Gesamtsummen zu verfolgen.

Am Ende wirst du verstehen, warum Window Functions ein echter Game-Changer in SQL sind und wie du sie praktisch einsetzt.

SQL Window Funktionen

Grundlagen: Die Syntax von Window Functions kurz erklärt

Window Functions sind spezielle SQL-Funktionen, die Berechnungen über ein „Fenster“ von Zeilen durchführen, ohne die einzelnen Zeilen zusammenzufassen. Das bedeutet, du kannst beispielsweise den Durchschnittswert einer Abteilung berechnen, während du die einzelnen Mitarbeiterzeilen behältst.

Das Schlüsselwort OVER()

Jede Window Function nutzt das Schlüsselwort OVER(). Dieses Wandelt eine normale Aggregatfunktion wie SUM() oder AVG() in eine Window Function um. Mit OVER() sagst du SQL, dass die Berechnung über ein bestimmtes Fenster von Zeilen erfolgen soll, aber die Detailzeilen erhalten bleiben.

Die drei Hauptkomponenten innerhalb von OVER()

  • PARTITION BY: Unterteilt das Ergebnis in Gruppen, ähnlich wie GROUP BY.
    Der Unterschied: Die Zeilen innerhalb jeder Partition bleiben sichtbar. Die Berechnung wird separat für jede Partition durchgeführt.
  • ORDER BY: Legt die Reihenfolge der Zeilen innerhalb einer Partition fest.
    Diese Reihenfolge ist entscheidend für Ranglisten oder kumulative Berechnungen. Ohne ORDER BY wird das Fenster einfach über die gesamte Partition angewendet.
  • ROWS / RANGE BETWEEN: Definiert den Fensterrahmen – also welche Zeilen relativ zur aktuellen Zeile in die Berechnung einfließen.
    Zum Beispiel: „die 3 vorherigen Zeilen bis zur aktuellen Zeile“ oder „von Beginn der Partition bis zur aktuellen Zeile“.
    Dies ist der fortgeschrittene, aber mächtigste Teil von Window Functions.

Ein einfaches Beispiel

Du möchtest den Durchschnittsgehalt pro Abteilung berechnen, aber alle Mitarbeiterzeilen sollen erhalten bleiben:

SELECT
    mitarbeiter,
    abteilung,
    gehalt,
    AVG(gehalt) OVER (PARTITION BY abteilung) AS avg_gehalt_abteilung
FROM gehaltsdaten;

In diesem Beispiel:

  • PARTITION BY abteilung sorgt dafür, dass der Durchschnitt für jede Abteilung separat berechnet wird.
  • Jede Mitarbeiterzeile bleibt erhalten, du verlierst keine Details.

So kannst du zum Beispiel sehen, wie jeder Mitarbeiter im Vergleich zum Durchschnitt seiner Abteilung abschneidet.

Anwendungsfall 1: Ranking-Funktionen (ROW_NUMBER, RANK, DENSE_RANK)

Ranking-Funktionen in SQL helfen dir, Zeilen innerhalb einer Partition eine Rangfolge zuzuweisen. Typische Anwendungsfälle sind: die besten Verkäufer pro Monat finden, Top-Produkte nach Umsatz ermitteln oder Platzierungen innerhalb eines Teams berechnen.

Die wichtigsten Ranking-Funktionen

  • ROW_NUMBER(): Vergibt eine eindeutige, fortlaufende Nummer an jede Zeile, auch bei gleichen Werten. Beispiel: 1, 2, 3, 4…
  • RANK(): Vergibt Ränge und lässt Lücken, wenn Werte gleich sind. Beispiel: 1, 2, 2, 4…
  • DENSE_RANK(): Vergibt Ränge ohne Lücken bei gleichen Werten. Beispiel: 1, 2, 2, 3…

Praxis-Beispiel: Top 3-Umsatz pro Verkäufer

Angenommen, du möchtest herausfinden, welche Umsätze jeder Verkäufer in seinem Team erzielt hat und nur die Top 3 pro Verkäufer anzeigen: 

SELECT
    verkaeufer,
    datum,
    umsatz,
    RANK() OVER (PARTITION BY verkaeufer ORDER BY umsatz DESC) AS rang
FROM verkaufszahlen
QUALIFY RANK() <= 3;

Erklärung:

  • PARTITION BY verkaeufer sorgt dafür, dass der Rang für jeden Verkäufer separat berechnet wird.
  • ORDER BY umsatz DESC sortiert die Umsätze innerhalb der Partition vom höchsten zum niedrigsten.
  • QUALIFY RANK() <= 3 filtert die Zeilen auf die Top 3 pro Verkäufer. Hinweis: QUALIFY wird in SQL-Dialekten wie BigQuery oder Snowflake unterstützt.

Mit diesen Funktionen kannst du also sehr flexibel Ranglisten erstellen und unterschiedliche Szenarien abbilden – von eindeutigen Platzierungen bis hin zu Rängen mit Gleichständen.

Anwendungsfall 2: Gleitende Durchschnitte (Moving Average)

Gleitende Durchschnitte helfen, Trends in deinen Daten zu erkennen und Schwankungen zu glätten. Typische Beispiele sind tägliche Besucherzahlen einer Website, Lagerbestände oder Umsätze über die Zeit.

Fokus auf ROWS BETWEEN

Hier kommt der Fensterrahmen voll zum Einsatz. Mit ROWS BETWEEN kannst du genau festlegen, welche Zeilen relativ zur aktuellen Zeile in die Berechnung einfließen. Zum Beispiel: „die letzten 6 Tage plus heute“ oder „von Beginn der Zeitreihe bis heute“.

Praxis-Beispiel: 7-Tage gleitender Durchschnitt der Website-Besucher

SELECT
    datum,
    besucher,
    AVG(besucher) OVER (
        ORDER BY datum
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7days
FROM besucherzahlen
ORDER BY datum;

Erklärung:

  • ORDER BY datum sortiert die Daten chronologisch.
  • ROWS BETWEEN 6 PRECEDING AND CURRENT ROW definiert das Fenster: Die letzten 6 Tage plus der aktuelle Tag = 7 Tage.
  • AVG(besucher) OVER (…) berechnet den Durchschnitt innerhalb dieses Fensters.

Variationen

  • BETWEEN 3 PRECEDING AND 1 FOLLOWING: Ein symmetrisches Fenster – 3 Zeilen davor und 1 Zeile danach.
  • BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Kumulativ von Beginn der Datenreihe bis zur aktuellen Zeile.

Gleitende Durchschnitte sind ein mächtiges Werkzeug für die Trendanalyse und zeigen, wie sich Werte über die Zeit verändern, ohne einzelne Datenpunkte zu verlieren.

Anwendungsfall 3: Kumulierte Summen (Cumulative Sum - CumSum)

Kumulierte Summen zeigen den laufenden Gesamtwert bis zur aktuellen Zeile. Das ist besonders nützlich, um beispielsweise den Gesamtumsatz eines Produkts über die Zeit oder Lagerbestände nachzuvollziehen.

Praxis-Beispiel: Kumulierten Umsatz pro Produkt berechnen

SELECT
    produkt,
    datum,
    taeglicher_umsatz,
    SUM(taeglicher_umsatz) OVER (
        PARTITION BY produkt
        ORDER BY datum
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS kumulierter_umsatz
FROM umsatzdaten
ORDER BY produkt, datum;

Erklärung:

  • PARTITION BY produkt: Berechnet die kumulierte Summe separat für jedes Produkt.
  • ORDER BY datum: Sortiert die Zeilen chronologisch, damit die kumulierte Summe die Reihenfolge der Zeit widerspiegelt.
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Der Fensterrahmen startet ganz am Anfang der Partition und endet bei der aktuellen Zeile. So wird die Summe von Beginn bis jetzt berechnet.
  • SUM(taeglicher_umsatz) OVER (…): Berechnet die laufende Gesamtsumme innerhalb des definierten Fensters.

Mit kumulierten Summen behältst du jederzeit den Überblick über die Gesamtsummen in deinen Daten, ohne die Detailzeilen zu verlieren.

Kombinierte Anwendung: Ein komplexeres Praxisbeispiel

In der Praxis möchtest du oft mehrere Window Functions gleichzeitig nutzen. Zum Beispiel, um die monatliche Leistung der Mitarbeiter zu analysieren, ihren Umsatz mit dem Team-Durchschnitt zu vergleichen, ihre Rangfolge zu bestimmen und den kumulierten Team-Umsatz zu berechnen.

Praxis-Beispiel: Monatsleistung der Mitarbeiter analysieren

SELECT
    mitarbeiter,
    team,
    monat,
    umsatz,
    -- Durchschnitt pro Team und Monat
    AVG(umsatz) OVER (PARTITION BY team, monat) AS avg_team_umsatz,
    -- Rang im Team pro Monat
    RANK() OVER (PARTITION BY team, monat ORDER BY umsatz DESC) AS rang_im_team,
    -- Laufende Summe des Teams im Monat (Reihenfolge nach Umsatz)
    SUM(umsatz) OVER (
        PARTITION BY team, monat
        ORDER BY umsatz DESC
        ROWS UNBOUNDED PRECEDING
    ) AS kum_sum_team_monat
FROM leistungsdaten
ORDER BY team, monat, rang_im_team;

Erklärung:

  • AVG(umsatz) OVER (PARTITION BY team, monat): Berechnet den Durchschnittsumsatz pro Team und Monat, ohne die einzelnen Zeilen zu aggregieren.
  • RANK() OVER (PARTITION BY team, monat ORDER BY umsatz DESC): Vergibt einen Rang im Team basierend auf dem Umsatz, wobei gleiche Umsätze gleiche Ränge erhalten.
  • SUM(umsatz) OVER (PARTITION BY team, monat ORDER BY umsatz DESC ROWS UNBOUNDED PRECEDING): Berechnet den kumulierten Umsatz innerhalb des Teams für den Monat in der Reihenfolge der Umsätze.

Mit dieser kombinierten Anwendung siehst du auf einen Blick:

  • Wie jeder Mitarbeiter im Vergleich zu seinem Team abschneidet
  • Wer die Top-Umsätze erzielt
  • Wie sich der Gesamtumsatz innerhalb des Teams entwickelt
Das zeigt die wahre Stärke von Window Functions: komplexe Analysen, ohne die Detailzeilen zu verlieren.

Performance-Hinweise und Best Practices

Window Functions sind mächtig, können aber bei großen Datenmengen die Performance beeinträchtigen. Mit ein paar einfachen Regeln kannst du deine Abfragen effizienter gestalten.

Indexierung

Ein Index auf den Spalten, die in PARTITION BY und ORDER BY verwendet werden, kann die Berechnung erheblich beschleunigen. Dadurch muss die Datenbank die Zeilen nicht jedes Mal neu sortieren.

Fenstergröße im Blick behalten

Große Fensterrahmen wie BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING sind sehr rechenintensiv. Überlege, ob du wirklich alle Zeilen benötigst oder ein kleineres, begrenztes Fenster ausreicht.

Über die Partition nachdenken

Die Wahl der Partition beeinflusst die Performance:

  • Zu viele kleine Partitionen: Die Berechnung wird oft wiederholt, was ineffizient sein kann.
  • Zu wenige große Partitionen: Das Fenster kann sehr groß werden und die Berechnung verlangsamen.
Finde eine Balance zwischen sinnvollen Gruppierungen und Performance.

Weitere Tipps

  • Vermeide komplexe Berechnungen innerhalb des Fensters, wenn möglich.
  • Teste deine Abfragen mit kleineren Datenmengen, bevor du sie auf große Tabellen anwendest.
  • Nutze Datenbank-spezifische Optimierungen, z.B. QUALIFY in BigQuery oder Snowflake, um Ergebnisse nach Window Functions zu filtern.

Mit diesen Best Practices stellst du sicher, dass Window Functions nicht nur mächtig, sondern auch effizient bleiben.

Fazit: Praktische Methode

Window Functions erweitern deine SQL-Fähigkeiten enorm und lösen viele Probleme, die mit klassischen GROUP BY-Abfragen nur umständlich zu lösen sind. Du kannst Berechnungen über Zeilen hinweg durchführen, ohne die Detailzeilen zu verlieren.

Key Takeaway

Der magische Dreiklang, den du dir merken solltest, ist:

  • PARTITION BY: Gruppieren, aber die Zeilen bleiben erhalten.
  • ORDER BY: Sortieren der Zeilen innerhalb der Partition, entscheidend für Rangfolgen und kumulative Berechnungen.
  • ROWS BETWEEN: Den Fensterrahmen setzen, also welche Zeilen in die Berechnung einbezogen werden.

Praxis-Tipp

Probiere die Beispiele in deiner eigenen Datenbank aus. Experimentiere mit den Einstellungen von Partitionen, Fenstergrößen und Sortierreihenfolgen. Überlege, welche Analysen für deine eigenen Daten spannend sind – vielleicht findest du neue Insights, die vorher verborgen waren.