Verwenden Sie SQL, um eine laufende Summe zu berechnen

Die laufende Summe in SQL kann auf verschiedene Arten berechnet werden. Dieser Artikel behandelt zwei Methoden: die Verknüpfungen und die Fensterfunktionen.

Wir werden uns zunächst ansehen, wie die laufende Summe mit dem INNER JOIN berechnet wird. Auf diese Weise erfahren Sie nicht nur mehr über Join-Bedingungen, sondern auch, wie Sie das Ergebnis zusammenfassen, um die laufende Summe zu erhalten.

Sobald Sie gesehen haben, wie man es im „Old School“ -Stil macht, verwenden wir die OVER-Klausel, um laufende Summen mithilfe einer Fensterfunktion zu berechnen. Diese Methode ist neuer und prägnanter zu verwenden.

Alle Beispiele für diese Lektion basieren auf Microsoft SQL Server Management Studio und der WideWorldImporters-Datenbank. Sie können diese kostenlosen Tools mit meinem Leitfaden verwenden Erste Schritte mit SQL Server 2016.

Was ist eine laufende Summe?

Unser Ziel ist es, die laufende Summe zu berechnen, die zurückgesetzt wird, wenn sich das Transaktionsdatum ändert. Wir summieren den Transaktionsbetrag. Für jede nachfolgende Rechnung innerhalb des Transaktionsdatums sollte runningTotal der laufenden Summe der vorherigen InvoiceID plus dem aktuellen TransactionAmount entsprechen.

Sie werden dies im folgenden Beispiel in Aktion sehen. Die laufende Summe für Rechnung 3 ist die vorherige laufende Summe von 3110.75 zuzüglich des Transaktionsbetrags der Rechnung 3 von 103.50

Berechnen Sie eine laufende Summe in SQL mit einem INNEREN JOIN

Wir berechnen zuerst die laufende Summe mit den INNEREN JOINS. Diese Methode zeigt mehr von der Mechanik der Berechnung einer laufenden Summe als die Verwendung der PARTITION. Als solches gibt es Ihnen eine weitere Möglichkeit, INNER JOINS zu verstehen und diese Konzepte auf einen anderen Anwendungsfall anzuwenden.

Es gibt drei Schritte, um dieses Problem zu lösen:

1. Abrufen von Zeilen für die laufende Summe
2. Setup-Details für die laufende Summe mit Inner Joins
3. Berechnen Sie die laufende Summe, indem Sie Daten zusammenfassen.

Los geht’s!

Schritt 1 – Zeilen für die laufende Summe abrufen

Um die laufende Summe zu berechnen, fragen wir die CustomerTransactions-Tabelle ab. Wir fügen die InvoiceID, TransactionDate und TransactionAmount in unser Ergebnis ein. Natürlich wird die laufende Summe aus der Transaktion berechnetbetrag.

Hier ist die Abfrage, um die Basisdaten zu erhalten.

SELECT InvoiceID ,TransactionDate ,TransactionAmountFROM Sales.CustomerTransactionsWHERE TransactionTypeID = 1ORDER BY TransactionDate

Hier sind die Daten, mit denen wir arbeiten werden.

Wirklich, dieser Schritt soll Sie mit den grundlegenden Informationen vertraut machen. Es gibt keine Notwendigkeit, es wirklich zu tun. Manchmal möchte ich jedoch die grundlegende Abfrage empfehlen, nur um die Daten zu sehen. Darüber hinaus, um sicherzustellen, dass es keine Anomalien oder besonderen Situationen gibt, die ich berücksichtigen muss.

Schritt 2 – Setup-Details für die laufende Summe mit Inner Joins

In diesem Schritt werden die Details eingerichtet, damit wir die laufende Summe berechnen können. Um dies zu tun, erhalten wir jede InvoiceID den Transaktionsbetrag und alle Transaktionsbeträge vor.

Um dies tun zu können, verbinden wir die CustomerTransactions-Tabelle mit sich selbst.

Wenn wir dies ohne Join-Bedingung tun, würden wir jede Kombination von Transaktionen erhalten, das ist nicht das, was wir wollen.

Um sicherzustellen, dass wir die richtige Kombination von Zeilen aus jeder Tabelle erhalten, fügen wir zwei Join-Bedingungen hinzu. Eine, um jede Rechnung und die davor zu erhalten (Grün).

Die zweite stellt sicher, dass wir nur Rechnungen am selben Transaktionsdatum (Rot)

SELECT T1.InvoiceID ,T2.InvoiceID ,T1.TransactionDate ,T1.TransactionAmount ,T2.TransactionAmountFROM Sales.CustomerTransactions T1 INNER JOIN Sales.CustomerTransactions T2 ON T1.InvoiceID >= T2.InvoiceID AND T1.TransactionDate = T2.TransactionDateWHERE T1.TransactionTypeID = 1ORDER BY T1.InvoiceID, T1.TransactionAmount

Mal sehen, wie das funktioniert.

Die am einfachsten zu verstehende Bedingung ist, wo wir TransactionDate . Dadurch wird sichergestellt, dass die Rechnungen ein gemeinsames Transaktionsdatum haben. Wenn dies der einzige Join wäre, würden wir eine Zwischensumme für alle Transaktionen innerhalb eines Datums berechnen.

Da wir die laufende Summe berechnen möchten, müssen wir irgendwie für jede InvoiceID den TransactionAmount für die Rechnung und alle Rechnungen davor erhalten. Mit anderen Worten, geben Sie alle übereinstimmenden Zeilen zurück, in denen die Rechnung größer oder gleich den entsprechenden Rechnungen ist, die wir summieren möchten.

Wenn Sie sich das obige Ergebnis ansehen, sehen Sie dies für jede Rechnung in der ersten Spalte (T1.InvoiceID), ist sie größer oder gleich der InvoiceID in der zweiten Spalte (T2.InvoiceID).

Dies ist ein Ergebnis der Join-Bedingung T1.InvoiceID >= T2.InvoiceID.

Das Ergebnis dieses Joins und der Join-Bedingungen ist, dass wir jetzt die Rohstoffe haben, um die laufende Summe zu berechnen.

Beachten Sie, wie sich die erste, dritte und vierte Spalte wiederholen. Wir können dies zu unserem Vorteil nutzen, um das Ergebnis zusammenzufassen und zur laufenden Summe zu gelangen.

Schritt 3 – Berechnen Sie die laufende Summe, indem Sie die Zeilen zusammenfassen

Mit den detaillierten Informationen besteht der letzte Schritt darin, die Zeilen zusammenzufassen. Auf diese Weise können wir die laufenden Summen berechnen.

Hier ist die Abfrage, mit der wir die Zusammenfassung durchführen:

SELECT T1.InvoiceID ,T1.TransactionDate ,T1.TransactionAmount ,Sum(T2.TransactionAmount) RunningTotalFROM Sales.CustomerTransactions T1 INNER JOIN Sales.CustomerTransactions T2 ON T1.InvoiceID >= T2.InvoiceID AND T1.TransactionDate = T2.TransactionDateWHERE T1.TransactionTypeID = 1GROUP BY T1.InvoiceID ,T1.TransactionDate ,T1.TransactionAmountORDER BY T1.InvoiceID ,T1.TransactionAmount

Beachten Sie, wie wir nach T1 gruppieren.InvoiceID, T1.Transaktionsdatum, und T1.TransactionAmount. Dies sind die Werte, die in unseren detaillierten Daten in Schritt 2 wiederholt wurden.

Die laufende Summe ergibt sich aus T2.TransactionAmount. Denken Sie daran, dass diese Werte TransactionAmount aus allen Rechnungen vor der angezeigten Rechnung sind. Mit anderen Worten, die angezeigte Rechnung ist größer oder gleich ihnen.

Dies ermöglicht es uns, eine laufende Summe aufzubauen.

Jede nachfolgende Rechnung in der Liste berechnet ihren runningTotal Wert, indem alle TransactionAmount aus ihrer Rechnung und denen davor summiert werden.

Nun, da Sie einen traditionellen Weg gesehen haben, um zur laufenden Summe zu gelangen, und vielleicht eine größere Wertschätzung dafür gewonnen haben, wie man Joins und Join-Bedingungen verwendet, um es zu lösen, schauen wir uns eine der neueren Funktionen von SQL, Partitionen, an und sehen, wie sie verwendet werden können, um das gleiche Ergebnis zu erzielen.

Berechnen Sie eine laufende Summe in SQL mit einer OVER-Klausel

Die OVER-Klausel ist eine sehr leistungsfähige Anweisung. Sie können eine Reihe von Zeilen innerhalb einer Ergebnismenge definieren, die sich auf eine Operation auswirkt.

Ähnlich wie OFFSET und FETCH es uns ermöglichen, einen bestimmten Bereich von Zeilen aus einer Ergebnismenge abzurufen, können wir mit der OVER Klausel eine ähnliche Operation relativ zur aktuellen Zeile für eine bestimmte Spalte ausführen.

Mit OVER können wir ein Fenster über einen bestimmten Satz von Zeilen definieren, auf das wir Funktionen wie sum anwenden können.

Damit Sie das Konzept verstehen, werden wir dies in zwei Schritte unterteilen:

1. Partitionieren Sie Daten mithilfe der OVER-Klausel.
2. Partitionen mit Bestellung bestellen.

Lass uns rollen.

Schritt 1 – Partitionieren Sie Daten mithilfe der OVER-Klausel

Wenn wir sagen, dass wir eine laufende Summe für alle Rechnungen innerhalb eines Transaktionsdatums erstellen möchten, möchten wir unsere Daten nach Transaktionsdatum partitionieren. Um die Daten zu partitionieren, können wir die over-Klausel verwenden.

In der folgenden Anweisung SUMMIEREN wir den TransactionAmount und nach der Summe gibt es eine OVER Klausel.

Beachten Sie auch, dass es keine GROUP BY-Klausel gibt. Dies ist überraschend, da Aggregatfunktionen wie SUM normalerweise eine GROUP BY-Klausel erfordern. Warum ist das der Fall?

Da wir die OVER–Klausel verwenden, wird die SUMME als Fensterfunktion betrachtet – sie arbeitet mit allen in der OVER-Klausel definierten Zeilen.

Hier ist die Fensterfunktion, die wir verwenden werden:

SUM(TransactionAmount) OVER(PARTITION BY TransactionDate) runningTotal

Was dies zu einer Windows-Funktion macht, ist die OVER-Klausel. überprüfen Sie die TEILPARTITION NACH Transaktionsdatum. Dies bedeutet, dass die SUMME für alle Zeilen mit demselben Transaktionsdatum gilt. Dies definiert das Zeilenfenster, auf das sich die Summenfunktion auswirkt.

Hier ist die Abfrage bisher.

SELECT InvoiceID ,TransactionDate ,TransactionAmount ,SUM(TransactionAmount) OVER(PARTITION BY TransactionDate) RunningTotalFROM Sales.CustomerTransactions T1WHERE TransactionTypeID = 1ORDER BY InvoiceID ,TransactionAmount

Schritt 2 – Partitionen mit Order BY

bestellen Bis zu diesem Punkt haben wir die Daten partitioniert und können eine Zwischensumme für alle TransactionAmount-Werte innerhalb eines TransactionDate berechnen. Der nächste Schritt besteht nun darin, die Zwischensumme zu berechnen.

Dazu können wir ORDER BY innerhalb der OVER-Klausel verwenden, um den „Umfang“ der window-Funktion zu definieren. Die ORDER BY gibt die logische Reihenfolge an, in der die Fensterfunktion ausgeführt wird.

Hier ist die Fensterfunktion, die wir verwenden werden:

SUM(TransactionAmount) OVER(PARTITION BY TransactionDate ORDER BY InvoiceID) runningTotal

Der Unterschied zwischen dieser Fensterfunktion und der aus dem ersten Schritt ist ORDER BY InvoiceID. Dies gibt die logische Reihenfolge an, die innerhalb der Partition verarbeitet werden soll.

Ohne die Reihenfolge durch die logische Reihenfolge ist zu warten, bis wir am Ende des Fensters sind, um die Summe zu berechnen. Wenn ORDER BY angegeben ist, besteht die logische Reihenfolge darin, eine Summe für jede Zeile einschließlich der vorherigen TransactionAmount-Werte innerhalb des Fensters zu berechnen.

SELECT InvoiceID ,TransactionDate ,TransactionAmount ,SUM(TransactionAmount) OVER(PARTITION BY TransactionDate ORDER BY InvoiceID) RunningTotalFROM Sales.CustomerTransactions T1WHERE TransactionTypeID = 1ORDER BY InvoiceID ,TransactionAmount

Hier ist das Ergebnis der Ausführung der Abfrage.

Haben Sie beim Ausführen dieser Abfrage festgestellt, wie viel schneller sie ausgeführt wurde als die mit INNER JOINS? Ich war überrascht. Ich weiß, dass die INNER JOIN-Operation viele Ressourcen verbraucht, wenn die Kombinationen oder Zeilen groß werden, aber ich hätte gedacht, dass dies für die Lösung mit OVER der gleiche Fall wäre.

Ich möchte Sie ermutigen, sich den Abfrageplan jeder Abfrage anzusehen. Sie werden anfangen, ziemlich viel über SQL zu lernen, wenn Sie damit beginnen.