Gebruik SQL om een lopend totaal

te berekenen het lopende totaal in SQL kan op verschillende manieren worden berekend. Dit artikel behandelt twee methoden: de Joins en de Window functies.

we zullen eerst kijken hoe we het lopende totaal kunnen berekenen met behulp van de INNER JOIN. Door dit te doen, je zult niet alleen meer te leren over de voorwaarden van de deelname, maar zie hoe je het resultaat te nemen en samen te vatten, om het lopende totaal te krijgen.

als je eenmaal hebt gezien hoe het “old school” stijl te doen, zullen we de over clausule gebruiken om lopende totalen te berekenen met behulp van een window functie. Deze methode is nieuwer en beknopter te gebruiken.

alle voorbeelden voor deze les zijn gebaseerd op Microsoft SQL Server Management Studio en de wideworldimporters database. U kunt aan de slag met behulp van deze gratis tools met behulp van mijn gids aan de slag met behulp van SQL Server 2016.

Wat is een lopend totaal?

ons doel is om het lopende totaal te berekenen dat opnieuw wordt ingesteld wanneer de transactiedatum verandert. We tellen het totale bedrag. Voor elke volgende factuur binnen de transactiedatum moet het lopende totaal gelijk zijn aan het lopende totaal van de voorafgaande factuur plus het lopende transactiebedrag.

u zult dit in actie zien in het volgende voorbeeld. De RunningTotal voor Factuur 3, is de voorafgaande RunningTotal van 3110.75 plus de factuur 3 transactie bedrag van 103.50

Bereken een lopend totaal in SQL met behulp van een INNER JOIN

we berekenen eerst het lopende totaal met behulp van de INNER JOINS. Deze methode onthult meer van de mechanica van het berekenen van een lopende totaal dan met behulp van de partitie. Als zodanig, het geeft je nog een kans om innerlijke JOINS te begrijpen en deze concepten toe te passen op een andere use case.

er zijn drie stappen om dit probleem op te lossen:

1. krijg rijen voor het lopende totaal
2. Setup details voor het lopende totaal met behulp van innerlijke joins
3. Bereken het lopende totaal door gegevens samen te vatten.

aan de slag!

Stap 1-Krijg rijen voor het lopende totaal

om het lopende totaal te berekenen, zullen we de tabel CustomerTransactions opvragen. We zullen de factuur, transactiedatum en transactiebedrag in ons resultaat opnemen. Uiteraard wordt het lopende totaal berekend op basis van het transactiebedrag.

hier is de query om de basisgegevens te verkrijgen.

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

hier zijn de gegevens waarmee we zullen werken.

echt, deze stap is bedoeld om u kennis te maken met de basisinformatie. Je hoeft het niet echt te doen. Echter, Ik zou soms willen aanbevelen de basis query alleen maar om de gegevens te zien. Bovendien, om er ook voor te zorgen dat er geen anomalieën of speciale situaties zijn die ik moet accommoderen.

Stap 2-Setup Details voor het lopende totaal met behulp van innerlijke Joins

voor deze stap, zullen we de details instellen zodat we het lopende totaal kunnen berekenen. Om dit te doen, krijgen we elke factuur het transactiebedrag en alle transactiebedragen voorafgaand.

om dit te kunnen doen, voegen we de tabel CustomerTransactions aan zichzelf toe.

als we dit doen zonder join voorwaarde zouden we elke combinatie van transacties krijgen, Dit is niet wat we willen.

om er zeker van te zijn dat we de juiste combinatie van rijen uit elke tabel krijgen, voegen we twee join voorwaarden toe. Een om elke factuur en die voorafgaand aan het te krijgen (groen).

de tweede zorgt ervoor dat we alleen facturen op dezelfde transactiedatum (rood)

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

eens kijken hoe dit werkt.

de gemakkelijkste voorwaarde om te begrijpen is waar we de transactiedatum matchen. Dit zorgt ervoor dat de facturen overeenkomen een gemeenschappelijke transactiedatum hebben. Als dit de enige join was die we deden, zouden we een subtotaal berekenen voor alle transacties binnen een datum.

omdat we het lopende totaal willen berekenen, moeten we op de een of andere manier voor elke factuur het transactiebedrag voor de factuur en alle facturen ervoor verkrijgen. Met andere woorden, retourneer alle overeenkomende rijen waar de factuur groter is dan of gelijk is aan de overeenkomstige facturen die we proberen te Totaal.

als je naar het resultaat hierboven kijkt, zie je dat voor elke factuur in de eerste kolom (T1.InvoiceID), is het groter dan of gelijk aan InvoiceID ‘ s in de tweede kolom (T2.Facturatie).

Dit is het resultaat van de join-voorwaarde T1.Factuurnummer > = T2.Facturatie.

het resultaat van deze join en de join voorwaarden is dat we nu de grondstoffen hebben om het lopende totaal te berekenen.

merk op hoe de eerste, derde en vierde kolom zich herhalen. We kunnen dit in ons voordeel gebruiken om het resultaat samen te vatten om tot het lopende totaal te komen.

Stap 3-Bereken het lopende totaal door rijen

samen te vatten met de gedetailleerde informatie bij de hand, de laatste stap is het samenvatten van de rijen. Hierdoor kunnen we de lopende totalen berekenen.

hier is de query die we gebruiken om de samenvatting uit te voeren:

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

merk op hoe we groeperen op T1.InvoiceID, T1.Transactiedatum en T1.Transactiebedrag. Dit zijn de waarden die werden herhaald in onze gedetailleerde gegevens in Stap 2.

het lopende totaal is afgeleid van T2.Transactiebedrag. Terugroepen deze waarden zijn transactiebedrag van alle facturen voorafgaand aan de factuur weergegeven. Met andere woorden, de weergegeven factuur is groter dan of gelijk aan hen.

hiermee kunnen we een lopend totaal opbouwen.

elke volgende factuur in de lijst berekent de lopende totale waarde door het totale bedrag van de transactie op te sommen uit de factuur en de voorafgaande facturen.

Nu u een traditionele manier hebt gezien om tot het running total te komen, en u misschien meer waardering hebt gekregen voor het gebruik van joins en join voorwaarden om het op te lossen, laten we eens kijken naar een van de nieuwere functies van SQL, partities, en zien hoe ze kunnen worden gebruikt om hetzelfde resultaat te bereiken.

Bereken een lopend totaal in SQL met behulp van een Over-Clausule

de over-clausule als dit een zeer krachtige verklaring is. Hiermee kunt u een reeks rijen definiëren, binnen een resultaatset die een bewerking beïnvloedt.

net zoals OFFSET en FETCH ons toestaan om een specifiek bereik van rijen uit een resultaatset op te halen, staat de over-clausule ons toe om een soortgelijke bewerking uit te voeren, ten opzichte van de huidige rij, voor een specifieke kolom.

met OVER kunnen we een venster definiëren over een opgegeven reeks rijen, waarop we functies kunnen toepassen, zoals Som.

om het concept te begrijpen zullen we dit in twee stappen opsplitsen:

1. partitiegegevens met behulp van de over-clausule.
2. Orderpartities met volgorde.

laten we beginnen.

Stap 1-partitiegegevens met over-Clausule

als we zeggen dat we willen dat we een draaiend totaal willen maken voor alle facturen binnen een transactiedatum, willen we onze gegevens partitioneren op transactiedatum. Om de gegevens te partitioneren, kunnen we de over-clausule gebruiken.

in de volgende mededeling wordt het bedrag van de transactie opgeteld en na de som is er een over-clausule.

merk ook op dat er geen groepsgewijze clausule is. Dit is verrassend, typisch geaggregeerde functies, zoals Som, vereisen een groep per clausule; waarom is dit het geval?

omdat we de over – clausule gebruiken, wordt de som beschouwd als een vensterfunctie-het werkt op alle rijen gedefinieerd in de OVER-clausule.

hier is de window-functie die we zullen gebruiken:

SUM(TransactionAmount) OVER (partitie per TransactionDate) RunningTotal

wat dit een windows-functie maakt is de over-clausule. bekijk de partitie op TransactionDate. Dit betekent dat de som op alle rijen met dezelfde transactiedatum werkt. Dit definieert het venster van rijen die de SUM-functie beïnvloedt.

hier is de zoekopdracht tot nu toe.

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

Stap 2-Orderpartities met volgorde van

tot op dit punt hebben we de gegevens gepartitioneerd en kunnen we een subtotaal berekenen voor alle transactiebedrag waarden binnen een transactiedatum. De volgende stap is om nu het subtotaal te berekenen.

om dit te doen kunnen we de volgorde binnen de over-clausule gebruiken om de “scope” van de window-functie te definiëren. De volgorde door gespecificeerd de logische volgorde de vensterfunctie werkt.

hier is de window-functie die we zullen gebruiken:

SUM(TransactionAmount) OVER (partitie per TransactionDate ORDER BY InvoiceID) Runningtotaal

het verschil tussen deze window functie en die vanaf de eerste stap, is ORDER BY InvoiceID. Dit specificeert de logische volgorde te verwerken binnen de partitie.

zonder de volgorde door de logische volgorde is om te wachten tot we aan het einde van het venster om de som te berekenen. Met de volgorde van opgegeven, de logische volgorde is om een som te berekenen voor elke rij inclusief eerdere transactiebedrag waarden binnen het venster.

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

hier is het resultaat van het uitvoeren van de query.

toen u deze query uitvoerde, zag u hoeveel sneller het liep dan degene die interne JOINS gebruikte? Ik was verrast. Ik weet dat de innerlijke JOIN operatie verbruiken veel middelen als de combinaties of rijen worden groot, maar ik zou hebben gedacht dat het zou hetzelfde geval voor de oplossing met behulp van meer.

ik zou u willen aanmoedigen om het queryplan van elke query te bekijken. Je leert heel wat over SQL als je dit begint te doen.