Diese Frage entstand aus der Evolution der SQL-Standards von SQL-92 bis SQL:2003, als Fensterfunktionen offiziell in den Standard eingeführt wurden. Vor diesem Fortschritt waren Entwickler auf prozedurale Cursor oder rechenintensive Selbstverknüpfungen angewiesen, um sequenzorientierte Probleme zu lösen. Das Muster "gaps-and-islands" stellt den Paradigmenwechsel von prozeduralen Algorithmen zu deklarativer, mengenbasierter Logik dar, die Ergebnismengen definiert, anstatt Verarbeitungsschritte.
Bei der Arbeit mit Tabellen, die sequenzielle Werte wie Zeitstempel, IDs oder Daten enthalten, müssen Sie aufeinanderfolgende Werte in zusammenhängende Blöcke (Inseln) gruppieren, während Sie sie von Unterbrechungen (Lücken) unterscheiden. Die grundlegende Herausforderung ergibt sich daraus, dass ANSI SQL-Tabellen ungeordnete mathematische Mengen repräsentieren, während zur Erkennung von Sequenzen eine explizite Ordnung erforderlich ist. Traditionelle GROUP BY-Klauseln aggregieren ähnliche Werte, zerstören jedoch die sequentiellen Beziehungen, die erforderlich sind, um Kontinuität zu erkennen.
Nutzen Sie den arithmetischen Unterschied zwischen ROW_NUMBER() über den gesamten Datensatz und ROW_NUMBER() partitioniert nach dem Gruppierungsschlüssel, um einen konstanten Inselidentifikator zu generieren. Diese Technik erstellt identische berechnete Werte für alle Zeilen innerhalb derselben zusammenhängenden Sequenz, wodurch eine standardmäßige Aggregation die Inseln rekonstruieren kann.
WITH numbered AS ( SELECT event_date, ROW_NUMBER() OVER (ORDER BY event_date) AS rn_global, event_date - ROW_NUMBER() OVER (ORDER BY event_date) AS island_grp FROM events ) SELECT MIN(event_date) AS island_start, MAX(event_date) AS island_end, COUNT(*) AS consecutive_days FROM numbered GROUP BY island_grp;
Ein Einzelhandelsanalyse-Team musste Kunden Einkaufssitzungen aus Clickstream-Daten rekonstruieren, die in PostgreSQL gespeichert waren. Das System protokollierte Millionen von Ereignissen mit user_id und event_time, hatte jedoch keine vorab berechneten Sitzungsidentifikatoren. Geschäftliche Anforderungen definierten eine Sitzung als eine Folge von Ereignissen, bei denen keine Lücke 30 Minuten Inaktivität überschritt.
Der erste Ansatz erwog die Verwendung einer Selbstverknüpfung mit einer korrelierten Unterabfrage, um den unmittelbaren Vorgänger jedes Ereignisses zu finden. Diese Methode erforderte O(n²) Zeilenvergleiche, was zu Zeitüberschreitungen bei Abfragen führte, wenn tägliche Batches von über fünf Millionen Zeilen verarbeitet wurden, obwohl sie mit älteren SQL-92-Systemen ohne moderne Fensterfunktionen kompatibel blieb.
Das Team bewertete anschließend pl/pgSQL-Cursor, um durch Ereignisse zeilenweise zu iterieren, während der Sitzungsstatus in prozeduralen Variablen beibehalten wurde. Während dieser Ansatz eine intuitive Logik bot, die Anwendung Entwicklern vertraut war, gab er die Prinzipien der mengenbasierten Verarbeitung auf und benötigte über vier Stunden, um tägliche Batches abzuschließen, was zu unakzeptabler ETL-Latenz und erheblichen Tabellenverriegelungsproblemen führte.
Die gewählte Lösung nutzte ausschließlich ANSI SQL-Fensterfunktionen. Durch die Anwendung von LAG(), um den vorherigen Zeitstempel pro Benutzer zu erfassen, und die Berechnung von Zeitunterschieden identifizierte das Team Sitzungsgrenzen, bei denen Lücken 30 Minuten überschritten. Eine bedingte laufende Summe generierte eindeutige Sitzungsidentifikatoren und ermöglichte mengenbasierte Aggregation. Diese Methode verarbeitete den gesamten Datensatz in acht Minuten, skalierte linear mit dem Volumen und blieb portabel über Oracle, SQL Server und PostgreSQL ohne vendor-spezifische Syntaxänderungen.
Warum kann ich Zeitstempel nicht einfach auf die Stunde kürzen und nach diesem Wert gruppieren, um Sitzungen zu finden?
Das Kürzen von Zeitstempeln mit DATE_TRUNC oder ähnlichen Funktionen erzwingt künstliche Grenzen zu Uhrzeiten, anstatt relative Zeitunterschiede zu berücksichtigen. Zwei Ereignisse, die um 10:55 und 11:05 auftreten, würden in unterschiedliche Gruppen getrennt, obwohl sie nur 10 Minuten auseinanderliegen, während Ereignisse um 10:01 und 10:59 trotz einer Lücke von 58 Minuten zusammen gruppiert würden. Echte Sitzungsdetektion erfordert die Berechnung des Intervalls vom unmittelbaren Vorgänger jedes Ereignisses, nicht die Ausrichtung an kalenderbasierten Grenzen.
Wie beeinflussen NULL-Werte in der Ordering-Spalte die Inseldetektion mit LAG oder LEAD?
LAG und LEAD geben für die ersten und letzten Zeilen jeder Partition jeweils NULL zurück. Bei der Subtraktion des lagged Zeitstempels vom aktuellen Zeitstempel zur Berechnung der Lücken ergibt die Arithmetik mit NULL NULL-Ergebnisse, was dazu führen kann, dass ganze Inseln aus Aggregationen verschwinden. Sie müssen den optionalen default-Parameter in LAG nutzen (z. B. LAG(event_time, 1, event_time) OVER (...)) oder NULL explizit mit COALESCE behandeln, um die Fragmentierung von Inseln an Partitionierungsgrenzen zu verhindern.
Was ändert sich bei der Detektion von Inseln über mehrere Kategorien hinweg gleichzeitig, beispielsweise pro Benutzer oder pro Gerät?
Kandidaten lassen häufig die PARTITION BY-Klausel in Fensterfunktionen aus, und berechnen ROW_NUMBER global über die gesamte Tabelle anstatt pro Kategorie. Ohne Partitionierung nach user_id oder entsprechenden Gruppierungsspalten werden Inseln unterschiedlicher Benutzer fälschlicherweise zusammengeführt, wenn ihre Sequenzen zufällig zeitlich übereinstimmen. Jede Fensterfunktion, die an der Inselfindung beteiligt ist, muss PARTITION BY user_id enthalten, um sicherzustellen, dass die Arithmetik für jede einzelne Entität zurückgesetzt wird und eine unabhängige Inseldetektion pro Partition beibehalten wird.