Zeitliche Analyse mit Stream-Windowing-Funktionen in DuckDB
Petrica Leuca, Gábor Szárnyas
Einleitung
In Datenplattformen kategorisieren wir Daten üblicherweise in Dimensions- und Fakten-Daten. Während Dimensionen Informationen über Entitäten enthalten (Name, Adresse, Seriennummer usw.), enthalten Fakten Ereignisse zu solchen Entitäten (Klicks, Verkäufe, Banktransaktionen, Messwerte von IoT-Geräten usw.). Im Allgemeinen enthalten Fakten-Daten ein Zeitstempel-Attribut, das den Moment bezeichnet, in dem das Ereignis stattfand (oder beobachtet wurde).
Wenn zeitgestempelte Daten auf einer Streaming-Plattform verarbeitet werden, werden sie oft mit Stream-Windowing-Funktionen verarbeitet, um die Daten in Zeitfenster zu organisieren.
In diesem Beitrag zeigen wir, wie sich Stream-Windows auf statische zeitgestempelte Fakten-Daten in DuckDB anwenden lassen, als Teil einer Datenanalyse, um Zugverkehrszusammenfassungen, Trends und Unterbrechungen am Bahnhof Amsterdam Centraal zu berechnen.
In einem zukünftigen Beitrag behandeln wir Streaming-Design-Muster mit DuckDB.
Warnung: Die Datenbank ist ziemlich groß (ca. 1,2 GB), stellen Sie also eine stabile Internetverbindung sicher. Statt die Datenbank anzuhängen, können Sie die Datenbankdatei auch herunterladen und von der Kommandozeile aus verbinden:
Tumbling Windows sind festgroße [links-geschlossen, rechts-offen) Zeitintervalle, um Zusammenfassungen auf einer bestimmten Zeiteinheit (Jahr, Tag, Stunde usw.) zu berechnen. Tumbling Windows werden auch genutzt, um (unregelmäßige) Fakten-Daten in Zeitreihendaten zu verwandeln, indem sie in einem regelmäßigen Zeitintervall aggregiert werden.
Ein Weg, Tumbling Windows umzusetzen, ist die Funktion date_trunc, die den Zeitstempel auf die angegebene Genauigkeit abschneidet. Im Folgenden holen wir zum Beispiel die Zahl der Fahrten für jede Stunde und jeden Tag in 2024:
SELECT
date_trunc('hour', station_service_time) AS window_start,
Ein anderer Ansatz ist die Funktion time_bucket, die den Zeitstempel auf die angegebene Bucket-Breite abschneidet, beginnend beim angegebenen Offset. Zum Beispiel berechnen wir die Zahl der Fahrten jedes Viertelstunde, beginnend mit 00:
Die Time-Bucket-Funktion erzeugt die Buckets aus der Zeitstempel-Spalte selbst, in den Zeitreihendaten können Lücken sein.
Wie im Ergebnis oben zu sehen, ist der erste Datensatz 2024-01-01 01:30:00, weil es vor diesem Zeitstempel keine Datensätze gibt.
Da Tumbling Windows nicht überlappende Intervalle sind, können wir Zusammenfassungen berechnen, etwa die durchschnittliche Zahl der Fahrten in einem 15-Minuten-Intervall. Interessant: Die Zahl der Zugfahrten ist tagsüber ziemlich stabil, nachts aber deutlich niedriger – sogar in Amsterdam.
Hopping Windows
Hopping Windows sind festgroße Zeitintervalle, überlappen im Gegensatz zu Tumbling Windows aber. Ein Hopping Window ist definiert durch:
wie viel Zeit zwischen den Fensterstartzeiten vergehen soll, genannt Hopping Size;
wie viel Zeit ein Fenster enthalten soll, genannt Window Size.
Ein Use Case für Hopping Windows ist, die fünf geschäftigsten 15-Minuten-Perioden (Window Size) in 2024 zu identifizieren, beginnend alle 5 Minuten (Hopping Size). Wir beginnen damit, künstliche Hopping Windows für alle interessierenden Daten zu erzeugen:
WITH time_range AS (
SELECT
rangeAS window_start,
window_start + INTERVAL 15MINUTEAS window_end
FROMrange(
'2024-01-01 00:00:00'::TIMESTAMP,
'2025-01-01 00:00:00'::TIMESTAMP,
INTERVAL 5MINUTE-- hopping size
)
)
┌─────────────────────┬─────────────────────┐
│ window_start │ window_end │
│ timestamp │ timestamp │
├─────────────────────┼─────────────────────┤
│ 2024-01-01 00:00:00 │ 2024-01-01 00:15:00 │
│ 2024-01-01 00:05:00 │ 2024-01-01 00:20:00 │
│ 2024-01-01 00:10:00 │ 2024-01-01 00:25:00 │
│ · │ · │
│ · │ · │
│ · │ · │
│ 2024-12-31 23:45:00 │ 2025-01-01 00:00:00 │
│ 2024-12-31 23:50:00 │ 2025-01-01 00:05:00 │
│ 2024-12-31 23:55:00 │ 2025-01-01 00:10:00 │
├─────────────────────┴─────────────────────┤
│ 105408 rows (6 shown) 2 columns │
└───────────────────────────────────────────┘
Dann joinen wir die obigen Intervalle mit den Zugverkehrsdaten, um die Zahl der Fahrten für jedes [links-geschlossen, rechts-offen) Intervall zu berechnen:
Können Sie sich vorstellen, wie es im Stellwerk gewesen sein muss, als innerhalb von 15 Minuten 28 Züge in einem Bahnhof mit 15 Gleisen ankamen oder abfuhren?
Mit einem RIGHT OUTER JOIN in der obigen Abfrage werden Lücken mit 0 Fahrten gefüllt.
Sliding Windows
Sliding Windows sind überlappende Intervalle, werden im Vergleich zu Hopping Windows aber dynamisch aus der analysierten Zeitspalte erzeugt und ändern sich daher, wenn neue Datensätze eingefügt werden.
Sliding Windows lassen sich mit RANGE-Window-Framing umsetzen:
Weil die aktuelle Zeile in die Berechnung einbezogen wird, sind Sliding Windows [links-geschlossen, rechts-geschlossen].
Session Windows
Ein Session Window gruppiert Ereignisse, die zeitlich nah beieinanderliegen, getrennt durch Inaktivitätslücken. Eine neue Session beginnt, wenn die Zeit zwischen zwei Ereignissen ein definiertes Timeout überschreitet. Der häufigste Use Case von Session Windows ist, Lücken in den zeitgestempelten Daten zu erkennen.
Wir setzen die Datenanalyse fort, indem wir die Tage identifizieren, an denen es Zeiträume von mehr als 10 Minuten gab, in denen kein Zug am Amsterdam Centraal ankam oder abfuhr. In diesem Kontext ist ein Session Window der Zeitraum, in dem Zugfahrten ohne eine Inaktivitätslücke länger als 10 Minuten laufen.
Wir beginnen damit, für jeden Datensatz die vorherige Fahrtzeit mit der Window-Funktion lag zu berechnen. Oben haben wir gesehen, dass nachts fast kein Verkehr ist, daher nehmen wir nur Fahrten zwischen 6 und 23 Uhr:
SELECT
service_sk,
station_service_time,
lag(station_service_time) OVER (
PARTITIONBY station_service_time::DATE
ORDER BY station_service_time
) AS previous_service_time,
date_diff('minute', previous_service_time, station_service_time) AS gap_minutes
FROM ams_traffic_v
WHEREhour(station_service_time) BETWEEN6AND23
In der obigen Abfrage berechnen wir auch die Lücke in Minuten zwischen der aktuellen und der vorherigen Fahrt mit date_diff. Gibt es keine vorherige Fahrt, ist die Spalte NULL und bezeichnet die erste Fahrt-Session des Tages:
Tipp: Weil gap_minutes auf einer Window-Funktion basiert, können wir mit QUALIFY darauf filtern, z. B.: QUALIFY gap_minutes IS NULL
Dann markieren wir, ob der aktuelle Datensatz in derselben Session wie der vorherige ist, indem wir die verstrichenen Minuten mit einem Timeout vergleichen, in unserem Fall 10 Minuten:
IF(gap_minutes >=10OR gap_minutes ISNULL, 1, 0) AS new_session
Mit einer gleitenden Summe auf Tagesebene über das Attribut new_session weisen wir der Session eine Kennung zu:
sum(new_session) OVER (
PARTITIONBY station_service_date
ORDER BY station_service_time ROWSUNBOUNDEDPRECEDING
) AS session_id_in_day
Alles zusammen können wir jetzt die Daten holen, die mindestens eine 10-Minuten-Inaktivitätslücke während der 18 Stunden Tagesverkehr (die Stunden zwischen 6 und 23 Uhr) hatten:
WITH ams_daily_traffic AS (
SELECT
service_sk,
station_service_time,
lag(station_service_time) OVER (
PARTITIONBY station_service_time::DATE
ORDER BY station_service_time
) AS previous_service_time,
date_diff('minute', previous_service_time, station_service_time) AS gap_minutes
Am 29. April 2024 muss etwas passiert sein! Wir sehen, dass es während der 18 Betriebsstunden 12 Session Windows gab, das heißt mindestens zehnmal kam oder fuhr 10 Minuten lang kein Zug. Ein Grund könnte sein, dass an dem Tag ein regulärer Zugverkehr nicht lief. Und tatsächlich begannen Wartungsarbeiten zwischen Amsterdam und Utrecht.
Tipp: Zeitfenster werden mit Plotly-Timeline-Charts visualisiert, einer Art Gantt-Diagramm.
Fazit
In diesem Beitrag haben wir gezeigt, wie sich Stream-Windowing-Funktionen auf historische zeitgestempelte Daten in DuckDB umsetzen lassen – ein Ausgangspunkt für Zeit-(reihen-)Datenanalyse. Wir empfehlen auch „Catching up with Windowing“, einen Beitrag zu DuckDBs Windowing-Features, die in den hier vorgestellten Funktionen übernommen werden können.
