Window-Funktionen für Zeitdaten: LEAD(), LAG()

Jetzt ist unsere Aufgabe – noch einen Schritt weiterzugehen und zu lernen, wie man Window-Funktionen für die Analyse von Zeitdaten einsetzt. Bereit? Ich hoffe, du hast dir eine Tasse Kaffee geschnappt, denn das wird spannend.

Also, wie immer zuerst die wichtigste Frage: Wozu brauchen wir Window-Funktionen (LEAD(), LAG())? Stell dir vor, du arbeitest mit zeitlichen Daten, egal ob Event-Logs, Arbeitszeiten, Zeitreihen oder was auch immer, wo die Reihenfolge der Ereignisse wichtig ist.

Zum Beispiel willst du:

• Herausfinden, wann das nächste Event nach dem aktuellen passiert ist.
• Die Zeitdifferenz zwischen dem aktuellen und dem vorherigen Event berechnen.
• Daten sortieren und die Differenz zwischen den Einträgen berechnen.

Hier kommen zwei richtig coole Funktionen ins Spiel: LEAD() und LAG(). Damit kannst du Daten aus der vorherigen oder nächsten Zeile innerhalb eines bestimmten Fensters holen. Das ist, als hättest du ein magisches Buch, in dem du schon auf die nächste Seite schauen kannst, ohne die aktuelle umzublättern.

LEAD() und LAG(): Syntax und Grundprinzipien

Beide Funktionen nutzen eine ähnliche Syntax:

LEAD(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)
LAG(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)

• column_name — die Spalte, aus der wir die Daten holen wollen.
• offset (optional) — das Offset relativ zur aktuellen Zeile. Standardmäßig ist das 1.
• default_value (optional) — der Wert, der zurückgegeben wird, wenn es keine Zeile mit dem gewünschten Offset gibt (zum Beispiel, wenn du in der letzten Zeile bist).
• OVER() — hier wird das "Fenster" definiert, über das gerechnet wird. Meistens ist das ORDER BY, manchmal nutzt man PARTITION BY, um die Daten in Gruppen zu teilen.

Beispiel: Einfaches LEAD() und LAG()

Lass uns eine einfache Tabelle events für unsere Experimente anlegen:

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT NOT NULL,
    event_date TIMESTAMP NOT NULL
);

INSERT INTO events (event_name, event_date)
VALUES
    ('Event A', '2023-10-01 10:00:00'),
    ('Event B', '2023-10-01 11:00:00'),
    ('Event C', '2023-10-01 12:00:00'),
    ('Event D', '2023-10-01 13:00:00');

Jetzt wollen wir sehen, wann die vorherigen und nächsten Events im Vergleich zu jedem Event passiert sind:

SELECT
    id,
    event_name,
    event_date,
    LAG(event_date) OVER (ORDER BY event_date) AS previous_event,
    LEAD(event_date) OVER (ORDER BY event_date) AS next_event
FROM events;

Das Ergebnis sieht so aus:

Hier holt sich LAG() die Daten aus der vorherigen Zeile und LEAD() aus der nächsten. Das erste Event hat keinen Vorgänger, das letzte keinen Nachfolger, deshalb gibt’s dort NULL.

Beispiel: Differenz zwischen Events

Manchmal willst du wissen, wie viel Zeit zwischen den Events vergangen ist. Dafür kannst du einfach die eine Zeit von der anderen abziehen:

SELECT
    id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (ORDER BY event_date) AS time_since_last_event
FROM events;

Das Ergebnis:

Beispiel: Nutzung von PARTITION BY

Angenommen, wir haben mehrere User, jeder mit seinen eigenen Events. Wir wollen die Differenz zwischen den Events für jeden User berechnen.

Wir aktualisieren die Tabelle und fügen die Spalte user_id hinzu:

ALTER TABLE events ADD COLUMN user_id INT;

UPDATE events SET user_id = 1 WHERE id <= 2;
UPDATE events SET user_id = 2 WHERE id > 2;

Jetzt haben wir zwei User. Wir nutzen PARTITION BY, um innerhalb jeder Gruppe zu rechnen:

SELECT
    user_id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS time_since_last_event
FROM events;

Das Ergebnis:

Beispiele für den Einsatz in echten Aufgaben

1. Event-Logs: Analyse der Zeit zwischen Events, wie User-Login und Logout.
2. Time-Tracking: Berechnung der Zeit, die für bestimmte Tasks aufgewendet wurde.
3. Verhaltensanalyse: Analyse der Reihenfolge von Aktionen der Kunden im Online-Shop.
4. Berechnung von kumulativen Metriken: Einsatz von Window-Funktionen für Zeitreihen.

Typische Fehler

Beim Arbeiten mit LEAD() und LAG() sind die wichtigsten Stolpersteine:

• ORDER BY im OVER() vergessen. Ohne das kann die Funktion die Reihenfolge der Zeilen nicht bestimmen.
• Probleme mit Zeitintervallen oder Datentypen (TIMESTAMP vs DATE).
• NULL-Werte ignorieren, die am Anfang und Ende des Fensters auftauchen können.

Um diese Fehler zu vermeiden, check immer deine Daten und stell sicher, dass du das richtige Fenster für deine Operationen definiert hast.