dtcSoftware - powerShellDba

📘 Grundlagen 🏆 Ranking 📈 Aggregat ↔️ LAG/LEAD 🎯 FIRST/LAST 🖼️ Fensterrahmen

1. Grundlagen: OVER() und die Funktionsweise

EINSTIEG

📌 Was sind Fensterfunktionen? Fensterfunktionen berechnen Werte über eine Gruppe von Zeilen (das Fenster), ohne die Ergebnismenge auf eine einzelne Zeile zu reduzieren. Sie werden mit der OVER-Klausel definiert und sind essenziell für laufende Summen, Ränge, gleitende Durchschnitte und vieles mehr.

-- Allgemeine Syntax
SELECT Spalte1, Spalte2,
  Fensterfunktion() OVER ([PARTITION BY SpalteX] [ORDER BY SpalteY] [<Fensterrahmen>]) AS NeueSpalte
FROM Tabelle;

-- Einfaches Beispiel: Gesamtsumme aller Verkäufe als weitere Spalte ausgeben
SELECT 
  VerkaufID,
  Betrag,
  SUM(Betrag) OVER() AS Gesamtumsatz
FROM Verkäufe;
-- Ergebnis: Jede Zeile zeigt zusätzlich den globalen Summenwert.

💡 Erklärung: Eine leere OVER() betrachtet stets die gesamte Ergebnismenge. Mit PARTITION BY und ORDER BY kannst du Fenster innerhalb der Daten definieren. Im Gegensatz zu GROUP BY bleiben alle Detailzeilen erhalten.

2. Ranking-Funktionen: ROW_NUMBER, RANK & DENSE_RANK

RANGBILDUNG

📌 Wofür? Vergib fortlaufende Nummern, Rangpositionen oder teile Datensätze in Gruppen (NTILE) – ideal für Top-N-Abfragen oder Duplikatsmarkierung.

-- Vergleich ROW_NUMBER vs. RANK vs. DENSE_RANK
SELECT 
  Produktname,
  Preis,
  ROW_NUMBER() OVER (ORDER BY Preis DESC) AS Rang_ROW_NUMBER,
  RANK() OVER (ORDER BY Preis DESC) AS Rang_RANK,
  DENSE_RANK() OVER (ORDER BY Preis DESC) AS Rang_DENSE_RANK
FROM Produkte;

-- Top-3-Produkte pro Kategorie (ROW_NUMBER + PARTITION BY)
WITH Bewertet AS (
  SELECT 
    Kategorie,
    Produktname,
    Umsatz,
    ROW_NUMBER() OVER (PARTITION BY Kategorie ORDER BY Umsatz DESC) AS Rang
  FROM Vertriebsdaten
)
SELECT * FROM Bewertet WHERE Rang <= 3;

🏷️ Erklärung: ROW_NUMBER() erzeugt lückenlos aufsteigende Nummern. RANK() und DENSE_RANK() behandeln Gleichstände unterschiedlich: Während der erste bei gleichen Werten Lücken lässt, behält der zweite die Sequenz bei. Mit NTILE(4) kannst du Daten in Quartile teilen.

3. Aggregat-Fensterfunktionen: SUM, AVG, MIN, MAX

LAUFENDE SUMMEN

📌 Wofür? Klassische Aggregation (Summe, Durchschnitt) über ein Fenster ohne GROUP BY – perfekt für kumulierte Werte, gleitende Durchschnitte oder Benchmark-Vergleiche.

-- Laufende Summe der Bestellungen pro Monat (aufsteigend sortiert)
SELECT 
  Monat,
  Umsatz,
  SUM(Umsatz) OVER (ORDER BY Monat) AS Kumulierte_Umsätze
FROM Monatsumsätze;

-- Kumulierte Summe pro Kategorie (PARTITION BY + ORDER BY)
SELECT 
  Kategorie,
  Monat,
  Umsatz,
  SUM(Umsatz) OVER (PARTITION BY Kategorie ORDER BY Monat) AS Kategorie_Kumuliert
FROM Vertriebsdaten
ORDER BY Kategorie, Monat;

-- Gleitender 3-Monats-Durchschnitt (mit Fensterrahmen)
SELECT 
  Monat,
  Umsatz,
  AVG(Umsatz) OVER (ORDER BY Monat
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Gleitender_Durchschnitt
FROM Monatsumsätze;

📈 Erklärung: In Verbindung mit ORDER BY erzeugt die Aggregat-Fensterfunktion einen laufenden Wert, der Zeile für Zeile aktualisiert wird. Der Fensterrahmen (z.B. ROWS BETWEEN ...) erlaubt präzise Steuerung, welche Zeilen in die Berechnung einfließen – essenziell für rollierende Durchschnitte.

4. Offset-Funktionen: LAG und LEAD

ZEILENVERGLEICH

📌 Wofür? Greife auf Werte vorhergehender (LAG) oder folgender (LEAD) Zeilen zu – ideal für Zeitreihenvergleiche (Vorjahreswert, prozentuale Änderung).

-- Umsatzänderung zum Vormonat
SELECT 
  Monat,
  Umsatz,
  LAG(Umsatz, 1) OVER (ORDER BY Monat) AS Vormonat,
  Umsatz - LAG(Umsatz, 1) OVER (ORDER BY Monat) AS Differenz
FROM Monatsumsätze;

-- LEAD: Nächster Bestellwert innerhalb einer Bestellungs-Partition
SELECT 
  BestellID,
  Artikel,
  Menge,
  LEAD(Menge) OVER (PARTITION BY BestellID ORDER BY Artikel) AS Naechste_Menge
FROM Bestelldetails;

🔄 Erklärung: LAG zeigt auf die vorherige Zeile innerhalb des Fensters, LEAD auf die nächste. Standardmäßig wird eine Zeile übersprungen, du kannst aber auch einen Offset (z.B. 2) sowie einen Default-Wert für nicht vorhandene Zeilen angeben – nützlich für gefüllte NULL-Werte.

5. FIRST_VALUE & LAST_VALUE – Erster/letzter Wert im Fenster

RAHMENREFERENZ

📌 Wofür? Extrahiere den ersten oder letzten Wert einer Partition – etwa um den günstigsten Preis eines Produkts im Vergleich zum Durchschnitt zu setzen.

-- Erster und letzter Umsatz pro Produkt (über alle Monate)
SELECT 
  ProduktID,
  Monat,
  Umsatz,
  FIRST_VALUE(Umsatz) OVER (PARTITION BY ProduktID ORDER BY Monat) AS Erster_Umsatz,
  LAST_VALUE(Umsatz) OVER (PARTITION BY ProduktID ORDER BY Monat
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS Letzter_Umsatz
FROM Vertriebsdaten;

🎯 Erklärung: FIRST_VALUE ist einfach, LAST_VALUE benötigt einen expliziten Fensterrahmen (z.B. UNBOUNDED FOLLOWING), da der Standardrahmen nur bis zur aktuellen Zeile reicht. Eine Alternative ist das Umkehren der Sortierung mit ORDER BY ... DESC.

6. Fensterrahmen: ROWS vs. RANGE

FEINTUNING

📌 Wofür? Definiere präzise, welche Zeilen zur Berechnung herangezogen werden. ROWS arbeitet mit absoluten Zeilennummern, RANGE mit logischen Werten.

-- ROWS UNBOUNDED PRECEDING: klassische laufende Summe
SELECT 
  Datum,
  Verkauf,
  SUM(Verkauf) OVER (ORDER BY Datum ROWS UNBOUNDED PRECEDING) AS Laufende_Summe
FROM Tagesverkaeufe;

-- RANGE UNBOUNDED PRECEDING inkl. gleicher ORDER-BY-Werte
SELECT 
  Ort,
  Einwohner,
  SUM(Einwohner) OVER (ORDER BY Ort RANGE UNBOUNDED PRECEDING) AS Kumuliert
FROM Staedte;

⚙ Erklärung: Bei ROWS bezieht sich der Rahmen streng auf die Anzahl der Zeilen, bei RANGE dagegen auf den logischen Wert in der ORDER-BY-Spalte. Bevorzuge ROWS, wenn du exakte Zeilen steuern willst, und RANGE, wenn alle gleichen ORDER-BY-Werte einbezogen werden sollen.

📌 Wann verwende ich welche Fensterfunktion?

/* Ranking */ Zeilen nummerieren: ROW_NUMBER() / Rang mit Lücken: RANK() / Rang ohne Lücken: DENSE_RANK() / Einteilung in Gruppen: NTILE(n)
/* Aggregat */ Laufende Summe / Durchschnitt: SUM() / AVG() mit OVER(ORDER BY ...)
/* Offset */ Vorherige Zeile: LAG() / Nächste Zeile: LEAD()
/* Wert */ Erster im Fenster: FIRST_VALUE() / Letzter im Fenster: LAST_VALUE()
/* Fensterrahmen */ ROWS UNBOUNDED PRECEDING (Start bei erster Zeile) / ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING (gleitender Dreierblock)

🚀 Performance‑Tipps

Verwende ROWS statt RANGE, wenn du exakte Zeilen benötigst – das vermeidet unnötigen Speicheraufwand.
Nutze eine geeignete Indexstruktur (insbesondere auf den PARTITION BY- und ORDER BY-Spalten), um Sortiervorgänge zu reduzieren.
In SQL Server 2022 kannst du mit der WINDOW-Klausel wiederkehrende Fensterdefinitionen einmal zentral festlegen, was den Code erheblich schlanker macht.
Fensterfunktionen ersparen dir oft aufwendige Selbstverknüpfungen und korrelierte Unterabfragen – sie sind nicht nur lesbarer, sondern häufig auch schneller.

✅ Fazit: Fensterfunktionen sind eines der mächtigsten Werkzeuge in T-SQL. Mit OVER(), PARTITION BY und dem passenden Fensterrahmen löst du analytische Aufgaben elegant, wartbar und performant.

📊 T-SQL Fensterfunktionen · Komplettleitfaden