Använd SQL för att beräkna en löpande summa

den löpande summan i SQL kan beräknas på flera sätt. Den här artikeln kommer att täcka två metoder: anslutningarna och Fönsterfunktionerna.

vi kommer först att titta på hur man beräknar den löpande summan med hjälp av INNER JOIN. Genom att göra det kommer du inte bara att lära dig mer om anslutningsvillkor, men se hur du tar resultatet och sammanfattar det för att få den löpande summan.

när du har sett hur man gör det ”old school” – stil, använder vi överklausulen för att beräkna löpande summor med en fönsterfunktion. Denna metod är nyare och mer koncis att använda.

alla exempel för den här lektionen är baserade på Microsoft SQL Server Management Studio och WideWorldImporters-databasen. Du kan komma igång med att använda dessa gratis verktyg med min Guide Kom igång med SQL Server 2016.

Vad är en löpande Total?

vårt mål är att beräkna den löpande summan som återställs när transaktionsdatumet ändras. Vi summerar transaktionsbeloppet. För varje efterföljande faktura inom transaktionsdatumet ska det löpande beloppet motsvara det tidigare fakturerings-ID: s löpande summa plus det aktuella transaktionsbeloppet.

du kommer att se detta i åtgärd i följande exempel. Den RunningTotal för faktura 3, är den tidigare RunningTotal av 3110,75 plus Faktura 3 transaktionsbelopp av 103.50

Beräkna en löpande summa i SQL med hjälp av en inre koppling

vi beräknar först den löpande summan med de inre kopplingarna. Denna metod avslöjar mer av mekaniken för att beräkna en löpande summa än att använda partitionen. Som sådan ger det dig ytterligare en möjlighet att förstå inre kopplingar och tillämpa dessa begrepp i ett annat användningsfall.

det finns tre steg för att lösa detta problem:

1. få rader för den löpande summan
2. Inställningsdetaljer för den löpande summan med inre kopplingar
3. beräkna den löpande summan genom att sammanfatta data.

Låt oss komma igång!

Steg 1 – Få rader för att köra totalt

för att beräkna löpande total, frågar vi tabellen CustomerTransactions. Vi inkluderar InvoiceID, TransactionDate och TransactionAmount i vårt resultat. Naturligtvis beräknas den löpande summan från Transaktionenbelopp.

här är frågan för att få grundläggande data.

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

här är de data vi kommer att arbeta med.

verkligen, detta steg är tänkt att få dig bekant med den grundläggande informationen. Det finns ingen anledning att verkligen göra det. Men jag skulle ibland vilja rekommendera den grundläggande frågan bara för att se data. Dessutom, för att också se till att det inte finns några avvikelser eller speciella situationer som jag behöver ta emot.

steg 2 – Inställningsdetaljer för att köra totalt med inre kopplingar

för det här steget får vi detaljerna så att vi kan beräkna löpande total. För att göra detta kommer vi att få varje InvoiceID transaktionsbeloppet och alla transaktionsbelopp tidigare.

för att kunna göra detta kommer vi att gå med i CustomerTransactions-tabellen till sig själv.

om vi gör detta utan anslutningsvillkor skulle vi få varje kombination av transaktioner, det här är inte vad vi vill ha.

för att säkerställa att vi får rätt kombination av rader från varje tabell lägger vi till två kopplingsvillkor. En för att få varje faktura och de före den (Grön).

den andra säkerställer att vi bara inkluderar fakturor på samma transaktionsdatum (röd)

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

Låt oss se hur detta fungerar.

det enklaste villkoret att förstå är var vi matchar Transaktionsdatum. Detta säkerställer att fakturamatchningen har ett gemensamt transaktionsdatum. Om detta var den enda gå Vi gjorde vi skulle beräkna en delsumma för alla transaktioner inom ett datum.

eftersom vi vill beräkna den löpande summan måste vi på något sätt få för varje Fakturaid transaktionsbeloppet för fakturan och alla fakturor före den. Med andra ord, returnera alla matchande rader där fakturan är större än eller lika med motsvarande fakturor vi försöker totalt.

om du tittar på resultatet ovan ser du det för varje faktura som anges i den första kolumnen (T1.InvoiceID), det är större än eller lika med InvoiceID i den andra kolumnen (T2.InvoiceID).

Detta är ett resultat av kopplingstillståndet T1.InvoiceID > = T2.InvoiceID.

resultatet av denna koppling och anslutningsvillkoren är att vi nu har råvarorna för att beräkna löpande total.

Lägg märke till hur de första, tredje och fjärde kolumnerna upprepas. Vi kan använda detta till vår fördel för att sammanfatta resultatet för att komma fram till den löpande summan.

steg 3 – Beräkna löpande Total genom att sammanfatta rader

med den detaljerade informationen till hands är det sista steget att sammanfatta raderna. Genom att göra det kan vi beräkna löpande summor.

här är frågan vi använder för att utföra sammanfattningen:

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

Lägg märke till hur vi grupperar efter T1.InvoiceID, T1.Transaktionsdatum, och T1.Transaktionsbelopp. Dessa är de värden som upprepades i våra detaljerade data i steg 2.

den löpande summan härrör från T2.Transaktionsbelopp. Minns dessa värden är Transaktionbelopp från alla fakturor före fakturan visas. Med andra ord är fakturan som visas större än eller lika med dem.

detta gör att vi kan bygga upp en löpande summa.

varje efterföljande faktura i listan, beräknar det RunningTotal värde genom att summera alla TransactionAmount från sin faktura och de före den.

nu när du har sett ett traditionellt sätt att komma fram till running total, och kanske fått en större uppskattning av hur man använder joins och join villkor för att lösa det, låt oss titta på en av de nyare funktionerna i SQL, partitioner, och se hur de kan användas för att uppnå samma resultat.

Beräkna en löpande summa i SQL med hjälp av en Överklausul

överklausulen om ett mycket kraftfullt uttalande. Det låter dig definiera en uppsättning rader, inom en resultatuppsättning som en operation påverkar.

precis som OFFSET och FETCH tillåter oss att hämta ett specifikt radintervall från en resultatuppsättning, tillåter överklausulen oss att göra en liknande operation, i förhållande till den aktuella raden, för en specifik kolumn.

med över kan vi definiera ett fönster över en viss uppsättning rader, till vilka vi kan tillämpa funktioner, till exempel summa.

för att du ska förstå konceptet kommer vi att dela upp detta i två steg:

1. partitionsdata med överklausulen.
2. Beställ partitioner med Order.

låt oss rulla.

Steg 1 – partitionsdata med ÖVERKLAUSUL

när vi säger att vi vill vill vi skapa en löpande summa för alla fakturor inom en Transaktionsdatum, vill vi partitionera våra data efter Transaktionsdatum. För att partitionera data kan vi använda överklausulen.

i följande meddelande summerar vi Transaktionenbelopp och efter summan finns en överklausul.

märker också att det inte finns någon grupp av klausul. Detta är förvånande, vanligtvis aggregerade funktioner, såsom summa, kräver en grupp av klausul; varför är detta fallet?

eftersom vi använder överklausulen betraktas summan som en fönsterfunktion – den fungerar på alla rader som definieras i överklausulen.

här är fönsterfunktionen vi använder:

summa(Transaktionamount) över(PARTITION BY TransactionDate) RunningTotal

Vad gör detta till en windows-funktion är överklausulen. kassa DELPARTITIONEN genom Transaktionsdatum. Detta innebär att summan fungerar på alla rader med samma Transaktionsdatum. Detta definierar fönstret för rader som SUM-funktionen påverkar.

här är frågan hittills.

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

steg 2 – Beställ partitioner med Order BY

fram till denna punkt har vi partitionerat data och kan beräkna en delsumma för alla transaktionsvärden inom en Transaktionsdatum. Nästa steg är att nu beräkna delsumman.

för att göra detta kan vi använda ORDER BY inom over-klausulen för att definiera ”omfattning” för fönsterfunktionen. Ordningen med specificerade den logiska ordningen fönsterfunktionen fungerar.

här är fönsterfunktionen vi använder:

summa(TransactionAmount) över(PARTITION by TransactionDate ORDER BY InvoiceID) RunningTotal

skillnaden mellan den här fönsterfunktionen och den från det första steget är ORDER BY InvoiceID. Detta anger den logiska ordningen att bearbeta inom partitionen.

utan ordningen med den logiska ordningen är att vänta tills vi är i slutet av fönstret för att beräkna summan. Med den angivna ordningen är den logiska ordningen att beräkna en summa för varje rad inklusive tidigare transaktionsvärden i fönstret.

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

här är resultatet av att köra frågan.

när du körde den här frågan märkte du hur mycket snabbare den körde än den som använde inre kopplingar? Jag blev förvånad. Jag vet att INNER JOIN-operationen förbrukar mycket resurser eftersom kombinationerna eller raderna blir stora, men jag skulle ha trott att det skulle ha samma fall för lösningen med över.

jag skulle uppmuntra dig att titta på varje fråges frågeplan. Du börjar lära dig en hel del om SQL när du börjar göra detta.