7. diel - MS-SQL krok za krokom: Radenie, Limit a agregačné funkcie

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

Dnes sa v MS-SQL tutoriáli pozrieme na radenie a agregačné funkcie.

Radenie

Doteraz sme nijako neriešili poradie nájdených položiek, ktoré nám dotaz SELECT vrátil. Poradie položiek však bez explicitného určenia nie je zaručené. Väčšina databáz neudržiava žiadne stabilné poradie položiek a vracia ich tak, ako sa im to práve hodí. MS-SQL nie je výnimkou a výsledky nevracia vždy zoradené podľa Id, ako by sa mohlo zdať. Databáza nám však vrátený výsledok samozrejme zoradiť dokáže, keď si o to povieme.

Radiť môžeme podľa ktoréhokoľvek stĺpca. Keď budeme radiť podľa nášho (číselného) Id s nastavenou Identity Specification, 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 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 použí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 [Meno], [Priezvisko] FROM [Pouzivatelia] ORDER BY [Priezvisko];

Výsledok:

Meno	Priezvisko
Václav	Baláž
Ondrej	Bohatý
Víťazoslav	Chudý
Mária	Čierna
Peter	Čierny
Pavol	Dušín
...	...

V dotaze by samozrejme mohlo byť aj WHERE (písalo by sa pred ORDER BY), pre jednoduchosť sme vybrali všetkých používateľov.

Radenie podľa viacerých stĺpcov

Radiť 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 zoraďme ešte podľa abecedy:

SELECT [Meno], [Priezvisko], [PocetClankov]
FROM [Pouzivatelia]
ORDER BY [PocetClankov], [Priezvisko];

Výsledok:

Meno	Priezvisko	PocetClankov
Matej	Horák	0
Michal	Krajčí	0
Peter	Čierny	1
Miroslav	Kučera	1
Vladimír	Pokorný	1
Jana	Veselá	1
František	Veselý	1
Ľudmila	Dvorská	2
Viera	Slobodová	2
Juraj	Veselý	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 [Meno], [Priezvisko], [PocetClankov]
FROM [Pouzivatelia]
ORDER BY [PocetClankov] DESC, [Priezvisko];

Výsledok:

Meno	Priezvisko	PocetClankov
Peter	Dvorský	18
Ján	Kováč	17
Tomáš	Horváth	12
Eva	Kučerová	12
Pavol	Dušín	9
Otakar	Polák	9
...	...	...

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

Limit

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

S limitovaním počtu záznamov sme sa už stretli v podobe kľúčového slova TOP, ktoré uvádzame s požadovaným počtom hneď za kľúčovým slovom SELECT. Skúsme si to:

SELECT TOP 5 [Meno], [Priezvisko], [PocetClankov]
FROM [Pouzivatelia]
ORDER BY [PocetClankov] DESC, [Priezvisko];

Výsledok:

Meno	Priezvisko	PocetClankov
Peter	Dvorský	18
Ján	Kováč	17
Tomáš	Horváth	12
Eva	Kučerová	12
Pavol	Dušín	9

TOP možno použiť aj bez toho, aby sme záznamy zoradili pomocou klauzuly ORDER BY. Potom však nie je zaručené poradie záznamov a nemôžeme sa spoliehať na to, že nám príkaz SELECT bude vždy vracať rovnaký výsledok.

Preskočenie záznamov

Čo keby sme však chceli naopak prvých päť používateľov rebríčka preskočiť a vybrať napríklad iba nasledujúcich troch? V tom prípade by sme použili klauzuly OFFSET a FETCH, ktoré uvádzame na koniec dotazu:

SELECT [Meno], [Priezvisko], [PocetClankov]
FROM [Pouzivatelia]
ORDER BY [PocetClankov] DESC, [Priezvisko]
OFFSET 5 ROWS
FETCH NEXT 3 ROWS ONLY;

Hovoríme, že chceme „odsadiť prvých päť riadkov a načítať iba tri nasledujúce“. Výsledok:

Meno	Priezvisko	PocetClankov
Otakar	Polák	9
Pavol	Kráľ	8
Jaroslav	Novotný	8

Keby sme chceli iba preskočiť prvých 5 záznamov a získať všetky nasledujúce, klauzulu FETCH by sme vynechali.

Klauzuly OFFSET a FETCH možno použiť iba pri súčasnom zoradení záznamov pomocou klauzuly ORDER BY.

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 jednu hodnotu.

Funkcia COUNT()

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

SELECT COUNT(*) FROM [Pouzivatelia] WHERE [PocetClankov] > 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ý príkazom SELECT. Funkcia má rovnako ako v iných programovacích jazykoch (aspoň vo väčšine z nich) zátvorky. Hviezdička v nich znamená, že nás zaujímajú všetky stĺpce. Môžeme totiž počítať napríklad iba používateľov, ktorí majú vyplnené meno (presnejšie ktorí ho nemajú NULL, ale to nechajme na ďalšie lekcie).

Nesprávne alternatívne riešenie

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 mien je. Dáta by ste potom zahodili. Takýto prenos je však 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 pomocou funkcie COUNT()!

Funkcia AVG()

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

SELECT AVG([PocetClankov]) FROM [Pouzivatelia];

Výsledok:

5

Funkcia SUM()

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

SELECT SUM([PocetClankov]) FROM [Pouzivatelia] WHERE [DatumNarodenia] > '1980-12-31';

Výsledok:

57

Funkcia MIN()

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

SELECT MIN([DatumNarodenia]) FROM [Pouzivatelia];

Výsledok:

1935-05-15

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

-- Tento kód nebude fungovať
SELECT [Meno], [Priezvisko], MIN([DatumNarodenia]) FROM [Pouzivatelia];

Agregačná funkcia pracuje s hodnotami viacerých stĺpcov a vybrané stĺpce (Meno a Priezvisko) 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 TOP:

SELECT TOP 1 [Meno], [Priezvisko], [DatumNarodenia] FROM [Pouzivatelia] ORDER BY [DatumNarodenia];

Výsledok:

Meno	Priezvisko	DatumNarodenia
Alfonz	Sloboda	1935-05-15

Funkcia MAX()

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

SELECT MAX([PocetClankov]) FROM [Pouzivatelia];

Výsledok:

18

MS-SQL 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 [Meno] FROM [Pouzivatelia] GROUP BY [Meno];

Výsledok:

Meno
Alfonz
Eva
František
Ján
Jana
...

Vidíme, že každé meno je tu zastúpené iba 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 [Meno], COUNT(*) FROM [Pouzivatelia] GROUP BY [Meno];

Výsledok:

Meno
Alfonz	1
Eva	1
František	1
Ján	2
Jana	1
...

Vidíme, že napríklad Jánov máme v databáze dvoch.

Zoskupovanie podľa viacerých stĺpcov

Zoskupovať môžeme aj podľa viacerých stĺpcov. Keby sme chceli používateľov zoskupiť nielen podľa mena, ale aj podľa priezviska, dotaz by vyzeral nasledovne:

SELECT [Meno], [Priezvisko], COUNT(*) FROM [Pouzivatelia] GROUP BY [Meno], [Priezvisko];

Všetky požadované stĺpce sme oddelené čiarkami uviedli v klauzule GROUP BY. Do rovnakej skupiny tak budú zaradení všetci používatelia so zhodnou kombináciou hodnôt v uvedených stĺpcoch.

Pri použití zoskupovania možno príkazom SELECT vybrať iba stĺpce uvedené v klauzule GROUP BY a stĺpce, ktoré vzniknú pomocou agregačnej funkcie.

Klauzula AS

Na 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 viac 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 ne v databáze nie je žiadny stĺpec a mohlo by sa nám s ich výsledkom zle pracovať.

Upravme si predposledný dotaz a premenujme si stĺpec s agregovaným počtom používateľov na Pocet:

SELECT [Meno], COUNT(*) AS [Pocet] FROM [Pouzivatelia] GROUP BY [Meno];

Výsledok:

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