Fensterfunktionen in SQL: ROW_NUMBER, RANK und OVER verstehen

<p>Du kennst wahrscheinlich schon <code>GROUP BY</code>: Du fasst Zeilen zu Gruppen zusammen und berechnest pro Gruppe eine Summe, einen Durchschnitt oder eine Anzahl. Das Problem dabei: Die Einzelzeilen verschwinden. Wenn du wissen willst, wie viel Umsatz ein Verkäufer gemacht hat <strong>und gleichzeitig</strong> jede einzelne Bestellung sehen möchtest, wird es mit reinem <code>GROUP BY</code> umständlich.</p><p>Genau hier kommen <strong>Fensterfunktionen</strong> (englisch: window functions) ins Spiel. Sie rechnen über eine Menge von Zeilen – das sogenannte "Fenster" – geben dir aber trotzdem jede Zeile einzeln zurück. In diesem Beitrag schauen wir uns an, wie das funktioniert.</p> <h2>Die Ausgangsdaten</h2><p>Damit wir etwas zum Anfassen haben, legen wir eine kleine Tabelle mit Bestellungen an. Die Beispiele laufen in PostgreSQL, SQLite (ab 3.25), MySQL (ab 8.0) und MariaDB (ab 10.2).</p> <pre><code class="language-sql">CREATE TABLE bestellungen ( id INTEGER PRIMARY KEY, verkaeufer TEXT NOT NULL, region TEXT NOT NULL, betrag NUMERIC(10,2) NOT NULL ); INSERT INTO bestellungen (id, verkaeufer, region, betrag) VALUES (1, 'Anna', 'Nord', 1200.00), (2, 'Anna', 'Nord', 800.00), (3, 'Ben', 'Nord', 950.00), (4, 'Clara', 'Sued', 1500.00), (5, 'Clara', 'Sued', 300.00), (6, 'David', 'Sued', 1500.00);</code></pre> <h2>OVER: Das Fenster öffnen</h2><p>Jede Fensterfunktion braucht eine <code>OVER</code>-Klausel. Sie sagt der Datenbank: "Rechne über diese Zeilen, aber fasse sie nicht zusammen." Ein leeres <code>OVER ()</code> bedeutet: Das Fenster umfasst <em>alle</em> Zeilen des Ergebnisses.</p> <pre><code class="language-sql">SELECT verkaeufer, betrag, SUM(betrag) OVER () AS gesamtumsatz, ROUND(betrag * 100.0 / SUM(betrag) OVER (), 1) AS anteil_prozent FROM bestellungen;</code></pre> <p>Das Ergebnis hat weiterhin sechs Zeilen – aber jede Zeile kennt jetzt den Gesamtumsatz und ihren prozentualen Anteil daran. Mit <code>GROUP BY</code> allein wäre das nur über eine Subquery gegangen.</p> <h2>PARTITION BY: Das Fenster aufteilen</h2><p>Meistens willst du nicht über alle Zeilen rechnen, sondern gruppenweise. Dafür gibt es <code>PARTITION BY</code>. Du kannst es dir als das <code>GROUP BY</code> innerhalb des Fensters vorstellen – nur eben ohne dass Zeilen verschwinden.</p> <pre><code class="language-sql">SELECT verkaeufer, region, betrag, SUM(betrag) OVER (PARTITION BY region) AS umsatz_region, AVG(betrag) OVER (PARTITION BY region) AS schnitt_region, COUNT(*) OVER (PARTITION BY region) AS anzahl_region FROM bestellungen ORDER BY region, betrag DESC;</code></pre> <p>Jede Zeile aus der Region "Nord" bekommt den Nord-Umsatz, jede Zeile aus "Sued" den Sued-Umsatz. Die Einzelbeträge bleiben sichtbar. Genau das macht Fensterfunktionen so praktisch für Auswertungen und Dashboards.</p> <h2>ROW_NUMBER, RANK und DENSE_RANK</h2><p>Die drei bekanntesten Fensterfunktionen nummerieren Zeilen. Sie unterscheiden sich nur darin, wie sie mit <strong>Gleichständen</strong> umgehen. Dafür brauchen sie ein <code>ORDER BY</code> innerhalb der <code>OVER</code>-Klausel:</p> <ul> <li><code>ROW_NUMBER()</code> – zählt stur 1, 2, 3, 4 durch. Gleiche Werte bekommen trotzdem unterschiedliche Nummern.</li> <li><code>RANK()</code> – gleiche Werte teilen sich den Rang, danach entsteht eine Lücke: 1, 1, 3.</li> <li><code>DENSE_RANK()</code> – gleiche Werte teilen sich den Rang, ohne Lücke: 1, 1, 2.</li> </ul> <pre><code class="language-sql">SELECT verkaeufer, betrag, ROW_NUMBER() OVER (ORDER BY betrag DESC) AS zeilennr, RANK() OVER (ORDER BY betrag DESC) AS rang, DENSE_RANK() OVER (ORDER BY betrag DESC) AS dichter_rang FROM bestellungen;</code></pre> <p>Clara und David haben beide 1500,00 – hier siehst du den Unterschied deutlich: <code>ROW_NUMBER</code> vergibt 1 und 2, <code>RANK</code> vergibt 1, 1 und danach 3, <code>DENSE_RANK</code> vergibt 1, 1 und danach 2.</p> <h2>Der Klassiker: Top-N pro Gruppe</h2><p>Eine Aufgabe, die ohne Fensterfunktionen richtig unangenehm wird: "Gib mir die teuerste Bestellung pro Region." Mit <code>ROW_NUMBER</code> und <code>PARTITION BY</code> wird das zum Dreizeiler.</p> <p>Wichtig: Fensterfunktionen dürfen <strong>nicht</strong> direkt in der <code>WHERE</code>-Klausel stehen, weil sie erst nach dem Filtern berechnet werden. Du brauchst deshalb eine Subquery oder – schöner – ein <code>WITH</code> (Common Table Expression):</p> <pre><code class="language-sql">WITH rangliste AS ( SELECT verkaeufer, region, betrag, ROW_NUMBER() OVER ( PARTITION BY region ORDER BY betrag DESC, id ASC ) AS platz FROM bestellungen ) SELECT verkaeufer, region, betrag FROM rangliste WHERE platz = 1;</code></pre> <p>Der zusätzliche Sortierschlüssel <code>id ASC</code> ist kein Zufall: Bei Gleichstand wäre die Reihenfolge sonst nicht festgelegt und dein Ergebnis könnte bei jedem Lauf anders aussehen. Ein eindeutiges Feld als Tiebreaker macht die Abfrage <strong>deterministisch</strong>.</p> <h2>LAG, LEAD und laufende Summen</h2><p>Zwei weitere Funktionen lohnen sich zu kennen: <code>LAG()</code> greift auf die vorherige Zeile zu, <code>LEAD()</code> auf die nächste. Damit berechnest du Veränderungen von Zeile zu Zeile, ohne die Tabelle mit sich selbst zu verjoinen.</p> <pre><code class="language-sql">SELECT id, betrag, LAG(betrag) OVER (ORDER BY id) AS vorheriger_betrag, betrag - LAG(betrag) OVER (ORDER BY id) AS differenz, SUM(betrag) OVER ( ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS laufende_summe FROM bestellungen ORDER BY id;</code></pre> <p>Die <code>ROWS BETWEEN</code>-Klausel legt den sogenannten <em>Frame</em> fest: Hier "alle Zeilen von ganz oben bis zur aktuellen". Das ergibt eine laufende Summe. In der ersten Zeile liefert <code>LAG</code> übrigens <code>NULL</code>, weil es keine Vorgängerzeile gibt – mit <code>COALESCE(LAG(betrag) OVER (ORDER BY id), 0)</code> fängst du das ab.</p> <h2>Fazit</h2><p>Fensterfunktionen schließen die Lücke zwischen "jede Zeile einzeln" und "alles zusammengefasst". Merk dir die drei Bausteine: <code>OVER</code> öffnet das Fenster, <code>PARTITION BY</code> teilt es in Gruppen auf, und <code>ORDER BY</code> innerhalb von <code>OVER</code> legt die Reihenfolge für Ranglisten und laufende Berechnungen fest.</p><p>Sobald du das Muster "Rangliste in einer CTE, danach filtern" einmal verinnerlicht hast, löst du damit erstaunlich viele Aufgaben: neuester Datensatz pro Kunde, Top 3 pro Kategorie, Duplikate finden, Umsatzentwicklung über die Zeit. Nimm dir die Beispiele oben, spiel mit den Daten und schau dir an, wie sich das Ergebnis ändert, wenn du <code>PARTITION BY</code> weglässt oder <code>RANK</code> gegen <code>DENSE_RANK</code> tauschst. Viel Spaß beim Ausprobieren!</p>