5. diel - PostgreSQL - Radenie, Limit a agregačné funkcie

V minulom dieli seriálu tutoriálov o PostgreSQL databáze sme si pripravili testovacie dáta a naučili sa základy pátraniach, teda príkaz SELECT a niekoľko operátorov. Dnes sa pozrieme na radenie a agregačné funkcie.

Radenie

Doteraz sme nijako neriešili poradie nájdených výsledkov, ktoré nám dotaz SELECT vrátil. Ono vlastne žiadne ani neexistovalo, databázy 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 zrovna hodilo. Keby sme v databáze vykonali nejakú zmenu a zavolali znova ten istý dotaz, poradie by pravdepodobne vyzeralo úplne inak. Databáza nám ale navrá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ázy si s tým vždy nejako poradia. Poďme si vybrať úplne všetkých užívateľov a zoradí ich podľa priezviska. Slúži na to klauzuly ORDER BY (radiť podľa), ktorá sa píše na koniec dotazu:

SELECT jmeno, prijmeni FROM uzivatel ORDER BY prijmeni;

výsledok:

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

V dotaze by samozrejme mohlo byť aj WHERE, 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 užívateľa zoradiť podľa napísaných článkov a tie s rovnakým počtom řaďme ešte podľa abecedy:

SELECT jmeno, prijmeni, pocet_clanku FROM uzivatel ORDER BY pocet_clanku, prijmeni;

výsledok:

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

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 užívateľov podľa počtu článkov. Tí prví ich teda majú najviac, radiť budeme zostupne. Tie sa rovnakým počtom článkov budeme radiť ešte podľa abecedy:

SELECT jmeno, prijmeni, pocet_clanku FROM uzivatel ORDER BY pocet_clanku DESC, prijmeni;

výsledok:

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

DESC treba vždy uviesť, vidíte, že zoradenie podľa priezviska je normálne zostupnej, pretože sme DESC napísali len k pocet_clanku.

Limit

Zostaňme ešte u nášho rebríčka užívateľov podľa počtu článkov. Takto budeme chcieť vypísať 10 najlepších užívateľov. Keď ich bude ale milión, asi nie je dobrý nápad je 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 i radenie. Limit píšeme vždy na koniec dotazu:

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

Skúste si to.

LIMIT a ORDER BY možné používať aj u ďalších príkazov, napr. U DELETE alebo UPDATE. Môžeme si tak poistiť, aby bol vymazaný alebo editovaný vždy len 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 (Počet)

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

SELECT COUNT(*) FROM uzivatel WHERE pocet_clanku > 0;

výsledok:

29

Na COUNT sa pýtame pomocou SELECT, nie je to príkaz, je to funkcia, ktorá sa vykoná nad riadky a jej výsledok je vrátený SELECT. 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ý prenos je ale zbytočne náročný na databázu a spomaľoval by aplikáciu. COUNT prenáša len jedno jediné číslo. Nikdy nepočítajte pomocou výberu hodnoty, iba funkcií COUNT!

AVG ()

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

SELECT AVG(pocet_clanku) FROM uzivatel;

výsledok:

5.3225806451612903

Šum ()

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

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

výsledok:

65

MIN ()

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

SELECT MIN(datum_narozeni) FROM uzivatel;

výsledok:

1935-05-15

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

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

chyba

ERROR:  column "uzivatel.jmeno" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT jmeno, prijmeni, MIN(datum_narozeni) FROM uzivatel;
               ^

********** Error **********

ERROR: column "uzivatel.jmeno" must appear in the GROUP BY clause or be used in an aggregate function
SQL state: 42803
Character: 8

Agregačné funkcie pracuje s hodnotami viac stĺpcov a vybrané stĺpce (jmeno a prijmeni) nebudú nijako súvisieť s hodnotou MIN (). Problém by sme mohli vyriešiť poddotazov alebo ešte jednoduchšie sa funkciám MIN () a MAX () úplne vyhnúť a použiť namiesto nich radenia a LIMIT:

SELECT jmeno, prijmeni, datum_narozeni FROM uzivatel ORDER BY datum_narozeni LIMIT 1;

výsledok:

Alfons    Svoboda    1935-05-15

Max ()

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

SELECT MAX(pocet_clanku) FROM uzivatel;

výsledok:

18

Existuje ešte mnoho ďalších agregačných funkcií, ale na ukážku zatiaľ budú stačiť tie, ktoré sme si uviedli.

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ť užívateľa podľa mena:

SELECT jmeno FROM uzivatel GROUP BY jmeno ORDER BY jmeno;

výsledok:

Alfons
Eva
František
Jan
Jana
...

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úpenie v tabuľke, urobíme to pomocou agregačné funkcie COUNT (*)

SELECT jmeno, COUNT(*) FROM uzivatel GROUP BY jmeno ORDER BY jmeno;

výsledok:

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

Vidíme, že treba Jany máme v databáze dva.

AS ##

Pre zjednodušenie si môžeme v dotaze vytvoriť aliasy, teda premenovať napríklad nejaký dlhý stĺpec, aby bol dotaz prehľadnejšie. S týmto sa ešte stretneme u otázok cez viac tabuliek, kde je to veľmi užitočné. U tabuliek AS používame na zjednodušenie operácií vo vnútri dotazu. U stĺpcov sa AS používa k tomu, aby aplikácia videla dáta pod iným názvom, ako sú skutočne v databáze. To môže byť užitočné najmä u agregačných funkcií, 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ý otázka:

SELECT jmeno, COUNT(*) AS pocet FROM uzivatel GROUP BY jmeno ORDER BY jmeno;

Výsledok (vo výsledku je stĺpec s názvom "počet" a nie "count"):

Nabudúce si povieme niečo o hodnote NULL a predstavíme si tabuľku dátových typov v PostgreSQL. Následne mám pre vás ďalšie testovacie dáta pre redakčný systém, aby sme mohli robiť pokročilejšie otázky .

V nasledujúcom kvíze, Kvíz - Tvorba, mazanie, vkladanie a export dát v PostgreSQL, si vyskúšame nadobudnuté skúsenosti z predchádzajúcich lekcií.