Použijte SQL k výpočtu běžícího součtu

běžícího součtu v SQL lze vypočítat několika způsoby. Tento článek se bude týkat dvou metod: spojení a funkce okna.

nejprve se podíváme na to, jak vypočítat běžící součet pomocí vnitřního spojení. Tímto způsobem se nejen dozvíte více o podmínkách připojení, ale uvidíte, jak výsledek vzít a shrnout, abyste získali celkový počet.

jakmile uvidíte, jak to udělat ve stylu „staré školy“, použijeme klauzuli OVER pro výpočet běžících součtů pomocí funkce okna. Tato metoda je novější a stručnější k použití.

všechny příklady této lekce jsou založeny na Microsoft SQL Server Management Studio A databázi WideWorldImporters. Můžete začít používat tyto bezplatné nástroje pomocí mého průvodce začněte používat SQL Server 2016.

co je běžný součet?

Naším cílem je vypočítat průběžný součet, který resetuje vždy, když TransactionDate změny. Celou transakci sečteme. Pro každou následující fakturu v rámci data transakce by se RunningTotal měl rovnat běžnému součtu předchozí Fakturaceid plus aktuálnímu počtu transakcí.

uvidíte to v akci v následujícím příkladu. Na RunningTotal pro Faktury 3, je před RunningTotal z 3110.75 plus Faktury 3 je Transakce Částka 103.50

Vypočítat průběžný součet v SQL pomocí INNER JOIN

Jsme první vypočítat průběžný součet pomocí INNER join. Tato metoda odhaluje více mechaniky výpočtu běžícího součtu než použití oddílu. Jako takový vám dává další příležitost pochopit vnitřní spojení a aplikovat tyto pojmy na jiný případ použití.

Tam jsou tři kroky k řešení tohoto problému:

1. Získat řádky pro běh celkem
2. Nastavení podrobnosti pro celkovou pomocí inner join
3. Vypočítat celkovou shrnující údaje.

začněme!

Krok 1-Získejte řádky pro spuštění celkem

pro výpočet běžícího součtu se dotazujeme na tabulku CustomerTransactions. Do našeho výsledku zahrneme InvoiceID, TransactionDate a TransactionAmount. Samozřejmě, běžící součet se vypočítá z Transakcemnožství.

zde je dotaz pro získání základních dat.

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

zde jsou data, se kterými budeme pracovat.

opravdu, tento krok je určen k seznámení se základními informacemi. Není třeba to opravdu dělat. Někdy bych však chtěl doporučit základní dotaz, abych viděl data. Kromě toho také zajistit, aby neexistovaly žádné anomálie nebo zvláštní situace, které musím vyhovět.

Krok 2 – podrobnosti nastavení pro spuštění celkem pomocí vnitřních spojů

v tomto kroku dostaneme podrobnosti nastavené tak, abychom mohli vypočítat běžící součet. Za tímto účelem dostaneme každou Fakturuid částku transakce a všechny částky transakce před.

abychom to mohli udělat, připojíme se k tabulce CustomerTransactions.

pokud to uděláme bez podmínky připojení, dostaneme každou kombinaci transakcí, to není to, co chceme.

abychom zajistili správnou kombinaci řádků z každé tabulky, přidáme dvě podmínky připojení. Jeden, který dostane každou fakturu a ty před ní (Zelená).

druhý zajišťuje, jsme obsahovat pouze faktury na stejné datum transakce (Red)

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

Pojďme se podívat, jak to funguje.

nejjednodušší podmínkou je pochopit, kde jsme zápas TransactionDate. Tím je zajištěno, že shoda faktur má společné datum transakce. Pokud to byl jediný připojili jsme udělali bychom výpočtu mezisoučet všech transakcí v rámci rande.

vzhledem k tomu, že chceme vypočítat průběžný součet, musíme pro každou fakturu nějak získat transakční částku za fakturu a všechny faktury před ní. Jinými slovy, vraťte všechny odpovídající řádky, kde je faktura větší nebo rovna odpovídajícím fakturám, které se snažíme celkem.

Pokud se podíváte na výsledek výše, uvidíte, že pro každé faktuře uvedené v prvním sloupci (T1.InvoiceID), je větší nebo rovna InvoiceID ve druhém sloupci (T2.InvoiceID).

toto je výsledek podmínky spojení T1.InvoiceID >= T2.InvoiceID.

výsledkem tohoto spojení a podmínek spojení je, že nyní máme suroviny pro výpočet průběžného součtu.

Všimněte si, jak se první, třetí a čtvrtý sloupec opakují. Můžeme to využít k našemu prospěchu, abychom shrnuli výsledek, abychom dospěli k celkovému počtu.

Krok 3-Vypočítejte celkový počet řádků shrnutím řádků

s podrobnými informacemi po ruce je posledním krokem shrnutí řádků. To nám umožňuje vypočítat průběžné součty.

zde je dotaz, který používáme k provedení shrnutí:

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

Všimněte si, jak seskupujeme T1.InvoiceID, T1.Datum transakce, a T1.Počet transakcí. Toto jsou hodnoty, které byly opakovány v našich podrobných datech v kroku 2.

běžící součet je odvozen od T2.Počet transakcí. Připomeňme, že tyto hodnoty jsou Transakcepočítat ze všech faktur před zobrazenou fakturou. Jinými slovy, zobrazená faktura je větší nebo rovna.

to nám umožňuje vytvořit běžící součet.

Každé další faktury v seznamu, je výpočet je RunningTotal hodnotu sečtením všech TransactionAmount z jeho Faktury a ty před ní.

Nyní, že jste viděli tradiční způsob, jak dorazit na průběžný součet, a možná získali větší uznání jak používat připojí a připojit podmínky, aby se to vyřešit, pojďme se podívat na jeden z novějších funkcí, SQL, oddíly, a uvidíte, jak mohou být použity k dosažení stejného výsledku.

Vypočítejte běžící součet v SQL pomocí klauzule OVER

klauzule OVER, pokud je velmi silný příkaz. To vám umožní definovat sadu řádků, v rámci sady výsledků, které operace ovlivňuje.

stejně jako OFFSET a FETCH nám umožňují získat určitý rozsah řádků z výsledku, klauzule OVER nám umožňuje dělat podobné operace, vzhledem k současné řadě, pro konkrétní sloupec.

pomocí OVER můžeme definovat okno nad zadanou sadou řádků, na které můžeme použít funkce, jako je sum.

abyste pochopili koncept, rozdělíme to na dva kroky:

1. rozdělení dat pomocí klauzule OVER.
2. objednejte oddíly s objednávkou.

let ‚ s roll.

Krok 1 – Oddíl dat s použitím VÍCE než Bodě

Když říkáme, že chceme, chceme vytvořit průběžný součet pro všechny Faktury v rámci TransactionDate, chceme, aby náš oddíl údaje o TransactionDate. K rozdělení dat můžeme použít klauzuli over.

v následujícím oznámení o výpisu sečteme Transakciamount a za součtem je klauzule OVER.

také si všimněte, že neexistuje žádná klauzule podle skupiny. To je překvapivé, obvykle agregované funkce, jako je součet, vyžadují klauzuli o skupině; proč tomu tak je?

protože používáme klauzuli OVER, je součet považován za funkci okna – pracuje na všech řádcích definovaných v klauzuli OVER.

Tady je funkce okna použijeme:

SUM(TransactionAmount) OVER(PARTITION BY TransactionDate) RunningTotal

Co udělat funkci systému windows, je PŘES klauzule. podívejte se na oddíl části podle data TransactionDate. To znamená, že součet pracuje na všech řádcích se stejným transakčním datem. To definuje okno řádků, které funkce SUM ovlivňuje.

zde je dotaz zatím.

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

2. Krok – Pořadí Oddílů s Order BY

Až do tohoto bodu jsme rozdělili data a jsou schopni vypočítat mezisoučet pro všechny TransactionAmount hodnoty v TransactionDate. Dalším krokem je nyní vypočítat mezisoučet.

k tomu můžeme použít klauzuli ORDER by within OVER k definování „rozsahu“ funkce okna. Pořadí podle zadaného logického pořadí funguje funkce okna.

zde je funkce okna, kterou použijeme:

SUM(TransactionAmount) OVER(PARTITION BY TransactionDate POŘADÍ PODLE InvoiceID) RunningTotal

rozdíl mezi tímto oknem funkce, a to od prvního kroku, je POŘADÍ PODLE InvoiceID. To určuje logické pořadí, které se má zpracovat v rámci oddílu.

bez pořadí podle logického pořadí je počkat, až budeme na konci okna, abychom vypočítali součet. S ORDER BY zadaný, logickým pořadím je vypočítat součet pro každý řádek včetně předchozích hodnot TransactionAmount v okně.

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

zde je výsledek spuštění dotazu.

když jste spustili tento dotaz, všimli jste si, jak rychleji běžel než ten, který používá vnitřní spojení? Překvapilo mě to. Vím, že operace INNER JOIN spotřebovává spoustu zdrojů, protože kombinace nebo řádky se zvětšují, ale myslel bych si, že by to mělo stejný případ pro řešení pomocí více než.

doporučuji vám podívat se na plán dotazů každého dotazu. Začnete se učit trochu o SQL, když začnete dělat to.