Was ist eine laufende Summe in SQL und wie wird sie berechnet?

Die SQL-Summenbildung ist ein weit verbreitetes Muster, das häufig im Finanzwesen und in der Trendanalyse verwendet wird. In diesem Artikel erfahren Sie, was eine laufende Summe ist und wie Sie eine SQL-Abfrage schreiben, um sie zu berechnen.

Der beste Weg, SQL zu lernen, ist die Praxis. LearnSQL.de bietet über 30 interaktive SQL-Kurse mit verschiedenen Schwierigkeitsgraden an. Jeder Kurs ist interaktiv: Sie lesen ein wenig, gefolgt von einer Übung, um das Gelesene zu üben. Mit jeder gelösten Übung gewinnen Sie Vertrauen in Ihre SQL-Kenntnisse. Melden Sie sich jetzt kostenlos an!

Was ist eine laufende Summe in SQL?

In SQL ist eine laufende Summe die kumulative Summe der vorherigen Zahlen in einer Spalte. Sehen Sie sich das folgende Beispiel an, in dem die tägliche Registrierung von Benutzern für einen Online-Shop dargestellt wird:

In der ersten Spalte steht das Datum. Die zweite Spalte zeigt die Anzahl der Benutzer, die sich an diesem Tag registriert haben. Die dritte Spalte, total_users, summiert die Gesamtzahl der registrierten Benutzer an diesem Tag.

Zum Beispiel haben sich am ersten Tag (2020-03-05) 32 Benutzer registriert, und der Gesamtwert der registrierten Benutzer beträgt 32. Am nächsten Tag (2020-03-06) registrierten sich 15 Benutzer; der Wert von total_users betrug 47 (32+15). Am dritten Tag (2020-03-07) registrierten sich sechs Benutzer, und der Wert von total_users betrug 53. Mit anderen Worten: total_users ist ein laufender Wert, der sich von Tag zu Tag ändert. Es handelt sich um die Gesamtzahl der Benutzer an jedem Tag.

Das nächste Beispiel verwendet die Spalte total_running, um die Einnahmen des Unternehmens auf ähnliche Weise zu behandeln. Sehen Sie sich die folgende Tabelle an:

Für jeden Tag wird in der Spalte total_revenue der bis zu diesem Tag generierte Umsatz berechnet. Am 04.04.2020 hat das Unternehmen einen Gesamtumsatz von 270.500 $ erzielt, da dies die Summe aller Umsätze von 02.04.2020 bis 04.04.2020 ist.

Relationale Datenbanken (wie SQL Server, Oracle, PostgreSQL und MySQL) und sogar nicht-relationale Engines wie Hive und Presto bieten Fensterfunktionen, mit denen wir eine laufende Summe berechnen können. Um mehr über Fensterfunktionen zu erfahren, empfehle ich den interaktiven Fensterfunktionen Kurs. Er enthält über 200 Übungen zum Erlernen von Fensterfunktionen durch deren Anwendung.

Als Nächstes werden wir uns mit der SQL-Abfrage befassen, mit der eine solche Summe gebildet wird, und mehr über Fensterfunktionen erfahren.

Wie man eine kumulative Summe in SQL berechnet

Wenn Sie eine laufende Summe in SQL berechnen möchten, müssen Sie mit den von Ihrer Datenbank bereitgestellten Fensterfunktionen vertraut sein. Fensterfunktionen arbeiten mit einer Reihe von Zeilen und geben für jede Zeile in der Ergebnismenge einen Summenwert zurück.

Die Syntax der SQL-Fensterfunktion, die eine kumulative Summe über Zeilen hinweg berechnet, lautet:

window_function ( column ) 
OVER ( [ PARTITION BY partition_list ] [ ORDER BY order_list] )

Die Verwendung der OVER-Klausel in einer Window-Funktion ist obligatorisch, aber die Argumente in dieser Klausel sind optional. Wir werden sie in den nächsten Abschnitten dieses Artikels besprechen.

Beispiel 1

In diesem Beispiel soll die laufende Gesamtsumme der registrierten Benutzer für jeden Tag berechnet werden.

Diese Abfrage ...

SELECT registration_date,registred_users,
  SUM(registred_users) OVER (ORDER BY registration_date)
  AS total_users
FROM registration;

... wählt das Registrierungsdatum für alle Benutzer aus. Wir benötigen auch die Summe aller Benutzer für jeden Tag, beginnend mit dem ersten angegebenen Tag (2020-03-05) bis zum Tag in dieser Zeile.

Dies ist die Ergebnismenge:

Um die laufende Summe zu berechnen, verwenden wir die Aggregatfunktion SUM() und geben als Argument die Spalte registered_users an; wir wollen die kumulierte Summe der Nutzer aus dieser Spalte erhalten.

Der nächste Schritt ist die Verwendung der OVER-Klausel. In unserem Beispiel hat diese Klausel ein Argument: ORDER BY registration_date. Die Zeilen der Ergebnismenge werden nach dieser Spalte sortiert (registration_date). Für jeden Wert in der Spalte registration_date wird die Gesamtsumme der vorherigen Spaltenwerte berechnet (d. h. die Summe der Anzahl der Nutzer vor dem Datum in der aktuellen Zeile), und der aktuelle Wert (d. h. die am Tag der aktuellen Zeile registrierten Nutzer) wird dazu addiert.

Beachten Sie, dass die Gesamtsumme in der neuen Spalte angezeigt wird, die wir total_users genannt haben.

Im ersten Schritt (dem Registrierungsdatum 2020-03-05) haben wir 57 registrierte Benutzer. Die Summe der an diesem Tag registrierten Benutzer beträgt ebenfalls 57. Im nächsten Schritt addieren wir zu diesem Gesamtwert (57). Was wird addiert? Die Anzahl der am aktuellen Datum (2020-03-06) registrierten Benutzer, also 27; das ergibt eine laufende Summe von 84. In der letzten Zeile der Ergebnismenge (für das letzte Registrierungsdatum, 2020-03-07) beträgt die laufende Summe 100.

Dank der SQL-Fensterfunktionen ist es einfach, die kumulative Gesamtzahl der Benutzer während eines bestimmten Zeitraums zu ermitteln. Zum Beispiel betrug die Gesamtzahl der registrierten Benutzer im Zeitraum 2020-03-05 - 2020-03-06 84.

Beispiel 2

Im zweiten Beispiel gehen wir näher auf die Benutzer ein. Wir zeigen die Benutzer mit ihren Ländern. Sehen Sie sich die Tabelle unten an:

Beachten Sie, dass für jeden Tag die Anzahl der Nutzer für jedes Land separat angegeben ist. In diesem Beispiel werden wir eine separate kumulative Summe der registrierten Benutzer für jedes Land berechnen.

Diese Abfrage ...

SELECT country, registration_date,registred_users,
  SUM(registred_users) 
  OVER (PARTITION BY country ORDER BY registration_date)
  AS total_users
FROM registration;

... berechnet die Summe der Benutzer für jeden Tag, zuerst für Benutzer aus England und dann für Benutzer aus Polen.

Hier ist die Ergebnismenge:

Für jedes Land erhält jeder Registrierungstag eine laufende Summe. Die PARTITION BY-Klausel in der OVER-Klausel hat die Spalte country als Argument. Dadurch werden die Zeilen nach Ländern aufgeteilt, so dass SQL eine laufende Summe nur für dieses Land berechnen kann (anstatt für beide Länder zusammen). So haben wir in England von 2020-03-05 bis 2020-03-07 insgesamt 47 Benutzer. Für den gleichen Zeitraum waren in Polen insgesamt 53 Nutzer registriert.

Beispiel 3

Im letzten Beispiel analysieren wir die Daten in der Tabelle competition Tabelle, die die Spalten game_id, gamer_id, game_level, competition_date und score enthält.

Wir müssen den kumulativen Gesamtscore eines jeden Spielers für jeden Tag in zwei verschiedenen Spielen überprüfen. Sehen Sie sich die folgende Abfrage an, mit der diese laufende Summe erstellt wird:

SELECT game_id,game_level,gamer_id,competition_date,score,
  SUM(score)
  OVER (PARTITION BY game_id, gamer_id 
        ORDER BY competition_date)
  AS total_score
FROM competition;

Das Ergebnis:

In dieser Ergebnistabelle können wir lesen, dass der Spieler mit ID=4 mit einer Punktzahl von 4 beginnt und mit einer Gesamtpunktzahl von 11 abschließt. Der beste Spieler war der Spieler mit ID=7, der mit einer Gesamtpunktzahl von 12 abschloss.

In der OVER-Klausel verwenden wir wieder PARTITION BY. Diesmal verwenden wir eine Liste von Spalten (game_id, gamer_id). So können wir zwei Partitionen erstellen: eine für Spiel 1 und eine für Spiel 2.

Als nächstes werden die Zeilen für jedes Spiel nach gamer_id unterteilt. In Spiel 1 haben wir die Spieler 4 und 5; in Spiel 2 haben wir die Spieler 6, 7 und 8. Innerhalb jeder Gruppe (ein bestimmter Spieler spielt in einem bestimmten Spiel) werden die Zeilen nach Wettbewerbsdatum sortiert und die Punktzahl jedes Tages addiert. In jeder Gruppe können wir beobachten, wie sich die Punktzahl jedes Spielers in einem bestimmten Spiel verändert.

Wie werden Sie SQL Running Totals verwenden?

Die Verwendung eines laufenden Gesamtwerts in SQL-Berichten kann sehr praktisch sein, insbesondere für Finanzfachleute. Daher lohnt es sich, zu wissen, was eine kumulative Summe ist und wie man sie mit SQL-Fensterfunktionen erstellt. In diesem Artikel werden ein paar ausgewählte Anwendungsfälle vorgestellt. Weitere Informationen zu Fensterfunktionen finden Sie in unserem Artikel SQL Window Function Beispiel mit Erklärungen oder im LearnSQL-Kurs Fensterfunktionen.