SQL agregační funkce - SUM, MIN, MAX, AVG, COUNT. Agregační funkce

Následující pododdíly popisují další klauzule příkazu SELECT, které lze použít v dotazech, a také agregační funkce a sady příkazů. Pro připomenutí, dosud jsme se zabývali použitím klauzule WHERE a v tomto článku se podíváme na klauzule GROUP BY, ORDER BY a HAVING a uvedeme některé příklady použití těchto klauzulí ve spojení s agregací funkce, které jsou podporovány v Transact-SQL.

Nabídka GROUP BY

Nabídka SKUPINA VYTVOŘENÁ seskupí vybranou sadu řádků a vytvoří sadu souhrnných řádků na základě hodnot jednoho nebo více sloupců nebo výrazů. Jednoduché použití klauzule GROUP BY je znázorněno v příkladu níže:

POUŽÍVEJTE SampleDb; SELECT Job FROM Works_On GROUP BY Job;

V tomto příkladu jsou vybrány a seskupeny pozice zaměstnanců.

Ve výše uvedeném příkladu klauzule GROUP BY vytvoří samostatnou skupinu pro všechny možné hodnoty (včetně NULL) sloupce Job.

Použití sloupců v nabídka SKUPINA BY musí splňovat určité podmínky. Zejména se každý sloupec ve výběrovém seznamu dotazu musí objevit také v klauzuli GROUP BY. Tento požadavek se nevztahuje na konstanty a sloupce, které jsou součástí agregační funkce. (Agregační funkce jsou diskutovány v další podkapitole.) To dává smysl, protože pouze sloupcům v klauzuli GROUP BY je zaručena jedna hodnota na skupinu.

Tabulku lze seskupit podle libovolné kombinace jejích sloupců. Níže uvedený příklad ukazuje seskupení řádků tabulky Works_on do dvou sloupců:

POUŽÍVEJTE SampleDb; SELECT ProjectNumber, Job FROM Works_On GROUP BY ProjectNumber, Job;

Výsledkem tohoto dotazu je:

Na základě výsledků dotazu můžete vidět, že existuje devět skupin s různými kombinacemi čísla projektu a pozice. Pořadí názvů sloupců v klauzuli GROUP BY nemusí být stejné jako v seznamu sloupců SELECT.

Agregační funkce

Agregační funkce se používají k získání celkových hodnot. Všechny agregační funkce lze rozdělit do následujících kategorií:

běžné agregační funkce;

statistické agregační funkce;

agregované funkce definované uživatelem;

analytické agregační funkce.

Zde se podíváme na první tři typy agregačních funkcí.

Obyčejné agregační funkce

Jazyk Transact-SQL podporuje následujících šest agregačních funkcí: MIN, MAX, SOUČET, AVG, POČET, COUNT_BIG.

Všechny agregační funkce provádějí výpočty s jedním argumentem, kterým může být sloupec nebo výraz. (Jedinou výjimkou je druhý tvar dvou funkcí COUNT a COUNT_BIG, konkrétně COUNT(*) a COUNT_BIG(*) v tomto pořadí.) Výsledkem jakékoli agregační funkce je konstantní hodnota zobrazená v samostatném sloupci výsledků.

Agregační funkce jsou specifikovány v seznamu sloupců příkazu SELECT, který může také obsahovat klauzuli GROUP BY. Pokud v příkazu SELECT není klauzule GROUP BY a seznam sloupců select obsahuje alespoň jednu agregační funkci, pak nesmí obsahovat jednoduché sloupce (jiné než sloupce, které slouží jako argumenty agregační funkce). Proto je kód v níže uvedeném příkladu nesprávný:

POUŽÍVEJTE SampleDb; SELECT Příjmení, MIN(Id) FROM Zaměstnanec;

Zde by sloupec Příjmení v tabulce Zaměstnanec neměl být ve výběrovém seznamu sloupců, protože se nejedná o argument agregační funkce. Na druhé straně může seznam pro výběr sloupců obsahovat názvy sloupců, které nejsou argumenty agregační funkce, pokud jsou tyto sloupce argumenty klauzule GROUP BY.

Argumentu agregační funkce může předcházet jeden ze dvou možných klíčová slova:

Určuje, že výpočty se provádějí se všemi hodnotami ve sloupci. Toto je výchozí hodnota.

Určuje, že pro výpočty se používají pouze jedinečné hodnoty sloupců.

Agregační funkce MIN a MAX

Agregační funkce MIN a MAX vypočítají nejmenší a nejvyšší hodnotu sloupců resp. Pokud dotaz obsahuje klauzuli WHERE, funkce MIN a MAX vrátí nejmenší a největší hodnotu řádků, které splňují zadané podmínky. Níže uvedený příklad ukazuje použití agregační funkce MIN:

POUŽÍVEJTE SampleDb; -- Vrátí 2581 SELECT MIN(Id) AS "Min Id" OD zaměstnance;

Výsledek vrácený ve výše uvedeném příkladu není příliš informativní. Například není známo jméno zaměstnance, který toto číslo vlastní. Toto příjmení však není možné získat běžným způsobem, protože, jak již bylo zmíněno dříve, není povoleno explicitní uvedení sloupce Příjmení. Pro získání příjmení tohoto zaměstnance spolu s nejnižším osobním číslem zaměstnance se používá poddotaz. Následující příklad ukazuje použití takového poddotazu, kde poddotaz obsahuje příkaz SELECT z předchozího příkladu:

Výsledek spuštění dotazu:

Použití agregační funkce MAX je znázorněno v příkladu níže:

Funkce MIN a MAX mohou také brát řetězce a data jako argumenty. V případě řetězcového argumentu se hodnoty porovnávají pomocí skutečného pořadí řazení. Pro všechny argumenty temp data typu "date" bude největší hodnota nejmenšího sloupce brzké datum, a největší - nejnovější.

Klíčové slovo DISTINCT můžete použít s funkcemi MIN a MAX. Před použitím agregačních funkcí MIN a MAX jsou všechny hodnoty NULL vyloučeny ze sloupců argumentů.

Agregační funkce SUM

Agregát Funkce SUM vypočítává Celková částka hodnoty sloupců. Argument této agregační funkce musí mít vždy číselný typ data. Použití agregační funkce SUM je znázorněno v příkladu níže:

POUŽÍVEJTE SampleDb; SELECT SUM (Rozpočet) "Souhrnný rozpočet" FROM Project;

Tento příklad vypočítá celkový součet rozpočtů všech projektů. Výsledek spuštění dotazu:

V tomto příkladu agregační funkce seskupuje všechny hodnoty rozpočtu projektu a určuje jejich celkovou částku. Z tohoto důvodu dotaz obsahuje implicitní seskupovací funkci (jako všechny podobné dotazy). Implicitní seskupovací funkce z výše uvedeného příkladu může být specifikována explicitně, jak ukazuje příklad níže:

POUŽÍVEJTE SampleDb; SELECT SUM (Rozpočet) "Celkový rozpočet" FROM Project GROUP BY();

Použití možnosti DISTINCT eliminuje všechny duplicitní hodnoty ve sloupci před použitím funkce SUM. Podobně jsou před použitím této agregační funkce odstraněny všechny hodnoty NULL.

Agregační funkce AVG

Agregát Funkce AVG vrátí aritmetický průměr všech hodnot ve sloupci. Argument této agregační funkce musí být vždy číselného datového typu. Před použitím funkce AVG jsou z jejího argumentu odstraněny všechny hodnoty NULL.

Použití agregační funkce AVG je znázorněno v příkladu níže:

POUŽÍVEJTE SampleDb; -- Vrátí 133833 SELECT AVG (Rozpočet) "Průměrný rozpočet na projekt" FROM Project;

Zde se vypočítá aritmetický průměr rozpočtu pro všechny rozpočty.

Agregační funkce COUNT a COUNT_BIG

Agregát Funkce COUNT má dvě různé podoby:

COUNT( název_sloupce) COUNT(*)

První forma funkce počítá počet hodnot ve sloupci col_name. Pokud je v dotazu použito klíčové slovo DISTINCT, budou před použitím funkce COUNT odstraněny všechny duplicitní hodnoty sloupců. Tato forma funkce COUNT nebere v úvahu hodnoty NULL při počítání počtu hodnot ve sloupci.

Použití první formy agregační funkce COUNT je znázorněno v příkladu níže:

POUŽÍVEJTE SampleDb; SELECT ProjectNumber, COUNT(DISTINCT Job) "Práce v projektu" FROM Works_on GROUP BY ProjectNumber;

Zde se počítá počet různých pozic pro každý projekt. Výsledkem tohoto dotazu je:

Jak můžete vidět z příkladu dotazu, funkce COUNT nezohlednila hodnoty NULL. (Součet všech hodnot ve sloupci práce se ukázal být 7, nikoli 11, jak by měl být.)

Druhá forma funkce COUNT, tzn. funkce COUNT(*) počítá počet řádků v tabulce. A pokud příkaz SELECT dotazu s funkcí COUNT(*) obsahuje klauzuli WHERE s podmínkou, vrátí funkce počet řádků, které splňují zadanou podmínku. Na rozdíl od první formy funkce COUNT, druhá forma neignoruje hodnoty NULL, protože tato funkce pracuje s řádky, nikoli se sloupci. Níže uvedený příklad ukazuje použití funkce COUNT(*):

POUŽÍVEJTE SampleDb; SELECT Job AS "Typ práce", COUNT(*) "Potřebuji pracovníky" FROM Works_on GROUP BY Job;

Zde se počítá počet pozic ve všech projektech. Výsledek spuštění dotazu:

Funkce COUNT_BIG podobně jako funkce COUNT. Jediný rozdíl mezi nimi je typ výsledku, který vracejí: funkce COUNT_BIG vždy vrací hodnoty BIGINT, zatímco funkce COUNT vrací hodnoty dat INTEGER.

Statistické agregační funkce

Následující funkce tvoří skupinu statistických agregačních funkcí:

Vypočítá statistický rozptyl všech hodnot reprezentovaných ve sloupci nebo výrazu.

Vypočítá statistický rozptyl populace všech hodnot reprezentovaných ve sloupci nebo výrazu.

Vypočítá směrodatnou odchylku (která se vypočítá jako Odmocnina z odpovídajícího rozptylu) všech hodnot sloupce nebo výrazu.

Vypočítá směrodatnou odchylku souhrnu všech hodnot ve sloupci nebo výrazu.

Uživatelsky definované agregační funkce

Database Engine také podporuje implementaci uživatelsky definovaných funkcí. Tato schopnost umožňuje uživatelům rozšířit systémové agregační funkce o funkce, které mohou implementovat a nainstalovat sami. Tyto funkce představují speciální třídu uživatelsky definovaných funkcí a budou podrobněji popsány později.

MÁME nabídku

Ve větě MÍT definuje podmínku, která platí pro skupinu řádků. Tato klauzule má tedy pro skupiny řádků stejný význam jako klauzule WHERE pro obsah odpovídající tabulky. Syntaxe klauzule HAVING je následující:

MÍT podmínku

Parametr podmínky zde představuje podmínku a obsahuje agregační funkce nebo konstanty.

Použití klauzule HAVING s agregační funkcí COUNT(*) je znázorněno v příkladu níže:

POUŽÍVEJTE SampleDb; -- Návrat "p3" SELECT ProjectNumber FROM Works_on GROUP BY ProjectNumber HAVING COUNT(*)

V tomto příkladu pomocí klauzule GROUP BY systém seskupuje všechny řádky na základě hodnot ve sloupci ProjectNumber. Poté se spočítá počet řádků v každé skupině a vyberou se skupiny obsahující méně než čtyři řádky (tři nebo méně).

Klauzuli HAVING lze také použít bez agregačních funkcí, jak ukazuje příklad níže:

POUŽÍVEJTE SampleDb; -- Vrátí "Consultant" SELECT Job FROM Works_on GROUP BY Job HAVING Job LIKE "K%";

Tento příklad seskupuje řádky v tabulce Works_on podle pozice a eliminuje ty pozice, které nezačínají písmenem "K".

Klauzuli HAVING lze použít i bez klauzule GROUP BY, i když to není běžná praxe. V tomto případě jsou všechny řádky tabulky vráceny ve stejné skupině.

OBJEDNEJTE PODLE nabídky

Nabídka SEŘADIT PODLE definuje pořadí řazení řádků v sadě výsledků vrácených dotazem. Tato věta má následující syntaxi:

Pořadí řazení je specifikováno v parametru col_name. Parametr col_number je alternativní specifikátor pořadí řazení, který identifikuje sloupce v pořadí, v jakém se objevují ve výběrovém seznamu příkazu SELECT (1 je první sloupec, 2 je druhý sloupec atd.). Parametr ASC definuje řazení ve vzestupném pořadí a Parametr DESC- sestupně. Výchozí hodnota je ASC.

Názvy sloupců v klauzuli ORDER BY nemusí být v seznamu vybrat sloupec. To se ale netýká dotazů SELECT DISTINCT, protože v takových dotazech musí být názvy sloupců uvedené v klauzuli ORDER BY také uvedeny v seznamu select sloupců. Kromě toho tato klauzule nemůže obsahovat názvy sloupců z tabulek, které nejsou uvedeny v klauzuli FROM.

Jak můžete vidět ze syntaxe klauzule ORDER BY, sadu výsledků lze třídit do více sloupců. Toto řazení je znázorněno na příkladu níže:

Tento příklad vybere čísla oddělení a příjmení a křestní jména zaměstnanců pro zaměstnance, jejichž osobní číslo je menší než 20 000, a seřadí je podle příjmení a jména. Výsledkem tohoto dotazu je:

Sloupce v klauzuli ORDER BY mohou být specifikovány nikoli svými názvy, ale pořadím ve výběrovém seznamu. Větu ve výše uvedeném příkladu lze tedy přepsat takto:

Takový alternativní způsob určení sloupců jejich pozicí namísto názvů se používá, pokud kritérium řazení obsahuje agregační funkci. (Dalším způsobem je použití názvů sloupců, které se pak objeví v klauzuli ORDER BY.) V klauzuli ORDER BY se však doporučuje, aby byly sloupce specifikovány jejich názvy, nikoli čísly, aby se usnadnila aktualizace dotazu pokud je třeba přidat nebo odebrat sloupce z výběrového seznamu. Určení sloupců v klauzuli ORDER BY jejich čísly je znázorněno v příkladu níže:

POUŽÍVEJTE SampleDb; SELECT ProjectNumber, COUNT(*) "Počet zaměstnanců" FROM Works_on GROUP BY ProjectNumber ORDER BY 2 DESC;

Zde se pro každý projekt vybere číslo projektu a počet zaměstnanců, kteří se na něm podílejí, přičemž výsledek seřadí sestupně podle počtu zaměstnanců.

Transact-SQL vkládá hodnoty NULL na začátek seznamu při vzestupném řazení a na konec seznamu při sestupném řazení.

Použití klauzule ORDER BY k stránkování výsledků

Zobrazení výsledků dotazu na aktuální stránce lze implementovat buď v uživatelská aplikace nebo dát pokyn databázovému serveru, aby tak učinil. V prvním případě jsou všechny databázové řádky odeslány do aplikace, jejímž úkolem je vybrat požadované řádky a zobrazit je. Ve druhém případě jsou na straně serveru vybrány a zobrazeny pouze řádky požadované pro aktuální stránku. Jak můžete očekávat, stránkování na straně serveru obvykle poskytuje lepší výkon, protože klientovi jsou odeslány pouze řádky potřebné pro zobrazení.

Pro podporu vytváření stránek na straně serveru SQL Server 2012 zavádí dvě nové klauzule příkazu SELECT: OFFSET a FETCH. Použití těchto dvou vět je demonstrováno na příkladu níže. Zde se z databáze AdventureWorks2012 (kterou můžete najít ve zdrojích) získávají obchodní ID, pracovní pozice a narozeniny všech zaměstnankyň a výsledek seřadí vzestupně podle pracovní pozice. Výsledná sada řádků se rozdělí na 10řádkové stránky a zobrazí se třetí stránka:

Ve větě OFFSET určuje počet řádků výsledků, které se mají v zobrazeném výsledku přeskočit. Toto číslo se vypočítá po seřazení řádků podle klauzule ORDER BY. Ve větě NAČÍST DALŠÍ určuje počet odpovídajících WHERE a seřazených řádků, které se mají vrátit. Parametrem této klauzule může být konstanta, výraz nebo výsledek jiného dotazu. Klauzule FETCH NEXT je podobná klauzuli NEJDŘÍVE PŘINÁŠEJTE.

Hlavním cílem při vytváření stránek na straně serveru je umět implementovat běžné formuláře stránek pomocí proměnných. Tento úkol můžete provést prostřednictvím balíčku SQL Server.

Příkaz SELECT a vlastnost IDENTITY

vlastnost IDENTITY umožňuje definovat hodnoty pro konkrétní sloupec tabulky jako automaticky se zvyšující čítač. Tuto vlastnost mohou mít číselné sloupce datového typu, jako jsou TINYINT, SMALLINT, INT a BIGINT. Pro takový sloupec tabulky Database Engine automaticky generuje sekvenční hodnoty počínaje zadanou počáteční hodnotou. Vlastnost IDENTITY lze tedy použít ke generování jednociferných číselných hodnot pro vybraný sloupec.

Tabulka může obsahovat pouze jeden sloupec s vlastností IDENTITY. Vlastník tabulky má možnost zadat počáteční hodnotu a přírůstek, jak ukazuje příklad níže:

POUŽÍVEJTE SampleDb; CREATE TABLE Product (Id INT IDENTITY(10000, 1) NOT NULL, Name NVARCHAR(30) NOT NULL, Price MONEY) INSERT INTO Product(Name, Price) VALUES ("Item1", 10), ("Item2", 15) , ("Položka 3", 8), ("Položka 4", 15), ("Položka 5", 40); -- Vrátí 10004 SELECT IDENTITYCOL FROM Product WHERE Name = "Product5"; -- Podobně jako u předchozího příkazu SELECT $identity FROM Product WHERE Name = "Product5";

Tento příklad nejprve vytvoří tabulku produktu, která obsahuje sloupec ID s vlastností IDENTITY. Hodnoty ve sloupci Id jsou generovány automaticky systémem, počínaje 10 000 a zvyšují se o jednu pro každou následující hodnotu: 10 000, 10 001, 10 002 atd.

S vlastností IDENTITY je spojeno několik systémových funkcí a proměnných. Například používá ukázkový kód $systémová proměnná identity. Jak můžete vidět z výstupu tohoto kódu, tato proměnná automaticky odkazuje na vlastnost IDENTITY. Místo toho můžete také použít systémovou funkci IDENTITYCOL.

Počáteční hodnotu a přírůstek sloupce s vlastností IDENTITY lze zjistit pomocí funkcí IDENT_SEED A IDENT_INCR respektive. Tyto funkce se používají následovně:

POUŽÍVEJTE SampleDb; SELECT IDENT_SEED("Produkt"), IDENT_INCR("Produkt")

Jak již bylo zmíněno, hodnoty IDENTITY nastavuje systém automaticky. Uživatel však může explicitně zadat své vlastní hodnoty pro určité řádky nastavením parametru IDENTITY_INSERT ON před vložením explicitní hodnoty:

SET IDENTITY INSERT název tabulky ON

Protože možnost IDENTITY_INSERT lze použít k nastavení libovolné hodnoty pro sloupec vlastnosti IDENTITY, včetně duplicitní hodnoty, vlastnost IDENTITY obvykle nevynucuje jedinečnost hodnot sloupců. Proto by se k vynucení jedinečnosti hodnot sloupců měla používat omezení UNIQUE nebo PRIMARY KEY.

Při vkládání hodnot do tabulky poté, co je IDENTITY_INSERT nastaveno na zapnuto, systém vytvoří další hodnotu sloupce IDENTITY zvýšením největší aktuální hodnoty tohoto sloupce.

příkaz CREATE SEQUENCE

Použití vlastnosti IDENTITY má několik významných nevýhod, z nichž nejvýznamnější jsou následující:

aplikace vlastností je omezena na uvedenou tabulku;

novou hodnotu sloupce nelze získat jiným způsobem než aplikací;

vlastnost IDENTITY lze zadat pouze při vytváření sloupce.

Z těchto důvodů SQL Server 2012 zavádí sekvence, které mají stejnou sémantiku jako vlastnost IDENTITY, ale bez výše uvedených nevýhod. V tomto kontextu je sekvence databázová funkce, která umožňuje zadat hodnoty čítačů pro různé databázové objekty, jako jsou sloupce a proměnné.

Sekvence jsou vytvořeny pomocí instrukce VYTVOŘTE SEKVENCI. Příkaz CREATE SEQUENCE je definován ve standardu SQL a je podporován jinými systémy relačních databází, jako jsou IBM DB2 a Oracle.

Níže uvedený příklad ukazuje, jak vytvořit sekvenci v SQL Server:

POUŽÍVEJTE SampleDb; CREATE SEQUENCE dbo.Sequence1 AS INT ZAČÁTE O 1 PŘÍRŮST O 5 MINVALUE 1 MAXVALUE 256 CYCLE;

Ve výše uvedeném příkladu jsou hodnoty Sequence1 generovány automaticky systémem, počínaje hodnotou 1 s přírůstky 5 pro každou následující hodnotu. Tedy v nabídka START je specifikována počáteční hodnota a NAVÍCOVÁ nabídka- krok. (Krok může být kladný nebo záporný.)

V dalších dvou nepovinných větách MIN A MAX minimální a maximální hodnota sekvenční objekt. (Všimněte si, že hodnota MINVALUE musí být menší nebo rovna počáteční hodnotě a hodnota MAXVALUE nemůže být větší než horní limit datového typu určeného pro sekvenci.) V klauzuli CYKLUS označuje, že sekvence se opakuje od začátku, když je překročena maximální (nebo minimum u sekvence se záporným krokem) hodnota. Ve výchozím nastavení je tato klauzule NO CYCLE, což znamená, že překročení maximální nebo minimální hodnoty sekvence způsobí výjimku.

Hlavním rysem sekvencí je jejich nezávislost na tabulkách, tzn. mohou být použity s jakýmkoli databázovým objektem, jako jsou sloupce tabulky nebo proměnné. (Tato vlastnost má pozitivní vliv na úložiště a tím i na výkon. Konkrétní sekvenci není třeba ukládat, ukládá se pouze její poslední hodnota.)

Nové sekvenční hodnoty jsou vytvořeny pomocí DALŠÍ HODNOTA PRO výrazy, jehož použití je uvedeno v příkladu níže:

POUŽÍVEJTE SampleDb; -- Vrátí 1 SELECT NEXT VALUE FOR dbo.sequence1; -- Vrátí 6 (další krok) SELECT NEXT VALUE FOR dbo.sequence1;

Výraz NEXT VALUE FOR můžete použít k přiřazení výsledku sekvence k proměnné nebo buňce sloupce. Níže uvedený příklad ukazuje použití tohoto výrazu k přiřazení výsledků ke sloupci:

POUŽÍVEJTE SampleDb; CREATE TABLE Product (Id INT NOT NULL, Name NVARCHAR(30) NOT NULL, Price MONEY) INSERT INTO Product VALUES (NEXT VALUE FOR dbo.sequence1, "Product1", 10); INSERT INTO Product VALUES (NEXT VALUE FOR dbo.sequence1, "Product2", 15); --...

Výše uvedený příklad nejprve vytvoří tabulku produktu se čtyřmi sloupci. Dále dva příkazy INSERT vloží dva řádky do této tabulky. První dvě buňky v prvním sloupci budou mít hodnoty 11 a 16.

Níže uvedený příklad ukazuje použití zobrazení katalogu sys.sekvence pro zobrazení aktuální hodnoty sekvence bez jejího použití:

Typicky se výraz NEXT VALUE FOR používá v příkazu INSERT, aby systém přinutil vložit vygenerované hodnoty. Tento výraz lze také použít jako součást víceřádkového dotazu pomocí klauzule OVER.

Chcete-li změnit vlastnost existující sekvence, použijte příkaz ALTER SEQUENCE. Jeden z nejvíce důležité aplikace Tento příkaz je spojen s volbou RESTART WITH, která resetuje zadanou sekvenci. Následující příklad ukazuje použití příkazu ALTER SEQUENCE k resetování téměř všech vlastností Sequence1:

POUŽÍVEJTE SampleDb; ALTER SEQUENCE dbo.sequence1 RESTARTUJTE SE 100 PŘÍRŮSTKY O 50 MINVALUE 50 MAXVALUE 200 ŽÁDNÝ CYKLUS;

Vymažte sekvenci pomocí instrukce DROP SEQUENCE.

Nastavit operátory

Kromě operátorů diskutovaných výše podporuje Transact-SQL další tři operátory sady: UNION, INTERSECT a EXCEPT.

operátor UNION

operátor UNION kombinuje výsledky dvou nebo více dotazů do jediné sady výsledků, která obsahuje všechny řádky, které patří všem dotazům ve spojení. Výsledkem spojení dvou tabulek je tedy nová tabulka obsahující všechny řádky obsažené v jedné z původních tabulek nebo v obou těchto tabulkách.

Obecná forma operátoru UNION vypadá takto:

select_1 UNION select_2(select_3])...

Volby select_1, select_2, ... jsou příkazy SELECT, které vytvářejí spojení. Pokud je použita volba VŠECHNY, zobrazí se všechny řádky, včetně duplikátů. V operátoru UNION má parametr ALL stejný význam jako ve výběrovém seznamu SELECT, s jedním rozdílem: pro výběrový seznam SELECT je tento parametr použit ve výchozím nastavení, ale pro operátor UNION musí být specifikován explicitně.

Databáze SampleDb ve své původní podobě není vhodná pro demonstraci použití operátoru UNION. Proto tato část vytvoří novou tabulku EmployeeEnh, která je identická s existující tabulkou Zaměstnanci, ale má další sloupec Město. Tento sloupec uvádí, kde zaměstnanci bydlí.

Vytvoření tabulky EmployeeEnh nám poskytuje příležitost prokázat použití klauzule DO v příkazu SELECT. Příkaz SELECT INTO provádí dvě operace. Nejprve se vytvoří nová tabulka se sloupci uvedenými v seznamu SELECT. Poté se řádky původní tabulky vloží do nové tabulky. Název nové tabulky je uveden v klauzuli INTO a název zdrojové tabulky je uveden v klauzuli FROM.

Níže uvedený příklad ukazuje vytvoření tabulky EmployeeEnh z tabulky Employee:

POUŽÍVEJTE SampleDb; SELECT * INTO EmployeeEnh FROM Employee; ALTER TABLE EmployeeEnh ADD City NCHAR(40) NULL;

V tomto příkladu příkaz SELECT INTO vytvoří tabulku EmployeeEnh, vloží do ní všechny řádky ze zdrojové tabulky Employee a poté příkaz ALTER TABLE přidá sloupec City do nové tabulky. Přidaný sloupec Město však žádné hodnoty neobsahuje. Hodnoty v tomto sloupci lze vložit pomocí Management Studio nebo pomocí následujícího kódu:

POUŽÍVEJTE SampleDb; UPDATE EmployeeEnh SET City="Kazan" WHERE Id=2581; UPDATE EmployeeEnh SET City = "Moskva" WHERE Id = 9031; UPDATE EmployeeEnh SET Město = "Jekatěrinburg" WHERE Id = 10102; UPDATE EmployeeEnh SET City = "Saint Petersburg" WHERE Id = 18316; UPDATE EmployeeEnh SET City = "Krasnodar" WHERE Id = 25348; UPDATE EmployeeEnh SET City="Kazan" WHERE Id=28559; UPDATE EmployeeEnh SET City="Perm" WHERE Id=29346;

Nyní jsme připraveni předvést použití příkazu UNION. Níže uvedený příklad ukazuje dotaz na vytvoření spojení mezi tabulkami EmployeeEnh a Department pomocí tohoto příkazu:

POUŽÍVEJTE SampleDb; SELECT City AS "City" FROM EmployeeEnh UNION SELECT Location FROM Department;

Výsledkem tohoto dotazu je:

Pomocí příkazu UNION lze spojit pouze kompatibilní tabulky. Kompatibilními tabulkami rozumíme, že oba seznamy sloupců ve výběru musí obsahovat stejný počet sloupců a že odpovídající sloupce musí mít kompatibilní datové typy. (Pokud jde o kompatibilitu, datové typy INT a SMALLINT nejsou kompatibilní.)

Výsledek spojení lze objednat pouze pomocí klauzule ORDER BY v poslední instrukce SELECT, jak je znázorněno v příkladu níže. Klauzule GROUP BY a HAVING lze použít s jednotlivými příkazy SELECT, ale ne v rámci samotného spojení.

Dotaz v tomto příkladu načte zaměstnance, kteří buď pracují v oddělení d1, nebo začali pracovat na projektu před 1. lednem 2008.

Operátor UNION podporuje volbu VŠECHNY. Při použití této možnosti se duplikáty ze sady výsledků neodstraní. Operátor OR můžete použít místo operátoru UNION, pokud všechny příkazy SELECT spojené jedním nebo více operátory UNION odkazují na stejnou tabulku. V tomto případě je sada příkazů SELECT nahrazena jedním příkaz SELECT se sadou operátorů OR.

Příkazy INTERSECT a EXCEPT

Dva další operátoři pro práci se sadami, INTERSECT A AŽ NA, definujte průsečík a rozdíl, resp. Pod průsečíkem je v tomto kontextu sada řádků, které patří do obou tabulek. A rozdíl dvou tabulek je definován jako všechny hodnoty, které patří do první tabulky a nejsou přítomny ve druhé. Níže uvedený příklad ukazuje použití příkazu INTERSECT:

Transact-SQL nepodporuje použití volby ALL s příkazem INTERSECT ani s příkazem EXCEPT. Použití příkazu EXCEPT je znázorněno v příkladu níže:

Mějte na paměti, že tyto tři množinové operátory mají různou prioritu provádění: operátor INTERSECT má nejvyšší prioritu, následovaný operátorem EXCEPT a operátor UNION má nejnižší prioritu. Nepozornost na prioritu provedení při použití více různých operátorů práce se sadami může vést k neočekávaným výsledkům.

CASE výrazy

V oblasti programování databázových aplikací je někdy nutné upravit prezentaci dat. Lidé mohou být například rozděleni kódováním podle jejich sociální příslušnosti pomocí hodnot 1, 2 a 3, které označují muže, ženy a děti. Tato programovací technika může zkrátit čas potřebný k implementaci programu. výraz CASE Jazyk Transact-SQL usnadňuje implementaci tohoto typu kódování.

Na rozdíl od většiny programovacích jazyků CASE není příkaz, ale výraz. Proto lze výraz CASE použít téměř kdekoli, kde jazyk Transact-SQL umožňuje použití výrazů. Výraz CASE má dvě podoby:

jednoduchý výraz CASE;

hledaný výraz CASE.

Syntaxe jednoduchého výrazu CASE je následující:

Příkaz s jednoduchým výrazem CASE nejprve prohledá seznam všech výrazů v klauzule WHEN první výraz, který odpovídá výrazu_1 a poté provede odpovídající PAK klauzule. Pokud v seznamu WHEN není žádný odpovídající výraz, pak klauzule ELSE.

Syntaxe pro vyhledávací výraz CASE je:

V tento případ vyhledá se první odpovídající podmínka a poté se provede odpovídající klauzule THEN. Pokud žádná z podmínek neodpovídá požadavkům, je provedena klauzule ELSE. Použití vyhledávacího výrazu CASE je znázorněno v příkladu níže:

POUŽÍVEJTE SampleDb; SELECT ProjectName, CASE WHEN Budget > 0 AND Budget 100000 AND Budget 150000 AND Budget

Výsledkem tohoto dotazu je:

Tento příklad zváží rozpočty všech projektů a zobrazí jejich vypočítané váhy spolu s příslušnými názvy projektů.

Níže uvedený příklad ukazuje další způsob použití výrazu CASE, kde klauzule WHEN obsahuje poddotazy, které jsou součástí výrazu:

POUŽÍVEJTE SampleDb; SELECT ProjectName, CASE WHEN p1.Budget (SELECT AVG(p2.Budget) FROM Project p2) THEN "Nadprůměrné" END "Kategorie rozpočtu" FROM Project p1;

Výsledek tohoto dotazu je následující:

Naučme se sčítat. Ne, toto nejsou výsledky učení SQL, ale výsledky hodnot sloupců databázových tabulek. Agregát SQL funkce pracovat s hodnotami sloupce a vytvořit jedinou výslednou hodnotu. Nejčastěji používané agregační funkce SQL jsou SUM, MIN, MAX, AVG a COUNT. Existují dva případy, kdy by se měly použít agregační funkce. Za prvé, agregační funkce se 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ě. Nejprve zvažte 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(NÁZEV SLOUPCE)...

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

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

Chcete-li získat součet všech platů, použijte následující dotaz:

VYBERTE SOUČTU (Plat) OD zaměstnanců

Tento dotaz vrátí hodnotu 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 také funguje na sloupcích, jejichž hodnoty jsou čísla a vrací minimum ze všech hodnot ve sloupci. Tato funkce má podobnou syntaxi jako funkce SUM.

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

Je nutné zjistit minimální mzdu pro zaměstnance oddělení číslo 42. K tomu napište následující dotaz:

Dotaz vrátí hodnotu 10505,90.

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

Příklad 4 Do tabulky Zaměstnanci je přidána tabulka Org, která obsahuje údaje o divizích společnosti. Výstup minimální počet let, které jeden zaměstnanec odpracoval 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ž chcete určit maximální hodnotu ze všech hodnot sloupce.

Příklad 5

Je nutné zjistit maximální plat zaměstnanců oddělení číslo 42. K tomu napište následující dotaz:

Dotaz vrátí hodnotu 18352,80

Je čas cvičení pro sebeurčení.

Příklad 6 Opět pracujeme se dvěma tabulkami – Staff a Org. Zobrazte název oddělení a maximální výši přijatých provizí jedním zaměstnancem v oddělení, které patří do skupiny oddělení (Divize) Východ. Použití JOIN (připojení ke stolům) .

Funkce SQL AVG

To, co bylo řečeno o syntaxi pro předchozí popsané funkce, platí i o 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.

Nechť je požadováno zjištění průměrné délky služby zaměstnanců oddělení číslo 42. K tomu napíšeme následující dotaz:

Výsledek bude 6,33

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

Funkce SQL COUNT

Funkce SQL COUNT vrací počet záznamů v databázové tabulce. Pokud v dotazu zadáte SELECT COUNT(COLUMNAME) ..., bude výsledkem počet záznamů bez zohlednění těch 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.

Chcete znát 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:

VYBERTE POČET (Komunikace) OD personálu

Výsledkem bude hodnota 11.

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

Pokud chcete znát celkový počet záznamů v tabulce, použijte dotaz s hvězdičkou jako argument funkce COUNT:

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

Výsledkem bude hodnota 17.

další cvičení pro sebeurčení musíte použít poddotaz.

Příklad 11. Pracujeme s jedním stolem – Staff. Zobrazte počet zaměstnanců v oddělení Plains.

Agregační funkce s SQL GROUP BY

Nyní se podívejme na použití agregačních funkcí spolu s klauzulí SQL GROUP BY. Klauzule SQL GROUP BY se používá k seskupení výsledných hodnot podle sloupců v databázové tabulce.

Příklad 12. Existuje databázový portál reklam. Obsahuje tabulku reklam, která obsahuje údaje o reklamách odeslaných za daný týden. Sloupec Kategorie obsahuje údaje o velkých kategoriích reklam (například Nemovitosti) a sloupec Části obsahuje údaje o menších částech zahrnutých do kategorií (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 obsahuje množství peněz vydělaných za odeslání inzerátů.

Pomocí klauzule SQL GROUP BY zjistěte částku peněz vygenerovanou odesláním reklam v každé kategorii. Píšeme následující žádost.

Lekce se bude zabývat tématem sql přejmenováním sloupce (polí) pomocí servisního slova AS; zvažuje se také téma agregačních funkcí v SQL. Bude demontováno konkrétní příkladyžádosti

Názvy sloupců v dotazech lze přejmenovat. Díky tomu jsou výsledky čitelnější.

V jazyk SQL přejmenování polí souvisí s používáním AS klíčové slovo, který se používá k přejmenování názvů polí v sadách výsledků

Syntax:

VYBRAT<имя поля>TAK JAKO<псевдоним>Z …

Zvažte příklad přejmenování v SQL:

Příklad databáze "Institut": Zobrazte jména učitelů a jejich platy, pro učitele, jejichž plat je nižší než 15 000, přejmenujte pole zarplata na "nízká mzda"

Přejmenování sloupců v SQL je často nutné při výpočtu hodnot spojených s více poli tabulky. Zvažte příklad:

Příklad databáze "Institut": Z tabulky učitelů zobrazte pole se jménem a vypočítejte součet platu a odměny, pole pojmenujte "plat_bonus"

SELECT jméno, (zarplata+premia) AS zarplata_premia FROM učitelů;

Výsledek:

Agregační funkce v SQL

Agregační funkce v sql se používají k získání celkových hodnot a vyhodnocení výrazů:

Všechny agregační funkce vracejí jedinou hodnotu.

Funkce COUNT , MIN a MAX platí pro jakýkoli typ dat.

Funkce SUM a AVG se používají pouze pro číselná pole.
Mezi funkcemi COUNT(*) a COUNT() je rozdíl: tato funkce při výpočtu nebere v úvahu hodnoty NULL.

Důležité: při práci s agregačními funkcemi v SQL se používá funkční slovo TAK JAKO

Příklad databáze "Institut": Získejte hodnotu nejvyššího platu mezi učiteli, zobrazte součet jako "max_zp"

SELECT MAX(zarplata) AS max_sal FROM učitelů;

Výsledek:

Zvažte více složitý příklad pomocí agregačních funkcí v sql.

Klauzule GROUP BY v SQL

Příkaz group by v sql se obvykle používá ve spojení s agregačními funkcemi.

Agregační funkce se provádějí na všech výsledných řetězcích dotazů. Pokud dotaz obsahuje klauzuli GROUP BY, každá sada řádků zadaná v klauzuli GROUP BY tvoří skupinu a jsou provedeny agregační funkce. pro každou skupinu zvlášť.

Zvažte příklad s tabulkou lekcí:

Příklad:

Důležité: V důsledku použití GROUP BY jsou tedy všechny výstupní řádky dotazu rozděleny do skupin charakterizovaných stejnými kombinacemi hodnot v těchto sloupcích (tj. agregační funkce se provádějí pro každou skupinu samostatně).

Zároveň je třeba vzít v úvahu, že při seskupování podle pole obsahujícího NULL -hodnoty budou všechny takové záznamy spadat do jedné skupiny.

Pro různé typy tiskáren ke stanovení jejich průměrných nákladů a množství (tj. zvlášť pro laserové, inkoustové a jehličkové). Používejte agregační funkce. Výsledek by měl vypadat takto:

S příkazem SQL

Klauzule HAVING v SQL je potřebná ke kontrole hodnot, které se získají pomocí agregační funkce po seskupení(po použití GROUP BY). Taková kontrola nemůže být obsaženo v klauzuli WHERE.

Příklad: Počítačový obchod DB. Vypočítejte průměrnou cenu počítačů se stejnou rychlostí procesoru. Spusťte výpočet pouze pro ty skupiny, jejichž průměrná cena je nižší než 30 000.

Mohou provádět zobecněné skupinové zpracování hodnot polí. To se provádí pomocí agregačních funkcí. Agregační funkce vytvářejí jednu hodnotu pro celou skupinu tabulek. SQL poskytuje následující agregační funkce:

• POČET– počítá počet řádků tabulky s nenulovými hodnotami pole zadaného jako argument.
• SOUČET– vypočítá aritmetický součet všech vybraných hodnot daného pole.
• AVG– zprůměruje všechny vybrané hodnoty daného pole.
• MAX- Zobrazí největší hodnotu ze všech vybraných hodnot daného pole.
• MIN– Zobrazí nejmenší hodnotu ze všech vybraných hodnot v daném poli.

  Použití agregačních funkcí

  Agregační funkce se používají jako názvy polí v klauzuli SELECT dotazu, s jednou výjimkou: berou názvy polí jako argument. S lze použít pouze číselná pole SOUČET A AVG. S POČET, MAX, A MIN lze použít jak číselná, tak znaková pole. Při použití s ​​poli znaků MAX A MIN přeloží je do jejich ekvivalentu ASCII. Znamená to, že MIN vybere první a MAX poslední hodnota v abecedním pořadí.

  Abychom zjistili celkovou částku prodeje v tabulce prodejů, musíme napsat následující dotaz:

  SELECT SUM(SSum) FROM Prodává

  V důsledku toho získáme:

  Tento dotaz spočítal počet neprázdných hodnot v poli SNum tabulky Sells. Pokud dotaz přepíšeme takto:

  SELECT COUNT(SDate) FROM Prodává

  Pak jako výsledek dostaneme:

  COUNT OF SDdate

  4

  Různé výsledky dotazu při výpočtu zdánlivě stejné věci jsou získány, protože jedna z hodnot pole SDate má prázdnou hodnotu ( NULA). Při používání takových požadavků buďte opatrní.

Použití agregačních funkcí

SQL definuje mnoho vestavěných funkcí různých kategorií, mezi nimiž zvláštní místo zaujímají agregační funkce, které pracují s hodnotami sloupců více řádků a vracejí jednu hodnotu. Argumenty agregačních funkcí mohou být jak sloupce tabulek, tak výsledky výrazů nad nimi. Agregátní funkce mohou být samy zahrnuty do jiných aritmetických výrazů. Následující tabulka uvádí nejčastěji používané standardní unární agregační funkce.

Obecný formát unární agregační funkce je následující:

název_funkce([VŠECHNY | DISTINCT] výraz)

kde DISTINCT určuje, že funkce by měla brát v úvahu pouze odlišné hodnoty argumentu, a ALL určuje všechny hodnoty, včetně duplikátů (toto je výchozí nastavení). Například funkce AVG s klíčovým slovem DISTINCT pro řádky sloupců s hodnotami 1, 1, 1 a 3 vrátí 2, ale s klíčovým slovem ALL vrátí 1,5.

Agregační funkce se používají v klauzulích SELECT a HAVING. Zde se podíváme na jejich použití v klauzuli SELECT. V tomto případě se výraz v argumentu funkce použije na všechny řádky vstupní tabulky klauzule SELECT. V klauzuli SELECT také nemůžete používat agregační funkce a sloupce tabulky (nebo výrazy s nimi), pokud nemáte klauzuli GROUP BY, na kterou se podíváme v další části.

Funkce COUNT má dva formáty. V prvním případě je vrácen počet řádků vstupní tabulky, ve druhém případě počet hodnot argumentů ve vstupní tabulce:

• POČET(*)
• POČET (výraz)

Nejjednodušším způsobem použití této funkce je spočítat počet řádků v tabulce (buď všech, nebo těch, které splňují danou podmínku). K tomu se používá první varianta syntaxe.

Dotaz: Počet typů produktů, o kterých jsou informace dostupné v databázi.

SELECT COUNT(*) AS "Počet produktů"

OD Produktu

Ve druhé variantě syntaxe funkce COUNT lze jako argument použít název jednoho sloupce. V tomto případě se počítá buď všech hodnot v tomto sloupci vstupní tabulky, nebo pouze neopakujících se (při použití klíčového slova DISTINCT).

Dotaz: Počet odlišných jmen obsažených v tabulce Zákazník.

VYBRAT POČET (DISTINCT FNAME)

OD ZÁKAZNÍKA

Použití ostatních unárních agregačních funkcí je podobné jako COUNT, kromě toho, že pro funkce MIN a MAX nedává použití klíčových slov DISTINCT a ALL smysl. Pomocí funkcí COUNT, MAX a MIN lze kromě číselných polí použít také pole znaků. Pokud argument agregační funkce neobsahuje žádné hodnoty, funkce COUNT vrátí 0 a všechny ostatní vrátí hodnotu NULL.

VYBRAT MAX (Datum objednávky)

Z

KDE Datum objednávky"1.09.2010"

Úkol pro samostatná práce: Formulovat v jazyce SQL dotazy vzorkovat následující údaje:

• celkové náklady na všechny objednávky;
• Počet odlišných měst obsažených v tabulce Zákazník.