5. díl - MySQL krok za krokem: Řazení, Limit a agregační funkce

MySQL MySQL krok za krokem: Řazení, Limit a agregační funkce

V minulém dílu seriálu tutoriálů o MySQL databázi jsme si připravili testovací data a naučili se základy dotazování, tedy příkaz SELECT a několik operátorů. Dnes se podívejme na řazení a agregační funkce.

Řazení

Doposud jsme nijak neřešili pořadí nalezených položek, které nám dotaz SELECT vrátil. Ono vlastně žádné ani neexistovalo, databáze uvnitř funguje pomocí určitých sofistikovaných pravidel (které jsou nad rámec tohoto seriálu) a vrátila nám položky tak, jak se jí to zrovna hodilo. Kdybychom v databázi provedli nějakou změnu a zavolali znovu ten samý dotaz, pořadí by pravděpodobně vypadalo úplně jinak. Databáze nám ale navrácený výsledek samozřejmě seřadit dokáže.

Řadit můžeme podle kteréhokoli sloupce. Když budeme řadit podle id, máme položky v pořadí, v jakém byly do databáze vloženy. Dále můžeme řadit podle číselných sloupců, ale i podle těch textových (řadí se podle abecedy). Řadit můžeme i podle datumu a všech dalších datových typů, databáze si s tím vždy nějak poradí. Pojďme si vybrat úplně všechny uživatele a seřaďme je podle příjmení. Slouží k tomu klauzule ORDER BY (řadit podle), která se píše na konec dotazu:

SELECT `jmeno`, `prijmeni` FROM `uzivatele` ORDER BY `prijmeni`

Výsledek:

Václav       Blažek
Ondřej       Bohatý
Marie        Černá
Petr         Černý
Vítezslav    Churý
Pavel        Dušín
...

V dotazu by samozřejmě mohlo být i WHERE, pro jednoduchost jsme vybrali všechny uživatele.

Řadit můžeme podle několika kritérií (sloupců), pojďme si uživatele seřadit podle napsaných článků a ty se stejným počtem řaďme ještě podle abecedy:

SELECT `jmeno`, `prijmeni`, `pocet_clanku` FROM `uzivatele` ORDER BY `pocet_clanku`, `prijmeni`

Výsledek:

Matěj        Horák        0
Michal       Krejčí       0
Petr         Černý        1
Miroslav     Kučera       1
Vladimír     Pokorný      1
Jana         Veselá       1
František    Veselý       1
Ludmila      Dvořáková    2
Věra         Svobodová    2
Jiří         Veselý       2
...

Směr řazení

Určit můžeme samozřejmě i směr řazení. Můžeme řadit vzestupně (výchozí směr) klíčovým slovem ASC a sestupně klíčovým slovem DESC. Zkusme si udělat žebříček uživatelů podle počtu článků. Ti první jich tedy mají nejvíce, řadit budeme sestupně. Ty se stejným počtem článků budeme řadit ještě podle abecedy:

SELECT `jmeno`, `prijmeni`, `pocet_clanku` FROM `uzivatele` ORDER BY `pocet_clanku` DESC, `prijmeni`;

Výsledek:

Petr      Dvořák      18
Jan       Novák       17
Eva       Kučerová    12
Tomáš     Marný       12
Pavel     Dušín       9
Otakar    Kovář       9
...

DESC je třeba vždy uvést, vidíte, že řazení podle příjmení je normálně sestupné, protože jsme DESC napsali jen k pocet_clanku.

Limit

Zůstaňme ještě u našeho žebříčku uživatelů podle počtu článků. Takto budeme chtít vypsat 10 nejlepších uživatelů. Když jich bude ale milion, asi není dobrý nápad je všechny vybrat a pak jich v aplikaci použít jen 10 a těch 999 990 zahodit. Dáme databázi limit, tedy maximální počet záznamů, které chceme vybrat. Zároveň uvedeme i řazení. Limit píšeme vždy na konec dotazu:

SELECT `jmeno`, `prijmeni`, `pocet_clanku` FROM `uzivatele` ORDER BY `pocet_clanku` DESC, `prijmeni` LIMIT 10;

Zkuste si to.

LIMIT a ORDER BY lze používat i u dalších příkazů, např. u DELETE nebo UPDATE. Můžeme si tak pojistit, aby byl vymazán nebo editován vždy jen jeden záznam.

Agregační funkce

Databáze nám nabízí spoustu tzv. agregačních funkcí. To jsou funkce, které nějakým způsobem zpracují více hodnot a jako výsledek vrátí hodnotu jednu.

COUNT (Počet)

Příkladem takové funkce je funkce COUNT(), která vrátí počet řádků v tabulce, splňující nějaká kritéria. Spočítejme, kolik z uživatelů napsalo alespoň jeden článek:

SELECT COUNT(*) FROM `uzivatele` WHERE `pocet_clanku` > 0;

Výsledek:

29

Na COUNT se ptáme pomocí SELECT, není to příkaz, je to funkce, která se vykoná nad řádky a její výsledek je vrácen selectem. Funkce má stejně jako v jiných programovacích jazycích (alespoň ve většině z nich) závorky. Ta hvězdička v nich znamená, že nás zajímají všechny sloupce. Můžeme totiž počítat třeba jen uživatele, kteří mají vyplněné jméno (přesněji kteří ho nemají NULL, ale to nechme na další díly).

Určitě by vás napadl i jiný způsob, jak tohoto výsledku docílit. Jednoduše byste si vybrali nějakou hodnotu jako doposud (třeba jméno), tyto řádky byste si přenesli do své aplikace a spočítali, kolik jmen je. Data byste poté zahodili. Takový přenos je ale zbytečně náročný na databázi a zpomaloval by aplikaci. COUNT přenáší jen jedno jediné číslo. Nikdy nepočítejte pomocí výběru hodnoty, pouze funkcí COUNT!

AVG()

AVG označuje průměr z daných hodnot. Podívejme se, jaký je průměrný počet článků na uživatele:

SELECT AVG(`pocet_clanku`) FROM `uzivatele`;

Výsledek:

5.3226

SUM()

Sum vrací součet hodnot. Podívejme se, kolik článků napsali dohromady lidé narození po roce 1980:

SELECT SUM(`pocet_clanku`) FROM `uzivatele` WHERE `datum_narozeni` > '1980-1-1';

Výsledek:

65

MIN()

Funkce Min() vrátí minimum (nejmenší hodnotu). Najděme nejnižší datum narození:

SELECT MIN(`datum_narozeni`) FROM `uzivatele`;

Výsledek:

1935-05-15

Pozor, pokud bychom chtěli vybrat i jméno a příjmení, tento kód nebude fungovat:

-- Tento kód nebude fungovat
SELECT `jmeno`, `prijmeni`, MIN(`datum_narozeni`) FROM `uzivatele`;

Agregační funkce pracuje s hodnotami více sloupců a vybrané sloupce (jmeno a prijmeni) nebudou nijak souviset s hodnotou MIN(). Problém bychom mohli vyřešit poddotazem nebo ještě jednodušeji se funkcím MIN() a MAX() úplně vyhnout a použít místo nich řazení a LIMIT:

SELECT `jmeno`, `prijmeni`, `datum_narozeni` FROM `uzivatele` ORDER BY `datum_narozeni` LIMIT 1;

Výsledek:

Alfons    Svoboda    1935-05-15

MAX()

Obdobně jako MIN existuje i funkce MAX, najděme maximální počet článků od 1 uživatele:

SELECT MAX(`pocet_clanku`) FROM `uzivatele`;

Výsledek:

18

MySQL má ještě nějaké agregační funkce, ale ty pro nás již nejsou zajímavé.

Seskupování (Grouping)

Položky v databázi můžeme seskupovat podle určitých kritérii. Seskupování používáme téměř vždy spolu s agregačními funkcemi. Pojďme seskupit uživatele podle jména:

SELECT `jmeno` FROM `uzivatele` GROUP BY `jmeno`;

Výsledek:

Alfons
Eva
František
Jan
Jana
...

Vidíme, že každé jméno je zde zastoupeno jen jednou, i když je v databázi vícekrát. Přidejme nyní kromě jména i počet jeho zastoupení v tabulce, uděláme to pomocí agregační funkce COUNT(*)

SELECT `jmeno`, COUNT(*) FROM `uzivatele` GROUP BY `jmeno`;

Výsledek:

Alfons       1
Eva          1
František    1
Jan          2
Jana         1
...

Vidíme, že třeba Jany máme v databázi dva.

AS

Pro zjednodušení si můžeme v dotazu vytvořit aliasy, tedy přejmenovat třeba nějaký dlouhý sloupec, aby byl dotaz přehlednější. S tímto se ještě setkáme u dotazů přes více tabulek, kde je to velmi užitečné. U tabulek AS používáme ke zjednodušení operací uvnitř dotazu. U sloupců se AS používá k tomu, aby aplikace viděla data pod jiným názvem, než jsou skutečně v databázi. To může být užitečné zejména u agregačních funkcí, protože pro ně v databázi není žádný sloupec a mohlo by se nám s jejich výsledkem špatně pracovat. Upravme si poslední dotaz:

SELECT `jmeno`, COUNT(*) AS `pocet` FROM `uzivatele` GROUP BY `jmeno`;

Výsledek:

Použití AS u agregační funkce COUNT v MySQL databázi

Příště si řekneme něco o hodnotě NULL a představíme si tabulku datových typů v MySQL. Následně mám pro vás další testovací data pro redakční systém, abychom mohli dělat pokročilejší dotazy :)


 

  Aktivity (1)

Článek pro vás napsal David Čápka
Avatar
Autor pracuje jako softwarový architekt a pedagog na projektu ITnetwork.cz (a jeho zahraničních verzích). Velmi si váží svobody podnikání v naší zemi a věří, že když se člověk neštítí práce, tak dokáže úplně cokoli.
Unicorn College Autor se informační technologie naučil na Unicorn College - prestižní soukromé vysoké škole IT a ekonomie.

Jak se ti líbí článek?
Celkem (11 hlasů) :
55555


 



 

 

Komentáře

Avatar
Iwitrag
Člen
Avatar
Iwitrag:

DESC je třeba vždy uvést, vidíte, že řazení podle příjmení je normálně sestupné, protože jsme DESC napsali jen k pocet_clanku.
Má tam být vzestupné (což je default, je to i napsané pár řádků výš)...

Jinak, mě osobně přijde trochu "confusing" to s tím MIN a MAX - konkrétně to, že k tomu nemůžeme s agregační funkcí připojit i Jmeno a Prijmeni... (u toho příkladu s AS to už zase jde). Ale snad to pochopím později, až se někdy naučím, jak to cca. funguje uvnitř :)

Odpovědět 6.2.2014 12:49
Učím se ostře vidět.
Avatar
Jan Vargovský
Redaktor
Avatar
Odpovídá na Iwitrag
Jan Vargovský:

agregační f-ce použít můžeš, ale všechny sloupce na které neaplikuješ agrekační f-ci musí být v klauzuli group by.

 
Odpovědět 6.2.2014 15:39
Avatar
Robert
Člen
Avatar
Robert:

S tým AS som to trochu nepochopil.

 
Odpovědět 6. listopadu 19:37
Děláme co je v našich silách, aby byly zdejší diskuze co nejkvalitnější. Proto do nich také mohou přispívat pouze registrovaní členové. Pro zapojení do diskuze se přihlas. Pokud ještě nemáš účet, zaregistruj se, je to zdarma.

Zobrazeno 3 zpráv z 3.