7. diel - MySQL krok za krokom - Radenie, limit a agregačné funkcie

V predchádzajúcom cvičení, Riešené úlohy k 6. lekcii MySQL/MariaDB, sme si precvičili získané skúsenosti z predchádzajúcich lekcií.

V dnešním tutoriále si v MySQL ukážeme radenie (triedenie) výsledkov, ďalej limit, zoskupovanie a agregačné funkcie ako COUNT() (počet), SUM() (súčet), AVG() (priemer), MIN() a MAX().

Radenie

Doteraz sme nijako neriešili poradie nájdených položiek, ktoré nám dotaz SELECT vrátil. Ono vlastne ani žiadne neexistovalo, databáza vo vnútri funguje pomocou určitých sofistikovaných pravidiel (ktoré sú nad rámec tohto seriálu) a vrátila nám položky tak, ako sa jej to práve hodilo. Keby sme v databáze vykonali nejakú zmenu a zavolali znovu ten istý dotaz, poradie by pravdepodobne vyzeralo úplne inak. Databáza nám ale vrátený výsledok samozrejme zoradiť dokáže.

Radiť môžeme podľa ktoréhokoľvek stĺpca. Keď budeme radiť podľa id, máme položky v poradí, v akom boli do databázy vložené. Ďalej môžeme radiť podľa číselných stĺpcov, ale aj podľa tých textových (radia sa podľa abecedy). Radiť môžeme aj podľa dátumu a všetkých ďalších dátových typov, databáza si s tým vždy nejako poradí. Poďme si vybrať úplne všetkých používateľov a zoraďme ich podľa priezviska. Slúži na to klauzula ORDER BY (zoradiť podľa), ktorá sa píše na koniec dotazu:

SELECT `first_name`, `last_name` FROM `users` ORDER BY `last_name`

Výsledok:

Patricia     Adams
James        Baker
Donald       Brown
David        Clark
Michelle     Davis
Lisa         Green
...

V dotaze by samozrejme mohlo byť aj WHERE, pre jednoduchosť sme vybrali všetkých používateľov.

Zoraďovať môžeme podľa niekoľkých kritérií (stĺpcov), poďme si používateľov zoradiť podľa napísaných článkov a tých s rovnakým počtom zaraďme ešte podľa abecedy:

SELECT `first_name`, `last_name`, `article_count` FROM `users` ORDER BY `article_count`, `last_name`

Výsledok:

Michelle     Davis        0
Charles      Lopez        0
Aron         Heaven       1
Daniel       Joens        1
Paul         Lee          1
Betty        Miller       1
Jenifer      Williams     1
Donald       Brown        2
Lucy         Harris       2
Anthony      Jakson       2
...

Smer radenia

Určiť môžeme samozrejme aj smer radenia. Môžeme radiť vzostupne (predvolený smer) kľúčovým slovom ASC a zostupne kľúčovým slovom DESC. Skúsme si urobiť rebríček používateľov podľa počtu článkov. Tí prví ich teda majú najviac, radiť budeme zostupne. Tých s rovnakým počtom článkov budeme radiť ešte podľa abecedy:

SELECT `first_name`, `last_name`, `article_count` FROM `users` ORDER BY `article_count` DESC, `last_name`;

Výsledok:

Jeff     Moore     18
John     Smith     17
Donna    Johnson   12
Steven   Murphy    12
Paul     Walker    9
Carol    Young     9
...

DESC treba vždy uviesť, vidíte, že radenie podľa priezviska je normálne vzostupné, pretože sme DESC napísali len k article_count.

Limit

Zostaňme ešte pri našom rebríčku používateľov podľa počtu článkov. Takto budeme chcieť vypísať 10 najlepších používateľov. Keď ich bude ale milión, asi nie je dobrý nápad ich všetkých vybrať a potom ich v aplikácii použiť len 10 a tých 999 990 zahodiť. Dáme databáze limit, teda maximálny počet záznamov, ktoré chceme vybrať. Zároveň uvedieme aj radenie. Limit píšeme vždy na koniec dotazu:

SELECT `first_name`, `last_name`, `article_count` FROM `users` ORDER BY `article_count` DESC, `last_name` LIMIT 10;

Skúste si to.

LIMIT a ORDER BY je možné používať aj pri ďalších príkazoch, napr. pri DELETE alebo UPDATE. Môžeme si tak poistiť, aby bol vymazaný alebo editovaný vždy iba jeden záznam.

Agregačné funkcie

Databáza nám ponúka množstvo tzv. agregačných funkcií. To sú funkcie, ktoré nejakým spôsobom spracujú viac hodnôt a ako výsledok vrátia hodnotu jednu.

COUNT()

Príkladom takejto funkcie je COUNT(), ktorá vráti počet riadkov v tabuľke spĺňajúcich nejaké kritériá. Spočítajme, koľko používateľov napísalo aspoň jeden článok:

SELECT COUNT(*) FROM `users` WHERE `article_count` > 0;

Výsledok:

29

Na COUNT() sa pýtame pomocou SELECT, nie je to príkaz, je to funkcia, ktorá sa vykoná nad riadkami a jej výsledok je vrátený selectom. Funkcia má rovnako ako v iných programovacích jazykoch (aspoň vo väčšine z nich) zátvorky. Tá hviezdička v nich znamená, že nás zaujímajú všetky stĺpce. Môžeme totiž počítať napríklad len používateľov, ktorí majú vyplnené meno (presnejšie ktorí ho nemajú NULL, ale to nechajme na ďalšie diely).

Určite by vám napadol aj iný spôsob, ako tento výsledok dosiahnuť. Jednoducho by ste si vybrali nejakú hodnotu ako doteraz (napríklad meno), tieto riadky by ste si preniesli do svojej aplikácie a spočítali, koľko je mien. Dáta by ste potom zahodili. Takýto prenos je ale zbytočne náročný na databázu a spomaľoval by aplikáciu. COUNT() prenáša iba jedno jediné číslo. Nikdy nepočítajte pomocou výberu hodnoty, iba funkciou COUNT()!

AVG()

AVG() označuje priemer z daných hodnôt. Pozrime sa, aký je priemerný počet článkov na používateľa:

SELECT AVG(`article_count`) FROM `users`;

Výsledok:

5.3226

SUM()

Funkcia SUM() vracia súčet hodnôt. Pozrime sa, koľko článkov napísali dohromady ľudia narodení po roku 1980:

SELECT SUM(`article_count`) FROM `users` WHERE `birthdate` > '1980-1-1';

Výsledok:

65

MIN()

Funkcia MIN() vráti minimum (najmenšiu hodnotu). Nájdime najnižší dátum narodenia:

SELECT MIN(`birthdate`) FROM `users`;

Výsledok:

1935-05-15

Pozor, ak by sme chceli vybrať aj meno a priezvisko, tento kód nebude fungovať:

-- This code won't work
SELECT `first_name`, `last_name`, MIN(`birthdate`) FROM `users`;

Agregačná funkcia pracuje s hodnotami viacerých stĺpcov a vybrané stĺpce (first_name a last_name) nebudú nijako súvisieť s hodnotou MIN(). Problém by sme mohli vyriešiť poddotazom alebo ešte jednoduchšie sa funkciám MIN() a MAX() úplne vyhnúť a použiť namiesto nich radenie a LIMIT:

SELECT `first_name`, `last_name`, `birthdate` FROM `users` ORDER BY `birthdate` LIMIT 1;

Výsledok:

Linda Martin    1935-05-15

MAX()

Podobne ako MIN() existuje aj funkcia MAX(), nájdime maximálny počet článkov od 1 používateľa:

SELECT MAX(`article_count`) FROM `users`;

Výsledok:

18

MySQL má ešte nejaké agregačné funkcie, ale tie pre nás už nie sú zaujímavé.

Zoskupovanie (Grouping)

Položky v databáze môžeme zoskupovať podľa určitých kritérií. Zoskupovanie používame takmer vždy spolu s agregačnými funkciami. Poďme zoskupiť používateľov podľa mena:

SELECT `first_name` FROM `users` GROUP BY `first_name`;

Výsledok:

Anthony
Aron
Barbara
Betty
Carol
...

Vidíme, že každé meno je tu zastúpené len raz, aj keď je v databáze viackrát. Pridajme teraz okrem mena aj počet jeho zastúpení v tabuľke, urobíme to pomocou agregačnej funkcie COUNT(*):

SELECT `first_name`, COUNT(*) FROM `users` GROUP BY `first_name`;

Výsledok:

...
Jeff        1
Jenifer     1
John        2
Joseph      1
Linda       1
...

AS

Pre zjednodušenie si môžeme v dotaze vytvoriť aliasy, teda premenovať napríklad nejaký dlhý stĺpec, aby bol dotaz prehľadnejší. S týmto sa ešte stretneme pri dotazoch cez viacero tabuliek, kde je to veľmi užitočné. Pri tabuľkách používame AS na zjednodušenie operácií vo vnútri dotazu. Pri stĺpcoch sa AS používa na to, aby aplikácia videla dáta pod iným názvom, než sú skutočne v databáze. To môže byť užitočné najmä pri agregačných funkciách, pretože pre ne v databáze nie je žiaden stĺpec a mohlo by sa nám s ich výsledkom zle pracovať. Upravme si posledný dotaz:

SELECT `first_name`, COUNT(*) AS `count` FROM `users` GROUP BY `first_name`;

Výsledok:

first_name	count
Anthony	1
Aron	1
Barbara	1
Betty	1
Carol	1
Charles	1
Daniel	1
...	...

V nasledujúcom cvičení, Riešené úlohy k 7. lekcii MySQL/MariaDB, si precvičíme nadobudnuté skúsenosti z predchádzajúcich lekcií.