agregační funkce SQL. Seskupování v SQL: GROUP BY, HAVING klauzule a agregační funkce

Agregační funkce se používají jako názvy polí v příkazu SELECT, s jednou výjimkou: berou název pole jako argument. S funkcemi SUM a AVG lze použít pouze číselná pole. S funkcemi COUNT , MAX a MIN lze použít jak číselná, tak znaková pole. Při použití se znakovými poli je MAX a MIN přeloží do jejich ekvivalentu ASCII a zpracují je jako abecední pořadí. Některé DBMS umožňují vnořené agregáty, ale to je odklon od standardu ANSI se všemi jeho důsledky.

Můžete například vypočítat počet studentů, kteří složili zkoušky v jednotlivých oborech. Chcete-li to provést, musíte provést dotaz seskupený podle pole "Předmět" a jako výsledek zobrazit název disciplíny a počet řádků ve skupině pro tuto disciplínu. Použití znaku * jako argumentu COUNT funkcí znamená spočítat všechny řádky ve skupině.

SELECT R1.Discipline, COUNT(*) ZE R1 GROUP BY R1.Discipline

Výsledek:

Pokud chceme spočítat počet studentů, kteří složili zkoušku v jakékoli disciplíně, musíme před seskupením z původního poměru vyloučit hodnoty null. V tomto případě bude žádost vypadat takto:

Dostáváme výsledek:

V tomto případě linka se studentem

nespadne do množiny n-tic před seskupením, takže počet n-tic ve skupině pro disciplínu " Teorie informace“ bude o 1 méně.

Lze aplikovat agregační funkce také bez operace předběžného seskupení, v takovém případě je celý vztah považován za jednu skupinu a pro tuto skupinu lze vypočítat jednu hodnotu na skupinu.

Opětovným odkazem na databázi "Session" (tabulky R1, R2, R3) zjistíme počet úspěšně složených zkoušek:

To se samozřejmě liší od výběru pole, protože vždy je vrácena jedna hodnota, bez ohledu na to, kolik řádků je v tabulce. Argument agregační funkce mohou být samostatné sloupce tabulek. Ale abyste mohli vypočítat například počet různých hodnot určitého sloupce ve skupině, musíte spolu s názvem sloupce použít klíčové slovo DISTINCT. Spočítejme si počet různých známek získaných v každé disciplíně:

Výsledek:

Výsledek může obsahovat hodnotu seskupeného pole a několik agregační funkce a v podmínkách seskupování lze použít více polí. V tomto případě se skupiny tvoří podle sady zadaných seskupovacích polí. Agregační operace lze použít ke spojení více zdrojových tabulek. Položme si například otázku: určete pro každou skupinu a každou disciplínu počet těch, kteří úspěšně složili zkoušku, a průměrné skóre v dané disciplíně.

Výsledek:

Nemůžeme použít agregační funkce v klauzuli WHERE, protože predikáty jsou vyhodnocovány z hlediska jednoho řádku, a agregační funkce- pokud jde o skupiny řádků.

Klauzule GROUP BY umožňuje definovat podmnožinu hodnot v konkrétním poli z hlediska jiného pole a aplikovat na podmnožinu agregační funkci. To umožňuje kombinovat obory a agregační funkce v jediné klauzuli SELECT. Agregační funkce lze použít jak ve výstupním vyjádření výsledků řádku SELECT, tak ve vyjádření podmínky zpracování pro vytvořené skupiny HAVING . V tomto případě se každá agregační funkce vypočítá pro každou vybranou skupinu. Hodnoty vyplývající z výpočtu agregační funkce, lze použít k zobrazení odpovídajících výsledků nebo k podmínění výběru skupin.

Pojďme sestavit dotaz, který zobrazí skupiny, ve kterých bylo v rámci zkoušek přijato více než jedna dvojka v jedné disciplíně:

V budoucnu jako příklad nebudeme pracovat s databází „Session“, ale s databází „Bank“, skládající se z jedné tabulky F , která uchovává relaci F obsahující informace o účtech v pobočkách určité banky:

F = (N, celé jméno, pobočka, datum otevření, datum uzavření, zůstatek); Q = (pobočka, město);

protože na tomto základě lze názorněji ilustrovat práci s agregačními funkcemi a seskupováním.

Předpokládejme například, že chceme zjistit celkový zůstatek účtů na pobočkách. Pro každou z nich můžete vytvořit samostatný dotaz výběrem SUM(Zůstatek) z tabulky pro každou větev. GROUP BY je však všechny vloží do jednoho příkazu:

SELECT Branch, SUM(Balance) FROM F GROUP BY Branch;

Platí GROUP BY agregační funkce nezávisle pro každou skupinu definovanou hodnotou pole Pobočka. Skupina se skládá z řádků se stejnou hodnotou v poli Pobočka a

• Agregační funkce se používají jako názvy polí v příkazu SELECT, s jednou výjimkou: berou název pole jako argument. S funkcemi SOUČET A AVG lze použít pouze číselná pole. S funkcemi COUNT, 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 a zpracuje je v abecedním pořadí. Některé DBMS umožňují vnořené agregáty, ale to je odklon od standardu ANSI se všemi jeho důsledky.

Můžete například vypočítat počet studentů, kteří složili zkoušky v jednotlivých oborech. Chcete-li to provést, musíte provést dotaz seskupený podle pole "Předmět" a zobrazit jako výsledek název disciplíny a počet řádků ve skupině pro tuto disciplínu. Použití znaku * jako argumentu funkce COUNT znamená spočítat všechny řádky ve skupině.

VYBERTE R1. Disciplína, COUNT(*)

GROUP BY R1. Disciplína;

Výsledek:

SELECT R1.Discipline, COUNT (*)

KDE R1. Hodnocení NENÍ NULL

GROUP BY R1. Disciplína;

Výsledek:

nebude spadat do množiny n-tic před seskupením, takže počet n-tic ve skupině pro disciplínu „Teorie informací“ bude o 1 menší.

Podobný výsledek lze získat, pokud je dotaz napsán následujícím způsobem:

VYBERTE R1. Disciplína, COUNT(R1. Skóre)

GROUP BY R1. Disciplína;

Funkce POČET (NÁZEV ATRIBUTU) počítá počet definovaných hodnot ve skupině, na rozdíl od funkce POČET(*), který počítá počet řádků ve skupině. Ve skupině s disciplínou „Teorie informací“ budou skutečně 4 řádky, ale pouze 3 určité hodnoty atributu „Vyhodnocení“.

Pravidla pro zpracování hodnot NULL v agregačních funkcích

Pokud jsou některé hodnoty ve sloupci stejné NULA při výpočtu výsledku funkce jsou vyloučeny.

Pokud jsou všechny hodnoty ve sloupci stejné NULA, Že Max Min. Součet Prům = NULL, počet = 0 (nula).

Pokud je stůl prázdný, počet(*) = 0 .

Agregační funkce lze také použít bez operace předběžného seskupení, v takovém případě je celý vztah považován za jednu skupinu a pro tuto skupinu lze vypočítat jednu hodnotu na skupinu.

Pravidla pro interpretaci agregačních funkcí

Agregační funkce lze zahrnout do seznamu výstupů a poté je aplikovat na celou tabulku.

VYBERTE MAX (Skóre) z R1 při sezení udělí maximální známku;

VYBERTE SOUČET (Skóre) z R1 uvede součet všech hodnocení pro relaci;

VYBERTE AVG (skóre) z R1 poskytne průměrné skóre za celou relaci.

Opět s odkazem na databázi „Session“ (tabulky R1) zjistíme počet úspěšně složených zkoušek:

VYBRAT POČET (*) Pronajato _ zkoušky

WHERE skóre 2;

Výsledek:

Agregační funkce mohou brát jednotlivé sloupce tabulek jako argumenty. Aby bylo možné vypočítat například počet odlišných hodnot pro určitý sloupec ve skupině, musí být spolu s názvem sloupce použito klíčové slovo DISTINCT. Spočítejme si počet různých známek získaných v každé disciplíně:

SELECT R1.Discipline, COUNT (ODLIŠNÁ R1.Skóre)

KDE R1. Hodnocení NENÍ NULL

GROUP BY R1. Disciplína;

Výsledek:

Stejného výsledku se dosáhne, pokud je vyloučena explicitní podmínka v části WHERE, v takovém případě bude dotaz vypadat takto:

VYBERTE R1. Disciplína, POČET (DISTINCT R1. Skóre)

GROUP BY R1. Disciplína;

Funkce POČET (ODLIŠNÉ R1. Skóre) počítá jen jisté rozličný hodnoty.

Aby bylo možné v tomto případě získat kýžený výsledek, je nutné provést předběžnou konverzi datového typu sloupce "Skóre" přetypováním na reálný typ, pak výsledkem výpočtu průměru nebude celé číslo. V tomto případě bude žádost vypadat takto:

Vyberte R2.Group, R1.Subject,Count(*) jako Total, AVG(cast(Score as decimal(3,1))) jako Average_score

Od R1,R2

kde R1. Celé jméno = R2. Celé jméno a R1. hodnocení není nulové

a R1. 2. třída

Seskupit podle R2. Skupina, R1. Disciplína;

Zde je funkce OBSAZENÍ() převede sloupec Skóre na platný datový typ.

V klauzuli WHERE nemůžete použít agregační funkce, protože podmínky v této části se vyhodnocují z hlediska jednoho řádku, zatímco agregační funkce se vyhodnocují z hlediska skupin řádků.

Klauzule GROUP BY umožňuje definovat podmnožinu hodnot v konkrétním poli z hlediska jiného pole a aplikovat na podmnožinu agregační funkci. To umožňuje kombinovat pole a agregační funkce do jediné klauzule SELECT. Agregační funkce lze použít jak ve výstupním vyjádření výsledků řádku SELECT, tak ve vyjádření podmínky pro zpracování vytvořených skupin HAVING. V tomto případě se každá agregační funkce vypočítá pro každou vybranou skupinu. Hodnoty získané při výpočtu agregačních funkcí lze použít k zobrazení odpovídajících výsledků nebo pro podmínky výběru skupiny.

Pojďme sestavit dotaz, který zobrazí skupiny, ve kterých bylo ve zkouškách přijato více než jedna dvojka v jedné disciplíně:

VYBERTE R2. Skupina

OD R1,R2

KDE R1. Celé jméno = R2. Celé jméno A

R1. Skóre = 2

SKUPINA PODLE R2.Skupina, R1.Disciplína

HAVING count(*) 1;

Výsledek:

Máme databázi "Banka", sestávající z jedné tabulky F, ve které je uložen vztah F, obsahující informace o účtech v pobočkách určité banky:

Zjistěte celkový zůstatek na účtech v pobočkách. Pro každou z nich můžete vytvořit samostatný dotaz výběrem SUM (Zbytek) z tabulky pro každou větev, ale operace GROUP BY je všechny vloží do jednoho příkazu:

VYBRAT Větev , SUM( Zbytek )

GROUP BY Branch;

SKUPINA VYTVOŘENÁ aplikuje agregační funkce nezávisle pro každou skupinu identifikovanou hodnotou pole Pobočka. Skupina se skládá z řádků se stejnou hodnotou v poli Větev a funkce SOUČET se uplatňuje samostatně pro každou takovou skupinu, tj. celkový zůstatek účtu se počítá samostatně pro každou pobočku. Hodnota pole, na které se vztahuje SKUPINA VYTVOŘENÁ, má podle definice pouze jednu hodnotu na výstupní skupinu, stejně jako výsledek agregační funkce.

Předpokládejme, že vyberete pouze ty pobočky, jejichž celkové zůstatky na účtech jsou vyšší než 5 000 USD, a také celkové zůstatky pro vybrané pobočky. Chcete-li zobrazit pobočky s celkovými zůstatky nad 5 000 USD, použijte klauzuli HAVING. Klauzule HAVING určuje kritéria použitá k odstranění určitých skupin z výstupu, stejně jako klauzule WHERE pro jednotlivé řádky.

Správný příkaz by byl:

SELECT větev, SUM(zůstatek)

SKUPINA VYTVOŘENÁ Větev

MÁTE SOUČET ( Zbytek ) 5 000;

Argumenty ve větě MÍT podléhají stejným pravidlům jako v návrhu VYBRAT kde se používá SKUPINA VYTVOŘENÁ. Musí mít jednu hodnotu na výstupní skupinu.

Následující příkaz bude zakázán:

SELECT Branch,SUM(Zůstatek)

SKUPINA PODLE pobočky

HAVING Datum otevření = 27/12/2004 ;

Pole Datum otevření nelze použít ve větě MÍT, protože může mít více než jednu hodnotu na výstupní skupinu. Aby se předešlo této situaci, návrh MÍT by měl odkazovat pouze na vybrané agregáty a pole SKUPINA VYTVOŘENÁ. Dostupný Správná cesta podejte výše uvedený požadavek:

SELECT Branch,SUM(Zůstatek)

WHERE OpenDate = '27/12/2004'

GROUP BY Branch;

Význam daná žádost dále: najděte součet zůstatků pro každou pobočku účtů otevřených 27. prosince 2004.

Jak bylo uvedeno dříve, HAVING může převzít pouze argumenty, které mají jednu hodnotu na výstupní skupinu. V praxi jsou nejběžnější odkazy na agregační funkce, ale platná jsou i pole vybraná pomocí GROUP BY. Chceme například vidět celkové zůstatky na účtech poboček v Petrohradu, Pskově a Urjupinsku:

SELECT větev, SUM(zůstatek)

OD F,Q

KDE F. Pobočka = Q. Pobočka

SKUPINA PODLE pobočky

MAJÍCÍ pobočku ('Petrohrad', 'Pskov', 'Urjupinsk');

100 000; Pokud je celkový zůstatek větší než 100 000 $, pak to uvidíme ve výsledném vztahu, jinak dostaneme prázdný vztah. "width="640"

Proto v aritmetických výrazech predikátů zahrnutých ve výběrové klauzuli klauzule HAVING lze přímo použít pouze specifikace sloupců specifikovaných jako seskupovací sloupce v klauzuli GROUP BY. Zbývající sloupce lze zadat pouze v rámci specifikací agregačních funkcí COUNT, SUM, AVG, MIN a MAX, které se vyhodnotí jako tento případ nějakou agregovanou hodnotu pro celou skupinu řádků. Výsledkem provádění sekce HAVING je seskupená tabulka obsahující pouze ty skupiny řádků, pro které je výsledek výpočtu podmínky výběru v části HAVING PRAVDA. Konkrétně, pokud je v dotazu, který neobsahuje GROUP BY, přítomna klauzule HAVING, pak výsledkem jejího provedení je buď prázdná tabulka, nebo výsledek provedení předchozích částí tabulkového výrazu, zacházeno jako s jednou skupinou. bez seskupování sloupců. Zvažte příklad. Řekněme, že chceme výstup Celková částka zůstatky pro všechny pobočky, ale pouze pokud je vyšší než 100 000 $. V tomto případě náš dotaz nebude obsahovat seskupovací operace, ale bude obsahovat sekci HAVING a bude vypadat takto:

VYBERTE SOUČET( Zbytek )

MÁTE SOUČET ( Zbytek ) 100 000;

Pokud je celkový zůstatek větší než 100 000 $, pak to uvidíme ve výsledném vztahu, jinak dostaneme prázdný vztah.

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é slovo DISTINCT pro řádky sloupců s hodnotami 1, 1, 1 a 3 vrátí 2, a pokud je přítomno klíčové slovo 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 i 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 hodnot (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: Formulujte dotazy v jazyce SQL a vyberte následující data:

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

Jak zjistím počet modelů PC vyrobených konkrétním prodejcem? Jak zjistit průměrnou cenu počítačů, které mají stejné Specifikace? Tyto a mnohé další otázky související s některými statistické informace, můžete získat odpovědi pomocí souhrnné (agregační) funkce. Standard poskytuje následující agregační funkce:

Všechny tyto funkce vracejí jedinou hodnotu. Zároveň funkce POČET, MIN A MAX platí pro jakýkoli typ dat, zatímco SOUČET A AVG používá se pouze pro číselná pole. Rozdíl mezi funkcí POČET(*) A POČET(<имя поля>) je, že druhý nezohledňuje při výpočtu hodnoty NULL.

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

Příklad. Najděte dostupný počet počítačů vyrobených výrobcem A:

Příklad. Pokud nás zajímá množství různé modely vyrábí výrobce A, pak může být dotaz formulován následovně (s využitím skutečnosti, že každý model je v tabulce Produktů zaznamenán jednou):

Příklad. Zjistěte počet dostupných různých modelů vyrobených výrobcem 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 nutné zjistit počet různých modelů v PC tabulce (tj. k dispozici na prodej).

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í funkce může být použito Parametr DISTINCT. Další parametr VŠECHNY je výchozí a očekává, že bude počítat všechny návratové hodnoty ve sloupci. Operátor,

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

Nabídka GROUP BY

Pokud by byl ve SELECTu 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 agregační funkce vypočítaly pro každou kombinaci hodnot (model-date).

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

• Pokud v důsledku žádosti nebyl přijat žádný řádek(nebo více než jeden řádek pro tuto skupinu), pak neexistují žádná počáteční data pro výpočet žádné 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 jednom požadavku je nemožné, řekněme, získat maximum průměrných hodnot.
• 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řekročil maximální hodnotu použitého datového typu, chyba.

Pokud tedy žádost neobsahuje GROUP BY nabídky, Že agregační funkce obsažen v klauzule SELECT, jsou prováděny na všech výsledných řetězcích dotazů. Pokud žádost obsahuje Nabídka GROUP BY, každá sada řádků, která má stejné hodnoty jako sloupec nebo skupina sloupců zadaná v nabídka GROUP BY, tvoří skupinu a agregační funkce provádí pro každou skupinu zvlášť.

MÁME nabídku

Li klauzule WHERE definuje predikát pro filtrování řetězců klauzule HAVING 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 ověření hodnot, které jsou získány agregační funkce nikoli ze samostatných řádků zdroje záznamů definovaného v klauzule FROM a od skupiny takových linek. Taková kontrola tedy nemůže být obsažena v klauzule WHERE.

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- displeje nejvyšší hodnotu ze všech vybraných hodnot tohoto 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í.