Rollierende Summe in SQL: Was es ist und wie man es berechnet

Die Berechnung einer rollierenden Gesamtsumme in SQL ist einfach, wenn Sie die Fensterfunktionen verwenden. Diese praktischen Metriken ähneln laufenden Summen und gleitenden Durchschnitten und helfen Ihnen, sich ein klares Bild von Ihren Daten zu machen. In diesem Artikel werden wir rollierende Gesamtwerte erklären und untersuchen.

In SQL ist eine gleitende Gesamtsumme eine wichtige Metrik, die Ihnen hilft, Trends im Laufe der Zeit zu erkennen und ein klares Bild von Ihren Daten zu erhalten. Es wird eine Summe über ein "Fenster" oder eine Teilmenge von Zeilen berechnet, die sich durch die Ergebnismenge bewegt. Um beispielsweise die Besuche auf einer Website zu verfolgen, können Sie die Gesamtanzahl der Besuche für jeden Tag abfragen und auch die Summe der letzten 7 Tage bilden, um Trends zu erkennen. Wenn Sie die Fensterfunktionen von SQL verwenden, können Sie diese Trends in jeder Zeile Ihrer Abfrageergebnisse sehen, ohne auf externe Tools zurückgreifen zu müssen.

Um rollierende Summen in SQL einfach zu berechnen, benötigen Sie Fensterfunktionen. Unser Fensterfunktionen Kurs ist perfekt, wenn Sie dieses Thema wirklich vertiefen möchten. In diesem Kurs absolvieren Sie 218 Übungen, die die Syntax und Semantik von Fensterfunktionen behandeln. Sie lernen das volle Potenzial von Fensterfunktionen und typische Anwendungsfälle kennen. Und Sie werden ein Verständnis für fortgeschrittenes SQL erlangen, das Sie in die Lage versetzen wird, komplexere und effizientere Analysen durchzuführen.

Eine einfache rollierende Summe in SQL

Schauen wir uns eine häufige Geschäftssituation an, in der die rollierende Summe ihre Nützlichkeit demonstriert. Nehmen wir eine Tabelle namens daily_sales die die täglichen Verkaufszahlen eines Geschäfts aufzeichnet:

Wenn wir uns diese Daten ansehen oder sogar ein Diagramm daraus erstellen, sehen wir folgendes:

Wir können bestimmte Ereignisse analysieren, z. B. Daten mit ungewöhnlich niedrigen Umsätzen. Zum Beispiel sind die Verkäufe am 2024-08-03 niedriger als an anderen Tagen. Diese Art der Visualisierung von Informationen ist jedoch nicht geeignet, um Trends in der Umsatzentwicklung zu erkennen. Dazu müssen wir uns die rollierenden Gesamtwerte ansehen. Und dafür benötigen wir SQL-Fensterfunktionen.

Wenn Sie eine Kurzanleitung zu den SQL-Fensterfunktionen benötigen, halten Sie dieses Fensterfunktionen Cheat Sheet bereit. Es wird Ihre täglichen Datenanalyseaufgaben beschleunigen.

Berechnung einer gleitenden Gesamtsumme in SQL

Lassen Sie uns eine SQL-Abfrage schreiben, die die beiden Spalten aus daily_sales zurückgibt und eine gleitende Drei-Tage-Summe zu der Ergebnismenge hinzufügt:

SELECT
    purchase_date,
    total_sales,
    SUM(total_sales) OVER (
        	ORDER BY purchase_date
        	ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
     ) AS rolling_total
FROM daily_sales;

Im obigen Beispiel definiert die OVER -Klausel nach der Funktion SUM() das Fenster (d. h. die Gruppe von Zeilen), die wir für jede Zeile summieren wollen. In diesem Fall teilt sie SQL mit, dass die Summe die Daten der beiden vorangegangenen Zeilen und der aktuellen Zeile umfassen soll. Die Reihenfolge richtet sich nach dem Kaufdatum. Dies ist unser rollierendes Fenster: der aktuelle Tag und zwei Tage davor.

Die Abfrage ergibt diese Daten:

In der ersten Zeile sehen Sie, dass die fortlaufende Gesamtsumme nur den Wert aus der Spalte total_sales wiederholt - es gibt keine vorherigen Zeilen zu summieren. In der zweiten Zeile wird der Wert von total_sales für die erste und zweite Zeile summiert; ab der dritten Zeile wird die Summe der Verkaufswerte aus den beiden vorangegangenen Zeilen und der aktuellen Zeile zurückgegeben.

Lassen Sie uns ein Diagramm mit den rollierenden Gesamtdaten erstellen, um zu verstehen, wie es uns bei der Analyse von Trends hilft:

Das erste, was bei diesem Diagramm auffällt, ist, dass wir die ersten beiden Datenpunkte (die ersten beiden Daten) verwerfen sollten, da sie nicht wie die übrigen Daten eine Drei-Tage-Summe darstellen. Ab dem 03.08.2024 wird die Linie fast horizontal, was darauf hindeutet, dass die dreitägigen Verkaufszahlen stabil bleiben. Dies gilt bis zum letzten Datum, an dem ein leichter Anstieg zu verzeichnen ist, da die Gesamtzahl nicht mehr durch den "Rückgang" der Verkäufe vom 2024-08-03 beeinflusst wird.

Bei einem so kleinen Datensatz wirken sich selbst geringe Schwankungen auf den Gesamttrend aus; bei einem großen Datensatz wäre dies nicht der Fall. Wir können jedoch feststellen, dass die Kombination von Aggregatfunktionen und Fensterfunktionen die Auswirkungen von Schwankungen oder Anomalien in den Daten "glättet". Dies ermöglicht eine klare Visualisierung von Datentrends.

Rollierende Summe, laufende Summe und gleitender Durchschnitt

Neben der gleitenden Summe gibt es zwei weitere häufige Anwendungsfälle für Fensterfunktionen: die laufende Summe und der gleitende Durchschnitt.

• Eine laufende Summe ist eine kumulative Berechnung, die durch eine Ergebnismenge läuft. Für jede Zeile wird die Summe aller Werte in einer Spalte berechnet, von der ersten Zeile bis zur aktuellen Zeile. Sie ähnelt der gleitenden Summe, summiert aber immer ab der ersten Zeile der Ergebnismenge.
• Der gleitende Durchschnitt ähnelt der gleitenden Summe insofern, als er Werte aus dem Fenster nimmt, das in Bezug auf die aktuelle Zeile definiert wurde. Der Unterschied besteht darin, dass er den Durchschnitt dieser Werte anstelle der Gesamtsumme zurückgibt.

Die Berechnung dieser Metriken ist der Berechnung einer gleitenden Gesamtsumme sehr ähnlich. Sehen wir uns ein paar Beispiele an.

Berechnen einer laufenden Summe in SQL

Hier ist ein Beispiel für eine laufende Gesamtsumme:

SELECT
    purchase_date,
    total_sales,
    SUM(total_sales) OVER (ORDER BY purchase_date) AS running_total
FROM
    daily_sales;

Der Unterschied zwischen laufender Summe und rollierender Summe in SQL liegt in der Definition des Fensters in der OVER Klausel. Anstatt eine Teilmenge zu definieren, die sich über eine Anzahl von Zeilen relativ zur aktuellen Zeile erstreckt, müssen wir nur das Ordnungskriterium angeben; es wird davon ausgegangen, dass die Summe von der ersten bis zur aktuellen Zeile reicht. Weitere Informationen über laufende Summen finden Sie in unserem Artikel Was ist eine laufende Summe und wie berechnet man sie in SQL.

Berechnung eines gleitenden Durchschnitts in SQL

Das Konzept des gleitenden Durchschnitts ist dem der gleitenden Summe sehr ähnlich. Die Aggregatfunktion AVG() arbeitet mit einem Fenster, das über eine bestimmte Anzahl von Zeilen blättert. Die Abfrage zur Erstellung eines gleitenden Durchschnitts in SQL ist praktisch dieselbe wie die, die wir für die gleitende Summe verwendet haben. Wir müssen nur die Funktion SUM() in AVG() ändern:

SELECT
    purchase_date,
    total_sales,
    AVG(total_sales) OVER (
        	ORDER BY purchase_date
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS moving_average
FROM
    daily_sales;

Dies gibt den durchschnittlichen Umsatz über einen Zeitraum von drei Tagen zurück: den aktuellen Tag und die beiden vorangegangenen Tage. Weitere Einzelheiten über gleitende Durchschnitte finden Sie in diesem Artikel.

Andere Anwendungsfälle für gleitende Gesamtwerte

Wöchentlicher Energieverbrauch

Es gibt viele andere Szenarien, in denen eine gleitende Gesamtsumme verwendet werden kann. Die meisten Beispiele betreffen Tabellen in chronologischer Reihenfolge, wobei das Datenfenster ein Zeitintervall darstellt (z. B. 7 Tage, 30 Tage, 3 Monate usw.).

Ein Beispiel könnte die Analyse der Entwicklung des Energieverbrauchs eines Haushalts im Laufe der Zeit sein. Solche Informationen könnten in einer Tabelle (genannt daily_consumption_per_day) gespeichert werden, die die gesamten Kilowattstunden aufzeichnet, die der Haushalt jeden Tag verbraucht.

Hier ist die Abfrage:

SELECT
    measure_date,
    daily_consumption,
    SUM(daily_consumption) OVER (
        ORDER BY measure_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS weekly_consumption
FROM
    daily_consumption_per_day;

Für jedes Datum zeigt das Ergebnis die Summe des Verbrauchs der letzten 7 Tage (einschließlich des aktuellen Tages). So können wir die Entwicklung des Energieverbrauchs im Laufe der Zeit verfolgen.

Fensterfunktionen haben ein großes Potenzial. In unserem umfassenden Leitfaden zu SQL-Fensterfunktionen erfahren Sie alles, was Sie mit ihnen tun können.

Verwendung von Rolling Totals über die Zeitreihenanalyse hinaus

Die meisten Anwendungsfälle von Rolling Totals beziehen sich auf die Zeitreihenanalyse. Diese Statistiken können jedoch auf jeden nach einer Dimension sortierten Datensatz angewendet werden, bei dem es sinnvoll ist, aggregierte Berechnungen (z. B. Summen, Durchschnitte) über gleitende Fenster durchzuführen, die Teilmengen dieser Dimension isolieren.

Ein Beispiel wäre eine Analyse der Kosten für medizinische Leistungen nach Alter. Nehmen wir an, wir haben eine Tabelle, in der die Kosten für medizinische Leistungen, die von Personen unterschiedlichen Alters in Anspruch genommen werden, erfasst sind. Wenn wir die Zeilen nach Alter ordnen, können wir anhand einer gleitenden Gesamtsumme sehen, wie die Kosten für medizinische Leistungen nach Altersgruppen variieren.

Nehmen wir an, dass die Tabelle den Namen medical_costs_by_age und hat zwei Spalten: Alter und total_medical_cost. Um diese Informationen zu analysieren, schreiben wir eine Abfrage mit einer gleitenden Gesamtsumme, die sich über ein Fünf-Jahres-Fenster erstreckt, das auf das Alter der aktuellen Zeile zentriert ist (von zwei Jahren unter bis zwei Jahren über):

SELECT
    age,
    total_medical_cost,
    SUM(total_medical_cost) OVER (
        ORDER BY age
        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
        ) AS weighted_medial_cost
FROM
    medical_costs_by_age;

Anhand dieses Beispiels können wir ein klares und recht detailliertes Verständnis der Beziehung zwischen Altersgruppen und Gesundheitskosten gewinnen.

Möchten Sie mehr über rollierende Summen in SQL erfahren?

In diesem Artikel haben wir verschiedene Möglichkeiten zur Verwendung einer rollenden Gesamtsumme in SQL kennengelernt. Wir haben sie mit ähnlichen Metriken verglichen, vor allem mit der laufenden Summe und dem gleitenden Durchschnitt.

Wenn Sie die SQL-Fensterfunktionen noch nicht kannten, bevor Sie diesen Artikel gelesen haben, werden Sie wahrscheinlich beeindruckt sein, was sie alles können. Wenn Sie die Fensterfunktionen von Grund auf kennenlernen möchten, empfehle ich Ihnen unseren Fensterfunktionen Kurs. Wenn Sie bereits mit ihnen vertraut sind, nehmen Sie das Fensterfunktionen Practice Set. Es enthält 100 interaktive Übungen zu Fensterfunktionen, die drei verschiedene reale Datenbanken verwenden. Und unsere Lernplattform stellt Ihnen alle notwendigen Werkzeuge zur Verfügung - Sie brauchen nichts zu installieren. Verwenden Sie einfach Ihren Browser und eine Internetverbindung, und schon können Sie loslegen!

Und schließlich können Sie Ihr Verständnis für rollierende Summen in SQL mit dieser Reihe kostenloser Übungsaufgaben für Fensterfunktionen auffrischen. Und wenn Sie eine Stelle anstreben, die SQL-Kenntnisse voraussetzt, und sich auf das Vorstellungsgespräch vorbereiten wollen, sollten Sie unbedingt diese Fragen zum Vorstellungsgespräch mit SQL-Fensterfunktionen lesen. Vielen Dank für die Lektüre dieses Artikels und viel Spaß beim Lernen!