Zum Inhalt

Analytische SQL-Fensterfunktionen: OVER, PARTITION BY & Fortlaufende Metriken

Erstellt: 08.11.2026  ·  Zuletzt aktualisiert: 08.11.2026  ·  Nejat Hakan

Management Summary: In klassischem SQL aggregiert eine GROUP BY-Klausel mehrere Datensätze zu einer einzigen Summenzeile pro Gruppe zusammen. Dabei gehen jedoch die individuellen Attribute und Zeilenidentitäten der einzelnen Einträge unwiederbringlich verloren. Analytische Fensterfunktionen (OVER (...)) heben diese Beschränkung auf: Sie berechnen kumulierte Summen, Rangfolgen, gleitende Durchschnitte und Vorperioden-Deltas über eine dynamische Partition von Zeilen hinweg – während jede einzelne Zeile ihre vollständige Identität beibehält. Dieser Leitfaden analysiert die Ausführungsreihenfolge im Datenbankkern, Frame-Spezifikationen, Navigationsfunktionen und praxiserprobte Deduplizierungsmuster.

  • Zeilenidentität bewahren

    Gruppenweite Metriken (z. B. Gesamtsumme der Abteilung) parallel zu den Detaildaten des einzelnen Mitarbeiters in einem einzigen Tabellenscan ermitteln.

  • Rangfolgen & Quantile

    ROW_NUMBER(), RANK(), DENSE_RANK() und NTILE() mathematisch unterscheiden und Top-N-Kategorieabfragen deterministisch lösen.

  • Temporale Navigation

    Zeitreihen vor- und zurückspringen mit LAG() und LEAD() – ohne langsame, ressourcenfressende Self-Joins.

  • Gleitende Fensterrahmen

    Präzise Berechnungsgrenzen setzen: ROWS BETWEEN UNBOUNDED PRECEDING für Kontobücher und rollierende 7-Tage-Durchschnitte.

1. Das Kernparadigma: GROUP BY vs. OVER (...)

Der fundamentale Unterschied zwischen Gruppierungen und Fensterfunktionen liegt in der Verdichtung versus Anreicherung der Daten:

%%{init: {'flowchart': {'wrappingWidth': 350}}}%%
flowchart TB
    SRC["Rohdatensatz (100 Zeilen)<br/>(Bestellungen je Kunde)"]
    GRP_OP["GROUP BY customer_id<br/>Fasst zu Gruppen zusammen"]
    WIN_OP["OVER (PARTITION BY ...)<br/>Berechnet Fenster"]
    GRP_RES["Aggregiert (10 Zeilen)<br/>Bestell-IDs verloren!"]
    WIN_RES["Angereichert (100 Zeilen)<br/>Jede Bestell-ID bleibt"]

    SRC --> GRP_OP
    SRC --> WIN_OP
    GRP_OP --> GRP_RES
    WIN_OP --> WIN_RES

Der SQL-Ausführungszyklus

Fensterfunktionen werden erst in einer späten Phase der SQL-Ausführungspipeline berechnet: 1. FROM & JOIN (Arbeitsdatenmenge laden) 2. WHERE (Einzelne Zeilen filtern) 3. GROUP BY (Zeilen zu Gruppen zusammenfassen) 4. HAVING (Aggregierte Gruppen filtern) 5. Fensterfunktionen (OVER) (Auf der gefilterten/gruppierten Datenmenge berechnen) 6. SELECT (Ausgabespalten projizieren) 7. DISTINCT (Duplikate entfernen) 8. ORDER BY (Endergebnis sortieren) 9. LIMIT / OFFSET (Ergebnisstrom begrenzen)

Warum Fensterfunktionen nicht in WHERE-Bedingungen stehen dürfen

Da WHERE Zeilen bereits in Schritt 2 filtert, die Fensterfunktionen jedoch erst in Schritt 5 berechnet werden, führt ein Aufruf wie WHERE ROW_NUMBER() OVER (...) = 1 zu einem Syntaxfehler. Um nach Fenster-Ergebnissen zu filtern, kapselt man die Abfrage in eine Common Table Expression (CTE) oder eine Subquery!

2. Anatomie der OVER-Klausel

Eine Fensterspezifikation besteht aus drei anpassbaren Bausteinen:

FUNKTION() OVER (
    PARTITION BY partition_spalte   -- 1. Teilt Daten in logische Töpfe
    ORDER BY sortier_spalte         -- 2. Sortiert Zeilen innerhalb des Topfes
    ROWS BETWEEN ... AND ...        -- 3. Definiert physische Berechnungsgrenzen
)

%%{init: {'flowchart': {'wrappingWidth': 350}}}%%
flowchart TB
    PART["Partition: customer_id = 42<br/>(Logische Grenze)"]
    R1["Zeile 1: 01.01.2026<br/>50 € | Summe: 50 €"]
    R2["Zeile 2: 15.01.2026<br/>30 € | Summe: 80 €"]
    R3["Zeile 3: 01.02.2026<br/>40 € | Summe: 120 €<br/>(Bezieht vorherige ein)"]

    PART --> R1
    R1 --> R2
    R2 --> R3

Frame-Spezifikationen (ROWS vs. RANGE)

  • Kumulierte laufende Summe: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW Summiert von der allerersten Zeile der Partition bis zur aktuellen Zeile.
  • Zentrierter 3-Punkte-Durchschnitt: ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING Berechnet den Durchschnitt aus der vorherigen, der aktuellen und der nachfolgenden Zeile.
  • Gleitendes 7-Tage-Fenster: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW Erzeugt einen rollierenden 7-Tage-Glättungsdurchschnitt über tägliche Metriken.

3. Die wichtigsten Fensterfunktionen im Werkzeugkasten

A. Rangfolge-Funktionen (Ranking)

Funktion Verhalten bei Werte-Gleichstand Ausgabesequenz für (100, 100, 50) Hauptanwendungsfall
ROW_NUMBER() Fortlaufender eindeutiger Integer 1, 2, 3 Deduplizierung (z. B. neuester Status pro Server).
RANK() Gleicher Rang, überspringt nächste 1, 1, 3 Sport-Ranglisten und Bestenlisten mit Lücken.
DENSE_RANK() Gleicher Rang, keine Lücken 1, 1, 2 Top-N-Kategorien (z. B. die 3 teuersten Produktpreise).
NTILE(n) Teilt Zeilen in n gleiche Buckets Z. B. NTILE(4) für Quartile Performance-Perzentile und Kohortenanalysen.

B. Navigationsfunktionen: Null Self-Joins

Vor der Einführung von Fensterfunktionen erforderte der Vergleich mit dem Vormonat einen Self-Join einer Tabelle auf sich selbst (month = prev_month + 1), was zu quadratischen (O(N2)) Laufzeiten führte. LAG und LEAD lösen dies linear und ohne Joins:

  • LAG(spalte, offset, default): Greift auf Werte zu, die N Zeilen vor der aktuellen Zeile in der Partition liegen.
  • LEAD(spalte, offset, default): Greift auf Werte zu, die N Zeilen nach der aktuellen Zeile in der Partition liegen.

4. Hands-on-Labor: Finanzbuchhaltung & Telemetrie-Deduplizierung

Führe diesen praktischen Workshop in psql aus, um laufende Kontostände, historische Vorumsätze und Server-Log-Deduplizierungen nachzuvollziehen:

-- Datei: /opt/db-ops/window_functions_lab.sql
BEGIN;

-- 1. Kontobuch mit laufendem Saldo und Vorumsatz-Deltas
CREATE TABLE account_ledger (
    entry_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id  INT NOT NULL,
    booked_at   TIMESTAMPTZ NOT NULL,
    amount_cents BIGINT NOT NULL -- Positiv = Haben, Negativ = Soll
);

INSERT INTO account_ledger (account_id, booked_at, amount_cents) VALUES
    (101, '2026-11-01 09:00:00Z', 100000),
    (101, '2026-11-02 14:30:00Z', -25000),
    (101, '2026-11-04 11:15:00Z',  40000),
    (102, '2026-11-01 10:00:00Z',  50000),
    (102, '2026-11-03 16:00:00Z', -15000);

-- Abfrage: Laufender Saldo & Vergleich mit der vorherigen Buchung
SELECT 
    account_id,
    booked_at,
    amount_cents / 100.0 AS transaction_eur,
    -- Vorheriger Buchungsbetrag für dieses Konto
    LAG(amount_cents / 100.0, 1, 0.00) OVER (
        PARTITION BY account_id ORDER BY booked_at
    ) AS prev_transaction_eur,
    -- Laufender Kontostand in Echtzeit
    SUM(amount_cents / 100.0) OVER (
        PARTITION BY account_id 
        ORDER BY booked_at 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_balance_eur
FROM account_ledger
ORDER BY account_id, booked_at;

-- 2. Deduplizierungs-Muster: Den jeweils neuesten Heartbeat je Server isolieren
CREATE TABLE server_heartbeats (
    heartbeat_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    server_node  TEXT NOT NULL,
    cpu_load     NUMERIC(5,2) NOT NULL,
    recorded_at  TIMESTAMPTZ NOT NULL
);

INSERT INTO server_heartbeats (server_node, cpu_load, recorded_at) VALUES
    ('node-alpha', 12.5, '2026-11-08 07:00:00Z'),
    ('node-alpha', 45.2, '2026-11-08 07:05:00Z'),
    ('node-beta',  88.1, '2026-11-08 07:01:00Z'),
    ('node-beta',  22.4, '2026-11-08 07:06:00Z');

-- Den aktuellsten Telemetriestatus je Host ermitteln
WITH ranked_heartbeats AS (
    SELECT 
        server_node,
        cpu_load,
        recorded_at,
        ROW_NUMBER() OVER (
            PARTITION BY server_node 
            ORDER BY recorded_at DESC
        ) AS rank_order
    FROM server_heartbeats
)
SELECT server_node, cpu_load, recorded_at
FROM ranked_heartbeats
WHERE rank_order = 1;

COMMIT;