Brug KVL til at beregne en løbende Total

den løbende total i KVL kan beregnes på flere måder. Denne artikel vil dække to metoder: joinforbindelserne og Vinduesfunktionerne.

vi vil først se på, hvordan man beregner den løbende total ved hjælp af den indre joinforbindelse. Ved at gøre det lærer du ikke kun mere om deltagelsesbetingelser, Men se, hvordan du tager resultatet og opsummerer det for at få den løbende total.

når du har set, hvordan du gør det “old school” – stil, bruger vi over-klausulen til at beregne løbende totaler ved hjælp af en vinduesfunktion. Denne metode er nyere og mere kortfattet at bruge.

alle eksempler på denne lektion er baseret på Microsoft Server Management Studio og den brede Verdensimporters database. Du kan komme i gang med at bruge disse gratis værktøjer ved hjælp af min Guide.

Hvad er en løbende Total?

vores mål er at beregne den løbende total, der nulstilles, når transaktionsdatoen ændres. Vi vil i alt transaktionsbeløbet. For hver efterfølgende faktura inden for transaktionsdatoen skal Løbetotalen svare til det tidligere Fakturaid ‘ s løbende total plus det aktuelle Transaktionsbeløb.

du vil se dette i aktion i følgende eksempel. RunningTotal for faktura 3 er den forudgående RunningTotal på 3110.75 plus faktura 3 ‘ s Transaktionsbeløb på 103.50

Beregn en løbende Total i kvadrat ved hjælp af en indre joinforbindelse

vi beregner først den løbende total ved hjælp af de indre joinforbindelser. Denne metode afslører mere af mekanikken til beregning af en løbende total end at bruge partitionen. Som sådan giver det dig en anden mulighed for at forstå indre sammenføjninger og anvende disse begreber til en anden brugssag.

der er tre trin til at løse dette problem:

1. Hent rækker for den løbende total
2. Opsætningsdetaljer for den løbende total ved hjælp af indre joinforbindelser
3. Beregn den løbende total ved at opsummere data.

lad os komme i gang!

Trin 1 – Hent rækker til at køre Total

for at beregne den løbende total, forespørger vi tabellen CustomerTransactions. Vi inkluderer InvoiceID, TransactionDate og TransactionAmount i Vores resultat. Selvfølgelig beregnes den løbende total ud fra Transaktionenbeløb.

her er forespørgslen for at få de grundlæggende data.

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

her er de data, vi skal arbejde med.

virkelig, dette trin er beregnet til at få dig bekendt med de grundlæggende oplysninger. Der er ingen grund til virkelig at gøre det. Jeg vil dog nogle gange anbefale den grundlæggende forespørgsel bare for at se dataene. Derudover for også at sikre, at der ikke er nogen uregelmæssigheder eller specielle situationer, som jeg har brug for at imødekomme.

Trin 2 – installationsdetaljer for at køre Total ved hjælp af indre joinforbindelser

for dette trin får vi detaljerne konfigureret, så vi kan beregne den løbende total. For at gøre dette får vi hver InvoiceID transaktionsbeløbet og alle transaktionsbeløbene forud.

for at kunne gøre dette, slutter vi os til tabellen CustomerTransactions til sig selv.

hvis vi gør dette uden deltagelsesbetingelse, ville vi få hver kombination af transaktioner, det er ikke det, vi ønsker.

for at sikre, at vi får den rette kombination af rækker fra hver tabel, tilføjer vi to joinbetingelser. En til at få hver faktura og dem forud for det (Grøn).

det andet sikrer, at vi kun inkluderer fakturaer på samme transaktionsdato (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

lad os se, hvordan dette fungerer.

den nemmeste betingelse at forstå er, hvor vi matcher Transaktionsdato. Dette sikrer, at fakturaernes match har en fælles transaktionsdato. Hvis dette var den eneste sammenføjning, vi gjorde, ville vi beregne en sub total for alle transaktioner inden for en dato.

da vi ønsker at beregne den løbende total, skal vi på en eller anden måde få for hver InvoiceID transaktionsbeløbet for fakturaen og alle fakturaer før den. Med andre ord skal du returnere alle matchende rækker, hvor fakturaen er større end eller lig med de tilsvarende fakturaer, vi prøver at total.

hvis du ser på resultatet ovenfor, vil du se det for hver faktura, der er angivet i den første kolonne (T1.InvoiceID), er det større end eller lig med InvoiceID ‘ er i den anden kolonne (T2.InvoiceID).

dette er et resultat af tilslutningsbetingelsen T1.InvoiceID > = T2.InvoiceID.

resultatet af denne sammenføjning og tilslutningsbetingelserne er, at vi nu har råmaterialerne til at beregne den løbende total.

bemærk, hvordan den første, tredje og fjerde kolonne gentages. Vi kan bruge dette til vores fordel til at opsummere resultatet for at nå frem til den løbende total.

Trin 3 – Beregn løbende Total ved at opsummere rækker

med de detaljerede oplysninger ved hånden er det sidste trin at opsummere rækkerne. Dette giver os mulighed for at beregne de løbende totaler.

her er den forespørgsel, vi bruger til at udføre resumeet:

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

bemærk, hvordan vi grupperer efter T1.InvoiceID, T1.Transaktionsdato og T1.Transaktionsbeløb. Dette er de værdier, der blev gentaget i vores detaljerede data i trin 2.

den løbende Total er afledt af T2.Transaktionsbeløb. Husk disse værdier er Transaktionsbeløb fra alle fakturaer forud for den viste faktura. Med andre ord er den viste faktura større end eller lig med dem.

dette giver os mulighed for at opbygge en løbende total.

hver efterfølgende faktura på listen beregner den samlede værdi ved at opsummere alle Transaktionsbeløb fra fakturaen og de forud for den.

nu hvor du har set en traditionel måde at nå frem til den løbende total og måske fået en større forståelse af, hvordan du bruger joinforbindelser og joinbetingelser for at løse det, lad os se på en af de nyere funktioner i KVM, partitioner, og se, hvordan de kan bruges til at opnå det samme resultat.

Beregn en løbende Total i KVM ved hjælp af en OVER-Klausul

OVER-klausulen, hvis en meget kraftig erklæring. Det giver dig mulighed for at definere et sæt rækker inden for et resultatsæt, som en handling påvirker.

ligesom OFFSET og hent giver os mulighed for at hente et bestemt interval af rækker fra et resultatsæt, giver OVER-klausulen os mulighed for at udføre en lignende handling i forhold til den aktuelle række for en bestemt kolonne.

ved hjælp af OVER kan vi definere et vindue over et bestemt sæt rækker, som vi kan anvende funktioner på, såsom sum.

for at du kan forstå konceptet, opdeler vi dette i to trin:

1. Partition data ved hjælp af over klausul.
2. Bestil partitioner med ordre.

lad os rulle.

Trin 1 – Partitionsdata ved hjælp af over-Klausul

når vi siger, at vi vil, vil vi oprette en løbende total for alle fakturaer inden for en Transaktionsdato, vi vil partitionere vores data efter Transaktionsdato. For at opdele dataene kan vi bruge over-klausulen.

i den følgende meddelelse opsummerer vi transaktionsbeløbet, og efter summen er der en overklausul.

Bemærk også, at der ikke er nogen gruppe efter klausul. Dette er overraskende, typisk aggregerede funktioner, såsom SUM, kræver en gruppe efter klausul; hvorfor er dette tilfældet?

da vi bruger over – klausulen, betragtes summen som en vinduesfunktion-den fungerer på alle rækker defineret i over-klausulen.

her er den vinduesfunktion, vi bruger:

SUM(TransactionAmount) OVER(PARTITION BY TransactionDate) RunningTotal

hvad gør dette til en vinduer-funktion er over-klausulen. tjek delpartitionen efter Transaktionsdato. Dette betyder, at summen fungerer på alle rækker med samme Transaktionsdato. Dette definerer vinduet med rækker, som SUMFUNKTIONEN påvirker.

her er forespørgslen indtil videre.

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

Trin 2 – Bestil partitioner med ordre efter

indtil dette tidspunkt har vi partitioneret dataene og kan beregne en subtotal for alle Transaktionsbeløbsværdier inden for en Transaktionsdato. Det næste trin er nu at beregne subtotalen.

for at gøre dette kan vi bruge ORDER BY inden for over-klausulen til at definere “omfanget” af vinduesfunktionen. Ordren ved specificeret den logiske rækkefølge vinduesfunktionen fungerer.

her er den vinduesfunktion, vi bruger:

SUM(Transaktionsbeløb) OVER(PARTITION efter Transaktionsdato rækkefølge efter InvoiceID) RunningTotal

forskellen mellem denne vinduesfunktion og den fra det første trin er rækkefølge efter InvoiceID. Dette angiver den logiske rækkefølge, der skal behandles i partitionen.

uden ordren ved den logiske rækkefølge er at vente, indtil vi er i slutningen af vinduet for at beregne summen. Med den angivne rækkefølge er den logiske rækkefølge at beregne en sum for hver række inklusive tidligere Transaktionsbeløbsværdier i vinduet.

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

her er resultatet af at køre forespørgslen.

da du kørte denne forespørgsel bemærkede du, hvor meget hurtigere det løb end den, der brugte indre joinforbindelser? Jeg blev overrasket. Jeg ved, at den indre SAMMENFØJNINGSOPERATION bruger mange ressourcer, da kombinationerne eller rækkerne bliver store, men jeg ville have troet, at det ville have samme sag for løsningen ved hjælp af OVER.

jeg vil opfordre dig til at se på hver forespørgsels forespørgselsplan. Du vil begynde at lære en hel del om CVR, når du begynder at gøre dette.