SQL – dotazy s agregací
Dnes se podíváme na další části příkazu SELECT. Rozšíříme si množinu základních pojmů o tzv. agregační funkce a seskupování záznamů. Agregační funkce slouží pro získávání celkových souhrnných údajů, jako jsou aritmetické průměry, součty, minima nebo maxima. Rozšíříme si tedy naši zjednodušenou definici syntaxe příkazu SELECT o části GROUP BY a HAVING.
Agregace
Pomocí projekce a restrikce jsme schopni z několika tabulek získat přehledy názvů nakladatelství, seznamy knih, apod. Někdy ale potřebujeme získat spíše souhrnné údaje. Těmi v jazyce SQL mohou být aritmetický průměr, součet, maximum, minimum, nebo počet hodnot. Toho docílíme pomocí agregace řádků. Agregaci si lze představit jako shluknutí několika řádků do jednoho. Pro tuto „skupinu“ řádků můžeme pomocí agregačních funkcí počítat uvedené matematické operace.
Seskupení
Než se dostaneme ke konkrétním agregačním funkcím a příkladům, povíme si, jak funguje seskupení řádků. Seskupení se provádí podle hodnot určitých sloupců. Někdy můžeme seskupovat podle hodnot jednoho sloupce, někdy podle dvou, obecně podle kolika chceme. Seskupení spočívá v tom, že když se objeví dva řádky, jejichž hodnoty se ve vybraných sloupcích (v těch, podle kterých seskupujeme) shodují, databázový systém na ně bude pohlížet jako na jeden řádek (na ostatní sloupce musíme aplikovat agregační funkce, které vrátí jednu hodnotu pro daný souhrnný řádek). Lépe to ukáže následující příklad. Mějme tabulku NAPSANÉ_KNIHY, která bude obsahovat stejná data, jako v předchozím díle:
KNIHA_ID AUTOR_ID 1021 13 1021 16 1024 12 1022 12 1024 14 1024 16
Pokud provedeme seskupení podle sloupce KNIHA_ID, vidíme, že různé hodnoty v tomto sloupci jsou tři. Tedy výsledek po seskupení bude mít pouze tři řádky, na zbývající sloupce je pak nutné aplikovat agregační funkce. Např. zde bychom mohli aplikovat agregační funkci počet hodnot, tzn. že bychom dostali tabulku, která by vypadala nějak takto:
1021 2 1024 3 1022 1
Ale to předbíhám, vraťme se k syntaxi příkazu SELECT. Tu si nyní rozšíříme o klíčové slovo GROUP BY:
|
Nejprve si všimněte, že GROUP BY je před ORDER BY. Pamatujte si, že kritérium pro případné třídění výstupu se vždy udává až na konec příkazu SELECT. Za GROUP BY uvádíme sloupce, podle kterých chceme jednotlivé záznamy seskupit. Nejjednoduší použití je napsání příkazu bez agregační funkce:
|
COUNT(*)
Je nejzákladnější agregační funkcí, která slouží jenom k získání počtu záznamů v rámci jedné skupiny agregovaných řádků. Nejjednodušší použití agregační funkce COUNT je následující:
|
Uvedený příkaz vrátí počet záznamů v tabulce KNIHA. Takto lze funkci COUNT aplikovat na libovolnou tabulku a vždy jako odpověď dostaneme jedno číslo udávající počet záznamů v tabulce. Dále se COUNT dá použít pro počítání počtu záznamů v rámci jednotlivých skupin. V příkazu SELECT musíme do GROUP BY uvést jména sloupců, podle kterých budeme seskupovat a na zbývající sloupce, podle kterých neseskupujeme, aplikujeme funkci COUNT(*). Podívejte se následující příklady:
Kolik autorů má každá kniha uložená v našem systému. Přehled vypište ve formě jméno knihy, následované počtem autorů:
|
Tento příkaz SELECT není úplně správný. A to z důvodu, jak je realizováno přirozené spojení tabulek, o kterém jsem mluvil v předchozím díle. Do tohoto výpisu se nezahrnou názvy knih, které nemají žádného autora. Samozřejme jazyk SQL obsahuje nástroj, jak dostat do výpisu i názvy knih, které mají 0 autorů. K tomu využijeme speciální spojení tabulek, o kterém budu mluvit později. Následuje další docela praktický příklad:
Vypište seznam měst, ve kterých sídlí naše nakladatelství, u každého města uveďte, kolik z nakladatelstvích má kontaktní e-mail nebo webovskou stránku:
|
SUM(sloupec)
Agregační funkce SUM(sloupec) vrátí součet hodnot v uvedeném sloupci v rámci shluknuté skupiny záznamů. Pokud bychom chtěli seznam všech názvů knih a nakladatelství a pro každou dvojici kniha – nakladatelství součet cen výtisků daného díla v daném nakladatelství, napsali bychom následující příkaz:
|
AVG(sloupec)
Funkce AVG počítá aritmetický průměr z vybraných hodnot zadaného sloupce. Mějme následující příklad. Vezměme si pouze brněnská nakladatelství a pro všechny knihy, které byly těmito nakladatelstvími vydány, zjistěte průměrnou hmotnost:
|
MIN(sloupec)
Agregační funkce MIN slouží pro vrácení minima z hodnot ve sloupci ze seskupených záznamů. Následující příkaz vypíše seznam všech nakladatelství, a pro každé z nich napíše, v kterém roce byla vydána nejstarší kniha.
|
MAX(sloupec)
Funkce MAX vrací maximum z hodnot zadaného sloupce. Příklad vypíše seznam nakladatelství, a pro každé z nich bude uvedeno kolik stojí jejich nejdražší výtisk.
|
Další použití agregačních funkcí
Je sice pěkné, že dostaneme na výstup všechny skupiny agregovaných záznamů, ale někdy potřebujeme v rámci takových záznamů vybrat jen ty záznamy, které ještě dále budou splňovat další podmínky. Jinými slovy, máme možnost udělat další restrikci po provedení agregace. Dodatečnou restrikci zapisujeme v příkazu SELECT v části HAVING:
|
Použití HAVING nejlépe ukáže následující příklad. Chceme seznam všech knih a nakladatelství a pro každou dvojici kniha – nakladatel součet cen výtisků daného díla v daném nakladatelství. Do výpisu zahrneme ale jen ta nakladatelství, která vydala alespoň 3 různá výdání dané knihy.
|
V části HAVING typicky figurují agregační funkce. Samozřejmě nemusí, ale pokud neděláme restrikci podle výsledku agregační funkce, pak ji nemusíme psát do části HAVING, ale můžeme ji napsat přímo do části WHERE.
V argumentu agregační funkce nemusí být vždy uveden jen jeden sloupec. Obecně lze zapsat matematický výraz. Např:
|
Nedefinované hodnoty sloupců se do výsledků agregačních funkcí nezapočítavají. Výjimkou je funkce COUNT(*), která počítá všechny položky bez ohledu na jejich obsah.
Třídění dle výsledků agregační funkce
Pokud bychom chtěli vypsat všechny knihy a u každé z nich jejich průměrnou cenu (pro všechna nakladatelství), je praktické takový výpis seřadit právě podle této ceny. Chceme-li výpis setřídit podle hodnoty agregační funkce, za klíčové slovo ORDER BY uvedeme agregační funkci, jak ukazuje následující příklad:
|
Některé databázové systémy mají však omezení, které neumožňuje zadat přímo agregační funkci za ORDER BY. V takovém případě máme jednoduchou pomoc. Použijeme už zmíňovaný modifikátor AS a dáme sloupci agregované funkce nějaký alias a ten uvedeme za ORDER BY. Předchozí příklad by se dal ekvivalentně zapsat takto:
|
Toto byl základní přehled a použití agregačních funkcí. Většinou se tyto funkce používají s kombinací s vnořování záznamů, které nám umožňují získat i další zajímavé informace z databáze. Např. předchozí příklad bychom mohli modifikovat tak, že nás nebude pouze zajímat nejdražší cena, ale že k ní budeme chtít i název knihy, která je nejdražší. O vnořování dotazů budu mluvit v některém z příštích dílů.
Mohlo by vás také zajímat
-
Thunderbolt 4 vs. OCuLink: Přišel čas na upgrade?
27. května 2024 -
Jak si vyzkoušet Apple Intelligence v EU
2. srpna 2024 -
Jak zvýšit CTR vašeho e-mail marketingu
9. září 2024 -
Jak vybrat doménu: Co je dobré vědět?
2. září 2024
Nejnovější
-
Výkonný a kompaktní: ASOME Max Studio s výjimečným poměrem cena/výkon
11. listopadu 2024 -
Šokující data od Microsoftu: Kyberútoky rostou o stovky procent!
8. listopadu 2024 -
Chcete jedinečnou doménu? Objevte koncovky FOOD, MEME a MUSIC!
7. listopadu 2024 -
OpenAI představilo novou funkci ChatGPT Search
6. listopadu 2024