Nikdy nevíš, kdy to přijde
Zobrazit menu   MENU

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() 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.


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áklady
10. Spojování více tabulek - pokročilé
11. Agregační funkce
12. Poddotazy
13. Fulltextové vyhledávání