SQLite Window Functions: OVER, PARTITION BY & Frames

Eine Window Function ergänzt eine Spalte, ohne Zeilen zusammenzufassen

GROUP BY fasst viele Zeilen zu einer einzigen zusammen. SQL Window Functions gehen einen anderen Weg: Sie berechnen einen Wert über eine Gruppe zusammengehöriger Zeilen, behalten dabei aber jede einzelne Eingabezeile in der Ausgabe. So bekommst du Detail und Aggregat direkt nebeneinander.

Das Muster ist immer gleich: eine Funktion, gefolgt von OVER (...).

CREATE TABLE sales (
    employee TEXT,
    region   TEXT,
    amount   INTEGER
);

INSERT INTO sales VALUES
    ('Ada',   'EU', 100),
    ('Boris', 'EU', 250),
    ('Cleo',  'EU', 175),
    ('Dan',   'US', 300),
    ('Eve',   'US', 150);

SELECT employee, region, amount,
       SUM(amount) OVER () AS total_all
FROM sales;

Die Spalte total_all zeigt die Gesamtsumme über alle Zeilen, jeweils in jeder Zeile wiederholt. Die ursprünglichen Zeilen bleiben dabei unangetastet. Zum Vergleich: SELECT SUM(amount) FROM sales liefert zwar dieselbe Zahl, aber eben nur eine einzige Zeile zurück. Mit Window Functions bekommst du beide Sichtweisen auf einmal.

PARTITION BY: Aggregate innerhalb von Gruppen

Ein leeres OVER () aggregiert über die komplette Tabelle. Mit PARTITION BY aggregierst du stattdessen innerhalb von Gruppen – ähnlich wie bei GROUP BY, nur dass die Zeilen wieder erhalten bleiben.

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       amount * 100.0 / SUM(amount) OVER (PARTITION BY region) AS pct_of_region
FROM sales;

Pro Zeile bekommst du sowohl die Summe der jeweiligen Region als auch den prozentualen Anteil daran. Mit einem reinen GROUP BY wäre die Detailansicht pro Mitarbeiter verloren. Genau das ist die Stärke von Window Functions: Detail und Aggregat in einer einzigen Abfrage.

Ranking in SQLite: ROW_NUMBER, RANK und DENSE_RANK

Die Ranking-Funktionen nummerieren Zeilen anhand der ORDER BY-Klausel innerhalb von OVER. Die drei Varianten unterscheiden sich darin, wie sie mit Gleichständen umgehen.

CREATE TABLE scores (player TEXT, points INTEGER);
INSERT INTO scores VALUES
    ('Ada', 90), ('Boris', 85), ('Cleo', 90),
    ('Dan', 70), ('Eve', 85);

SELECT player, points,
       ROW_NUMBER() OVER (ORDER BY points DESC) AS rn,
       RANK()       OVER (ORDER BY points DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY points DESC) AS dr
FROM scores;

So liest du die Ausgabe:

• ROW_NUMBER() ist immer eindeutig — bei Gleichstand wird die Reihenfolge willkürlich festgelegt. Nimm das, wenn du pro Zeile eine stabile, eindeutige Nummer brauchst.
• RANK() vergibt bei gleichen Werten denselben Rang und überspringt dann die nächsten Zahlen. Auf zwei Spieler mit Rang 1 folgt direkt Rang 3.
• DENSE_RANK() vergibt bei Gleichstand ebenfalls denselben Rang, lässt aber keine Lücken. Der nächste Rang ist hier die 2.

Für "Top N pro Gruppe" kombinierst du das Ranking mit PARTITION BY und filterst in einer äußeren Query — denn WHERE kann Window Functions nicht direkt referenzieren:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT *
FROM (
    SELECT employee, region, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
)
WHERE rn <= 2;

Top-Verdiener pro Region – die jeweils zwei besten.

LAG und LEAD: Nachbarzeilen einbeziehen

LAG(col) liefert dir den Wert von col aus der vorherigen Zeile innerhalb des Fensters. LEAD(col) greift entsprechend nach vorn auf die nächste Zeile zu. Beide Funktionen sind ideal, wenn du Veränderungen über die Zeit hinweg auswerten willst.

CREATE TABLE temps (day INTEGER, celsius INTEGER);
INSERT INTO temps VALUES (1, 18), (2, 21), (3, 19), (4, 24), (5, 22);

SELECT day, celsius,
       LAG(celsius)  OVER (ORDER BY day) AS yesterday,
       celsius - LAG(celsius) OVER (ORDER BY day) AS delta
FROM temps;

Beim ersten Datensatz ist yesterday gleich NULL – davor gibt es ja schlicht keine Zeile. Du kannst aber einen Standardwert mitgeben: LAG(celsius, 1, celsius) OVER (ORDER BY day) greift auf den heutigen Wert zurück, falls kein Vorgänger existiert.

LEAD funktioniert genau spiegelverkehrt. In Kombination mit PARTITION BY bekommst du Sequenzen pro Gruppe – etwa, wenn du den Umsatz dieses Monats mit dem des Vormonats je Region vergleichen willst.

Laufende Summe in SQLite mit Window Frames

Sobald du innerhalb von OVER ein ORDER BY ergänzt, rechnen Aggregatfunktionen wie SUM, AVG oder COUNT kumulativ:

CREATE TABLE deposits (day INTEGER, amount INTEGER);
INSERT INTO deposits VALUES (1, 50), (2, 30), (3, 70), (4, 20), (5, 40);

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day) AS running_total,
       AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3day
FROM deposits;

Zwei Dinge fallen hier auf:

• SUM(amount) OVER (ORDER BY day) ergibt eine laufende Summe. Wenn du ORDER BY ohne expliziten Frame schreibst, ist der Default "vom Anfang des Fensters bis zur aktuellen Zeile".
• Die zweite Spalte nutzt einen expliziten Frame: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Das ist ein gleitendes Fenster über 3 Zeilen — also ein gleitender Durchschnitt.

Das mentale Modell für Frames: Jede Window Function rechnet über einen Frame von Zeilen, der relativ zur aktuellen Zeile definiert ist. Die gängigsten Frames:

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — laufende Summe (der implizite Default).
• ROWS BETWEEN N PRECEDING AND CURRENT ROW — nachlaufendes Fenster.
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — die komplette Partition.

ROWS zählt physische Zeilen. Daneben gibt es noch RANGE, das nach Wert gruppiert — praktisch, wenn die ORDER BY-Spalte Gleichstände enthält und du diese als einen einzigen Schritt behandeln willst.

FIRST_VALUE, LAST_VALUE, NTILE

Ein paar weitere Window Functions, die man kennen sollte:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT employee, region, amount,
       FIRST_VALUE(employee) OVER (PARTITION BY region ORDER BY amount DESC) AS top_earner,
       NTILE(2) OVER (ORDER BY amount DESC) AS half
FROM sales;

• FIRST_VALUE und LAST_VALUE liefern den ersten bzw. letzten Wert innerhalb des Frames. Bei LAST_VALUE solltest du den Frame im Blick behalten: Standardmäßig endet er bei CURRENT ROW – meistens willst du also ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING schreiben, um wirklich den letzten Wert der Partition zu bekommen.
• NTILE(n) teilt die Zeilen in n ungefähr gleich große Buckets auf – praktisch für Quartile, Perzentile oder A/B-artige Splits.

Fenster mit WINDOW benennen

Wenn mehrere Spalten dieselbe OVER (...)-Klausel verwenden, wird das Wiederholen schnell mühsam. In SQLite kannst du ein Fenster einmal benennen und dann mehrfach wiederverwenden:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount)   OVER region_window AS region_total,
       AVG(amount)   OVER region_window AS region_avg,
       RANK()        OVER region_window AS region_rank
FROM sales
WINDOW region_window AS (PARTITION BY region ORDER BY amount DESC);

Dieselbe Abfrage, weniger Rauschen. Die WINDOW-Klausel steht nach WHERE/GROUP BY/HAVING und vor ORDER BY.

Window Functions vs. GROUP BY

Beide aggregieren – aber sie beantworten unterschiedliche Fragen:

• GROUP BY reduziert. Eine Zeile pro Gruppe. Nimm das, wenn dich nur die Zusammenfassung interessiert.
• Window Functions erhalten alles. Jede Eingabezeile bleibt bestehen, ergänzt um zusätzliche berechnete Spalten.

Wenn du dich dabei ertappst, ein GROUP BY zu schreiben und die Aggregate anschließend wieder an die Originaltabelle zu joinen, ist das ein deutliches Indiz: Eine Window Function erledigt das in einer einzigen Abfrage.

Ein paar Stolperfallen

• WHERE kann keine Window Functions referenzieren. Filter greifen, bevor die Fenster berechnet werden. Pack die Abfrage in eine Subquery oder CTE und filtere auf der äußeren Ebene.
• Implizite Frames sind tückisch. SUM(x) OVER (ORDER BY y) ist eine laufende Summe, weil der Default-Frame RANGE UNBOUNDED PRECEDING lautet. Willst du die Summe über die gesamte Partition, schreib OVER (PARTITION BY ...) ohne ORDER BY – oder gib den Frame explizit an.
• LAST_VALUE überrascht jeden beim ersten Mal. Da der Default-Frame an der aktuellen Zeile endet, liefert es den aktuellen Wert zurück, nicht den letzten Wert der Partition. Frame überschreiben!
• Window Functions brauchen SQLite 3.25+ (Release 2018). Jede halbwegs aktuelle Installation kann das, aber manche Embedded-Umgebungen hinken hinterher.

Als Nächstes: Generated Columns

Window Functions berechnen zur Abfragezeit. Die nächste Seite zeigt Berechnungen zur Speicherzeit: Generated Columns, bei denen sich der Spaltenwert aus einem Ausdruck ergibt und automatisch aktualisiert wird, sobald sich die zugrundeliegenden Daten ändern.