Die Top-N-Zeilen pro Gruppe in SQL

"Die drei teuersten Produkte je Kategorie", "die letzte Bestellung jedes Kunden", "der Bestseller pro Filiale". Das sind alles Varianten eines Musters: nach einer Gruppe aufteilen, innerhalb jeder Gruppe sortieren, die obersten N nehmen. Es heißt "Top-N pro Gruppe", und ein einfaches ORDER BY ... LIMIT schafft es nicht, weil LIMIT das gesamte Ergebnis beschneidet, nicht jede Gruppe.

Warum reicht ein einfaches LIMIT nicht?

ORDER BY preis DESC LIMIT 3 gibt Ihnen die drei teuersten Produkte der gesamten Tabelle; die könnten alle aus einer einzigen Kategorie stammen. Sie wollen aber die Top drei für jede Kategorie einzeln. Genau dafür sind Fensterfunktionen da.

Wie baut man das Muster mit ROW_NUMBER?

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) erzeugt innerhalb jeder Gruppe eine laufende Nummer ab 1. Diese Nummer filtern Sie dann von außen. Die drei teuersten Produkte je Kategorie:

SELECT kategorie, name, preis
FROM (
  SELECT kategorie, name, preis,
         ROW_NUMBER() OVER (
           PARTITION BY kategorie
           ORDER BY preis DESC
         ) AS rn
  FROM produkte
) t
WHERE rn <= 3
ORDER BY kategorie, rn;

PARTITION BY kategorie heißt "jede Kategorie für sich"; ORDER BY preis DESC gibt der teuersten Zeile die Nummer 1. Die innere Abfrage erzeugt die Nummer, die äußere filtert mit rn <= 3 die Top drei. Die Nummer lässt sich nicht direkt in WHERE verwenden, weshalb zwei Ebenen nötig sind.

ROW_NUMBER, RANK oder DENSE_RANK?

Alle drei ranken, behandeln Gleichstände aber unterschiedlich. ROW_NUMBER gibt selbst gleichen Werten verschiedene Nummern (Sie erhalten genau N Zeilen). RANK gibt Gleichständen dieselbe Nummer und springt dann (1,1,3). DENSE_RANK springt nicht (1,1,2). Wollen Sie "die Top-3-Preise samt Gleichständen", nehmen Sie DENSE_RANK; wollen Sie "genau 3 Zeilen", ist ROW_NUMBER richtig.

Was tue ich ohne Fensterfunktionen, etwa in MySQL 5.7?

Fensterfunktionen kamen mit MySQL 8.0. Stecken Sie auf einer älteren Version fest, zählt eine korrelierte Unterabfrage "wie viele Produkte derselben Kategorie sind teurer als diese Zeile?" und behält die, bei denen diese Zahl unter N liegt:

SELECT p.kategorie, p.name, p.preis
FROM produkte p
WHERE (
  SELECT COUNT(*)
  FROM produkte x
  WHERE x.kategorie = p.kategorie
    AND x.preis > p.preis
) < 3
ORDER BY p.kategorie, p.preis DESC;

Das funktioniert auf kleinen Tabellen gut; auf großen ist die Fensterfunktion schneller und lesbarer. Ein Vorbehalt: Gleiche Werte teilen sich dieselbe Zählung, bei Gleichständen können also mehr als N Zeilen zurückkommen; brauchen Sie genau N, führt am Fensterfunktions-Muster nichts vorbei. Wenn möglich, aktualisieren Sie die Datenbank und wechseln zum ersten Muster.

Statt dieses zweistufige Muster auswendig zu lernen, fragen Sie PerSight nach "den 3 teuersten Produkten je Kategorie" oder "der letzten Bestellung jedes Kunden." Es wählt die richtige Fensterfunktion und zeigt Ihnen die geschriebene Abfrage.