Seskupování v SQL: GROUP BY, klauzule HAVING a agregační funkce. Agregační funkce v jazyce SQL
Naučme se shrnout. Ne, toto nejsou výsledky studia SQL, ale výsledky hodnot sloupců databázových tabulek. Agregační funkce SQL pracují s hodnotami sloupce 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í samostatně a vracejí jedinou výslednou hodnotu. Za druhé, agregační funkce se používají s klauzulí SQL GROUP BY, to znamená seskupování podle polí (sloupců) k získání výsledných hodnot 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 ve sloupci databázové tabulky. 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 COLUMN_NAME)...
Za tímto výrazem následuje FROM (TABLE_NAME) a poté lze zadat podmínku pomocí klauzule WHERE. Kromě toho může před názvem sloupce předcházet 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 není povinné).
Příklad 1 Existuje firemní databáze s údaji o jejích divizích a zaměstnancích. V tabulce Zaměstnanci je také sloupec s údaji o platech zaměstnanců. Výběr z tabulky vypadá takto (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 mezd, 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 a 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.
Potřebujeme zjistit minimální mzdu pro zaměstnance oddělení číslo 42. K tomu vypisujeme následující požadavek:
Dotaz vrátí hodnotu 10505,90.
A znovu cvičení pro sebeřešení. V tomto a některých dalších cvičeních budete potřebovat nejen tabulku Zaměstnanci, 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 odděleních společnosti. Vytiskněte 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.
Potřebujeme zjistit maximální plat zaměstnanců oddělení číslo 42. K tomu napište následující žádost:
Dotaz vrátí hodnotu 18352,80
Nastal čas cvičení k samostatnému řešení.
Příklad 6. Opět pracujeme se dvěma tabulkami – Staff a Org. Zobrazte název oddělení a maximální hodnotu provize, kterou obdrží jeden pracovník na oddělení patřící do skupiny oddělení (Divize) Východní. Použití JOIN (připojení ke stolům) .
Funkce SQL AVG
To, co je uvedeno ohledně syntaxe pro předchozí 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 délku služby zaměstnanců v oddělení číslo 42. K tomu napište 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í 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ů 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ů) tabulky.
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:
SELECT COUNT(Comm) FROM Staff
Výsledkem bude 11.
Příklad 10. Databáze a tabulka jsou stejné jako v předchozích příkladech.
Pokud chcete zjistit 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 17.
V dalším cvičení pro samostatné řeš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
Nyní se podíváme na použití agregačních funkcí společně s příkazem SQL GROUP BY. Příkaz SQL GROUP BY se používá k seskupení výsledných hodnot podle sloupců v databázové tabulce.
Příklad 12. Existuje databáze inzertního portálu. Obsahuje tabulku reklam obsahující údaje o reklamách odeslaných za daný týden. Sloupec Kategorie obsahuje údaje o velkých kategoriích inzerátů (například Nemovitosti) a Sloupec Díly obsahuje údaje o menších částech zařazených do kategorií (například části Byty a Chaty jsou součástí kategorie Nemovitosti). Sloupec Jednotky obsahuje údaje o počtu podaných inzerátů a sloupec Peníze údaje o výši přijatých peněz za podání inzerátu.
| Kategorie | Část | Jednotky | Peníze |
| Doprava | Auta | 110 | 17600 |
| Nemovitost | Byty | 89 | 18690 |
| Nemovitost | chaty | 57 | 11970 |
| Doprava | Motocykly | 131 | 20960 |
| Stavební materiály | Desky | 68 | 7140 |
| Elektrotechnika | televizory | 127 | 8255 |
| Elektrotechnika | Ledničky | 137 | 8905 |
| Stavební materiály | Regips | 112 | 11760 |
| Volný čas | knihy | 96 | 6240 |
| Nemovitost | Doma | 47 | 9870 |
| Volný čas | Hudba | 117 | 7605 |
| Volný čas | Hry | 41 | 2665 |
Pomocí příkazu SQL GROUP BY zjistěte množství peněz vydělaných zveřejňováním reklam v každé kategorii. Píšeme následující žádost.
- Agregační funkce se používají podobně 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 ekvivalentu kódu ASCII a zpracuje je v abecedním pořadí. Některé DBMS umožňují použití vnořených agregátů, ale to je odchylka od standardu ANSI se všemi z toho vyplývajícími 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 spustit dotaz seskupený podle pole „Disciplína“ a jako výsledek zobrazit název disciplíny a počet řádků ve skupině pro tuto disciplínu. Použití znaku * jako argumentu funkce COUNT znamená sčítání všech řádků ve skupině.
VYBERTE R1. Disciplína, COUNT(*)
GROUP BY R1.Disciplína;
Výsledek:
VYBERTE R1.Disciplína, POČET (*)
KDE R1. Hodnocení NENÍ NULL
GROUP BY R1.Disciplína;
Výsledek:
nebudou zahrnuty 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 dotaz napíšete následujícím způsobem:
VYBERTE R1. Disciplína, POČET (R1. Hodnocení)
GROUP BY R1. Disciplína;
Funkce POČET (NÁZEV ATRIBUTU) počítá počet konkrétních hodnot ve skupině, na rozdíl od funkce POČÍTAT(*), který počítá počet řádků ve skupině. Ve skupině s disciplínou „Teorie informací“ budou skutečně 4 řádky, ale pouze 3 konkrétní hodnoty pro atribut „Vyhodnocení“.
Pravidla pro zpracování hodnot NULL v agregačních funkcích
Pokud jsou některé hodnoty ve sloupci stejné NULL Při výpočtu výsledku funkce jsou vyloučeny.
Pokud jsou všechny hodnoty ve sloupci stejné NULL, To Max Min. Součet Prům = NULL počet = 0 (nula).
Pokud je stůl prázdný, počet(*) = 0 .
Můžete také použít agregační funkce 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 můžete 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 (Vyhodnocení) z R1 během sezení poskytne maximální hodnocení;
VYBERTE SOUČET (Skóre) z R1 dá součet všech známek za relaci;
VYBERTE AVG (hodnocení) z R1 poskytne průměrné skóre za celou relaci.
2; Výsledek: " width="640" Když se znovu vrátíme do databáze „Session“ (tabulka R1), zjistíme počet úspěšně složených zkoušek:
SELECT COUNT(*) As Doručeno _ zkoušky
KDE skóre 2;
Výsledek:
Argumenty pro agregační funkce mohou být jednotlivé sloupce tabulky. Chcete-li například vypočítat počet odlišný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ě:
VYBERTE R1.Disciplína, POČET (ODLIŠNÁ R1.Hodnocení)
KDE R1. Hodnocení NENÍ NULL
GROUP BY R1.Disciplína;
Výsledek:
Stejného výsledku dosáhneme, pokud vyloučíme explicitní podmínku v části WHERE, v takovém případě bude dotaz vypadat takto:
VYBERTE R1. Disciplína, POČET (DISTINCT R1. Hodnocení)
GROUP BY R1. Disciplína;
Funkce POČET (ODLIŠNÉ R1.Hodnocení) počítá jen jisté různé významy.
Aby bylo v tomto případě dosaženo požadovaného výsledku, je nutné provést předběžnou transformaci datového typu sloupce „Hodnocení“ a uvést jej na skutečný typ, pak výsledkem výpočtu průměru nebude celé číslo. V tomto případě bude žádost vypadat takto:
2 Seskupit podle R2. Skupina, R1. Disciplína; Zde funkce CAST() převede sloupec Score na platný datový typ. "width="640" Vyberte R2.Group, R1.Discipline,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. skóre není nulové
a R1. skóre 2
Seskupit podle R2. Skupina, R1. Disciplína;
Tady je funkce OBSAZENÍ() Převede sloupec Hodnocení na platný datový typ.
V klauzuli WHERE nelze použít agregační funkce, protože podmínky v této části se vyhodnocují z hlediska jednoho řádku a agregační funkce se vyhodnocují z hlediska skupin řádků.
Klauzule GROUP BY vám 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ýrazu pro výstup výsledků řádku SELECT, tak ve výrazu pro podmínku 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é z výpočtu agregačních funkcí 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 jedné disciplíně ve zkouškách obdrženo více než jedna špatná známka:
1; Výsledek: " width="640" VYBERTE R2. Skupina
OD R1,R2
KDE R1. Celé jméno = R2. Celé jméno AND
R1. Skóre = 2
SKUPINA PODLE R2.Skupina, R1.Disciplína
HAVING count(*) 1;
Výsledek:
Máme databázi „Banka“ složenou z jedné tabulky F, která uchovává relaci 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 provést samostatný dotaz výběrem SUM (Zbývající) z tabulky pro každou větev, ale operace GROUP BY vám umožňuje umístit je všechny do jednoho příkazu:
VYBRAT Větev , SUM( Zbytek )
GROUP BY Branch;
GROUP BY aplikuje agregační funkce nezávisle na každou skupinu identifikovanou hodnotou pole Pobočka. Skupina se skládá z řádků se stejnou hodnotou pole Branch a funkcí 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 GROUP BY, podle definice má pouze jednu hodnotu na výstupní skupinu, stejně jako výsledek agregační funkce.
5 000; Argumenty v klauzuli HAVING se řídí stejnými pravidly jako v klauzuli SELECT, která používá GROUP BY . Musí mít jednu hodnotu na výstupní skupinu. "width="640" Předpokládejme, že vybereme pouze ty pobočky, jejichž celkové zůstatky na účtech přesahují 5 000 USD, a také celkové zůstatky za vybrané pobočky. Chcete-li zobrazit výsledky pro pobočky s celkovými zůstatky nad 5 000 USD, musíte použít 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(zbývající)
GROUP BY Větev
MÁTE SOUČET ( Zbytek ) 5 000;
Argumenty ve větě MÍT dodržovat stejná pravidla jako ve větě VYBRAT kde se používá GROUP BY. Musí mít jednu hodnotu na výstupní skupinu.
Následující příkazy budou zakázány:
SELECT Branch,SUM(Zbývající)
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 takové situaci, návrh MÍT by měl odkazovat pouze na vybrané agregáty a pole GROUP BY. Existuje správný způsob, jak provést výše uvedený dotaz:
SELECT Branch,SUM(Zbývající)
WHEREOpenDate = '27/12/2004'
GROUP BY Branch;
Význam tohoto dotazu je následující: najít 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(zbývající)
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"
V predikátových aritmetických výrazech obsažených v klauzuli výběru klauzule HAVING lze tedy přímo použít pouze specifikace sloupců určený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é v tomto případě vypočítávají nějakou agregovanou hodnotu pro celou skupinu řádků. Výsledkem provedení klauzule HAVING je seskupená tabulka obsahující pouze ty skupiny řádků, pro které je výsledek výpočtu podmínky výběru v klauzuli HAVING TRUE. Konkrétně, pokud je v dotazu, který neobsahuje GROUP BY, přítomna klauzule HAVING, bude výsledkem jejího provedení 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ů. Podívejme se na příklad. Řekněme, že chceme zobrazit celkovou částku zůstatků napříč všemi pobočkami, 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 )
HAVING SUM( 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.
klauzule GROUP BY(příkazy SELECT) umožňují seskupit data (řádky) podle hodnoty sloupce nebo několika sloupců nebo výrazů. Výsledkem bude sada souhrnných řádků.
Každý sloupec ve výběrovém seznamu se musí objevit v klauzuli GROUP BY, s jedinou výjimkou jsou konstanty a sloupce, které jsou operandy agregačních funkcí.
Tabulku můžete seskupit podle libovolné kombinace jejích sloupců.
Agregační funkce se používají k získání jedné celkové hodnoty ze skupiny řádků. 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. Výsledkem jakéhokoli výpočtu 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 vybraných sloupců obsahuje alespoň jednu agregační funkci, pak nesmí obsahovat jednoduché sloupce. 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.
Pokud dotaz obsahuje klauzuli WHERE, pak agregační funkce vypočítají hodnotu pro výsledky výběru.
Agregační funkce MIN a MAX vypočítat nejmenší a největší hodnotu sloupce, resp. Argumenty mohou být čísla, řetězce a data. Všechny hodnoty NULL jsou před výpočtem odstraněny (to znamená, že se neberou v úvahu).
Agregační funkce SUM vypočítá celkový součet hodnot sloupce. Argumenty mohou být pouze čísla. Použití parametru DISTINCT odstraní 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 vrátí průměr všech hodnot ve sloupci. Argumenty mohou být také pouze čísla a všechny hodnoty NULL jsou před vyhodnocením odstraněny.
Agregační funkce COUNT má dvě různé podoby:
- COUNT(název_sloupce) - počítá počet hodnot ve sloupci název_sloupce, hodnoty NULL se neberou v úvahu
- COUNT(*) - počítá počet řádků v tabulce, zohledňují se také hodnoty NULL
Pokud dotaz používá klíčové slovo DISTINCT, budou před použitím funkce COUNT odstraněny všechny duplicitní hodnoty ve sloupci.
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.
V MÁME nabídku definuje podmínku, která platí pro skupinu řádků. Pro skupiny řádků má stejný význam jako klauzule WHERE pro obsah odpovídající tabulky (WHERE platí před seskupením, HAVING po).
o hodnotu sloupce Disciplína. Dostaneme 4 skupiny, pro které můžeme vypočítat nějaké skupinové hodnoty, jako je počet n-tic ve skupině, maximální nebo minimální hodnota sloupce Skóre.| Funkce | Výsledek |
|---|---|
| POČÍTAT | Počet řádků nebo neprázdných hodnot polí, které dotaz vybral |
| SOUČET | Součet všech vybraných hodnot pro toto pole |
| AVG | Aritmetický průměr všech vybraných hodnot pro toto pole |
| MIN | Nejmenší ze všech vybraných hodnot pro toto pole |
| MAX | Největší ze všech vybraných hodnot pro toto pole |
| R1 | |||
|---|---|---|---|
| Celé jméno | Disciplína | Stupeň | |
| Skupina 1 | Petrov F.I. | Databáze | 5 |
| Sidorov K.A. | Databáze | 4 | |
| Mironov A.V. | Databáze | 2 | |
| Štěpánová K.E. | Databáze | 2 | |
| Krylová T.S. | Databáze | 5 | |
| Vladimirov V.A. | Databáze | 5 | |
| Skupina 2 | Sidorov K.A. | Teorie informace | 4 |
| Štěpánová K.E. | Teorie informace | 2 | |
| Krylová T.S. | Teorie informace | 5 | |
| Mironov A.V. | Teorie informace | Null | |
| Skupina 3 | Trofimov P.A. | Sítě a telekomunikace | 4 |
| Ivanova E.A. | Sítě a telekomunikace | 5 | |
| Utkina N.V. | Sítě a telekomunikace | 5 | |
| Skupina 4 | Vladimirov V.A. | anglický jazyk | 4 |
| Trofimov P.A. | anglický jazyk | 5 | |
| Ivanova E.A. | anglický jazyk | 3 | |
| Petrov F.I. | anglický jazyk | 5 | |
Agregační funkce se používají podobně jako názvy polí v příkazu SELECT, ale 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ží na ekvivalentní kód ASCII a zpracují je v abecedním pořadí. Některé DBMS umožňují použití vnořených agregátů, ale to je odchylka od standardu ANSI se všemi z toho vyplývajícími 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 spustit dotaz seskupený podle pole "Disciplína" a jako výsledek zobrazit název disciplíny a počet řádků ve skupině pro tuto disciplínu. Použití znaku * jako argumentu funkce COUNT znamená sčítání všech řádků ve skupině.
SELECT R1.Discipline, COUNT(*) ZE R1 GROUP BY R1.Discipline
Výsledek:
Pokud chceme spočítat počet lidí, kteří složili zkoušku v jakékoli disciplíně, musíme před seskupením vyloučit z původního poměru nejisté hodnoty. V tomto případě bude žádost vypadat takto:
Dostáváme výsledek:
V tomto případě linka se studentem
| Mironov A.V. | Teorie informace | Null |
|---|
nespadne do množiny n-tic před seskupením, takže počet n-tic ve skupině k ukáznění " Teorie informace“ bude o 1 méně.
Lze použít agregační funkce také bez operace předběžného seskupení, v tomto případě je celý vztah považován za jednu skupinu a pro tuto skupinu lze vypočítat jednu hodnotu na skupinu.
Když se opět vrátíme do databáze „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 tabulky. Ale abyste mohli vypočítat například počet odlišný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 pole seskupení a několik agregační funkce a v podmínkách seskupování můžete použít více polí. V tomto případě jsou skupiny vytvořeny na základě sady zadaných seskupovacích polí. Operace agregačních funkcí 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 studentů, 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 linek.
Klauzule GROUP BY vám 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ýrazu pro výstup výsledků řádku SELECT, tak ve výrazu pro podmínku zpracování generovaných skupin HAVING. V tomto případě se každá agregační funkce vypočítá pro každou vybranou skupinu. Hodnoty získané 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 jedné disciplíně ve zkouškách obdrženo více než jedna špatná známka:
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ě je možné 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 bankovních účtů. 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 vám však umožní dát je všechny do jednoho příkazu:
SELECT Větev, SUM(Zbývající) FROM F GROUP BY Větev;
Platí GROUP BY agregační funkce nezávisle pro každou skupinu definovanou pomocí hodnoty pole Pobočka. Skupina se skládá z řádků se stejnou hodnotou pole Branch a
Jak zjistím počet modelů PC vyrobených konkrétním dodavatelem? Jak určit průměrnou cenu počítačů se stejnými technickými vlastnostmi? Tyto a mnohé další otázky související s některými statistickými informacemi lze zodpovědět pomocí konečné (agregátní) funkce. Norma poskytuje následující agregační funkce:
Všechny tyto funkce vracejí jedinou hodnotu. Zároveň funkce POČET, MIN A MAX 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ČÍTAT(*) A POČÍTAT(<имя поля>) 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á 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 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 také potřeba zjistit počet různých modelů v PC stůl (tj. ty, které jsou 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 agregovaných funkcí lze použít Parametr DISTINCT. Další parametr VŠECHNY je výchozí a předpokládá, že se započí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í po věty WHERE.
klauzule GROUP BY
klauzule GROUP BY slouží k definování skupin výstupních řetězců, na které lze použít agregační funkce (COUNT, MIN, MAX, AVG a SUM). Pokud tato klauzule chybí a jsou použity agregační funkce, pak všechny sloupce s názvy uvedenými v VYBRAT, musí být součástí agregační funkce a tyto funkce budou aplikovány na celou sadu řádků, které splňují predikát dotazu. Jinak všechny sloupce seznamu SELECT není součástí dodávky v agregačních funkcích musí být specifikováno v doložce GROUP BY. V důsledku toho jsou všechny řádky výstupního dotazu rozděleny do skupin charakterizovaných stejnými kombinacemi hodnot v těchto sloupcích.Poté budou agregační funkce aplikovány na každou skupinu. Vezměte prosím na vědomí, že pro GROUP BY jsou všechny hodnoty NULL považovány za stejné, tj. při seskupování podle pole obsahujícího hodnoty NULL budou všechny takové řádky spadat do jedné skupiny. Li pokud existuje klauzule GROUP BY , v klauzuli SELECTžádné agregační funkce
Podívejme se na jednoduchý příklad:
| SELECT model, COUNT(model) AS Množství_model, AVG(cena) AS Prům._cena Z PC GROUP BY modelu; |
V této žádosti je pro každý model PC stanoven jejich počet a průměrné náklady. Všechny řádky se stejnou hodnotou modelu tvoří skupinu a výstup SELECT vypočítá počet hodnot a průměrné hodnoty cen pro každou skupinu. Výsledkem dotazu bude následující tabulka:
| model | Model_množství | Průměrná_cena |
| 1121 | 3 | 850.0 |
| 1232 | 4 | 425.0 |
| 1233 | 3 | 843.33333333333337 |
| 1260 | 1 | 350.0 |
Pokud by měl SELECT sloupec datum, 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 nebyly přijaty žádné řádky(nebo více než jeden řádek pro danou skupinu), pak neexistují žádná zdrojová data pro výpočet žádné z agregačních funkcí. V tomto případě bude výsledek 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 dotazu je řekněme nemožné získat maximum průměrných hodnot.
- Výsledek provedení funkce COUNT je celé číslo(CELÉ ČÍSLO). Jiné agregační funkce dědí datové typy hodnot, které zpracovávají.
- Pokud funkce SUM vytvoří výsledek, který je větší než maximální hodnota použitého datového typu, chyba.
Pokud tedy žádost neobsahuje GROUP BY klauzule, To agregační funkce zahrnuto 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 jako sloupec nebo skupina sloupců zadaná v klauzule GROUP BY, tvoří skupinu a agregační funkce se provádí pro každou skupinu zvlášť.
MÁME nabídku
Poté budou agregační funkce aplikovány na každou skupinu. Vezměte prosím na vědomí, že pro GROUP BY jsou všechny hodnoty NULL považovány za stejné, tj. při seskupování podle pole obsahujícího hodnoty NULL budou všechny takové řádky spadat do jedné skupiny. klauzule WHERE pak definuje predikát pro filtrování řádků MÁME nabídku platí po seskupení k definování podobného predikátu, který filtruje skupiny podle hodnot agregační funkce. Tato klauzule je potřebná k ověření hodnot, které jsou získány pomocí agregační funkce nikoli z jednotlivých řádků zdroje záznamů definovaného v klauzule FROM a od skupiny takových linek. Takovou kontrolu tedy nelze obsáhnout klauzule WHERE.
