Was ein gleitender Durchschnitt ist und wie man ihn in SQL berechnet

Möchten Sie tief in Zeitreihendaten eintauchen und langfristige Trends untersuchen? Sie möchten wissen, was ein gleitender Durchschnitt ist und wie man ihn in SQL berechnet? Dann ist dieser Artikel genau das Richtige für Sie. Ich erkläre eine leistungsstarke Funktion in SQL, die Fensterfunktionen, und zeige, wie Sie gleitende Durchschnitte damit berechnen können.

Am besten lernen Sie die SQL-Fensterfunktionen in dem interaktiven Fensterfunktionen Kurs auf LearnSQL.de. Er enthält über 200 praktische Übungen, die Ihnen helfen, Ihre SQL-Kenntnisse zu festigen. Und dieser Kurs ist nur einer von über 30 interaktiven SQL-Kursen mit verschiedenen Schwierigkeitsgraden, die wir anbieten. Melden Sie sich jetzt kostenlos an!

Was ist ein gleitender Durchschnitt?

Der gleitende Durchschnitt ist eine Zeitreihentechnik zur Analyse und Bestimmung von Trends in Daten. Er wird manchmal auch als gleitender Mittelwert, gleitender Durchschnitt oder laufender Durchschnitt bezeichnet und wird als Mittelwert des aktuellen und einer bestimmten Anzahl unmittelbar vorausgehender Werte für jeden Zeitpunkt berechnet. Die Hauptidee besteht darin, zu untersuchen, wie sich diese Durchschnittswerte im Laufe der Zeit verhalten, anstatt das Verhalten der ursprünglichen oder rohen Datenpunkte zu untersuchen.

Die Arbeit mit gleitenden Durchschnitten ermöglicht eine bessere Darstellung der Zeitreihe, da längerfristige Trends mit gleitenden Durchschnitten viel einfacher zu erkennen sind als mit Rohdatenpunkten. Da gleitende Durchschnitte häufig in der Finanzanalyse verwendet werden, werde ich die Daten für die täglichen Aktienkurse eines bestimmten Unternehmens als Beispiel verwenden, um zu erklären, was sie sind.

Unten ist die Tabelle mit dem Namen stock_price die wir in diesem Artikel verwenden werden:

In dieser Tabelle gibt es zwei Spalten (date und price). Es handelt sich um einen Zeitreihendatensatz, da jeder Aktienkurs mit einem bestimmten Zeitpunkt verknüpft ist, d. h. der Preis für jeden Tag ist in dieser Tabelle gespeichert.

Das Liniendiagramm dieser Preise im Zeitverlauf sieht wie folgt aus:

Aus der Tabelle und dem Diagramm können wir einige Dinge ablesen, bevor wir überhaupt Berechnungen anstellen:

• Die Preise in unserer Tabelle stock_price beziehen sich auf den Zeitraum vom 7. Januar 2020 bis einschließlich 30. Juni 2020.
• Die Preise liegen ungefähr zwischen 1.000 und 1.400 (siehe y-Achse).
• Die Preise schwanken; es gibt einige Ausschläge im Februar, mehrere Ausschläge im Juni, usw.

Berechnen wir einen gleitenden Durchschnitt für die Spalte price und erstellen wir ein Liniendiagramm der Durchschnittswerte, um zu sehen, was passiert. In diesem Beispiel arbeiten wir mit einem gleitenden Drei-Tage-Durchschnitt. Dazu berechnen wir den Durchschnitt der Aktienkurse von drei aufeinander folgenden Tagen - dem fraglichen Tag und den beiden vorangegangenen Tagen - und wiederholen den Vorgang für jeden Tag des Datensatzes. Es handelt sich um einen gleitenden Drei-Tage-Durchschnitt, da wir den Durchschnitt über einen Zeitraum von drei Tagen ermitteln.

So wird ein gleitender Drei-Tage-Durchschnitt für den 9. Januar 2020 berechnet:

Für den 9. Januar 2020 wird der gleitende Drei-Tages-Durchschnitt als Mittelwert der Kurse dieses Tages (1.300) und der beiden vorangegangenen Tage berechnet: 8. Januar (1.300) und 7. Januar (1.320). Der gleitende Durchschnitt für den 9. Januar 2020 ist also der Durchschnitt dieser drei Werte, d.h. 1.306,66, wie in der Abbildung oben dargestellt.

Der gleitende Durchschnitt wird für jedes der übrigen Daten auf die gleiche Weise berechnet, indem die drei Aktienkurse des betreffenden Datums und der beiden vorangegangenen Tage addiert und dann durch 3 geteilt werden. Für den 30. Juni beträgt der gleitende Drei-Tage-Durchschnitt 1.070, der Mittelwert der Kurse des 30. Juni (1.067), 29. Juni (1.067) und 28. Juni (1.076).

Wenn wir die ursprünglichen Datenpunkte und den gleitenden Durchschnitt in ein Liniendiagramm eintragen, ergibt sich folgendes Bild:

Die rote Linie stellt den gleitenden Durchschnitt dar, und die blaue Linie die ursprünglichen Datenpunkte. Sie werden feststellen, dass die rote Linie glatter ist und keine Spitzen aufweist, wie sie in der blauen Linie zu sehen sind. Diese Glättung ist das Hauptziel der Technik des gleitenden Durchschnitts - sie dient dazu, Rauschen aus den Daten zu entfernen. Mit weniger Rauschen sind die tatsächlichen Trends in den Zeitreihendaten leichter zu erkennen.

In diesem Beispiel haben wir den gleitenden Durchschnitt für drei Tage berechnet. Wir können jedoch jeden beliebigen Zeitraum berechnen, z. B. den gleitenden Sieben-Tage-Durchschnitt, den gleitenden Zehn-Tage-Durchschnitt und so weiter.

Warum und wo gleitende Durchschnitte verwendet werden

Gleitende Durchschnitte werden im Finanzwesen und im technischen Handel, z. B. bei der Aktienkursanalyse, häufig verwendet, um kurz- und langfristige Trends zu untersuchen. Bleibt der Aktienkurs oberhalb des gleitenden Durchschnitts, liegt ein Aufwärtstrend vor; bleibt er darunter, sprechen Händler von einem Abwärtstrend. Signale wie Aufwärts- und Abwärtstrends dienen den Händlern als Entscheidungshilfe für den Kauf oder Verkauf von Aktien.

Die Analyse von Aktienkursen ist jedoch nicht der einzige Anwendungsfall für den gleitenden Durchschnitt. Andere Geschäftsanwendungen sind:

• Umsatzanalyse: Gleitende Durchschnitte glätten Schwankungen und Spitzen bei wöchentlichen oder täglichen Verkäufen.
• Analyse von bestätigten COVID-19-Fällen: Anhand der gleitenden Durchschnitte lässt sich erkennen, wie sich die Zahl der bestätigten Fälle im Laufe der Zeit verändert.
• Analyse des Internetverkehrs: Anhand gleitender Durchschnitte lassen sich langfristige Trends bei der Anzahl der Besuche und Seitenaufrufe erkennen.
• Persönliche Finanzen: Anhand der gleitenden Durchschnitte lassen sich Trends bei den Ausgaben erkennen (z. B. anhand der Zehntagesdurchschnitte der letzten zwei Jahre).

Gleitende Durchschnitte in SQL

Nachdem Sie nun wissen, was ein gleitender Durchschnitt ist, wollen wir uns ansehen, wie man ihn berechnet. In SQL ist das ganz einfach mit Fensterfunktionen möglich, einer speziellen SQL-Funktion, mit der Sie Aggregationen über Zeilen hinweg durchführen können.

Ähnlich wie bei GROUP BY behalten Fensterfunktionen bei der Anzeige des Ergebnisses alle Zeilen bei; es gibt kein Zusammenklappen von Zeilen. Stattdessen definieren wir mit Fensterfunktionen einen Rahmen oder ein "Fenster" von Zeilen einer bestimmten Größe um die aktuelle Zeile und führen dann eine Berechnung über dieses Fenster durch. Es wird also für jede Zeile in einer Tabelle eine Aggregation durchgeführt; jede Zeile hat ihr eigenes Fenster, über das eine Berechnung durchgeführt wird.

Nachfolgend sehen Sie, wie die Spalte moving_average aus unserem Beispiel in SQL berechnet wird:

select *,
  avg(Price) OVER(ORDER BY Date 
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) 
     as moving_average 
from stock_price;

Um den Code im Detail zu erklären:

• Wir verwenden eine Fensterfunktion, die mit einer OVER -Klausel gekennzeichnet ist. Wie bereits erläutert, werden die Zeilen nicht zusammengeklappt, und jede Zeile hat ihr eigenes Fenster, über das eine Berechnung durchgeführt wird.
• Die Größe des Fensters in unserem Beispiel ist drei. Für jede gegebene Zeile nehmen wir die Zeile selbst und die beiden vorherigen Zeilen und berechnen den Durchschnittspreis aus diesen drei Zeilen. Dies wird durch das Schlüsselwort ROW in der Anweisung ROWS BETWEEN 2 PRECEDING AND CURRENT ROW angegeben. Diese Anweisung besagt, dass für jede Zeile in der Tabelle etwas als Aggregation der aktuellen und der beiden vorhergehenden Zeilen berechnet wird. Das bedeutet, dass der gleitende Durchschnitt für jede Zeile als Mittelwert aus dem aktuellen Tag und den beiden vorangegangenen Tagen berechnet wird.
• Wir haben für jeden Tag einen anderen Fensterrahmen. Unten sehen Sie eine Illustration des Fensterrahmens, der für die Zeile des 9. Januar (in grün) verwendet wird, und des Fensterrahmens, der für die Zeile des 27. Juni (in blau) verwendet wird:
• Es ist wichtig, dass die Daten keine Datumslücken aufweisen. Für jeden Tag müssen wir den Durchschnitt der Preise von diesem Tag und den beiden vorangegangenen Tagen berechnen. Wenn in den Daten Datumslücken vorhanden sind, ist diese Analyse nicht sinnvoll.
• Mit dem Schlüsselwort ORDER BY in der Klausel OVER wird die Reihenfolge der Zeilen festgelegt, über die der gleitende Durchschnitt berechnet werden soll. In unserem Beispiel werden die Zeilen zunächst nach der Datumsspalte sortiert, dann wird der Fensterrahmen definiert und die Berechnung durchgeführt.
• In diesem Beispiel verwenden wir das Schlüsselwort PARTITION BY nicht in der Klausel OVER. PARTITION BY gruppiert die Zeilen in logische Abschnitte nach einer Kategorie, aber wir gruppieren die Zeilen hier nicht auf diese Weise. Im Grunde ist unser gesamter Datensatz eine einzige große Partition. Später in diesem Artikel werden wir ein Beispiel mit einer PARTITION BY sehen.

So, jetzt wissen Sie, wie man gleitende Durchschnitte in SQL berechnet! Der obige SQL-Code kann in vielen anderen Geschäftsszenarien verwendet werden; Sie müssen nur die Tabelle und die Spaltennamen ersetzen und die Anzahl der Zeilen anpassen, für die Sie die Durchschnitte berechnen möchten. Alles andere kann beibehalten werden.

Fensterfunktionen haben eine spezifische Syntax, und es braucht einige Zeit und Übung, um sich mit ihrer Verwendung vertraut zu machen. Um weiter zu lernen und zu üben, empfehle ich den Kurs Fensterfunktionen auf LearnSQL.de. Er ist interaktiv mit vielen Übungen und gibt Ihnen die Möglichkeit, den neuen Stoff schnell zu üben und zu lernen, denn man lernt, indem man es tut! Weitere Informationen über den Kursinhalt und die Fensterfunktionen selbst finden Sie im Artikel SQL-Kurs des Monats: Fensterfunktionen. Auf der gleichen Plattform finden Sie auch tolle Artikel über die Verwendung von Fensterfunktionen und einige Beispiele.

Gleitender Durchschnitt über eine bestimmte Anzahl von Tagen

Im vorherigen Beispiel haben wir einen gleitenden Durchschnitt über drei Tage berechnet. Sie können auch andere Durchschnitte berechnen, indem Sie eine beliebige Anzahl von vorherigen Werten verwenden. Je höher die Anzahl der vorhergehenden Werte ist, desto glatter wird die Kurve sein. Je kleiner die Anzahl der Zeilen ist, die für die Berechnung der Durchschnitte verwendet werden, desto näher liegt die Kurve des gleitenden Durchschnitts an der Kurve der Originalwerte. Es besteht ein großer Unterschied zwischen gleitenden Zwei-Tages- und 30-Tages-Durchschnitten.

Bevor wir eine Grafik erstellen, um dies zu beweisen, lassen Sie uns diese beiden gleitenden Durchschnitte berechnen:

select *,
  avg(Price) OVER(ORDER BY Date 
      ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) 
     as 2day_moving_average,
  avg(Price) OVER(ORDER BY Date 
      ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ) 
      as 30day_moving_average
from stock_price;

Jetzt können wir sehen, wie sich die beiden Linien unterscheiden:

Die grüne Linie stellt den gleitenden 30-Tage-Durchschnitt dar (für die Berechnung jedes Durchschnitts werden 30 Aktienkurse verwendet), und die rote Linie stellt den Zwei-Tage-Durchschnitt dar. Die rote Linie sieht fast genauso aus wie die blaue Linie der ursprünglichen Datenpunkte. Die grüne Linie ist glatter und lässt die langfristigen Trends besser erkennen.

Jetzt fragen Sie sich vielleicht, wie Sie die richtige Anzahl von Zeilen ermitteln können. Nun, da kann ich Ihnen nicht weiterhelfen... Es hängt wirklich von den Bedürfnissen und der Situation des Unternehmens selbst ab und davon, warum Sie die Kennzahl überhaupt analysieren.

Beispiel: Gleitender Sieben-Tage-Durchschnitt von COVID-Fällen

Lassen Sie uns nun ein wenig üben, um zu rekapitulieren, was wir bisher gelernt haben. COVID-19 ist immer noch sehr real in unserem Leben, also werden wir den gleitenden Sieben-Tage-Durchschnitt für die Gesamtzahl der bestätigten Fälle nach Ländern berechnen. Die Anzahl der bestätigten Fälle für jeden Tag und jedes Land wird in einer Tabelle mit dem Namen confirmed_covidgespeichert, und zwar in der Spalte confirmed_day:

Um den gleitenden Sieben-Tage-Durchschnitt der Anzahl der bestätigten Fälle zu berechnen, können wir wie folgt vorgehen:

SELECT *,
      avg(confirmed_day) OVER(
          PARTITION BY country 
          ORDER BY date 
          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 
          AS 7day_moving_average
FROM confirmed_covid;

Sie haben vielleicht bemerkt, dass dieser Code genauso aussieht wie der, den wir für den gleitenden Durchschnitt des Aktienkurses geschrieben haben. Der wichtigste Unterschied ist, dass wir hier Partitionen haben - jedes Land ist eine Partition, weil wir die Sieben-Tage-Durchschnitte für jedes Land separat berechnen. Ansonsten sind nur die Tabelle und die Spaltennamen unterschiedlich. Sie können diesen Code leicht an jedes andere Geschäftsszenario anpassen.

Sobald wir diesen Code ausgeführt und den gleitenden Durchschnitt berechnet haben, können wir das Diagramm mit der Linie erstellen, die den Trend für das spezifische Land vom Beginn der Pandemie bis zum Monat Juli zeigt. Unten sehen Sie das Diagramm für das Land Kroatien:

Auch hier sehen wir, wie der gleitende Durchschnitt die Spitzen und Schwankungen im Vergleich zu den ursprünglichen Datenpunkten glättet und reduziert.

Sie können auch andere Fensterfunktionen verwenden, um aussagekräftige Erkenntnisse aus den COVID-19-Daten zu gewinnen. Wenn Sie an weiteren Informationen interessiert sind, lesen Sie unseren jüngsten Artikel über die Analyse von COVID-19-Daten mithilfe von Fensterfunktionen.

Gleitende Durchschnitte und Fensterfunktionen in SQL

In diesem Artikel haben wir gesehen, wie Sie mithilfe von Fensterfunktionen gleitende Durchschnitte in SQL berechnen können. Diese Durchschnitte helfen uns, die tatsächlichen Trends besser zu erkennen, indem sie das Rauschen reduzieren.

Wenn Sie die Fensterfunktionen üben und mehr darüber erfahren möchten, empfehle ich Ihnen den interaktiven Online-Kurs über Fensterfunktionen, der von LearnSQL.de. Dieser Kurs enthält über 200 Übungen, so dass Sie Ihre neu erlernten Fähigkeiten auf jeden Fall in die Praxis umsetzen werden. Und glauben Sie mir - wenn es um SQL und Programmierkenntnisse geht, ist es wichtig, zu üben - man lernt, indem man etwas tut!