11. Agregační funkce
Agregační funkce nám umožnují nad tabulkami databáze použít určité aritmetické a statistické funkce. Dále je možné data seskupovat podle zvolených kritérií. Obě tyto funcionality jsou velmi užitečné a reálně často využívané. MySQL nám nabízí tyto základní funkce:- MAX() - vratí nejvyšší hodnotu ze zvolených dat
- MIN() - vratí nejnižší hodnotu ze zvolených dat
- SUM() - vratí součet hodnot ze zvolených dat
- AVG() - vratí průměr hodnot ze zvolených dat
- COUNT() - vratí počet řádků odpovídajících zadaným podmínkám
MAX() a MIN()
Funkce MAX() a MIN slouží k získání nejvyšší/nejnižší hodnoty v daném sloupci ze zvolených řádku. Každá funkce má jako parametr název sloupce a vrací pouze jedno výsledné číslo. Opět budeme využívat připravené tabulky z minulých dílů tutoriálu:SELECT MAX(pocet_stran)
FROM `vydani_knihy`;
Jako výsledek získáme jediné číslo, které odpovídá nejvyššímu počtu stránek ze všech řádků tabulky:
+------------------+ | MAX(pocet_stran) | +------------------+ | 639 | +------------------+
Možná vás již napadlo, že stejného výsledku bychom mohli dosáhnout použítím normálního dotazu s ORDER BY pocet_stran DESC a LIMIT 1. V jednoduchých případech máte pravdu, ale pokud do dotazu začneme zahrnovat i seskupování dat, takový přístup již možný nebude.
Funkce vrací pouze danou hodnotu, nevybírá celý řádek. Pokud tedy v SELECT části zadáte další sloupce, získáte sice v daném sloupci nějakou hodnotu, ta ale nebude odpovídat řádku s MAX/MIN.
Pokud potřebujete informace o celém řádku, je skutečně nutné použít variantu s ORDER BY a LIMIT.
Pokud potřebujete informace o celém řádku, je skutečně nutné použít variantu s ORDER BY a LIMIT.
SUM()
Funkce SUM() nám vrací součet hodnot ve zvoleném sloupci. Pokud by nás tedy zajímal součet počtu stran ze všech vydání našich knih, zjistíme ho takto:SELECT SUM(pocet_stran)
FROM `vydani_knihy`;
+------------------+ | SUM(pocet_stran) | +------------------+ | 2429 | +------------------+
AVG()
Pomocí AVG() můžeme získat průměrnou hodnotu z daného sloupce. Ke všem těmto dotazům lze využívat podmínkovou klauzuli WHERE. Ukážeme si to na získání průměrného počtu stran u vydání knihy Duna (kniha_id = 1):SELECT AVG(pocet_stran)
FROM `vydani_knihy`
WHERE kniha_id = 1;
+------------------+ | AVG(pocet_stran) | +------------------+ | 605.6667 | +------------------+
COUNT()
Funkce COUNT() slouží ke zjištění počtu řádků buď celé tabulky, nebo odpovídajícím podmínce ve WHERE. Pokud by nás zajímal počet hodnocení knihy Duna:SELECT COUNT(hodnoceni_id)
FROM `hodnoceni`
WHERE kniha_id = 1;
+---------------------+ | COUNT(hodnoceni_id) | +---------------------+ | 6 | +---------------------+
Pokud potřebujete získat informaci o počtu řádků, vždy využívejte funkci COUNT() místo výběru všech odpovídající řádků a jejich následného počítání v rámci aplikace. Takový přístup je velice zatěžující pro databázi.
Seskupování dat
Společně a agregačními funkcemi velmi často používáme seskupování (grouping). Pomocí seskupování můžeme výsledky dotazu sloučit do skupin podle společné vlastnosti a nad touto skupinou zavolat agregační funkci. Společnou vlastností je myšlena stejná hodnota v zadaném sloupci. Pokud bychom měli například tabulku uživatelů, kde by bylo uloženo jejich křestní jmeno, mohli bychom je sloučit do skupin podle stejného jmena a získat počet uživatelů s každým jedním jménem.Pro seskupování použijeme nový klíčový výraz GROUP BY nazev_sloupce. V našem případě si to vyzkoušíme nad jednotlivými vydáními knih. Bude nás zajímat počet jednotlivých vydání u knih:
SELECT COUNT(kniha_id), kniha_id
FROM `vydani_knihy`
GROUP BY kniha_id;
Kromě sloupce se součtem, budeme chtít také zobrazit sloupec kniha_id, abychom vědeli, ke které knize se daný počet vztahuje.
+-----------------+----------+ | COUNT(kniha_id) | kniha_id | +-----------------+----------+ | 3 | 1 | | 2 | 2 | | 1 | 3 | | 1 | 4 | +-----------------+----------+
Dotaz si ještě trochu zkomplikujeme, protože ve skutečnosti bychom chtěli nejspíše znát i název knih, nejenom její id. Vytvoříme tedy ještě spojení s tabulkou knihy. Povšimněte si, že narozdíl od minulého případu je u sloupců nutné uvést i tabulku, protože obě obsahují sloupce se stejnými jmény:
SELECT COUNT(vydani_knihy.kniha_id), knihy.nazev
FROM `vydani_knihy` JOIN `knihy` ON vydani_knihy.kniha_id = knihy.kniha_id
GROUP BY vydani_knihy.kniha_id;
+------------------------------+------------------+ | COUNT(vydani_knihy.kniha_id) | nazev | +------------------------------+------------------+ | 3 | Duna | | 2 | Malý princ | | 1 | Záskok | | 1 | Svět ledu a ohně | +------------------------------+------------------+
Uvedeme si ještě poslední příklad na použití funkce AVG(). Z tabulky hodnoceni si spočítáme průměrné hodnocení každé knihy. Navíc opět připojíme tabulku knihy, abychom věděli o jakou knihu jde:
SELECT AVG(hodnoceni.hodnoceni), knihy.nazev
FROM `hodnoceni` JOIN `knihy` ON hodnoceni.kniha_id = knihy.kniha_id
GROUP BY hodnoceni.kniha_id;
+--------------------------+------------------+ | AVG(hodnoceni.hodnoceni) | nazev | +--------------------------+------------------+ | 9.6667 | Duna | | 8.8000 | Malý princ | | 5.6667 | Svět ledu a ohně | +--------------------------+------------------+
Jedna kniha je zde vynechána, protože žádné hodnocení nemá. Pokud bychom chtěli vypsat i takové knihy, museli bychom použít kontrukci knihy LEFT JOIN hodnoceni.
Podmínka HAVING
Tak jako slouží v běžných dotazech WHERE k filtrování výsledků dle podmínek, při použití agregačních funkcí můžeme ke stejnému účelu použít HAVING.Pokud bychom chtěli vybrat pouze knihy, které mají alespoň 4 hodnocení, pomocí HAVING můžeme takto:
SELECT COUNT(hodnoceni_id), kniha_id
FROM `hodnoceni`
GROUP BY kniha_id
HAVING COUNT(hodnoceni_id) >= 4
Agregačních funkcí je v MySQL ještě více, ale jejích reálné využití je spíš vyjímečné. Pro naše účely dostačuje těchto pět základních, které jsme si v tomto díle představili. V příštím článku se podíváme na poddotazy.
Další díly tutoriálu
9. Spojování více tabulek - základy10. Spojování více tabulek - pokročilé
11. Agregační funkce
12. Poddotazy
13. Fulltextové vyhledávání