Fensterfunktionen

Geschätzte Lektüre: 5 Minuten 95 Ansichten

Fensterfunktionen – Analysen ohne Gruppierung

Fensterfunktionen (Window Functions) gehören zu den leistungsfähigsten Werkzeugen in SQL. Sie ermöglichen Berechnungen über eine Gruppe von Zeilen (das „Fenster“), ohne dass die Zeilen zu einer einzigen Ergebniszeile zusammengefasst werden müssen. Während GROUP BY die Anzahl der Zeilen reduziert, behalten Fensterfunktionen die ursprüngliche Zeilenanzahl bei und fügen berechnete Werte hinzu.

Grundlegende Fensterfunktionen

ROW_NUMBER(): Weist jeder Zeile innerhalb einer Partition eine eindeutige Nummer zu.

-- Jeder Bestellung eine laufende Nummer pro Kunde geben
SELECT 
    b.id AS bestellung_id,
    b.kunden_id,
    b.bestelldatum,
    ROW_NUMBER() OVER (PARTITION BY b.kunden_id ORDER BY b.bestelldatum) AS bestellnummer_pro_kunde
FROM bestellungen b;

-- Jeder Bestellung eine laufende Nummer pro Kunde geben
SELECT 
    b.id AS bestellung_id,
    b.kunden_id,
    b.bestelldatum,
    ROW_NUMBER() OVER (PARTITION BY b.kunden_id ORDER BY b.bestelldatum) AS bestellnummer_pro_kunde
FROM bestellungen b;

RANK() und DENSE_RANK(): Weisen Rangnummern zu, wobei RANK() bei gleichen Werten Lücken lässt, DENSE_RANK() nicht.

-- Produkte nach Preis rangieren
SELECT 
    name,
    preis,
    RANK() OVER (ORDER BY preis DESC) AS preis_rank,
    DENSE_RANK() OVER (ORDER BY preis DESC) AS preis_dense_rank
FROM produkte;

-- Produkte nach Preis rangieren
SELECT 
    name,
    preis,
    RANK() OVER (ORDER BY preis DESC) AS preis_rank,
    DENSE_RANK() OVER (ORDER BY preis DESC) AS preis_dense_rank
FROM produkte;

Aggregierende Fensterfunktionen

Aggregatsfunktionen wie SUM(), AVG(), COUNT() können ebenfalls als Fensterfunktionen eingesetzt werden.

Beispiel: Kumulativer Umsatz pro Bestellung

SELECT 
    b.id AS bestellung_id,
    b.bestelldatum,
    k.nachname,
    COALESCE(SUM(bp.menge * bp.einzelpreis), 0) AS positionswert,
    SUM(COALESCE(SUM(bp.menge * bp.einzelpreis), 0)) OVER (
        PARTITION BY b.kunden_id 
        ORDER BY b.bestelldatum 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS kumulativer_kundenumsatz
FROM bestellungen b
INNER JOIN kunden k ON b.kunden_id = k.id
LEFT JOIN bestellpositionen bp ON b.id = bp.bestellung_id
GROUP BY b.id, b.bestelldatum, b.kunden_id, k.nachname
ORDER BY b.kunden_id, b.bestelldatum;

SELECT 
    b.id AS bestellung_id,
    b.bestelldatum,
    k.nachname,
    COALESCE(SUM(bp.menge * bp.einzelpreis), 0) AS positionswert,
    SUM(COALESCE(SUM(bp.menge * bp.einzelpreis), 0)) OVER (
        PARTITION BY b.kunden_id 
        ORDER BY b.bestelldatum 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS kumulativer_kundenumsatz
FROM bestellungen b
INNER JOIN kunden k ON b.kunden_id = k.id
LEFT JOIN bestellpositionen bp ON b.id = bp.bestellung_id
GROUP BY b.id, b.bestelldatum, b.kunden_id, k.nachname
ORDER BY b.kunden_id, b.bestelldatum;

Diese Abfrage zeigt für jede Bestellung, wie hoch der kumulierte Umsatz des Kunden bis zu diesem Zeitpunkt ist.

Beispiel: Durchschnittlicher Bestellwert pro Kunde im Vergleich

SELECT 
    b.id AS bestellung_id,
    b.kunden_id,
    COALESCE(SUM(bp.menge * bp.einzelpreis), 0) AS wert,
    AVG(COALESCE(SUM(bp.menge * bp.einzelpreis), 0)) OVER (
        PARTITION BY b.kunden_id
    ) AS durchschnitt_pro_kunde
FROM bestellungen b
LEFT JOIN bestellpositionen bp ON b.id = bp.bestellung_id
GROUP BY b.id, b.kunden_id;

SELECT 
    b.id AS bestellung_id,
    b.kunden_id,
    COALESCE(SUM(bp.menge * bp.einzelpreis), 0) AS wert,
    AVG(COALESCE(SUM(bp.menge * bp.einzelpreis), 0)) OVER (
        PARTITION BY b.kunden_id
    ) AS durchschnitt_pro_kunde
FROM bestellungen b
LEFT JOIN bestellpositionen bp ON b.id = bp.bestellung_id
GROUP BY b.id, b.kunden_id;

LEAD und LAG – Zugriff auf vorherige und nächste Zeilen

LAG() greift auf eine vorherige Zeile zu, LEAD() auf eine nachfolgende Zeile.

Beispiel: Zeitabstand zwischen Bestellungen eines Kunden

SELECT 
    b.id AS bestellung_id,
    b.kunden_id,
    b.bestelldatum,
    LAG(b.bestelldatum) OVER (
        PARTITION BY b.kunden_id 
        ORDER BY b.bestelldatum
    ) AS vorherige_bestellung,
    DATEDIFF(b.bestelldatum, 
        LAG(b.bestelldatum) OVER (
            PARTITION BY b.kunden_id 
            ORDER BY b.bestelldatum
        )
    ) AS tage_seit_letzter_bestellung
FROM bestellungen b
ORDER BY b.kunden_id, b.bestelldatum;

SELECT 
    b.id AS bestellung_id,
    b.kunden_id,
    b.bestelldatum,
    LAG(b.bestelldatum) OVER (
        PARTITION BY b.kunden_id 
        ORDER BY b.bestelldatum
    ) AS vorherige_bestellung,
    DATEDIFF(b.bestelldatum, 
        LAG(b.bestelldatum) OVER (
            PARTITION BY b.kunden_id 
            ORDER BY b.bestelldatum
        )
    ) AS tage_seit_letzter_bestellung
FROM bestellungen b
ORDER BY b.kunden_id, b.bestelldatum;

Fensterfunktionen