Zusammenhängende Aktivität in SQL Server erkennen

Eine Ereignistabelle speichert einzelne Beobachtungen. Die Fachfrage lautet dagegen oft: Wie viele Tage war ein Kunde ohne Unterbrechung aktiv, und wo endet eine Serie? Dafür muss zuerst Kontinuität definiert werden. Benachbarte Zeilen bedeuten nicht automatisch benachbarte Kalendertage.

Die Beobachtungseinheit normalisieren

Das Beispiel zählt je Kunde und Kalendertag genau eine Beobachtung. Mehrere Ereignisse am selben Tag zählen einmal. Ohne diese Normalisierung können doppelte Daten die Gruppenbildung und die Serienlänge verfälschen. Die Eingabe enthält absichtlich einen doppelten Tag, einen fehlenden Tag und einen zweiten Kunden.

CREATE TABLE #Activity(CustomerId int, ActivityDate date);
INSERT #Activity VALUES
(1,'20230101'),(1,'20230102'),(1,'20230102'),
(1,'20230104'),(1,'20230105'),(2,'20230102');
;WITH days AS
(SELECT DISTINCT CustomerId, ActivityDate FROM #Activity),
previous AS
(SELECT *, LAG(ActivityDate) OVER
 (PARTITION BY CustomerId ORDER BY ActivityDate) AS PrevDate FROM days),
flags AS
(SELECT *, CASE WHEN DATEDIFF(day, PrevDate, ActivityDate)=1
 THEN 0 ELSE 1 END AS NewIsland FROM previous),
groups AS
(SELECT *, SUM(NewIsland) OVER
 (PARTITION BY CustomerId ORDER BY ActivityDate
  ROWS UNBOUNDED PRECEDING) AS IslandId FROM flags)
SELECT CustomerId, MIN(ActivityDate) AS StartDate,
 MAX(ActivityDate) AS EndDate, COUNT(*) AS ActiveDays
FROM groups GROUP BY CustomerId, IslandId
ORDER BY CustomerId, StartDate;

Die erste CTE entfernt doppelte Kunde-Datum-Paare. LAG bestimmt anschließend den vorherigen unterschiedlichen Tag innerhalb jedes Kunden. Eine Markierung kennzeichnet den Anfang oder einen Abstand ungleich einem Tag. Die laufende Summe bildet daraus eine Gruppennummer. Die abschließende Aggregation liefert Beginn, Ende und Anzahl aktiver Tage.

Für Kunde 1 entstehen die Inseln 1. bis 2. Januar und 4. bis 5. Januar. Kunde 2 besitzt eine eigene eintägige Insel. COUNT(*) zählt hier Tage, weil die erste Stufe diese Granularität hergestellt hat. Ohne Bereinigung würde es Ereignisse zählen. Der explizite ROWS-Rahmen verdeutlicht die gewünschte laufende Summierung.

Den fachlichen Kalender festlegen

Kalendertage, Arbeitstage und aufeinanderfolgende Beobachtungen sind unterschiedliche Begriffe. Freitag und Montag unterbrechen eine Kalendertagsserie, können aber dieselbe Arbeitstagsserie bilden. Verwenden Sie dafür eine gepflegte Kalendertabelle mit fortlaufender Arbeitstagsnummer. Vergleichen Sie diese Nummer statt jeweils einen Kalendertag zu addieren.

Bei Zeitstempeln muss vor der Datumsbildung die Berichtszeitzone feststehen. Die Umrechnung aus UTC kann ein Ereignis auf den vorherigen oder nächsten lokalen Tag verschieben. Sommerzeitänderungen verändern die verstrichenen Stunden. Genau 24 Stunden Abstand entsprechen daher nicht immer aufeinanderfolgenden lokalen Tagen. Bewahren Sie den ursprünglichen Zeitstempel zur Nachvollziehbarkeit auf.

Ein Datumsfilter schafft eine weitere Grenze. Beginnt der Bericht am 1. Februar, kann die dort sichtbare Serie bereits im Januar begonnen haben. Entscheiden Sie zwischen einem abgeschnittenen Zeitraum und der vollständigen Serie. Der tatsächliche Anfang benötigt zusätzliche Historie oder einen gepflegten vorherigen Zustand. Eine andere Beschriftung allein löst das Problem nicht.

Grenzen des Musters erkennen

Überlappende Intervalle brauchen einen anderen Vergleich. Die Intervalle 1 bis 10, 2 bis 3 und 9 bis 12 verdeutlichen das: Der dritte Beginn liegt nach dem unmittelbar vorherigen Ende, aber innerhalb des ersten Intervalls. Zum Zusammenführen ist das laufende Maximum aller vorherigen Enden maßgeblich. Zusätzlich muss feststehen, ob Endpunkte eingeschlossen werden.

Beim Tagesmuster kann ein Index mit Kunde und Datum die Bereinigung und Ordnung unterstützen. Ob trotzdem sortiert wird, zeigt der tatsächliche Plan. Prüfen Sie bei großen Historien verarbeitete Zeilen und Speicherauslagerungen. Filtern Sie eine kleine Kundenauswahl möglichst früh, ohne die für vollständige Serien notwendige Vorgeschichte zu verlieren.

Testen Sie einen einzelnen Tag, Duplikate, mehrere Kunden, einen fehlenden Tag und eine Serie über der Berichtsgrenze. Ergänzen Sie verspätete Ereignisse: Ein nachträglich eingefügter fehlender Tag kann zwei vorhandene Inseln verbinden. Gespeicherte Inselnummern sind deshalb keine automatisch dauerhaften Geschäftsschlüssel. Prüfen Sie auch, ob ein gespeicherter Bericht solche Korrekturen neu berechnet oder bewusst seinen damaligen Datenstand behält. Ein belastbares Ergebnis entsteht aus Kalenderregeln und Grenzfalltests, nicht allein aus kurzem SQL.

Technische Referenzen: Microsoft Learn: LAG · Microsoft Learn: OVER.