7 Situationen aus dem wirklichen Leben, in denen Sie eine laufende Summe benötigen und wie Sie diese in SQL berechnen können

Möchten Sie erfahren, was eine laufende Summe ist und wie man sie in SQL berechnet? In diesem Artikel beschreibe ich verschiedene Geschäftsszenarien, in denen eine laufende Summe benötigt wird. Außerdem zeige ich Ihnen, wie Sie eine laufende Summe in SQL mit Hilfe von Fensterfunktionen berechnen können. Sind Sie bereit? Los geht's!

Definition der laufenden Summe

Eine laufende Summe ist die kumulative Summe eines Wertes und aller vorherigen Werte in der Spalte.

Stellen Sie sich zum Beispiel vor, Sie sind im Verkauf tätig und speichern Informationen über die Anzahl der an einem bestimmten Tag verkauften Artikel. Sie möchten vielleicht eine laufende Summe berechnen, also die Gesamtzahl der verkauften Artikel bis zu einem bestimmten Datum.

Nachfolgend finden Sie eine Tabelle mit der Anzahl der an einem bestimmten Tag verkauften Artikel:

Anzahl der verkauften Artikel und laufende Summe

Die dritte Spalte ist die berechnete Gesamtsumme. Beachten Sie, dass der aktuelle Zeilenwert immer in der laufenden Summe enthalten ist.

Am 05. Januar 2021 zum Beispiel beträgt die laufende Summe 66. Dies ist die Gesamtzahl der verkauften Artikel vom 01. Januar 2021 bis zum 05. Januar 2021 (einschließlich des 05. Januar 2021). Konkret lautet die Berechnung 10 + 12 + 15 + 9 + 20 = 66.

Berechnung der laufenden Summe in SQL

In SQL wird die laufende Summe mit Hilfe von Fensterfunktionen berechnet. Mit dieser speziellen SQL-Funktion können komplexe Berechnungen mit nur wenigen Zeilen SQL-Code durchgeführt werden.

Hier ist die Fensterfunktion für unser obiges Beispiel:

SELECT *,
      SUM(no_of_items) OVER(ORDER BY date) AS running_total 
FROM sales

Und hier ist eine Erklärung des Codes:

• Dies ist eine Fensterfunktion, die mit der OVER -Klausel gekennzeichnet ist.
• Innerhalb der OVER-Klausel befindet sich ein ORDER BY, das angibt, wie die Zeilen vor der Berechnung angeordnet sind. In unserem Fall sind die Zeilen nach der Datumsspalte geordnet.
• Bei der Verwendung von Fensterfunktionen wird für jeden Datensatz eine eigene Berechnung durchgeführt. Das Ergebnis wird also mit einer zusätzlichen Spalte angezeigt. Bei der Arbeit mit Fensterfunktionen gibt es keine eingeklappten Zeilen. Dies ist der Hauptunterschied zwischen der GROUP BY Klausel und Fensterfunktionen in SQL.
• Innerhalb der OVER-Anweisung finden Sie möglicherweise auch das Schlüsselwort PARTITION BY. PARTITION BY gruppiert Zeilen in Partitionen, wobei jede Partitionsberechnung separat durchgeführt wird. In unserem Beispiel sind die Zeilen nicht in Partitionen organisiert. Der gesamte Datensatz wird also als eine Gruppe behandelt.
• Bei der Arbeit mit Fensterfunktionen definieren wir für jeden Datensatz in einer Tabelle einen Fensterrahmen, innerhalb dessen eine bestimmte Berechnung durchgeführt wird. Dies wird durch das Schlüsselwort ROW/RANGE innerhalb der Anweisung OVER angegeben. Wenn dieses Schlüsselwort nicht definiert ist (wie in unserem Beispiel), wird ein Standardwert für den Fensterrahmen verwendet. Ein Standard-Fensterrahmen für die aktuelle Zeile umfasst die aktuelle Zeile und alle vorherigen Zeilen.
• In unserem Beispiel wird die Gesamtsumme jedes Datensatzes in der Tabelle durch Summierung aller vorherigen Werte plus des aktuellen Wertes berechnet. Zum Beispiel beträgt die laufende Summe für den 05. Januar 2021 66. Dies ist die Summe der vier vorherigen Werte (10, 12, 15 und 9) plus dem Wert in der aktuellen Zeile (20).

Beispiele für laufende Summen

Schauen wir uns nun einige reale Szenarien an, in denen laufende Summen verwendet werden.

1. die Verfolgung von geplanten und realisierten Quoten

Im Vertrieb muss jeder Vertriebsmitarbeiter Ziele erreichen, um bestimmte Arbeitsanforderungen zu erfüllen. Auch in der Telekommunikations- und Bankenbranche muss jede Abteilung in jedem Quartal eine bestimmte Anzahl neuer Kunden gewinnen und eine bestimmte Anzahl von Produkten an Kunden verkaufen.

Das Management verfolgt die Leistung anhand laufender Summen. Jeden Tag wird die laufende Summe mit neuen Daten aktualisiert. Diese Zahlen werden in der Regel monatlich, vierteljährlich oder jährlich ausgewertet.

Die folgende Tabelle zeigt die Anzahl der neuen Kredite im Jahr 2021 auf monatlicher Basis:

Anzahl der neuen Kredite, geplant vs. realisiert

Für das erste Halbjahr 2021 wird beschlossen, dass in diesem Zeitraum 535 Darlehen an Kunden verkauft werden sollen. Die Planungen werden für jeden Monat einzeln vorgenommen, und 535 ist das laufende Gesamtziel für diese sechs Monate.

Das Management verfolgt die geplanten gegenüber den realisierten Quoten. Bislang wurden 112 neue Darlehen verkauft. Die Tabelle wird im Laufe der Zeit immer wieder aktualisiert.

Nachfolgend finden Sie den SQL-Code zur Berechnung der laufenden Summen:

SELECT 
      date,
      plan,
      SUM(plan) OVER(order by Date) AS plan_running_total,
      realization,
      SUM(realization) OVER(order by Date) AS realization_running_total
FROM sales;

Wie im vorherigen Beispiel wird eine Fensterfunktion zur Berechnung der laufenden Summe verwendet. Die Fensterfunktion wird mit OVER bezeichnet, und innerhalb der Klammern wird die Reihenfolge der Zeilen definiert.

Die verwendete Aggregationsfunktion ist SUM. Wir haben zwei laufende Summen: ?geplante und realisierte Quoten. Wir haben also zwei Fensterfunktionen, und die Tabelle wird um zwei Spalten erweitert.

2. Berechnungen der Bilanz

Laufende Summen werden auch bei der Berechnung des Saldos verwendet. Jedes Mal, wenn eine neue Transaktion durchgeführt wird (eine Zahlung auf ein Konto oder eine Abhebung von einem Konto), wird die kumulierte Summe aktualisiert, und der aktuelle Saldo wird angezeigt.

Unten sehen Sie eine Saldotabelle:

Transaktionsbetrag und aktueller Saldo

In der obigen Tabelle sehen wir, dass die erste Transaktion am 01.12.2020-12-01 stattfand, ein Zufluss von 5.000 $. Der Saldo an diesem Tag betrug 5.000 $. Danach begann der Kunde, Geld auszugeben.

Am 03.12.2020 gab der Kunde 50 $ aus (diese Transaktion wird mit einem negativen Wert angezeigt). Damit sank der Saldo auf 4.950 $. Am nächsten Tag verringerte sich der Saldo um weitere 125 $ auf 4.825 $ und so weiter.

Der Kontostand wird als laufende Summe berechnet. Er ist die kumulative Summe aller mit diesem Konto verbundenen Transaktionen. Mit jeder neuen Transaktion wird der Saldo aktualisiert, d. h. die laufende Summe wird neu berechnet.

Hier ist die Fensterfunktion für dieses Beispiel:

SELECT date,
            transaction,
           SUM(transaction) OVER(order by date) AS balance_amount 
FROM balance;

3. Operationen in der Registrierkasse

Laufende Summen werden auch bei Kassenvorgängen verwendet.

Wenn zum Beispiel ein Kunde in einem Geschäft einkauft, scannt der Kassierer die Artikel im Korb. Bei jedem neu eingescannten Artikel wird die aktualisierte Gesamtsumme auf dem Bildschirm angezeigt. Diese zeigt an, wie viel der Kunde für die bisher eingescannten Artikel zu zahlen hat.

Unten sehen Sie ein Beispiel für einen Korb, der gerade gescannt wird. Die Windeln sind der zuletzt gescannte Artikel. Bis jetzt muss der Kunde 19,70 EUR bezahlen. Das Shampoo wurde noch nicht verarbeitet, daher ist sein Preis noch nicht in der laufenden Summe enthalten:

Registrierkasse: Scannen von Produkten

Dies ist fast die gleiche Fensterfunktion wie im vorherigen Beispiel (nur die Tabellen- und Spaltennamen unterscheiden sich):

SELECT 
       *,
      SUM(price) OVER(order by date) as running_total 
FROM cash_register;

Jedes Mal, wenn die Kassiererin einen Artikel einscannt, wird die kumulierte Summe aktualisiert. Mit jedem Scannen weiß die Kassiererin, wie viel der Kunde bis dahin zu zahlen hat.

4. Zählen der täglichen Kalorienzufuhr

Wenn Sie abnehmen möchten, können Sie Ihre tägliche Kalorienzufuhr anhand einer laufenden Summe berechnen. Jeden Tag beginnen Sie mit der Kalorienzählung bei Null und aktualisieren dann die laufende Summe auf der Grundlage dessen, was und wie viel Sie essen.

Unten sehen Sie eine Kalorienzählung für einen Tag:

Die laufende Gesamtzahl wird jedes Mal aktualisiert, wenn Sie etwas essen. So wissen Sie immer, wie viel Sie bereits gegessen haben und wie viel Sie an diesem Tag noch essen können. Dieser Ansatz wird als Kalorienzählen bezeichnet und ist eine beliebte Option.

Hier ist der Code, mit dem die laufende Summe berechnet wird:

SELECT calorie_intake.*,
      SUM(calories) OVER(ORDER BY hours) AS running_total
FROM calorie_intake;

Gehen wir noch ein paar weitere Szenarien durch, in denen laufende Summen verwendet werden. Denken Sie daran, dass die Fensterfunktionen zur Berechnung der Gesamtzahl der gelaufenen Runden meist gleich bleiben. Daher werde ich die Beispiele von nun an kurz erläutern, ohne den SQL-Code einzubeziehen.

5. bestätigte COVID-19-Fälle

Jedes Land sammelt jeden Tag Informationen über die Gesamtzahl der bestätigten COVID-19-Fälle, der Todesfälle und der geheilten Fälle. Mit diesen neuen Informationen werden die laufenden Summen aktualisiert, so dass jedes Land weiß, wie viele bestätigte Fälle, Todesfälle und geheilte Fälle es seit Beginn der Pandemie bis jetzt hatte.

Weitere Einzelheiten zur Berechnung der laufenden Gesamtzahl anhand der John Hopkins-Daten finden Sie unter: Analyse der COVID-19-Daten.

6. registrierte Nutzer einer mobilen Anwendung

Besitzer mobiler Anwendungen möchten in der Regel die kumulative Summe der neu registrierten Nutzer und der Installationen/Deinstallationen im letzten Monat, Quartal und/oder Jahr sehen. Auch hier können Sie laufende Summen verwenden, um einen besseren Einblick zu erhalten. Diese Statistiken geben Aufschluss darüber, wie die Anwendung oder eine bestimmte Funktion angenommen wurde und wie das Produkt in Zukunft weiterentwickelt werden kann.

Wenn Sie eine Website betreiben, ist die Gesamtzahl der Seitenaufrufe oder Besuche eine der wichtigsten Kennzahlen, die Sie verfolgen sollten. Diese Informationen werden in der Regel täglich erfasst. Durch die Berechnung der laufenden Gesamtzahl können Sie sehen, wie sich die kumulierte Summe im Laufe der Zeit verhält und wie sie sich mit früheren laufenden Gesamtzahlen vergleicht.

7) Treueprogramme von Fluggesellschaften

Dieses Konzept ist denjenigen, die häufig fliegen, wahrscheinlich bekannt. Mit jedem Flug sammeln Sie Punkte. Sobald Sie eine bestimmte Anzahl von Punkten gesammelt haben, erhalten Sie Rabatte und/oder Prämien.

Die Gesamtzahl der Punkte, die Sie auf Ihrem Konto haben, wird anhand einer laufenden Summe berechnet. Jedes Mal, wenn Sie ein Ticket kaufen, wird die laufende Summe aktualisiert, und Ihr Punktestand erhöht sich.

Zusammenfassung

In diesem Artikel habe ich gezeigt, wie laufende Summen in verschiedenen Szenarien verwendet werden. Wie Sie gelernt haben, haben laufende Summen viele Anwendungen.

Mit SQL-Fensterfunktionen können Sie laufende Summen relativ einfach mit nur wenigen Zeilen Code berechnen. Fensterfunktionen werden verwendet, wenn es um komplexe Berechnungen geht. Sobald Sie die Syntax gelernt haben, werden Sie saubereren und verständlicheren Code schreiben.

Obwohl ich nur erklärt habe, wie man Fensterfunktionen für laufende Summenberechnungen verwendet, werden Fensterfunktionen für eine Vielzahl von Berechnungen eingesetzt. In diesem Artikel mit Beispielen für Fensterfunktionen erfahren Sie mehr über andere Möglichkeiten, Fensterfunktionen zu verwenden.

Wir bieten auch einen großartigen interaktiven Fensterfunktionen Kurs mit vielen Beispielen an. Wenn Sie tief in die Fensterfunktionen eintauchen möchten, empfehle ich Ihnen diesen Kurs sehr. Er bietet eine Fülle von Übungen, was wichtig ist, wenn man sich neue SQL-Kenntnisse aneignet.