SQL – dotazy s agregací

25. září 2000

•

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:

SELECT seznam sloupců
FROM seznam tabulek
[WHERE podmínky] [GROUP BY výrazy pro seskupení] [ORDER BY dle čeho třídit]

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:

SELECT kniha_id, autor_id
FROM napsané_knihy
GROUP BY kniha_id

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í:

SELECT COUNT(*)
FROM KNIHA

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ů:

SELECT název, COUNT(*)
FROM kniha, napsané_knihy, autoři
WHERE kniha.id = napsané_knihy.kniha_id
AND napsané_knihy.autor_id = autoři.id
GROUP BY název

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:

SELECT adr_město, COUNT(*)
FROM nakladatelství
WHERE web_stránka IS NOT NULL
OR email IS NOT NULL
GROUP BY adr_město

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:

SELECT kniha.název, nakladatelství.název, SUM(cena)
FROM kniha, výtisk, nakladatelství
WHERE kniha.id = výtisk.kniha_id
AND výtisk.nakladatel_id = nakladatelství.id
GROUP BY kniha.název, nakladatelství.název

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:

SELECT kniha.název, nakladatelství.název, AVG(hmotnost)
FROM kniha, výtisk, nakladatelství
WHERE nakladatelství.id = výtisk.nakladatel_id
  AND výtisk.kniha_id = kniha.id
  AND adr_psč >= 60000
  AND adr_psč <= 64400
GROUP BY kniha.název, nakladatelství.název

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.

SELECT název, MIN(rok)
FROM výtisk, nakladatelství
WHERE nakladatelství.id = výtisk.nakladatel_id
GROUP BY název, kniha_id

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.

SELECT název, MAX(cena)
FROM nakladatelství, výtisk
WHERE nakladatelství.id = výtisk.nakladatel_id
GROUP BY název

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:

SELECT seznam sloupců
FROM seznam tabulek
[WHERE restrikce] [GROUP BY výrazy pro seskupení] [HAVING doplňující restrikce pro skupinu]

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ř:

SELECT C, AVG(A/B)
FROM TEMP
WHERE A<>B
GROUP BY C

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:

SELECT název, MAX(cena)
FROM nakladatelství, výtisk
WHERE nakladatelství.id = výtisk.nakladatel.id
GROUP BY název
ORDER BY MAX(cena)

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:

SELECT název, MAX(cena) AS ‚max_cena‘
FROM nakladatelství, výtisk
WHERE nakladatelství.id = výtisk.nakladatel.id
GROUP BY název
ORDER BY max_cena

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ů.

Předchozí článek Co bude dnes dobrého? Photoshop 6!

Další článek Preview Photoshopu 6 - pokračování