Wie man eine Zeitreihe in SQL analysiert

Nach Zeit geordnete Werte werden als Zeitreihen bezeichnet. In diesem Artikel zeige ich Ihnen die häufigsten Muster und zeige Ihnen, wie Sie Abfragen für Zeitreihen in SQL mit Hilfe von Fensterfunktionen schreiben können.

Vielleicht hatten Sie schon einmal die Gelegenheit, einige Variablen zu analysieren, bei denen jeder Wert mit einem Zeitwert verbunden war. Solche Daten - bei denen die Werte nach der Zeit geordnet sind - nennt man Zeitreihendaten. Da sie in der Wirtschaft so häufig verwendet werden, ist es wichtig, die gängigen Muster für die Analyse dieser Art von Daten zu kennen.

Beispiele für Zeitreihendaten

Zeitreihendaten sind Variablen mit einer zusätzlichen Komponente - der Zeit. Das bedeutet, dass jeder Wert eines Attributs mit einem Datum oder einem Zeitwert verknüpft ist. Hier sind einige Beispiele für Zeitreihendaten:

• Der tägliche Aktienkurs für ein bestimmtes Unternehmen im letzten Jahr. (Jeder Aktienkurs ist mit einem bestimmten Tag verknüpft.)
• Der Tagesdurchschnitt des Dow-Jones-Index für die letzten 10 Jahre. (Jeder Indexwert ist mit einem bestimmten Tag verknüpft.)
• Einzigartige Besuche auf einer Website über einen Monat. (Die Anzahl der Besuche ist jeden Monat unterschiedlich.)
• Die Anzahl der registrierten Nutzer einer Website für jeden Tag.
• Wöchentliche Verkaufszahlen.
• Jährliche Einnahmen und Ausgaben eines Unternehmens über ein Jahrzehnt. (Das Jahr ist der Zeitwert.)
• Tägliche App-Anmeldungen über zwei Monate. (Der Tag ist der Zeitwert.)

In diesem Artikel werden wir die Popularität von zwei fiktiven Websites anhand einer Kennzahl namens "tägliche Gesamtanzahl der Besuche" analysieren. Die SQL-Abfragen, die wir besprechen, können auch für andere Zeitreihenanalysen verwendet werden, d. h. sie sind auf andere Zeitreihendaten anwendbar.

Wir werden den Zeitraum vom 01. Juli 2019 bis zum 31. Dezember 2019 betrachten.

Hier sind die Daten:

daily_visits table

Diese Tabelle hat den Namen daily_visits und enthält die folgenden Attribute:

• date - Ein beliebiger Tag zwischen dem 01. Juli und dem 31. Dezember 2019.
• visits - Die Gesamtzahl der Website-Besuche an einem bestimmten Datum.
• weekend - Dieser Wert ist "N", wenn das Datum ein Wochentag ist, und "Y", wenn es sich um einen Samstag oder Sonntag handelt.
• Website - Der Domänenname der Website ("www.sqlanalysts.com" oder "www.sqldevelopers.com").

Wie Sie vielleicht bemerken, ist jede Zeile in unserer daily_visits Tabelle (d. h. jeder Wert des Attributs Besuche ) mit einem Tag (dem Attribut Datum ) verbunden ist. Dies ist ein Beispiel für Zeitreihendaten.

Ausführen von Summen mit SQL Fensterfunktionen

Wir beginnen unsere Analyse mit einem Muster, das als laufende Summe bezeichnet wird. Eine laufende Summe ist die kumulative Summe aller vorherigen Zahlen in einer Spalte.

Nachfolgend sehen Sie eine laufende Summe für die Anzahl der Besuche vom 01. Juli bis zu einem bestimmten Tag. Beachten Sie, dass dies für jede Website berechnet wird:

In der obigen Abbildung sehen Sie, dass die laufende Summe am 01. Juli 2.805 beträgt (das entspricht der Anzahl der Besuche an diesem Tag). Das liegt daran, dass es für die Daten vor dem 01. Juli keine Daten gibt; wir beginnen die Berechnung mit diesem Datum.

Am nächsten Tag (02. Juli) wird der Wert aus der vorherigen Zeile zur aktuellen Anzahl der Besuche addiert. Die laufende Summe für diesen Tag beträgt 7.203 - die Anzahl der Besuche am 01. Juli plus die Anzahl der Besuche am 02. Juli. Am nächsten Tag, dem 03. Juli, addieren wir diese Zahl (6.744) zu der vorherigen Gesamtzahl (7.203) und erhalten 13.947. Und so geht es weiter.

Dies ist nur ein Geschäftsbeispiel, bei dem das Muster der laufenden Gesamtzahl verwendet wird; einige andere Beispiele sind:

• Gesamtzahl der verkauften Artikel vom ersten Tag eines Monats bis zum beobachteten Tag desselben Monats.
• Laufende Summe (kumulative Summe) der Soll-/Haben-Transaktionen eines Bankkontos im letzten Quartal oder Jahr.
• Gesamteinnahmen von Januar bis zum beobachteten Monat desselben Jahres

Da wir nun wissen, was eine laufende Summe ist, können wir eine SQL-Abfrage schreiben, die eine solche berechnet. Eine laufende Summe (oder kumulative Summe) kann in SQL mit Hilfe der entsprechenden Fensterfunktionen berechnet werden.

Fensterfunktionen sind spezielle SQL-Funktionen, die über eine Reihe von Zeilen arbeiten. Sie ähneln der GROUP BY-Klausel, aber das Ergebnis wird am Ende anders angezeigt. Bei einem einfachen GROUP BY werden die Zeilen zusammengezogen (jede Gruppe wird als eine Zeile angezeigt). Bei Fensterfunktionen werden die Zeilen nicht zusammengeklappt; jede Zeile wird zurückgegeben und der Berechnungswert wird jeder Zeile in der Tabelle zugewiesen. Das ist genau das, was wir brauchen, wenn wir eine laufende Summe anzeigen wollen - einen zusätzlichen Wert in einer bestehenden Tabelle.

Nachfolgend finden Sie eine SQL-Abfrage, die die laufende Summe über die daily_visit Daten berechnet:

SELECT
   *, 
  SUM(visits) OVER (PARTITION BY website ORDER BY date) AS running_total 
FROM daily_visits;

Wenn Sie diese Abfrage ausführen, werden alle Zeilen angezeigt und eine zusätzliche Spalte, running_total, wird erstellt. Hier ist eine kurze Erklärung, was vor sich geht:

• Die OVER-Klausel mit PARTITION BY teilt SQL mit, dass es sich um eine Fensterfunktion handelt.
• PARTITION BY teilt die Zeilen in logische Gruppen ein. In unserem Beispiel werden die Zeilen auf der Ebene der Website gruppiert.
• ORDER BY legt die Reihenfolge der Zeilen fest. In unserem Beispiel werden alle Zeilen nach der Datumsspalte sortiert.
• ROW/RANGE ist in diesem Beispiel nicht explizit definiert. Diese optionalen Schlüsselwörter bedeuten, dass jeder Fensterrahmen innerhalb einer Partition alle Zeilen vom Beginn der Partition bis zur aktuellen Zeile umfasst. Mit anderen Worten, für jede Zeile wird die Gesamtsumme als die Summe der Werte von der ersten Zeile in der Partition bis zur aktuellen Zeile berechnet.

Mit dieser SQL-Anweisung (OVER in Kombination mit PARTITION BY und ORDER BY) wird eine laufende Gesamtsumme der Besuche auf der Ebene der Website erstellt.

Ich werde hier nicht tiefer in die Erklärung und Syntax eintauchen. Wenn Sie die Fensterfunktionen genauer kennenlernen möchten, empfehle ich Ihnen den LearnSQL.de Kurs über Fensterfunktionen. Schauen Sie sich den begleitenden Artikel SQL-Kurs des Monats - Fensterfunktionen an ; er beantwortet Fragen wie die, warum Sie Fensterfunktionen lernen sollten, was sie sind und warum dieser Kurs eine gute Wahl ist.

Fensterfunktionen werden in anderen Musterberechnungen verwendet. Im nächsten Abschnitt werfe ich einen Blick in den Spickzettel für Fensterfunktionen und zeige Ihnen, wie Sie SQL-Abfragen für prozentuale Veränderungen und gleitende Durchschnitte schreiben können.

Prozentuale Veränderung der täglichen Website-Besuche

Sie werden häufig beschreiben müssen, wie sich Ihr Unternehmen im Laufe der Zeit verändert. In der Zeitreihenanalyse wird häufig ein anderes Muster verwendet, das als "prozentuale Veränderung" (oder prozentuale Veränderung) bezeichnet wird. Damit lassen sich Fragen beantworten wie:

• Wie sieht der Umsatz in diesem Monat im Vergleich zum letzten Monat aus? Ist er gestiegen oder gesunken?
• Ist die Zahl der registrierten Nutzer unserer Website in diesem Quartal gestiegen oder gesunken?
• Habe ich heute mehr Artikel verkauft als vor einer Woche?
• Sind unsere Umsätze im Vergleich zum letzten Jahr gestiegen oder gesunken?

Als Nächstes zeige ich Ihnen, wie Sie mit SQL prozentuale Veränderungen berechnen können. Im folgenden Beispiel ermitteln wir die prozentuale Veränderung der Gesamtzahl der Website-Besuche (heute im Vergleich zu gestern und heute im Vergleich zu vor einer Woche). Sobald Sie anhand dieses Beispiels gelernt haben, wie man prozentuale Veränderungen berechnet, können Sie es auf jeden anderen Geschäftsfall anwenden. Die Abfragestruktur ist dieselbe, nur die Tabellen- und Spaltennamen - und vielleicht ein LAG()-Argument, das ich gleich erläutern werde - werden sich unterscheiden.

Verwendung der Funktion LAG() zum Abrufen des Wertes einer vorherigen Zeile

Der erste Schritt bei der Berechnung der prozentualen Veränderung besteht darin, einen Wert aus einer vorherigen Zeile abzurufen. Warum brauchen wir das? Weil die prozentuale Veränderung durch die Formel berechnet wird:

(current_value - previous_value)/previous value * 100.

Diese Formel bedeutet, dass Sie zur Berechnung der Zunahme oder Abnahme der Zahl der Besuche beide Werte in derselben Zeile haben müssen.

Unsere erste Aufgabe bei der Berechnung dieses Musters besteht also darin, einen Wert aus einer früheren Zeile abzurufen. Diese Aufgabe kann mit Hilfe der Fensterfunktion LAG() erledigt werden. Sie ermöglicht den Zugriff auf einen Wert in einer bestimmten Zeile, die vor der aktuellen Zeile liegt. Nachfolgend sehen Sie eine SELECT-Anweisung, die den Wert daily_visits aus der vorherigen Zeile einer neuen Spalte(previous_day_visits) in der aktuellen Zeile zuweist:

SELECT
  *,
  LAG(visits) OVER(PARTITION BY website ORDER BY date) AS previous_day_visits
FROM daily_visits;

Dies ist eine typische Fensterfunktion: Innerhalb der OVER-Klausel legen Sie die gewünschte Aufteilung und Reihenfolge fest. Die Funktion LAG() nimmt ein Argument entgegen (den Namen der Spalte, die die gewünschten Werte enthält) und weist jeder Zeile den Wert der vorherigen Zeile zu:

LAG() kann auch verwendet werden, um Werte aus n Zeilen zurück zuzuweisen, nicht nur aus der vorherigen Zeile. Nehmen wir an, Sie möchten die prozentuale Veränderung für denselben Tag in der vergangenen Woche berechnen. In diesem Fall müssen Sie jeder Zeile einen Wert von vor sieben Tagen zuweisen.

Dazu verwenden wir den optionalen Offset-Parameter von LAG. Sehen Sie sich die folgende Abfrage an:

SELECT
  *,
  LAG(visits,7) OVER(PARTITION BY website ORDER BY date) AS previous_day_visits
FROM daily_visits;

Der Standardversatzwert von LAG ist 1 (die vorherige Zeile), aber Sie können ihn auf jeden anderen Wert ändern. In unserem Beispiel haben wir einen Wert von 7 verwendet, der jeder Zeile die Anzahl der Besuche von vor 7 Tagen zuweist:

Erhöhung/Verringerung der Gesamtzahl der Besuche um 1 Tag

Jetzt können wir mit diesem SQL-Code ganz einfach einen Anstieg bzw. Rückgang um 1 Tag berechnen:

WITH daily_visits_lag AS (
 SELECT
  *,
  LAG(visits) OVER(PARTITION BY website ORDER BY date) AS previous_day_visits
  FROM daily_visits
)
SELECT
    *,
   COALESCE(round((visits — previous_day_visits)/previous_day_visits *100),0) AS percent_change
FROM daily_visits_lag;

Diese Anweisung sieht kompliziert aus, ist es aber nicht. Hier ist, was vor sich geht:

• Die SELECT-Anweisung, die jeder Zeile einen Wert aus der vorherigen Zeile zuweist, wird innerhalb einer WITH -Anweisung geschrieben. Das bedeutet, dass wir einen gemeinsamen Tabellenausdruck oder CTE (d. h. eine benannte temporäre Ergebnismenge) verwenden. Diese temporäre Ergebnismenge wird dann in der nachfolgenden SELECT-Anweisung verwendet.
• Die temporäre Ergebnismenge heißt daily_visits_lag. Sie enthält Werte, die für die Berechnung der prozentualen Veränderung benötigt werden (d. h. die Anzahl der Besuche für die aktuelle Zeile und die Anzahl der Besuche vom Vortag).
• daily_visits_lag wird in der Hauptabfrage verwendet. Eine zusätzliche Spalte, percent_change, wird in COALESCE() mit der Formel(visits-previous_day_visits)/previous_day_visits * 100 berechnet.
• Nachdem diese Anweisung ausgeführt wurde, verwirft die SQL-Engine die temporäre Ergebnismenge; sie kann nicht weiter im Code verwendet werden. (So funktioniert eine CTE.)

Hier ist nicht genug Platz, um auf CTEs einzugehen, aber unser Rekursive Abfragen Kurs ist eine gute Quelle, um mehr über die Verwendung und Syntax von CTEs zu erfahren.

Nach der Ausführung dieser Abfrage werden die folgenden Werte angezeigt:

7-tägige Zunahme/Abnahme der Gesamtzahl der Besuche

Da Sie nun wissen, wie man eine prozentuale Veränderung für 1 Tag berechnet, kann eine sehr ähnliche Anweisung verwendet werden, um eine 7-tägige Abnahme/Zunahme der Gesamtzahl der Besuche zu berechnen:

WITH daily_visits_lag AS (
 SELECT
  *,
  LAG(visits,7) OVER(PARTITION BY website ORDER BY date) AS previous_7day_visits
  FROM daily_visits
)
SELECT
    *,
   COALESCE(round((visits — previous_7day_visits)/previous_7day_visits *100),0) AS percent_change
FROM daily_visits_lag;

Der einzige Unterschied besteht darin, dass wir LAG() mit einem Offset-Parameter von 7 verwendet haben - wir rufen Besuchswerte von vor 7 Tagen (vor einer Woche) ab und berechnen die prozentuale Veränderung mit diesen Werten (aktueller Tag vs. eine Woche zuvor). Mit anderen Worten, wir berechnen die 7-tägige Zunahme oder Abnahme der Anzahl der Besuche:

Beachten Sie, dass die hier verwendete Abfrage auch für andere Geschäftsbeispiele verwendet werden kann. Passen Sie einfach die Tabellen- und Spaltennamen an; der Rest kann gleich bleiben.

Einfache gleitende Durchschnitte: 7 Tage

Ein weiteres sehr häufig verwendetes Muster in der Zeitreihenanalyse ist der einfache gleitende Durchschnitt (SMA). Ein SMA ist der ungewichtete Mittelwert der vorherigen n Zeilenwerte; er wird für jeden Wert in einer bestimmten Spalte berechnet.

SMAs werden häufig zur Bestimmung von Trends bei der Analyse von Aktienkursen oder Kryptowährungen verwendet. Diese Information hilft uns, das Verhalten unserer Variablen zu verstehen: Anstelle eines einzigen Wertes erhalten wir bessere Schätzungen, indem wir die Durchschnittswerte einer bestimmten Maßnahme verwenden. Wir glätten die Schwankungen, um einen Gesamtüberblick zu erhalten.

In unserem Website-Beispiel sind für jeden Tag zwei Zahlen von Interesse:

• Die Anzahl der Besuche, die an diesem bestimmten Tag stattfanden
• Die durchschnittliche Anzahl der Besuche in den letzten 7 Tagen.

Auch hier verwenden wir eine SQL-Fensterfunktion, um unseren SMA zu berechnen. Im Gegensatz zum vorherigen Beispiel, bei dem wir LAG() verwendet haben, um vorherige Zeilenwerte abzurufen, verwenden wir hier den Parameter ROW/RANGE innerhalb der OVER-Klausel:

SELECT
   *, 
   AVG(visits) OVER(PARTITION BY website ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as SMA7
FROM daily_visits ;

Wir haben bereits erwähnt, dass ROW innerhalb einer OVER-Klausel ein Fenster innerhalb jeder Partition definiert. Bei der Berechnung der laufenden Summe haben wir Standardwerte für die Parameter Zeile/Bereich verwendet. Das heißt, der Startpunkt für jedes Fenster innerhalb der Partition war die erste Zeile in dieser Partition und der Endpunkt war die aktuelle Zeile.

Nun ist jedes Fenster als 7 Zeilen definiert (die 6 vorherigen Zeilenwerte + der aktuelle Zeilenwert). Das haben wir mit ZIEHEN ZWISCHEN 6 VORHERIGEN UND AKTUELLER ZEILE gemacht. Mit anderen Worten, der Ausgangspunkt ist nicht die erste Zeile in der Partition. Hier wird der Durchschnittswert(avg(visits)) aus den letzten 7 Zeilen, einschließlich der aktuellen, berechnet. Das ist die Definition eines SMA.

Sobald Sie diese Anweisung ausführen, erhalten Sie einen Durchschnittswert für jede Zeile. Auf diese Weise können Sie Trends bei den Besuchen detaillierter beobachten. Hier sehen Sie das Ergebnis:

Die SQL-Fensterfunktionen sind eine wirklich leistungsstarke Funktion für die Trendanalyse, und der SMA ist nur einer der Indikatoren, die mit Fensterfunktionen ermittelt werden können. Wenn Sie weitere Beispiele für die Trendanalyse sehen möchten, besuchen Sie unseren Kurs Umsatztrendanalyse in SQL. Darin wird erläutert, wie Sie SQL verwenden können, um einen beliebigen Zeitreihentrend zu analysieren.

RANK() verwenden, um die höchste Anzahl von Besuchen zu finden

Unser letztes Analysemuster ist das Ranking. Wie Sie sich vielleicht denken können, ordnet es die Ergebnisse auf der Grundlage einer bestimmten Variablen. Nehmen wir an, wir möchten sehen, an welchen Tagen die höchste Anzahl von Besuchen für jede unserer Websites zu verzeichnen war. Dazu müssen wir die täglichen Besuche für jede Website einzeln auflisten. Das können wir mit der Fensterfunktion RANK() machen:

SELECT *,
  RANK() OVER (PARTITION by website ORDER BY visits DESC) AS rank 
FROM daily_visits;

In diesem Code geschieht Folgendes:

• Der Rang wird durch die Spalte Besuche bestimmt. Diese ist in der ORDER BY-Klausel (OVER) definiert. Die Zeile mit dem höchsten Besuchswert erhält den höchsten Rang.
• Die Zeilen (Besuche) werden für jede Website getrennt gruppiert, so dass der Rang für jede Website einzeln ermittelt wird. Dies wird in PARTITION BY in der OVER-Klausel definiert.
• Zeilen mit gleichen Rangfolgekriterien (d. h. mit demselben Besuchswert ) erhalten denselben Rang.

Sobald Sie diesen SELECT ausführen, gibt die SQL-Engine eine Ergebnismenge mit einer zusätzlichen Spalte namens Rang zurück. Jetzt können wir leicht sehen, welche Tage die höchste Anzahl von Besuchen hatten. Das Tolle daran ist, dass die Rangfolge für jede Website definiert ist, so dass wir die beiden Websites nicht miteinander vergleichen.

Der höchste Rang für sqldevelopers.com

Der höchste Rang für sqlanalysts.com

Erfahren Sie mehr über die Analyse von Zeitreihen mit SQL

Sie können nun SQL-Fensterfunktionen verwenden, um einige grundlegende Zeitreihenanalysen durchzuführen. Sie wissen, was Zeitreihen sind und wie Sie SQL-Fensterfunktionen verwenden können, um einige großartige Erkenntnisse zu gewinnen. Sie haben sogar eine Einführung in CTEs erhalten.

Wie geht es weiter? Ich empfehle auf jeden Fall einen organisierten Ansatz, um mehr über die SQL-Zeitreihenanalyse zu lernen. Die Kurse Fensterfunktionen und Revenue Trend Analysis in SQL-Kurse, die ich bereits erwähnt habe, sind gut. Denken Sie daran, dass es wichtig ist, Ihre Fähigkeiten in die Praxis umzusetzen, also stellen Sie sicher, dass Sie einige reale Aufgaben lösen können!