SQL Kumulativní součet

V SQL se kumulativní součet týká metody, která nám umožňuje vypočítat průběžný součet číselného sloupce v databázové tabulce. Kumulativní součet vstupuje do hry v široké škále scénářů, jako je sledování postupu nebo výpočet klouzavých průměrů dané hodnoty.

V tomto tutoriálu se naučíme, jak implementovat a využívat kumulativní součet v SQL pomocí různých metod a technik.

Vzorek dat

Abychom lépe pochopili, jak můžeme implementovat a používat kumulativní součet, začněme nastavením základní tabulky s ukázkovými daty. To nám umožňuje v tomto příspěvku rychle a efektivně demonstrovat fungování všech metod.

Začněte vytvořením základní tabulky, která ukládá informace o produktu.

To by mělo vytvořit novou tabulku nazvanou „produkty“, která ukládá ID produktu, název produktu a cenu každého produktu.

Poté můžeme přistoupit k přidání ukázkových dat, jak je znázorněno v následujícím příkladu příkazů vložení:

POZNÁMKA: Uvedené údaje jsou zcela smyšlené. Nepředstavuje skutečnou cenu žádné z uvedených položek.

Výsledná tabulka je následující:

Kumulativní součet SQL (vlastní spojení)

Jednou z technik, kterou můžeme použít k provedení kumulativního součtu na daném sloupci, je použití metody self-join. Jednou z výhod této metody je, že funguje téměř ve všech databázích SQL, dokonce i v těch, které nepodporují funkce Window.

Vezměte si například předchozí tabulku „produktů“. Můžeme vytvořit kumulativní součet sloupce ceny, jak je ukázáno v následujícím dotazu:

Všimli jste si fungování dotazu? Pokud ne, zůstaňte s námi, jak to krok za krokem vysvětlujeme.

V uvedeném příkladu dotazu začneme vytvořením dvou aliasů – „p1“ a „p2“ – pro tabulku „produktů“, což nám umožňuje provést vlastní spojení.

Poté přistoupíme ke spojení „p1“ a „p2“ za podmínky, že „id_produktu“ „p1“ je větší nebo rovno „id produktu“ „p2“.

V dalším kroku zavoláme funkci sum(), která v podstatě vypočítá kumulativní součet cen pro každý řádek.

Nakonec výsledky seskupíme pomocí „product_id“, „product_name“ a „price“ a výsledky seřadíme.

Po této operaci bychom měli mít kumulativní součet pro každý záznam, jak je ukázáno ve výsledné tabulce, jak je uvedeno v následujícím:

Jak vidíte, dostaneme celkový součet všech předchozích řádků. Poslední řádek by měl obsahovat celkový součet všech předchozích řádků.

SQL kumulativní součet (funkce okna)

Efektivnějším a praktičtějším způsobem výpočtu kumulativního součtu v SQL je využití funkcí okna, kdykoli jsou podporovány.

Pokud máte databázi, jako je SQL Server, PostgreSQL nebo MySQL verze 8.0 a vyšší, je to nejužitečnější a doporučená metoda pro určení kumulativního součtu daného sloupce.

Podívejte se na příklad, jak je ukázáno v následujícím:

V tomto případě začneme výběrem sloupců „product_id“, „product_name“ a „price“ z tabulky „products“.

Poté použijeme funkci SUM() jako funkci okna pomocí klauzule OVER.

V klauzuli OVER specifikujeme klauzuli ORDER BY, která definuje pořadí, ve kterém se počítá kumulativní součet.

To by mělo vrátit podobný výstup, jak je znázorněno v následujícím:

Všimnete si, že používání funkcí okna je koherentnější, efektivnější a čitelnější ve srovnání s používáním vlastních spojení.

Závěr

V tomto tutoriálu jsme se naučili vše o kumulativních součtech v SQL. Také jsme se zabývali tím, jak používat funkce self-joins a window k provádění kumulativního součtu v SQL.