Window Functions in MySQL 8: ROW_NUMBER, RANK, LAG, LEAD

1. Aufbau der OVER()-Klausel

Das Herzstück jeder Window Function MySQL ist die OVER()-Klausel. Sie definiert das "Fenster" – also die Menge von Zeilen, über die die Funktion berechnet wird. Ohne OVER() ist eine Aggregatfunktion wie SUM() oder COUNT() eine gewöhnliche Aggregation, die Zeilen zusammenfasst. Mit OVER() wird sie zur analytischen Funktion, die jeder Zeile ihren berechneten Wert zuweist, ohne die Granularität zu verlieren.

Die OVER()-Klausel hat zwei optionale Bestandteile: PARTITION BY unterteilt die Zeilen in Gruppen – ähnlich wie GROUP BY, aber ohne Kollabierung. ORDER BY innerhalb von OVER() legt die Reihenfolge innerhalb der Partition fest, was für Rangfunktionen und laufende Berechnungen unverzichtbar ist. Der entscheidende Unterschied: Das Ergebnis hat genauso viele Zeilen wie die Eingabe.

-- Grundstruktur der OVER()-Klausel
SELECT
    spalte,
    funktion() OVER (
        PARTITION BY gruppierungsspalte    -- optional: Fenster pro Gruppe
        ORDER BY reihenfolgenspalte DESC   -- optional: Reihenfolge im Fenster
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- optional: Frame
    ) AS ergebnis
FROM tabelle;

-- Einfaches Beispiel: globaler Rang aller abgeschlossenen Bestellungen
SELECT
    entity_id,
    increment_id,
    grand_total,
    ROW_NUMBER() OVER (ORDER BY grand_total DESC) AS rang
FROM sales_order
WHERE status = 'complete';
    

2. ROW_NUMBER, RANK, DENSE_RANK

Die drei Rangfunktionen unterscheiden sich im Umgang mit Gleichständen (Ties). ROW_NUMBER() vergibt immer eindeutige aufsteigende Nummern, auch wenn zwei Zeilen denselben ORDER-BY-Wert haben. RANK() vergibt bei Gleichstand denselben Rang, überspringt aber die folgenden Positionen. DENSE_RANK() vergibt bei Gleichstand denselben Rang, ohne Positionen zu überspringen.

-- Vergleich: ROW_NUMBER vs. RANK vs. DENSE_RANK
SELECT
    p.sku,
    SUM(oi.row_total)                                       AS umsatz,
    ROW_NUMBER() OVER (ORDER BY SUM(oi.row_total) DESC)     AS row_nr,
    -- 1, 2, 3 ... (immer eindeutig, auch bei Gleichstand)
    RANK()       OVER (ORDER BY SUM(oi.row_total) DESC)     AS rang,
    -- 1, 1, 3 ... (Gleichstand → gleicher Rang, Lücke danach)
    DENSE_RANK() OVER (ORDER BY SUM(oi.row_total) DESC)     AS dichten_rang
    -- 1, 1, 2 ... (Gleichstand → gleicher Rang, keine Lücke)
FROM catalog_product_entity p
JOIN sales_order_item oi ON oi.sku = p.sku
JOIN sales_order o ON o.entity_id = oi.order_id AND o.status = 'complete'
GROUP BY p.sku
ORDER BY umsatz DESC
LIMIT 20;
    

Mit PARTITION BY lässt sich die Rangfolge innerhalb jeder Gruppe berechnen. Das ist der Schlüssel für Abfragen wie "Top-3-Produkte pro Kategorie":

-- Top-3-Produkte pro Kategorie nach Umsatz
SELECT *
FROM (
    SELECT
        ccp.category_id,
        p.sku,
        SUM(oi.row_total)                       AS umsatz,
        RANK() OVER (
            PARTITION BY ccp.category_id
            ORDER BY SUM(oi.row_total) DESC
        )                                        AS rang_in_kategorie
    FROM catalog_category_product ccp
    JOIN catalog_product_entity p ON p.entity_id = ccp.product_id
    JOIN sales_order_item oi ON oi.sku = p.sku
    JOIN sales_order o ON o.entity_id = oi.order_id AND o.status = 'complete'
    GROUP BY ccp.category_id, p.sku
) ranked
WHERE rang_in_kategorie <= 3
ORDER BY category_id, rang_in_kategorie;
    

3. LAG und LEAD: Vorherige und nächste Zeile

LAG() und LEAD() greifen auf Werte aus der vorherigen bzw. nächsten Zeile im Fenster zu – ohne Self-Join. Die Signatur ist LAG(spalte, offset, default). offset gibt an, wie viele Zeilen zurückgeblickt wird (Standard: 1). default ist der Rückgabewert, wenn keine vorherige Zeile existiert (Standard: NULL).

-- Tag-über-Tag-Umsatzvergleich mit LAG()
SELECT
    DATE(created_at)                              AS tag,
    SUM(grand_total)                              AS umsatz_heute,
    LAG(SUM(grand_total), 1, 0)
        OVER (ORDER BY DATE(created_at))          AS umsatz_gestern,
    ROUND(
        (SUM(grand_total)
         - LAG(SUM(grand_total), 1, NULL) OVER (ORDER BY DATE(created_at)))
        / NULLIF(
            LAG(SUM(grand_total), 1, NULL) OVER (ORDER BY DATE(created_at)),
            0
          ) * 100,
        2
    )                                             AS wachstum_prozent
FROM sales_order
WHERE status = 'complete'
  AND created_at >= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY DATE(created_at)
ORDER BY tag;
    

-- LEAD(): Tage bis zur nächsten Bestellung desselben Kunden
SELECT
    entity_id,
    customer_id,
    created_at                                    AS bestellung_datum,
    LEAD(created_at, 1, NULL)
        OVER (PARTITION BY customer_id
              ORDER BY created_at)                AS naechste_bestellung,
    DATEDIFF(
        LEAD(created_at, 1, NULL)
            OVER (PARTITION BY customer_id ORDER BY created_at),
        created_at
    )                                             AS tage_bis_naechste
FROM sales_order
WHERE status = 'complete'
ORDER BY customer_id, created_at;
    

4. Laufende Summen und gleitende Durchschnitte

SUM() OVER() mit ORDER BY berechnet eine kumulierte Summe (Running Total). Der Default-Frame bei explizitem ORDER BY in der OVER()-Klausel ist RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, was für laufende Summen korrekt ist. Für Rolling Windows empfiehlt sich die explizite ROWS-Spezifikation.

-- Kumulierter Jahresumsatz (Running Total)
SELECT
    DATE_FORMAT(created_at, '%Y-%m')              AS monat,
    SUM(grand_total)                              AS monatsumsatz,
    SUM(SUM(grand_total)) OVER (
        ORDER BY DATE_FORMAT(created_at, '%Y-%m')
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                             AS kumulierter_umsatz
FROM sales_order
WHERE status = 'complete'
  AND YEAR(created_at) = 2024
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY monat;
    

-- Gleitender 3-Tage-Durchschnitt (Moving Average)
SELECT
    DATE(created_at)                              AS tag,
    SUM(grand_total)                              AS tagesumsatz,
    ROUND(
        AVG(SUM(grand_total)) OVER (
            ORDER BY DATE(created_at)
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ), 2
    )                                             AS durchschnitt_3_tage
FROM sales_order
WHERE status = 'complete'
GROUP BY DATE(created_at)
ORDER BY tag;
    

5. NTILE für Percentile-Buckets

NTILE(n) teilt die Ergebnismenge in n möglichst gleich grosse Buckets auf und weist jeder Zeile ihre Bucket-Nummer zu. Typischer Anwendungsfall: Kunden in Umsatz-Quartile einteilen für Kundensegmentierung oder RFM-Analysen.

-- Kunden in 4 Umsatz-Quartile einteilen
SELECT
    customer_id,
    customer_email,
    gesamt_umsatz,
    NTILE(4) OVER (ORDER BY gesamt_umsatz DESC)   AS quartil
    -- Quartil 1 = Top 25%, Quartil 4 = untere 25%
FROM (
    SELECT
        customer_id,
        customer_email,
        SUM(grand_total) AS gesamt_umsatz
    FROM sales_order
    WHERE status = 'complete'
      AND customer_id IS NOT NULL
    GROUP BY customer_id, customer_email
) umsatz_pro_kunde
ORDER BY gesamt_umsatz DESC;
    

6. FIRST_VALUE und LAST_VALUE

FIRST_VALUE() gibt den ersten Wert im Fenster zurück, LAST_VALUE() den letzten. Für LAST_VALUE() muss der Frame explizit auf ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING gesetzt werden – der Default-Frame reicht sonst nur bis zur aktuellen Zeile.

-- Erstes und letztes Bestelldatum pro Kunde (ohne Self-Join)
SELECT DISTINCT
    customer_id,
    FIRST_VALUE(created_at) OVER (
        PARTITION BY customer_id
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    )                                             AS erste_bestellung,
    LAST_VALUE(created_at) OVER (
        PARTITION BY customer_id
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    )                                             AS letzte_bestellung
FROM sales_order
WHERE status = 'complete'
  AND customer_id IS NOT NULL;
    

7. Frame-Spezifikation: ROWS vs. RANGE

Die Frame-Spezifikation bestimmt, welche Zeilen relativ zur aktuellen Zeile in die Berechnung einfliessen. ROWS bezieht sich auf physische Zeilenpositionen. RANGE bezieht sich auf Werteabstände – wenn mehrere Zeilen denselben ORDER-BY-Wert haben, werden sie alle zum gleichen Frame gezählt. Für Rolling Windows ist ROWS deterministischer.

-- ROWS: physische Positionen (für Rolling Windows empfohlen)
-- Genau 7 Zeilen: aktuelle Zeile + 6 vorherige
AVG(umsatz) OVER (
    ORDER BY tag
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

-- RANGE: wertebasiert (alle Zeilen mit gleichem ORDER BY-Wert im Frame)
SUM(umsatz) OVER (
    ORDER BY monat
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

-- 7-Tage gleitender Durchschnitt mit ROWS (deterministisch)
SELECT
    tag,
    tagesumsatz,
    ROUND(
        AVG(tagesumsatz) OVER (
            ORDER BY tag
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ), 2
    )                                             AS avg_7_tage
FROM (
    SELECT DATE(created_at) AS tag, SUM(grand_total) AS tagesumsatz
    FROM sales_order WHERE status = 'complete'
    GROUP BY DATE(created_at)
) t
ORDER BY tag;
    

8. Magento: Revenue-Ranking und Monatswachstum

Window Functions sind für Magento-Analysen besonders wertvoll, weil viele Reporting-Anforderungen genau das erfordern, was GROUP BY alleine nicht leisten kann: einen Wert pro Zeile berechnen, der von der Gesamtmenge oder der Periode abhängt, ohne die Granularität zu verlieren. Monatlicher Umsatz, kumulierter Jahresumsatz und Wachstumsrate – alles in einer Abfrage.

-- Monat-über-Monat-Wachstumsrate für Magento 2024
SELECT
    monat,
    umsatz,
    LAG(umsatz, 1, NULL) OVER (ORDER BY monat)    AS vormonat_umsatz,
    ROUND(
        (umsatz - LAG(umsatz, 1, NULL) OVER (ORDER BY monat))
        / NULLIF(LAG(umsatz, 1, NULL) OVER (ORDER BY monat), 0) * 100,
        1
    )                                             AS wachstum_mom_pct,
    SUM(umsatz) OVER (
        ORDER BY monat ROWS UNBOUNDED PRECEDING
    )                                             AS kumuliert_ytd
FROM (
    SELECT
        DATE_FORMAT(created_at, '%Y-%m') AS monat,
        SUM(grand_total)                 AS umsatz
    FROM sales_order
    WHERE status NOT IN ('canceled', 'closed', 'pending')
      AND YEAR(created_at) = 2024
    GROUP BY DATE_FORMAT(created_at, '%Y-%m')
) monatsagg
ORDER BY monat;
    

-- Produktumsatz-Rang pro Kategorie + Anteil am Kategorieumsatz
SELECT
    category_id,
    sku,
    umsatz,
    RANK() OVER (
        PARTITION BY category_id
        ORDER BY umsatz DESC
    )                                             AS rang_in_kategorie,
    ROUND(
        umsatz / SUM(umsatz) OVER (PARTITION BY category_id) * 100,
        1
    )                                             AS anteil_kategorie_pct
FROM (
    SELECT
        ccp.category_id,
        oi.sku,
        SUM(oi.row_total) AS umsatz
    FROM catalog_category_product ccp
    JOIN sales_order_item oi ON oi.product_id = ccp.product_id
    JOIN sales_order o ON o.entity_id = oi.order_id AND o.status = 'complete'
    GROUP BY ccp.category_id, oi.sku
) kategorie_umsatz
ORDER BY category_id, rang_in_kategorie;
    

10. Zusammenfassung

Window Functions in MySQL 8 eröffnen eine neue Klasse analytischer Abfragen, die ohne korrelierte Subqueries oder Self-Joins auskommen. ROW_NUMBER(), RANK() und DENSE_RANK() lösen Rangfolge-Aufgaben mit unterschiedlicher Behandlung von Gleichständen. LAG()/LEAD() ermöglichen Perioden-Vergleiche. SUM() OVER() berechnet laufende Summen. NTILE() teilt in Percentile-Buckets ein.

11. FAQ: Window Functions MySQL 8