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,
suskupovanie 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 žiadne ani 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 (radí 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 užívateľov a zoraďme ich podľa priezviska. Slúži na to
klauzula ORDER BY
(radiť 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 užívateľov.
Zoradiť môžeme podľa niekoľkých kritérií (stĺpcov), poďme si užívateľov zoradiť podľa napísaných článkov a tie 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. Tie
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 uží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šetky vybrať a potom ich v aplikácii použiť len 10 a tých 999 990 zahodiť. Dáme databázu 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áti hodnotu jednu.
COUNT()
Príkladom takejto funkcie je funkcia COUNT()
, ktorá vráti
počet riadkov v tabuľke, spĺňajúca nejaké kritériá. Spočítajme, koľko
z uží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ás napadol aj iný spôsob, ako tento výsledok
docieliť. 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 mien je. 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()
Funkcie 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 uží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ľa 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 AS
používame 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 nich v databáze
nie je žiadny stĺpec a mohlo by sa nám s ich výsledkom zle pracovať.
Upravme si poslednú príklad:
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í.