Berechnung der Länge einer Zeitreihe mit SQL

Wie berechnet man die Länge einer Zeitreihe in SQL? Das kann ich in zwei Worten beantworten: Fensterfunktionen! Ja, Sie benötigen einige Kenntnisse der SQL-Fensterfunktionen, um die Länge von Zeitreihen zu berechnen. Aber was ist eine Zeitreihe, und warum wollen Sie herausfinden, wie lang sie ist?

Was ist eine Zeitreihe?

Obwohl die Definition des Begriffs "Zeitreihe" variiert, handelt es sich im Grunde um eine Folge von Daten, die in zeitlicher Reihenfolge aufgeführt sind.

In einer Datenbank wird dies normalerweise durch Ereignisse dargestellt, die durch eine gleiche Zeitspanne getrennt sind. Beispielsweise werden die Anmeldungen von Benutzern auf der Website an aufeinanderfolgenden Tagen als Serie betrachtet. Die folgende Tabelle zeigt solche Anmeldedaten:

Wenn ich die Länge dieser Serie analysieren wollte, würde ich die Anzahl der aufeinanderfolgenden Anmeldungen zählen. Die Länge der ersten Serie beträgt drei Tage, da sich der Benutzer am 01.06.2020, 02.06.2020 und 03.06.2020 angemeldet hat. Die Länge der zweiten Serie beträgt sechs Tage (der Benutzer hat sich zwischen dem 06.06.2020 und dem 11.06.2020 jeden Tag angemeldet). Nach der gleichen Logik sind die nächsten beiden Serien vier bzw. zwei Tage lang.

Warum eine Serienlänge berechnen?

Zeitreihen werden häufig verwendet, und es gibt viele Situationen, in denen Sie die Länge von Reihen berechnen müssen. Einige Beispiele für die Berechnung der Länge von Zeitreihen sind:

• Messung einer Anmeldestrecke bei Stack Overflow.
• Ihre Aktivitätsreihe bei Duolingo zu sehen.
• Nachverfolgen, wie viele Tage Sie eine Fitness-App verwendet haben.
• Analysieren einer Verkaufsreihe auf einer E-Commerce-Website.
• Ermitteln des aufeinanderfolgenden Anstiegs oder Rückgangs eines Währungswerts.

Im Grunde genommen müssen Sie überall dort, wo Sie eine Zeitreihe haben, wahrscheinlich deren Länge berechnen.

Die wichtigste Frage bleibt: Wie berechnet man die Länge einer Zeitreihe, und wie macht man das in SQL? Wie die obige Tabelle zeigt, ist das Konzept der Zeitreihe nicht schwer zu begreifen. In dem Moment, in dem Sie dieses Beispiel gesehen haben, wussten Sie intuitiv, wie Sie die Länge der Reihe ermitteln können.

Es ist jedoch eine Sache, die Länge einer Reihe manuell über mehrere Datenzeilen zu ermitteln. Bei einer Datenbank mit Hunderten, Tausenden oder Millionen von Zeilen ist das jedoch nicht möglich. Zum Glück gibt es die SQL-Fensterfunktionen, die den Tag retten!

Wenn Sie Ihr Wissen über Fensterfunktionen auffrischen möchten, sollten Sie diesen Spickzettel für Fensterfunktionen lesen. Er kann vor allem dann nützlich sein, wenn wir zum Berechnungsteil kommen.

Wenn Sie nicht einmal wissen, was Fensterfunktionen sind, empfehle ich Ihnen die Lektüre von "Warum sollte ich SQL lernen? Fensterfunktionen ", bevor Sie weitermachen. Der beste Weg, um wirklich ein umfassendes Wissen über Fensterfunktionen zu erlangen, ist der Kurs LearnSQL.de Fensterfunktionen.

Wie man die Länge einer Reihe in SQL berechnet

Für dieses Beispiel stellen wir uns vor, Sie lernen Hochvalyrisch auf Duolingo. Auf der Duolingo-Plattform gibt es eine Funktion, die sich "Streak" nennt. Wie die Website erklärt, ist eine Serie "die Anzahl der Tage in Folge, an denen Sie eine Lektion abgeschlossen haben". Sobald Sie eine Lektion in der App oder auf der Website abgeschlossen haben, erhöht sich Ihr Streak um einen Tag. Du erhältst deine tägliche Belohnung, wenn du dein XP-Ziel erreichst." (XP bedeutet übrigens "Erfahrungspunkte".) Die Überwachung Ihrer Serie ermöglicht es Ihnen, bestimmte Plattformanreize zu erhalten. Woher weiß Duolingo also, wie lang Ihre Serie ist?

Ihre Lernreihe für Juli 2020 kann in der Tabelle dargestellt werden lesson_completed. Sie enthält die folgenden Spalten:

• id: Die ID der Lektion.
• date_completed: Das Datum, an dem Sie die Lektion abgeschlossen haben.

Lassen Sie uns nun eine Abfrage schreiben, um Ihre Serie zu finden. Ich werde einen Common Table Expression (CTE) verwenden, um diese Abfrage zu organisieren. Für diejenigen unter Ihnen, die damit nicht vertraut sind, ist unser Artikel über CTEs ein guter Ausgangspunkt. Wenn Sie diese Abfrage ausführen, wird die Länge der Serie berechnet:

WITH groups AS (
	SELECT	RANK() OVER (ORDER BY date_completed) AS row_number,
			date_completed,
			DATEADD (day, -RANK() OVER (ORDER BY date_completed), 
date_completed) AS date_group
FROM lesson_completed)

SELECT	COUNT(*) AS days_streak,
		MIN (date_completed) AS min_date,
		MAX (date_completed) AS max_date
FROM groups
GROUP BY date_group;

Lassen Sie mich erklären, was ich getan habe. Die Abfrage kann in zwei Teile unterteilt werden:

• Erstellen der CTE.
• Auswählen von Daten aus der CTE.

Erstellen der CTE

Der Teil der Abfrage, der den CTE erstellt, wird unten noch einmal aufgeführt:

WITH groups AS (
	SELECT	RANK() OVER (ORDER BY date_completed) AS row_number,
			date_completed,
			DATEADD (day, -RANK() OVER (ORDER BY date_completed), 
date_completed) AS date_group
FROM lesson_completed)

Der CTE wird durch die WITH Klausel definiert. Ich habe beschlossen, dass der Name dieser CTE Gruppen sein soll. Alles, was in den Klammern nach dem Schlüsselwort AS steht, ist eine ganz normale SELECT -Anweisung.

Was wird nun damit gemacht? Zuerst habe ich die Anzahl der Zeilen zur Tabelle hinzugefügt. Dazu habe ich die Funktion RANK() verwendet. Dies ist eine Fensterfunktion, weshalb sie durch die OVER() -Klausel definiert ist. Ich möchte, dass die Zeilen der Reihe nach nach dem Datum hinzugefügt werden, daher ist das Ergebnis dieser Funktion nach der Spalte date_completed geordnet.

Die CTE wählt dann die Spalte date_completed aus. Ich habe die Funktion DATEADD (SQL Server) verwendet, um die Zeilennummer von date_completed abzuziehen. In dieser Anweisung ...

DATEADD (day, -RANK() OVER (ORDER BY date_completed), date_completed) AS date_group 

... musste ich das Intervall definieren, das hinzugefügt (oder abgezogen) werden soll, nämlich den Tag. Wie viele Tage möchte ich abziehen? Die Tage, die gleich der Anzahl der Zeilen sind - deshalb habe ich nur die Funktion RANK() window kopiert, die ich bereits definiert hatte, und das negative Vorzeichen davor hinzugefügt. Wovon soll das abgezogen werden? Von der date_completed, natürlich!

Dieser Teil der Abfrage wird das folgende Ergebnis liefern:

Wozu brauche ich das? Es wird mir helfen, die Länge der Reihe zu berechnen. Sehen Sie sich die Tabelle an. Haben Sie bemerkt, dass die aufeinanderfolgenden Tage zur gleichen Datumsgruppe gehören? Warum ist das so? Wenn die Daten fortlaufend sind und Sie die Zeilennummer davon abziehen, erhalten Sie immer dasselbe Datum. Sehen Sie sich das an:

Ziehen Sie die Zeilennummer (1) von dem Datum (2020-07-01) ab, und Sie erhalten 2020-06-30. Das ist genau das, was ich in der Tabelle habe.

Und dann die nächste Zeile. Wenn Sie die Zeilennummer (2) vom Datum (2020-07-02) abziehen, ist das Ergebnis wieder 2020-06-30! Schauen wir uns nun an, was passiert, wenn die Daten nicht aufeinanderfolgend sind.

Für Zeile 5 ist das Ergebnis immer noch dasselbe: 2020-06-30. Das ist in Ordnung, aber was ist mit der nächsten Zeile? Ziehen Sie das Datum von Zeile 6 ab (2020-07-08), und was erhalten Sie? 2020-07-02. Dies ist nun eine neue Datumsgruppe, was nicht überraschend ist, da 2020-07-08 in keinem Kalender auf 2020-07-05 folgt!

Ein netter kleiner Trick, nicht wahr? Was die Daten in der Spalte date_group betrifft, so ist es wirklich egal, welche Daten Sie erhalten. Sie dienen nur als Werte, die im zweiten Teil der Abfrage gezählt werden. Zählen Sie, wie oft jede Gruppe vorkommt, und Sie haben Ihre Serienlänge!

Auswählen von Daten aus der CTE

Im zweiten Teil der Abfrage werden die Daten aus der CTE ausgewählt, die ich oben definiert habe:

SELECT	COUNT(*) AS days_streak,
		MIN (date_completed) AS min_date,
		MAX (date_completed) AS max_date
FROM groups
GROUP BY date_group;

Diese einfache Anweisung SELECT zählt die Anzahl der Zeilen und zeigt die Ergebnisse in der neuen Spalte days_streak an. Dann werden das Mindest- und Höchstdatum in der Spalte date_completed ausgewählt und die Ergebnisse in den Spalten min_date bzw. max_date angezeigt. Alle diese Daten werden aus dem CTE namens Gruppen ausgewählt.

Schließlich müssen die Daten nach date_group gruppiert werden. Warum? Weil ich nicht die Gesamtzahl der Zeilen benötige, sondern die Anzahl der Zeilen für jede Datumsgruppe.

Das ist eine Möglichkeit, eine Reihenlänge zu berechnen ...

Dieses kleine Beispiel soll Ihnen eine Vorstellung davon geben, wie man die Länge einer Reihe mit SQL berechnen kann. Es gibt jedoch keinen einzigen Weg, dies zu tun. Und wie Sie gesehen haben, gibt es auch keine einfache SQL-Funktion, die Ihnen das Gewünschte liefert. Ihre Daten und das, was Sie berechnen müssen, bestimmen Ihren SQL-Code. In der Regel ist ein kleiner Trick erforderlich; Ihre SQL-Erfahrung und -Kenntnisse werden Ihnen dabei helfen, den richtigen zu finden!

Was Sie mit Sicherheit wissen müssen, wenn Sie die Länge der Reihe berechnen wollen, sind die SQL-Fensterfunktionen. Wenn Sie immer noch nicht überzeugt sind, dass Sie Fensterfunktionen lernen sollten, lesen Sie dieses Interview mit der Kurserstellerin - sie erklärt alle Vorteile von Fensterfunktionen.

Dieser Artikel hat Ihnen nur einen kleinen Einblick gegeben, was Zeitreihen sind. Wenn Sie lernen möchten, wie Sie diese spezielle Art von Daten analysieren können, finden Sie auf LearnSQL eine sehr detaillierte Anleitung.

Wenn dieser Artikel Ihnen bei der Lösung von Zeitreihenproblemen geholfen hat, lassen Sie es mich im Kommentarbereich wissen.