Użyj SQL, aby obliczyć sumę roboczą

sumę roboczą w SQL można obliczyć na kilka sposobów. W tym artykule omówimy dwie metody: funkcje łączenia i Okna.

najpierw przyjrzymy się, jak obliczyć sumę bieżącą za pomocą połączenia wewnętrznego. W ten sposób nie tylko dowiesz się więcej o warunkach przyłączenia, ale także dowiesz się, jak obliczyć wynik i podsumować go, aby uzyskać sumę bieżącą.

gdy zobaczysz, jak to zrobić w stylu „old school”, użyjemy klauzuli OVER, aby obliczyć bieżące sumy za pomocą funkcji okna. Ta metoda jest nowsza i bardziej zwięzła w użyciu.

wszystkie przykłady tej lekcji są oparte na Microsoft SQL Server Management Studio i Bazie Danych WideWorldImporters. Możesz zacząć korzystać z tych bezpłatnych narzędzi za pomocą mojego przewodnika zacznij korzystać z SQL Server 2016.

co to jest bieżąca suma?

naszym celem jest obliczenie sumy bieżącej, która resetuje się przy każdej zmianie daty transakcji. Policzymy kwotę transakcji. Dla każdej kolejnej faktury w dniu transakcji suma bieżąca powinna być równa sumie bieżącej rachunku poprzedniego rachunku Plus bieżąca Kwota transakcji.

zobaczysz to w akcji w poniższym przykładzie. Suma bieżąca dla faktury 3 wynosi 3110,75 plus Kwota transakcji na fakturze 3 103.50

Oblicz sumę uruchomieniową w SQL za pomocą połączenia wewnętrznego

najpierw obliczamy sumę uruchomieniową za pomocą połączeń wewnętrznych. Ta metoda ujawnia więcej mechaniki obliczania sumy bieżącej niż korzystania z partycji. W związku z tym daje Ci kolejną okazję do zrozumienia wewnętrznych połączeń i zastosowania tych pojęć w innym przypadku użycia.

istnieją trzy kroki do rozwiązania tego problemu:

1. uzyskaj wiersze dla sumy roboczej
2. szczegóły konfiguracji dla sumy roboczej za pomocą połączeń wewnętrznych
3. Oblicz sumę roboczą, sumując dane.

zaczynajmy!

Krok 1 – Pobierz Wiersze dla sumy bieżącej

aby obliczyć sumę bieżącą, zapytamy tabelę CustomerTransactions. W wyniku zamieścimy identyfikator InvoiceID, TransactionDate i TransactionAmount. Oczywiście suma bieżąca jest obliczana na podstawie liczby transakcji.

oto zapytanie, aby uzyskać podstawowe dane.

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

oto dane, z którymi będziemy pracować.

naprawdę, ten krok ma na celu zapoznanie się z podstawowymi informacjami. Nie ma takiej potrzeby. Jednak czasami chciałbym polecić podstawowe zapytanie, aby zobaczyć dane. Ponadto, aby również upewnić się, że nie ma żadnych anomalii lub szczególnych sytuacji, które muszę dostosować.

Krok 2 – Szczegóły konfiguracji dla Running Total za pomocą połączeń wewnętrznych

w tym kroku uzyskamy szczegóły skonfigurowane, abyśmy mogli obliczyć running total. W tym celu otrzymamy dla każdego InvoiceID kwotę transakcji i wszystkie kwoty transakcji przed.

aby móc to zrobić, dołączymy do tabeli CustomerTransactions.

jeśli zrobimy to bez warunku join, otrzymamy każdą kombinację transakcji, nie tego chcemy.

aby upewnić się, że otrzymamy odpowiednią kombinację wierszy z każdej tabeli, dodamy dwa warunki łączenia. Po jednym, aby otrzymać każdą fakturę i te przed nią (zielone).

drugi zapewnia, że uwzględniamy faktury tylko w tym samym dniu transakcji (Czerwony)

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

zobaczmy, jak to działa.

najprostszym warunkiem do zrozumienia jest dopasowanie daty transakcji. Zapewnia to, że faktury dopasowane mają wspólną datę transakcji. Gdyby było to jedyne połączenie, które zrobiliśmy, obliczalibyśmy sumę sub dla wszystkich transakcji w ciągu daty.

ponieważ chcemy obliczyć sumę bieżącą, musimy w jakiś sposób uzyskać dla każdej faktury Identyfikator transakcji dla faktury i wszystkich faktur przed nią. Innymi słowy, zwróć wszystkie pasujące wiersze, w których faktura jest większa lub równa odpowiednim fakturom, które staramy się zsumować.

jeśli spojrzysz na wynik powyżej, zobaczysz, że dla każdej faktury wymienionej w pierwszej kolumnie (T1.InvoiceID), jest większy lub równy InvoiceID w drugiej kolumnie (T2.InvoiceID).

jest to wynik warunku łączenia T1.InvoiceID > = T2.InvoiceID.

wynikiem tego połączenia i warunków łączenia jest to, że mamy teraz surowce do obliczenia sumy bieżącej.

zwróć uwagę, jak powtarzają się pierwsze, trzecie i czwarte kolumny. Możemy to wykorzystać na naszą korzyść, aby podsumować wynik, aby osiągnąć sumę bieżącą.

Krok 3 – Oblicz sumę bieżącą, Podsumowując wiersze

dzięki szczegółowym informacjom pod ręką, ostatnim krokiem jest podsumowanie wierszy. Dzięki temu możemy obliczyć bieżące sumy.

oto zapytanie, którego używamy do wykonania podsumowania:

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

zauważ, jak grupujemy przez T1.InvoiceID, T1.TransactionDate, oraz T1.Transakcjamount. Są to wartości, które zostały powtórzone w naszych szczegółowych danych w Kroku 2.

suma bieżąca pochodzi z T2.Transakcjamount. Przypomnij te wartości są TransactionAmount ze wszystkich faktur przed wyświetlaną fakturą. Innymi słowy, wyświetlana faktura jest większa lub równa.

to pozwala nam na zbudowanie całego biegu.

każda kolejna faktura na liście, oblicza jej bieżącą wartość sumując wszystkie transakcje z jej faktury i tych przed nią.

teraz, gdy widziałeś tradycyjny sposób osiągnięcia sumy uruchomieniowej i być może zyskałeś większe uznanie, jak używać joins i warunków join, aby go rozwiązać, spójrzmy na jedną z nowszych funkcji SQL, partycji i zobaczmy, jak można ich użyć, aby osiągnąć ten sam wynik.

Oblicz sumę bieżącą w SQL, używając klauzuli OVER

klauzuli OVER, jeśli jest to bardzo potężne polecenie. Pozwala na zdefiniowanie zbioru wierszy w ramach zbioru wynikowego, na który wpływa operacja.

podobnie jak OFFSET i FETCH pozwalają nam pobrać określony zakres wierszy z zestawu wyników, klauzula OVER pozwala nam wykonać podobną operację, względem bieżącego wiersza, dla określonej kolumny.

używając OVER, możemy zdefiniować okno nad określonym zbiorem wierszy, do którego możemy zastosować funkcje, takie jak sum.

aby zrozumieć koncepcję, podzielimy ją na dwa kroki:

1. dane partycji za pomocą klauzuli OVER.
2. Zamów partycje z porządkiem.

Let ’ s roll.

Krok 1-Dane partycji za pomocą klauzuli OVER

kiedy mówimy, że chcemy, chcemy utworzyć sumę bieżącą dla wszystkich faktur w TransactionDate, chcemy podzielić nasze dane przez TransactionDate. Aby podzielić dane, możemy użyć klauzuli over.

w poniższym oświadczeniu sumujemy kwotę transakcji, a po sumie znajduje się klauzula OVER.

zauważ również, że nie ma klauzuli GROUP BY. Jest to zaskakujące, zazwyczaj funkcje zbiorcze, takie jak suma, wymagają klauzuli GROUP BY; dlaczego tak jest?

ponieważ używamy klauzuli OVER, suma jest uważana za funkcję okienną-działa na dowolnych wierszach zdefiniowanych w klauzuli OVER.

oto funkcja okna, której użyjemy:

SUM(TransactionAmount) OVER(PARTITION BY TransactionDate) RunningTotal

to, co czyni tę funkcję windows, To klauzula OVER. kasowanie partycji part przez TransactionDate. Oznacza to, że suma działa na wszystkich wierszach z tą samą datą transakcji. Określa okno wierszy, na które wpływa funkcja SUM.

oto zapytanie do tej pory.

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

Krok 2-Uporządkuj partycje za pomocą Order BY

do tego momentu podzieliliśmy Dane i jesteśmy w stanie obliczyć sumę częściową dla wszystkich wartości TransactionAmount w ramach TransactionDate. Następnym krokiem jest teraz obliczenie sumy cząstkowej.

aby to zrobić, możemy użyć ORDER by w klauzuli OVER, aby zdefiniować „zakres” funkcji okna. Kolejność według określonego porządku logicznego działa funkcja okna.

oto funkcja okna, której użyjemy:

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

różnica między tą funkcją okna a tym, że od pierwszego kroku, jest ORDER BY InvoiceID. Określa logiczną kolejność przetwarzania na partycji.

bez kolejności logicznej należy poczekać, aż będziemy na końcu okna, aby obliczyć sumę. Przy określonej kolejności według, logicznym porządkiem jest obliczenie sumy dla każdego wiersza z uwzględnieniem poprzednich wartości TransactionAmount w oknie.

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

oto wynik z uruchomienia zapytania.

kiedy uruchomiłeś to zapytanie, zauważyłeś, ile szybciej działało niż to, które używało połączeń wewnętrznych? Byłem zaskoczony. Wiem, że operacja INNER JOIN zużywa dużo zasobów, ponieważ kombinacje lub wiersze stają się duże, ale myślałem, że będzie to miało taki sam przypadek dla rozwiązania wykorzystującego OVER.

zachęcam do zapoznania się z planem zapytań każdego zapytania. Zaczniesz uczyć się trochę o SQL, gdy zaczniesz to robić.