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 kurze MS-SQL pozrieme na radenie a agregačné funkcie.

Radenie

Doteraz sme nijako neriešili poradie nájdených položiek, ktoré nám dopyt SELECT vrátil. Niektoré databázy neudržujú žiadne poradie prvkov a vracajú ich tak, ako sa im to hodí. MS-SQL je výnimka a výsledky vracia vždy zoradené podľa Id. Databáza nám ale vrátený výsledok samozrejme zoradiť dokáže, keď to povieme.

Radiť môžeme podľa ktoréhokoľvek stĺpca. Keď budeme radiť podľa Id alebo ponecháme východiskové radenie, 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 dopytu:

SELECT [FirstName], [LastName] FROM [Users] ORDER BY [LastName];

Výsledok:

Meno	Priezvisko
Tadeáš	Alan
Gabriela	Andrášová
Martina	Balogová
Laura	Britáková
Anton	Burdák
Marek	Capko
...	...

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

Radiť môžeme podľa niekoľkých kritérií (stĺpcov). Poďme si používateľa zoradiť podľa napísaných článkov a tie s rovnakým počtom raďme ešte podľa abecedy:

SELECT [Meno], [Priezvisko], [Pocetclankov] FROM [Uzivatelia] ORDER BY [Pocetclankov];

Výsledok:

Meno	Priezvisko	Počet článkov
František	Janák	0
Michal	Ostrý	0
Anton	Burdák	1
Helena	Dolná	1
Barbora	Mrázová	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 [Meno], [Priezvisko], [Pocetclankov] FROM [Uzivatelia] ORDER BY [Pocetclankov] DESC, [Priezvisko];

Výsledok:

Meno	Priezvisko	Počet článkov
Karol	Svitko	17
Ján	Smelý	17
Barbara	Gorálová	14
Jozef	Malý	9
...	...	...

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

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() (Počet)

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 [Uzivatelia] 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ý 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 užívateľov, ktorí majú vyplnené meno (presnejšie ktorí ho nemajú NULL, ale to nechajme na ďalšie lekcie).

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 na databázu zbytočne náročný 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() 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 [Uzivatelia];

Výsledok:

6

SUM()

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

SELECT SUM([Pocetclankov]) FROM [Uzivatelia] WHERE [Datumnarodenia] > '1980-12-31';

Výsledok:

77

MIN()

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

SELECT MIN([Datumnarodenia]) FROM [Uzivatelia];

Výsledok:

1942-10-17

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

-- This code will not work
SELECT [Meno], [Priezvisko], MIN([Datumnarodenia]) FROM [Uzivatelia];

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ť poddopytom 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 [Uzivatelia] ORDER BY [Datumnarodenia];

Výsledok:

Meno	Priezvisko	Dátum narodenia
Tomáš	Veselý	1942-10-17

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 [Uzivatelia];

Výsledok:

17

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ľa podľa mena:

SELECT [Meno] FROM [Uzivatelia] GROUP BY [Meno];

Výsledok:

Meno
Andrea
Anton
Arnold
Barbora
Betka
...

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 [Meno], COUNT(*) FROM [Uzivatelia] GROUP BY [Meno];

Výsledok:

FirstName
Andrea	1
Anton	1
Arnold	1
Barbora	1
...
Ján	2
...

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

AS

Pre zjednodušenie si môžeme v dopyte vytvoriť aliasy, teda premenovať napríklad nejaký dlhý stĺpec, aby bol dopyt prehľadnejší. S týmto sa ešte stretneme pri dopytoch 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 dopytu. 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ý dopyt:

SELECT [Meno], COUNT(*) AS [Pocet] FROM [Uzivatelia] 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í.