Fensterfunktionen: ROW_NUMBER, RANK, SUM OVER

Berechne Ränge, laufende Summen und Vergleiche innerhalb von Gruppen, ohne Zeilen zusammenzufassen, dank OVER und PARTITION BY.

10 Min. · 3 Fragen

GROUP BY fasst Zeilen zu Gruppen zusammen; eine Fensterfunktion berechnet ein Aggregat oder einen Rang, während jede Zeile erhalten bleibt. Sie ist das Werkzeug für Top N pro Gruppe, laufende Summen und Vergleiche mit dem Durchschnitt des eigenen Teams. In SQLite seit Version 3.25 (2018) verfügbar. Lade den Datensatz.

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);

INSERT INTO employees (name, dept, salary)
VALUES
  ('alice', 'tech', 5200),
  ('bruno', 'tech', 4800),
  ('chloe', 'tech', 4800),
  ('david', 'tech', 4100),
  ('emma', 'sales', 3900),
  ('fanny', 'sales', 3600),
  ('gilles', 'sales', 3600),
  ('hugo', 'sales', 3200);

Drei Rangfunktionen, drei Verhaltensweisen bei Gleichständen: ROW_NUMBER nummeriert ohne Duplikate (willkürliche Auflösung des Gleichstands), RANK gibt Gleichständen denselben Rang und überspringt dann (1, 2, 2, 4), DENSE_RANK überspringt nicht (1, 2, 2, 3). Die WINDOW-Klausel vermeidet es, die Fensterdefinition zu wiederholen (unterstützt von SQLite und PostgreSQL, nicht vom SQL Server).

SELECT name, salary,
  ROW_NUMBER() OVER w AS row_num,
  RANK() OVER w AS rnk,
  DENSE_RANK() OVER w AS dense
FROM employees
WHERE dept = 'tech'
WINDOW w AS (ORDER BY salary DESC);

PARTITION BY startet die Berechnung für jede Teilmenge neu: ein Fenster pro Abteilung, ohne etwas zusammenzufassen. In Kombination mit einer CTE ergibt das das Interview-Muster schlechthin: die N Bestbezahlten in jeder Abteilung. Die CTE ist zwingend, weil eine Fensterfunktion nach dem WHERE ausgewertet wird: Du kannst nicht direkt auf rn filtern.

WITH ranked AS (
  SELECT name, dept, salary,
    ROW_NUMBER() OVER (
      PARTITION BY dept
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT name, dept, salary
FROM ranked
WHERE rn <= 2;

Klassische Aggregate werden mit OVER zu Fenstern: SUM(salary) OVER (ORDER BY ...) erzeugt Zeile für Zeile eine laufende Summe. Gib den Rahmen mit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW an, um eine streng zeilenweise laufende Summe zu erhalten.

SELECT name, dept, salary,
  SUM(salary) OVER (
    PARTITION BY dept
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW
  ) AS running_total
FROM employees;

Wissenscheck

Stelle sicher, dass du die wichtigsten Punkte dieser Lektion behalten hast.

1. Welchen Rang weist RANK() bei den Gehältern 5200, 4800, 4800, 4100 dem Wert 4100 zu?
   • 3
   • 4
   • 2
2. Warum brauchst du eine CTE, um auf ROW_NUMBER() zu filtern?
   • OVER wird nach WHERE ausgewertet, daher ist es darin verboten
   • Eine CTE ist immer effizienter
   • ROW_NUMBER funktioniert nur innerhalb eines WITH
3. Was macht PARTITION BY dept in einer OVER-Klausel?
   • Es sortiert das Endergebnis nach Abteilung
   • Es startet die Fensterberechnung für jede Abteilung neu
   • Es fasst die Zeilen zu einer einzigen pro Abteilung zusammen