To je další běžná výzva. Základním principem je shromažďování hodnot jednoho atributu (prvku, který se má agregovat) na základě řazení podle jiného atributu nebo atributů (prvku řazení), pokud existují sekce řádků definované na základě jiného atributu nebo atributů (rozdělení živel). V životě existuje mnoho příkladů výpočtu kumulativních součtů, například výpočet bankovních zůstatků, sledování dostupnosti zboží na skladě nebo aktuálních údajů o prodeji atd.

Před SQL ServerŘešení pro rok 2012 používaná k výpočtu průběžných součtů byla extrémně náročná na zdroje. Lidé se proto obvykle obraceli k iterativním řešením, která byla v některých situacích pomalejší, ale stále rychlejší než řešení založená na množinách. S vylepšenou podporou oken v SQL Server 2012 lze vypočítat průběžné součty pomocí jednoduchého kódu založeného na sadě, který funguje mnohem lépe než starší řešení založená na T-SQL, a to jak na základě sady, tak iterativní. Mohl bych ukázat nové řešení a přejít k další sekci; ale abyste skutečně pochopili rozsah změny, popíšu staré způsoby a porovnám jejich výkon s novým přístupem. Samozřejmě si můžete přečíst pouze první část, která popisuje nový přístup, a zbytek článku přeskočit.

Pro ukázku různá řešení Využiji zůstatky na účtech. Zde je kód, který vytvoří a naplní tabulku transakcí malým množstvím testovacích dat:

SET NOCOUNT ON; POUŽÍVEJTE TSQL2012; POKUD OBJECT_ID ("dbo.Transactions", "U") NENÍ NULL DROP TABLE dbo.Transactions; CREATE TABLE dbo.Transactions (actid INT NOT NULL, - tranidní rozdělovací sloupec INT NOT NULL, - objednávkový sloupec hodnota MONEY NOT NULL, - opatření OMEZENÍ PK_Transactions PRIMARY KEY (actid, tranid)); GO - malá testovací sada dat INSERT INTO dbo.Transakce (aktid, tranid, val) HODNOTY (1, 1, 4.00), (1, 2, -2.00), (1, 3, 5.00), (1, 4, 2.00 ), (1, 5, 1,00), (1, 6, 3,00), (1, 7, -4,00), (1, 8, -1,00), (1, 9, -2,00), (1, 10, -3,00), (2, 1, 2,00), (2, 2, 1,00), (2, 3, 5,00), (2, 4, 1,00), (2, 5, -5,00), (2, 6, 4,00), (2, 7, 2,00), (2, 8, -4,00), (2, 9, -5,00), (2, 10, 4,00), (3, 1, -3,00), (3, 2 , 3,00), (3, 3, -2,00), (3, 4, 1,00), (3, 5, 4,00), (3, 6, -1,00), (3, 7, 5,00), (3, 8 , 3,00), (3, 9, 5,00), (3, 10, -3,00);

Každý řádek v tabulce představuje bankovní transakci na účtu. Vklady jsou označeny jako transakce s kladnou hodnotou ve sloupci val a výběry jako záporná hodnota transakce. Naším úkolem je vypočítat zůstatek účtu v každém časovém okamžiku akumulací součtů operací v řádce val, seřazených podle sloupce tranid, a to je nutné provést pro každý účet zvlášť. Požadovaný výstup by měl vypadat takto:

K testování obou řešení je potřeba více dat. To lze provést pomocí dotazu, jako je tento:

DECLARE @num_partitions AS INT = 10, @rows_per_partition AS INT = 10000; TRUNCATE TABLE dbo.Transakce; INSERT INTO dbo.Transakce S (TABLOCK) (aktid, tranid, val) SELECT NP.n, RPP.n, (ABS (CHECKSUM (NEWID ())% 2) * 2-1) * (1 + ABS (CHECKSUM () NEWID ())% 5)) Z dbo.GetNums (1, @počet_oddílů) JAKO NP CROSS JOIN dbo.GetNums (1, @řádky_na_oddíl) AS RPP;

Zadáním vstupních dat můžete změnit počet sekcí (účtů) a řádků (transakcí) v sekci.

Řešení založené na množinách využívající funkce oken

Začnu s řešením založeným na množině, které využívá agregační funkci v okně SUM. Definice okna je zde celkem jasná: je potřeba rozdělit okno podle actid, seřadit podle tranidu a filtrovat čáry v rámu od krajního dna (NEVYPNUTÉ PŘEDCHOZÍ) k aktuálnímu. Zde je relevantní dotaz:

VYBERTE actid, tranid, val, SUM (val) NAD (ODDĚLENÍ PODLE actid POŘADÍ PODLE tranidních ŘÁDKŮ MEZI NEOZAMKNUTÝMI PŘEDCHOZÍM A AKTUÁLNÍM ŘÁDKEM) JAKO zůstatek OD dbo.Transakce;

Tento kód je nejen jednoduchý a přímočarý, ale také se rychle spouští. Plán pro tento dotaz je znázorněn na obrázku:

Tabulka má seskupený index, který splňuje požadavky POC a je použitelný pro funkce okna. Konkrétně je seznam klíčů indexu založen na rozdělovacím prvku (actid), po kterém následuje prvek řazení (tranid), a také zahrnuje všechny ostatní sloupce v dotazu (val), aby bylo zajištěno pokrytí. Plán obsahuje uspořádaný sken, následovaný výpočtem čísla řádku pro interní potřeby a poté agregací oken. Protože existuje index POC, optimalizátor nemusí do plánu přidávat operátor řazení. Toto je velmi účinný plán. Navíc se lineárně mění. Později, až ukážu výsledky srovnání výkonu, uvidíte, o kolik je tato metoda efektivnější ve srovnání se starými řešeními.

Před SQL Serverem 2012 se používaly buď vnořené dotazy, nebo spojení. Při použití poddotazu se průběžné součty vypočítávají filtrováním všech řádků se stejnou hodnotou actid jako má vnější řádek a tranidovou hodnotou, která je menší nebo rovna hodnotě vnějšího řádku. Na filtrované řádky se pak použije agregace. Zde je relevantní dotaz:

Podobný přístup lze implementovat pomocí spojení. Je použit stejný predikát jako v klauzuli WHERE poddotazu v klauzuli ON spojení. V tomto případě pro N-tou transakci stejného účtu A v instanci označené jako T1 najdete N shod v instanci T2, přičemž čísla transakcí jsou od 1 do N. Výsledkem porovnání je, že řádky v T1 se opakují, takže potřebujete seskupit řádky napříč všemi prvky s T1, abyste získali informace o aktuální transakci a agregovali atribut val z T2 pro výpočet průběžného součtu. Hotová žádost vypadá asi takto:

VYBERTE T1.aktid, T1.tranid, T1.val, SUM (T2.val) AS zůstatek Z dbo.Transakce AS T1 PŘIPOJIT dbo.Transakce AS T2 ON T2.aktid = T1.aktid AND T2.tranid<= T1.tranid GROUP BY T1.actid, T1.tranid, T1.val;

Níže uvedený obrázek ukazuje plány pro obě řešení:

Všimněte si, že v obou případech se na instanci T1 provede úplné skenování seskupeného indexu. Poté je pro každý řádek v plánu poskytnuta vyhledávací operace v indexu začátku sekce běžného účtu na poslední stránce indexu a všechny transakce, ve kterých je T2.tranid menší nebo roven T1.tranid jsou přečteny. Bod, kde dochází k agregaci řádků, se v plánech mírně liší, ale počet přečtených řádků je stejný.

Abyste pochopili, kolik řádků se skenuje, musíte vzít v úvahu počet datových položek. Nechť p - počet sekcí (účtů) a r - počet řádků v sekci (transakce). Pak je počet řádků v tabulce přibližně roven p * r, pokud předpokládáme, že transakce jsou rovnoměrně rozloženy mezi účty. Takže pohled nahoře zahrnuje p * r čar. Nejvíce nás ale zajímá to, co se děje v iterátoru Nested Loops.

V každé sekci plán počítá se čtením 1 + 2 + ... + r řádků, což je celkem (r + r * 2) / 2. Celkový počet řádků zpracovaných v plánech je p * r + p * (r + r2) / 2. To znamená, že počet operací v plánu roste na druhou s rostoucí velikostí sekce, to znamená, že pokud se velikost sekce zvětší f krát, množství práce bude zvýšit asi o f 2krát. Je to špatné. Například 100 řádků odpovídá 10 tisícům řádků a tisíc řádků odpovídá milionu atd. Jednoduše řečeno to vede k silnému zpomalení provádění dotazu s poměrně velkou velikostí sekce, protože kvadratická funkce roste velmi rychle. Taková řešení uspokojivě fungují s několika desítkami linek na sekci, ale ne více.

Kurzorová řešení

Řešení založená na kurzoru jsou implementována přímo. Kurzor je deklarován na základě dotazu řazení dat podle actid a tranid. Poté se provede iterativní procházení záznamů kurzoru. Když je nalezen nový účet, proměnná obsahující agregaci se resetuje. V každé iteraci je k proměnné přidána částka nové transakce, načež je řádek uložen do proměnné tabulky s informací o aktuální transakci plus aktuální hodnota kumulativního součtu. Po iterativním průchodu je výsledek vrácen z proměnné tabulky. Zde je kód pro kompletní řešení:

DECLARE @Result AS TABLE (aktid INT, tranid INT, val MONEY, zůstatek MONEY); DECLARE @actid AS INT, @prvactid AS INT, @tranid AS INT, @val AS MONEY, @balance AS MONEY; DECLARE C CURSOR FAST_FORWARD FOR SELECT actid, tranid, val FROM dbo.Transactions ORDER BY actid, tranid; OTEVŘÍT C PŘINÁŠENÍ NEXT Z C DO @actid, @tranid, @val; SELECT @prvaktid = @aktid, @zůstatek = 0; WHILE @@ fetch_status = 0 ZAČÍNÁ, POKUD @actid<>@prvactid SELECT @prvactid = @aktid, @zůstatek = 0; SET @zůstatek = @zůstatek + @val; INSERT INTO @Result VALUES (@actid, @tranid, @val, @balance); PŘITAŽTE DALŠÍ Z C DO @actid, @tranid, @val; KONEC ZAVŘÍT C; DEALOCATE C; SELECT * FROM @Result;

Plán dotazů pomocí kurzoru je znázorněn na obrázku:

Tento plán se mění lineárně, protože data z indexu jsou skenována pouze jednou v určitém pořadí. Každá operace získání řádku z kurzoru má také přibližně stejné náklady na řádek. Pokud vezmeme zatížení generované zpracováním jednoho řádku kurzoru rovné g, náklady na toto řešení lze odhadnout jako p * r + p * r * g (jak si pamatujete, p je počet sekcí a r je počet řádků v sekci). Pokud tedy zvýšíme počet řádků na sekci f krát, zatížení systému bude p * r * f + p * r * f * g, to znamená, že poroste lineárně. Náklady na zpracování na řádek jsou vysoké, ale vzhledem k lineární povaze škálování bude toto řešení od určité velikosti oddílu vykazovat lepší škálovatelnost než vnořená a spojová řešení kvůli kvadratickému škálování těchto řešení. Měření výkonu, které jsem provedl, ukázalo, že počet, kdy je kurzorové řešení rychlejší, je několik set řádků na sekci.

Navzdory zvýšení výkonu poskytovaného řešeními založenými na kurzoru by se jim obecně mělo vyhnout, protože nejsou relační.

Řešení založená na CLR

Jedno možné řešení založené na CLR (Common Language Runtime) je v podstatě forma řešení založeného na kurzoru. Rozdíl je v tom, že namísto použití kurzoru T-SQL, který plýtvá spoustou prostředků k načtení dalšího řádku a iteraci, použijte iterace .NET SQLDataReader a .NET, které jsou mnohem rychlejší. Jednou z funkcí CLR, díky které je tato možnost rychlejší, je to, že výsledný řádek v dočasné tabulce není potřeba – výsledky jsou odesílány přímo volajícímu procesu. Logika rozhodování založená na CLR je podobná logice rozhodování založené na kurzoru T-SQL. Zde je kód C #, který definuje uloženou proceduru řešení:

Použití systému; pomocí System.Data; pomocí System.Data.SqlClient; pomocí System.Data.SqlTypes; pomocí Microsoft.SqlServer.Server; veřejná částečná třída StoredProcedures (veřejná statická void AccountBalances () (pomocí (SqlConnection conn = new SqlConnection ("kontextové připojení = true;")) (SqlCommand comm = new SqlCommand (); comm.Connection = conn; comm.CommandText = @ " "+" SELECT actid, tranid, val "+" FROM dbo.Transakce "+" ORDER BY actid, tranid; "; Sloupce SqlMetaData = nové SqlMetaData; sloupce = nové SqlMetaData (" actid ", SqlDbType.Int); sloupce = sloupce SqlMetaData ("tranid", SqlDbType.Int); columns = new SqlMetaData ("val", SqlDbType.Money); columns = new SqlMetaData ("zůstatek", SqlDbType.Money); záznam SqlDataqlord = nový SqlDataqlord = nový SqlDataqpe. (záznam); conn.Open (); Reader SqlDataReader = comm.ExecuteReader (); SqlInt32 prvactid = 0; Zůstatek SqlMoney = 0; while (reader.Read ()) (SqlInt32 actid = reader.GetSqlInt32 (0) ; SqlMoney = reader.GetSqlMoney (2); if (actid == prvactid) (zůstatek + = hodnota;) else (zůstatek = hodnota;) prvactid = actid; rec ord.SetSqlInt32 (0, reader.GetSqlInt32 (0)); record.SetSqlInt32 (1, reader.GetSqlInt32 (1)); záznam.SetSqlMoney (2, val); záznam.SetSqlMoney (3, zůstatek); SqlContext.Pipe.SendResultsRow (záznam); ) SqlContext.Pipe.SendResultsEnd (); )))

Abyste mohli spustit tuto uloženou proceduru na SQL Serveru, musíte nejprve z tohoto kódu sestavit sestavení s názvem AccountBalances a nasadit jej do databáze TSQL2012. Pokud s nasazováním sestavení na SQL Server teprve začínáte, můžete si přečíst část Uložené procedury a CLR v článku Uložené procedury.

Pokud jste sestavení pojmenovali AccountBalances a cesta k souboru sestavení je "C: \ Projects \ AccountBalances \ bin \ Debug \ AccountBalances.dll", můžete sestavení načíst do databáze a zaregistrovat uloženou proceduru s následujícím kódem:

VYTVOŘIT SESTAVENÍ AccountBalances Z "C: \ Projects \ AccountBalances \ bin \ Debug \ AccountBalances.dll"; GO CREATE PROCEDURE dbo.AccountBalances JAKO EXTERNÍ NÁZEV AccountBalances.StoredProcedures.AccountBalances;

Po nasazení sestavení a registraci procedury ji můžete spustit pomocí následujícího kódu:

EXEC dbo.AccountBalances;

Jak jsem řekl dříve, SQLDataReader je jen další forma kurzoru, ale v této verzi jsou náklady na čtení řádků výrazně nižší než při použití tradičního kurzoru T-SQL. Také v .NET jsou iterace mnohem rychlejší než v T-SQL. Řešení založená na CLR se tedy také lineárně škálují. Testování ukázalo, že výkon tohoto řešení je vyšší než výkon řešení využívajících poddotazy a spojení, když počet řádků v sekci překročí 15.

Po dokončení spusťte následující kód čištění:

PROCEDUR DROP dbo.AccountBalances; DROP MONTÁŽ Zůstatky účtů;

Vnořené iterace

Až do tohoto bodu jsem ukazoval iterativní řešení a řešení založená na množinách. Následující řešení je založeno na vnořených iteracích, což je hybrid iterativních a množinových přístupů. Cílem je předem zkopírovat řádky ze zdrojové tabulky (v našem případě se jedná o bankovní účty) do dočasné tabulky spolu s novým atributem s názvem rownum, který se vypočítá pomocí funkce POČET_ŘÁDKU. Čísla řádků jsou rozdělena podle actid a řazena podle tranidu, takže první transakci na každém bankovním účtu je přiřazeno číslo 1, druhé transakci je přiřazeno číslo 2 atd. Poté se v dočasné tabulce vytvoří seskupený index se seznamem klíčů (rownum, actid). Potom se použije rekurzivní výraz CTE nebo speciálně navržená smyčka ke zpracování jednoho řádku na iteraci napříč všemi fakturami. Průběžný součet se pak vypočítá sečtením hodnoty odpovídající aktuálnímu řádku s hodnotou spojenou s předchozím řádkem. Zde je implementace této logiky pomocí rekurzivního CTE:

SELECT actid, tranid, val, ROW_NUMBER () OVER (ODDĚLENÍ PODLE actid ORDER BY tranid) AS rownum INTO #Transactions FROM dbo.Transactions; VYTVOŘIT JEDINEČNÝ CLUSTERED INDEX idx_rownum_actid ON #Transactions (rownum, actid); S C AS (SELECT 1 AS rownum, actid, tranid, val, val AS sumqty FROM #Transactions WHERE rownum = 1 UNION ALL SELECT PRV.rownum + 1, PRV.actid, CUR.tranid, CUR.val, PRV.sumqty + CUR.val Z C JAKO PRV JOIN #Transactions AS CUR ON CUR.rownum = PRV.rownum + 1 AND CUR.actid = PRV.actid) SELECT actid, tranid, val, sumqty FROM C OPTION (MAXRECURSION 0); DROP TABLE #Transakce;

A toto je implementace pomocí explicitní smyčky:

SELECT ROW_NUMBER () OVER (PARTITION BY actid ORDER BY tranid) AS rownum, actid, tranid, val, CAST (val AS BIGINT) AS sumqty INTO #Transactions FROM dbo.Transactions; VYTVOŘIT JEDINEČNÝ CLUSTERED INDEX idx_rownum_actid ON #Transactions (rownum, actid); DECLARE @rownum JAKO INT; SET @rownum = 1; WHILE 1 = 1 BEGIN SET @rownum = @rownum + 1; AKTUALIZOVAT CUR SET sumqty = PRV.sumqty + CUR.val FROM #Transactions AS CUR JOIN #Transactions AS PRV ON CUR.rownum = @rownum AND PRV.rownum = @rownum - 1 AND CUR.actid = PRV.actid; IF @@ rowcount = 0 BREAK; END SELECT actid, tranid, val, sumqty FROM #Transactions; DROP TABLE #Transakce;

Toto řešení poskytuje dobrý výkon, když existuje velký počet sekcí s malým počtem řádků na sekci. Potom je počet iterací malý a většinu práce vykonává množinová část řešení, která spojuje řádky spojené se stejným číslem řádku s řádky přidruženými k předchozímu číslu řádku.

Víceřádková aktualizace s proměnnými

Techniky pro výpočet kumulativních součtů uvedené do tohoto bodu zaručují správný výsledek. Metodika popsaná v této části je nejednoznačná, protože je založena spíše na pozorovaném než dokumentovaném chování systému a také odporuje principům relativity. Jeho vysoká atraktivita je dána vysokou rychlostí práce.

Tato metoda používá příkaz UPDATE s proměnnými. Příkaz UPDATE může přiřadit výrazy proměnným na základě hodnoty sloupce a také přiřadit hodnoty ve sloupcích výrazu s proměnnou. Řešení začíná vytvořením dočasné tabulky s názvem Transactions s atributy actid, tranid, val a balance a shlukovaného indexu se seznamem klíčů (actid, tranid). Dočasná tabulka se poté vyplní všemi řádky z původní databáze transakcí, přičemž do sloupce zůstatku všech řádků se zadá 0,00. Poté je vyvolán příkaz UPDATE s proměnnými spojenými s dočasnou tabulkou pro výpočet průběžných součtů a vložení vypočtené hodnoty do sloupce zůstatku.

Používají se proměnné @prevaccount a @prevbalance a hodnota ve sloupci zůstatek se vypočítá pomocí následujícího výrazu:

SET @prevbalance = zůstatek = PŘÍPAD, KDYŽ actid = @prevaccount THEN @prevbalance + val ELSE val END

Výraz CASE zkontroluje, zda jsou identifikátory aktuálního a předchozího účtu stejné, a pokud jsou stejné, vrátí součet předchozích a aktuálních hodnot ve sloupci zůstatek. Pokud se ID účtu liší, vrátí se aktuální částka transakce. Výsledek výrazu CASE je poté vložen do sloupce zůstatek a přiřazen k proměnné @prevbalance. V samostatném výrazu je proměnné © prevaccount přiřazen identifikátor běžného účtu.

Po příkazu UPDATE řešení zobrazí řádky z dočasné tabulky a odstraní poslední. Zde je kód pro kompletní řešení:

CREATE TABLE #Transactions (actid INT, tranid INT, val MONEY, zůstatek MONEY); VYTVOŘIT CLUSTERED INDEX idx_actid_tranid ON #Transactions (actid, tranid); INSERT INTO #Transactions WITH (TABLOCK) (actid, tranid, val, balance) SELECT actid, tranid, val, 0,00 FROM dbo.Transakce ORDER BY actid, tranid; DECLARE @prevaccount JAKO INT, @prevbalance JAKO PENÍZE; AKTUALIZACE #NASTAVENÍ transakcí @prevbalance = zůstatek = PŘÍPAD, KDYŽ actid = @prevaccount THEN @prevbilance + val ELSE val END, @prevaccount = actid FROM #Transactions WITH (INDEX (1), TABLOCKX) OPTION (MAXDOP 1); SELECT * FROM #Transactions; DROP TABLE #Transakce;

Plán tohoto řešení je znázorněn na následujícím obrázku. První část je příkaz INSERT, druhá je UPDATE a třetí je SELECT:

Toto řešení předpokládá, že optimalizace provádění UPDATE vždy provedou uspořádané skenování seskupeného indexu, a řešení poskytuje řadu rad, jak zabránit okolnostem, které by tomu mohly zabránit, jako je například souběžnost. Problém je v tom, že neexistuje žádná oficiální záruka, že optimalizátor bude vždy hledat v pořadí seskupeného indexu. Nemůžete se spoléhat na specifika fyzického výpočtu při vynucení logické správnosti vašeho kódu, pokud v kódu nejsou logická hradla, která mohou toto chování zaručit. V tomto kódu nejsou žádné logické funkce, které by mohly zaručit přesně toto chování. Volba, zda tuto metodu použít či nikoli, je přirozeně zcela na vašem svědomí. Myslím, že je nezodpovědné to používat, i když jste to zkontrolovali tisíckrát a "zdá se, že všechno funguje, jak má."

Naštěstí se v SQL Server 2012 tato volba stává téměř zbytečnou. Díky extrémně efektivnímu řešení agregace v okně nemusíte přemýšlet o jiných řešeních.

měření výkonu

Měřil jsem a porovnával výkon různých technik. Výsledky jsou zobrazeny na obrázcích níže:

Výsledky jsem rozdělil do dvou grafů, protože metoda subquery nebo join je tak pomalejší než ostatní, že jsem pro ni musel použít jiné měřítko. V každém případě si všimněte, že většina řešení vykazuje lineární závislost množství práce na velikosti oddílu a pouze řešení založené na dílčím dotazu nebo spojení vykazuje kvadratickou závislost. Je také jasné, o kolik efektivnější je nové řešení agregace v okně. Řešení UPDATE s proměnnými je také velmi rychlé, ale z již popsaných důvodů jej nedoporučuji používat. Řešení CLR je také docela rychlé, ale musíte napsat celý tento .NET kód a nasadit sestavení do databáze. Ať se na to podíváte jakkoli, preferovaným řešením zůstává set-based řešení využívající okenní agregáty.

Jak zjistím počet modelů PC vyrobených konkrétním prodejcem? Jak zjistit průměrnou cenu za počítače se stejnými specifikacemi? Tyto a mnohé další otázky související s některými statistickými informacemi lze zodpovědět pomocí souhrnné (agregační) funkce... Norma poskytuje následující agregační funkce:

Všechny tyto funkce vracejí jedinou hodnotu. V tomto případě funkce POČET, MIN a MAX jsou použitelné pro jakýkoli typ dat, zatímco SOUČET a AVG se používají pouze pro číselná pole. Rozdíl mezi funkcí POČET (*) a POČET (<имя поля>) je, že druhý nebere při výpočtu v úvahu hodnoty NULL.

Příklad. Najděte minimální a maximální cenu osobních počítačů:

Příklad. Zjistěte počet dostupných počítačů od výrobce A:

Příklad. Pokud nás zajímá počet různých modelů vyrobených výrobcem A, pak lze dotaz formulovat následovně (s využitím skutečnosti, že v tabulce Produkt je každý model zaznamenán jednou):

Příklad. Najděte počet různých modelů dostupných od výrobce A. Dotaz je podobný předchozímu, ve kterém bylo požadováno zjistit celkový počet modelů vyrobených výrobcem A. Zde je také potřeba zjistit počet různých modelů v PC stůl (tj. komerčně dostupný).

Aby bylo zajištěno, že při získávání statistických ukazatelů budou použity pouze jedinečné hodnoty, když argument agregačních funkcí může být použito DISTINCT parametr... Další parametr VŠECHNY je výchozí a předpokládá, že se počítají všechny vrácené hodnoty ve sloupci. Operátor,

Pokud potřebujeme získat počet vyrobených modelů PC každý výrobce, budete muset použít klauzule GROUP BY syntakticky následující věty WHERE.

klauzule GROUP BY

Pokud by SELECT měl sloupec s datem, pak by bylo možné tyto ukazatele vypočítat pro každé konkrétní datum. Chcete-li to provést, musíte přidat datum jako sloupec seskupení a pak by se pro každou kombinaci hodnot (model-date) vypočítaly agregační funkce.

Existuje několik konkrétních pravidla pro provádění agregačních funkcí:

• Pokud v důsledku dotazu nebyly přijaty žádné řádky(nebo více než jeden řádek pro danou skupinu), pak chybí počáteční data pro výpočet kterékoli z agregačních funkcí. V tomto případě bude výsledek provádění funkcí COUNT nula a výsledek všech ostatních funkcí bude NULL.
• Argument agregační funkce nemůže sám obsahovat agregační funkce(funkce z funkce). Tito. v jediném dotazu nemůžete, řekněme, získat maximum průměrů.
• Výsledek provedení funkce COUNT je celé číslo(CELÉ ČÍSLO). Ostatní agregační funkce dědí datové typy zpracovávaných hodnot.
• Pokud byl při provádění funkce SUM získán výsledek, který překračuje maximální hodnotu použitého datového typu, chyba.

Pokud tedy žádost neobsahuje GROUP BY klauzule, pak agregační funkce obsažen v klauzule SELECT, jsou prováděny na všech výsledných řádcích dotazu. Pokud žádost obsahuje klauzule GROUP BY, každá sada řádků, která má stejné hodnoty sloupce nebo skupiny sloupců specifikované v klauzule GROUP BY, tvoří skupinu a agregační funkce se provádějí pro každou skupinu zvlášť.

klauzule HAVING

Li klauzule WHERE pak definuje predikát pro filtrování řádků MÁME nabídku aplikovaný po seskupení k definování podobných predikátových skupin filtrování podle hodnot agregační funkce... Tato klauzule je potřebná k testování hodnot, které jsou získány pomocí agregační funkce nikoli ze samostatných řádků zdroje záznamu definovaného v klauzule FROM a od skupiny takových linek... Takovou kontrolu tedy nelze obsahovat klauzule WHERE.

Tento tutoriál vám ukáže, jak používat funkce SUM v SQL Server (Transact-SQL) se syntaxí a příklady.

Popis

SQL Server (Transact-SQL) funkce SUM vrátí celkovou hodnotu výrazu.

Syntax

Syntaxe funkce SUM v SQL Server (Transact-SQL) je:

NEBO syntaxe funkce SUM při seskupování výsledků podle jednoho nebo více sloupců je:

Parametry nebo argumenty

výraz1, výraz2, ... výraz_n jsou výrazy, které nejsou zahrnuty ve funkci SUM a musí být zahrnuty v klauzuli GROUP BY na konci příkazu SQL.
agregovaný_výraz je sloupec nebo výraz, který se má sečíst.
tabulky - tabulky, ze kterých chcete získat záznamy. V klauzuli FROM musí být uvedena alespoň jedna tabulka.
WHERE podmínky jsou nepovinné. Toto jsou podmínky, které musí být splněny u vybraných záznamů.

aplikace

Funkci SUM lze použít v následujících verzích SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Příklad jednoho pole

Podívejme se na některé příklady funkcí SQL Server SUM, abychom pochopili, jak používat funkci SUM v SQL Server (Transact-SQL).

Můžete například zjistit, jak je součet všech produktů, jejichž počet je větší než 10.

V tomto příkladu funkce SUM nastavíme výraz SUM (množství) na alias "Celkové množství". Při vrácení sady výsledků - "Celkové množství" se zobrazí jako název pole.

Příklad použití DISTINCT

Ve funkci SUM můžete použít operátor DISTINCT. Například následující příkaz SQL vrátí celkový plat s jedinečnými hodnotami platu, kde je plat nižší než 29 000 $ za rok.

Pokud by dva platy byly 24 000 $ ročně, ve funkci SUM by byla použita pouze jedna z těchto hodnot.

Příklad použití vzorce

Výraz obsažený ve funkci SUM nemusí být jedno pole. Můžete také použít vzorec. Můžete si například spočítat celkovou provizi.

Transact-SQL

VYBERTE SOUČET (prodej * 0,03) JAKO "Celková provize" Z objednávek;

Příklad použití GROUP BY

V některých případech budete muset použít klauzuli GROUP BY s funkcí SUM.

Lekce SQL 11. Souhrnné funkce, vypočítané sloupce a pohledy

• Funkce AVG () vrací průměrnou hodnotu sloupce.


• COUNT () Funkce vrací počet řádků ve sloupci.


• MAX () Funkce vrací největší hodnotu ve sloupci.


• MIN () Funkce vrací nejmenší hodnotu ve sloupci.


• SUM () Funkce vrací součet hodnot sloupců.

VYBERTE MIN (cena), MAX (cena), AVG (cena) Z cen;

Nyní chceme zjistit, za kolik nám dodavatel "Tiskárna" přivezl zboží (id = 2). Sestavit takovou žádost není tak jednoduché. Pojďme se zamyslet nad tím, jak to složit:

1. Nejprve z tabulky Dodávky (příchozí) vyberte identifikátory (id_incoming) těch dodávek, které provedl dodavatel „Tiskárny“ (id = 2):

2. Nyní z tabulky Zásobník dodávek (magazine_incoming) je třeba vybrat zboží (id_product) a jeho množství (množství), které byly provedeny v dodávkách uvedených v odstavci 1. To znamená, že požadavek z odstavce 1 se vnoří:

3. Nyní musíme do výsledné tabulky doplnit ceny za nalezené produkty, které jsou uloženy v tabulce cen. To znamená, že potřebujeme spojit tabulky Magazine_incoming a Price sloupcem id_product:

4. Ve výsledné tabulce jednoznačně chybí sloupec Součet, tzn vypočítaný sloupec... Schopnost vytvářet takové sloupce poskytuje MySQL. K tomu stačí v dotazu zadat název počítaného sloupce a co má počítat. V našem příkladu se takový sloupec bude nazývat summa a vypočítá součin sloupců množství a cena. Název nového sloupce je oddělen slovem AS:

SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity * prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product = prices.id_product AND id_incoming = (SELECT id_incoming FROM incoming WHERE 2 id

5. Super, nezbývá nám než sečíst sloupec summa a nakonec zjistit, za kolik nám dodavatel "Tiskárna" zboží přivezl. Syntaxe pro použití funkce SUM () je následující:

SELECT SUM (název_sloupce) FROM název_tabulky;

Známe název sloupce - summa, ale nemáme název tabulky, protože je výsledkem dotazu. Co dělat? Pro takové případy poskytuje MySQL pohledy. Pohled je výběrový dotaz, kterému je přiřazen jedinečný název a lze jej uložit do databáze pro pozdější použití.

Syntaxe pro vytvoření pohledu je následující:

CREATE VIEW název_zobrazení JAKO dotaz;

Uložme náš požadavek jako pohled s názvem report_vendor:

VYTVOŘIT ZOBRAZENÍ report_vendor JAKO VÝBĚR magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity * prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product = prices.id_product AND id_incoming = (OD (OD HE id_incomingREincoming) ;

6. Nyní můžete použít konečnou funkci SUM ():

SELECT SUM (součet) FROM report_vendor;

Takže jsme dosáhli výsledku, i když jsme k tomu museli použít vnořené dotazy, spojení, vypočítané sloupce a pohledy. Ano, někdy musíte přemýšlet, abyste dosáhli výsledku, aniž by to bylo kdekoli. Dotkli jsme se ale dvou velmi důležitých témat – počítaných sloupců a pohledů. Pojďme si o nich povědět podrobněji.

Vypočítaná pole (sloupce)

SELECT název_sloupce_1, název_sloupce_2, název_sloupce_1 * název_sloupce_2 AS název vypočítaného_sloupce FROM název_tabulky;

Druhou nuancí je klíčové slovo AS, pomocí něj jsme nastavili název počítaného sloupce. Ve skutečnosti se toto klíčové slovo používá k nastavení aliasů pro libovolné sloupce. Proč je to potřeba? Pro kratší a čitelnější kód. Náš pohled může vypadat například takto:

CREATE VIEW report_vendor AS SELECT A.id_product, A.quantity, B.price, A.quantity * B.price AS summa FROM magazine_incoming AS A, prices AS B WHERE A.id_product = B.id_product AND id_incoming = (SELECT id_incoming FROM incoming WHERE id_vendor = 2);

Souhlaste, že je to mnohem kratší a přehlednější.

Reprezentace

Mějte však na paměti, že pohledy nejsou tabulky, to znamená, že neukládají data, pouze je načítají z jiných tabulek. Za prvé, když se změní data v tabulkách, změní se i výsledky prezentace. A za druhé, při dotazu na pohled se vyhledávají požadovaná data, to znamená, že výkon DBMS klesá. Proto by neměly být zneužívány.

Naučíme se shrnout. Ne, toto ještě nejsou výsledky studia SQL, ale výsledky hodnot sloupců databázových tabulek. Agregační funkce SQL působí na hodnoty sloupců a vytvářejí jedinou výslednou hodnotu. Nejčastěji používané agregační funkce SQL jsou SUM, MIN, MAX, AVG a COUNT. Je nutné rozlišovat dva případy použití agregačních funkcí. Nejprve se agregační funkce používají samy o sobě a vracejí jedinou výslednou hodnotu. Za druhé, agregační funkce se používají s klauzulí SQL GROUP BY, to znamená se seskupováním podle polí (sloupců), aby se získaly výsledné hodnoty v každé skupině. Podívejme se nejprve na případy použití agregačních funkcí bez seskupování.

Funkce SQL SUM

Funkce SQL SUM vrací součet hodnot sloupce v databázové tabulce. Lze jej použít pouze na sloupce, jejichž hodnoty jsou čísla. Dotazy SQL pro získání výsledného součtu začínají takto:

VYBRAT SOUČET (COLUMN_NAME)...

Za tímto výrazem následuje FROM (TABLE_NAME) a ​​poté lze zadat podmínku pomocí klauzule WHERE. Kromě toho lze před názvem sloupce zadat DISTINCT, což znamená, že se budou počítat pouze jedinečné hodnoty. Ve výchozím nastavení se berou v úvahu všechny hodnoty (k tomu můžete konkrétně zadat ne DISTINCT, ale ALL, ale slovo ALL je volitelné).

Pokud chcete provádět dotazy do databáze z této lekce na MS SQL Server, ale tento DBMS není na vašem počítači nainstalován, můžete jej nainstalovat podle pokynů na tomto odkazu .

Nejprve budeme pracovat s databází firmy – Firma1. Skript pro vytvoření této databáze, jejích tabulek a naplnění tabulek daty - v souboru na tomto odkazu .

Příklad 1 Existuje databáze společnosti s údaji o jejích divizích a zaměstnancích. Tabulka Zaměstnanci má kromě všeho ještě sloupec s údaji o platech zaměstnanců. Výběr z tabulky je následující (pro zvětšení obrázku na něj klikněte levým tlačítkem myši):

Pro získání součtu všech mezd použijeme následující dotaz (na MS SQL Server - s předponou USE company1;):

VYBERTE SOUČTU (plat) ZE zaměstnanců

Tento dotaz vrátí 287664,63.

A teď . Ve cvičeních již začínáme úkoly komplikovat, přibližovat je těm, se kterými se setkáváme v praxi.

Funkce SQL MIN

Funkce SQL MIN funguje také na sloupcích, jejichž hodnoty jsou čísla a vrací minimum ze všech hodnot ve sloupci. Tato funkce má stejnou syntaxi jako funkce SUM.

Příklad 3 Databáze a tabulka jsou stejné jako v příkladu 1.

Je nutné zjistit minimální mzdu zaměstnanců oddělení číslo 42. K tomu napište následující dotaz (na MS SQL Server - s předponou USE company1;):

Požadavek vrátí hodnotu 10505,90.

A znovu svépomocné cvičení... V tomto a některých dalších cvičeních budete potřebovat nejen tabulku Staff, ale také tabulku Org, která obsahuje údaje o divizích společnosti:

Příklad 4 Tabulka Org je přidána do tabulky Zaměstnanci, která obsahuje údaje o divizích firmy. Zobrazte minimální počet let odpracovaných jedním zaměstnancem v oddělení se sídlem v Bostonu.

Funkce SQL MAX

Funkce SQL MAX funguje podobně a má podobnou syntaxi, která se používá, když potřebujete určit maximální hodnotu mezi všemi hodnotami ve sloupci.

Příklad 5.

Je nutné zjistit maximální plat zaměstnanců oddělení číslo 42. K tomu napište následující dotaz (na MS SQL Server - s předchozí USE company1; konstruujte):

Požadavek vrátí hodnotu 18352,80

Je čas cvičení pro sebeřešení.

Příklad 6. Opět pracujeme se dvěma tabulkami – Staff a Org. Vytiskněte název oddělení a maximální provize získané jedním zaměstnancem v oddělení patřícím do východní divize. Použití JOIN (připojit se ke stolům) .

Funkce SQL AVG

Výše uvedená syntaxe pro dříve popsané funkce platí také pro funkci SQL AVG. Tato funkce vrací průměr všech hodnot ve sloupci.

Příklad 7. Databáze a tabulka jsou stejné jako v předchozích příkladech.

Předpokládejme, že chcete zjistit průměrnou senioritu zaměstnanců oddělení číslo 42. Chcete-li to provést, napište následující dotaz (na MS SQL Server - s předponou USE company1;):

Výsledkem bude hodnota 6,33

Příklad 8. Pracujeme s jedním stolem – Staff. Vyberte průměrnou mzdu zaměstnanců s praxí 4 až 6 let.

Funkce SQL COUNT

Funkce SQL COUNT vrací počet záznamů v databázové tabulce. Pokud v dotazu zadáte SELECT COUNT (COLUMN_NAME) ..., výsledkem bude počet záznamů s výjimkou záznamů, ve kterých je hodnota sloupce NULL (nedefinováno). Pokud jako argument použijete hvězdičku a spustíte dotaz SELECT COUNT (*) ..., výsledkem bude počet všech záznamů (řádků) v tabulce.

Příklad 9. Databáze a tabulka jsou stejné jako v předchozích příkladech.

Je nutné zjistit počet všech zaměstnanců, kteří dostávají provize. Počet zaměstnanců, jejichž hodnoty sloupce Comm nejsou NULL, vrátí následující dotaz (na MS SQL Server - s předchozí USE company1; konstrukt):

VYBERTE POČET (Komunikace) OD personálu

Výsledkem je 11.

Příklad 10. Databáze a tabulka jsou stejné jako v předchozích příkladech.

Pokud potřebujete zjistit celkový počet záznamů v tabulce, pak jako argument funkce COUNT použijeme dotaz s hvězdičkou (na MS SQL Server - s předchozím USE company1; konstrukt):

VYBERTE POČET (*) OD zaměstnanců

Výsledkem je 17.

V dalším svépomocné cvičení budete muset použít poddotaz.

Příklad 11. Pracujeme s jedním stolem – Staff. Zobrazení počtu zaměstnanců v oddělení plánování (Plains).

Agregační funkce s SQL GROUP BY (seskupení)

Nyní se podívejme na použití agregačních funkcí ve spojení s klauzulí SQL GROUP BY. Klauzule SQL GROUP BY se používá k seskupení výsledných hodnot podle sloupců databázové tabulky. Stránka má lekci věnovanou tomuto operátorovi samostatně .

Budeme pracovat s databází „Ads Portal 1“. Skript pro vytvoření této databáze, její tabulky a naplnění datové tabulky je v souboru na tomto odkazu .

Příklad 12. Existuje tedy databáze portálu s inzeráty. Obsahuje tabulku Reklamy, která obsahuje údaje o odeslaných reklamách za daný týden. Sloupec Kategorie obsahuje údaje o velkých kategoriích inzerátů (například Nemovitosti) a sloupec Části obsahuje údaje o menších částech zahrnutých do kategorie (například části Byty a vily jsou součástí kategorie Nemovitosti). Sloupec Jednotky obsahuje údaje o počtu odeslaných inzerátů a sloupec Peníze částku přijatou za odeslání inzerátu.

Pomocí příkazu SQL GROUP BY zjistěte množství peněz vydělaných zobrazováním reklam v každé kategorii. Napíšeme následující dotaz (na MS SQL Server - s předchozí konstrukcí USE adportal1;):

VYBERTE kategorii, SOUČET (Peníze) JAKO PENÍZE Z REKLAMNÍ SKUPINY PODLE kategorie

Příklad 13. Databáze a tabulka jsou stejné jako v předchozím příkladu.

Pomocí příkazu SQL GROUP BY zjistěte, která část každé kategorie měla nejvíce reklam. Napíšeme následující dotaz (na MS SQL Server - s předchozí konstrukcí USE adportal1;):

VYBERTE kategorii, díl, MAX (jednotky) JAKO maximum ZE SKUPINY REKLAM PODLE kategorie

Výsledkem bude následující tabulka:

Lze získat celkové a jednotlivé hodnoty v jedné tabulce kombinování výsledků dotazu pomocí operátoru UNION .

Relační databáze a jazyk SQL