Lekce 7 - PostgreSQL - Řazení, Limit a agregační funkce

V předešlém cvičení, Řešené úlohy k 6. lekci PostgreSQL, jsme si procvičili nabyté zkušenosti z předchozích lekcí.

V dnešním PostgreSQL tutoriálu si ukážeme řazení (třídění) výsledků, dále limit, seskupování a agregační funkce jako COUNT (počet), SUM, AVG (průměr), MIN a MAX.

Řazení

Doposud jsme nijak neřešili pořadí nalezených položek, které nám dotaz SELECT vrátil. Ono vlastně žádné ani neexistovalo, databáze uvnitř funguje pomocí určitých sofistikovaných pravidel (které jsou nad rámec tohoto seriálu) a vrátila nám položky tak, jak se jí to zrovna hodilo. Kdybychom v databázi provedli nějakou změnu a zavolali znovu ten samý dotaz, pořadí by pravděpodobně vypadalo úplně jinak. Databáze nám ale navrácený výsledek samozřejmě seřadit dokáže.

Řadit můžeme podle kteréhokoli sloupce. Když budeme řadit podle ID, máme položky v pořadí, v jakém byly do databáze vloženy. Dále můžeme řadit podle číselných sloupců, ale i podle těch textových (řadí se podle abecedy). Řadit můžeme i podle datumu a všech dalších datových typů, databáze si s tím vždy nějak poradí.

Pojďme si vybrat úplně všechny uživatele a seřaďme je podle příjmení. Slouží k tomu klauzule ORDER BY (řadit podle), která se píše na konec dotazu:

SELECT jmeno, prijmeni FROM uzivatel ORDER BY prijmeni;

Výsledek:

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

V dotazu by samozřejmě mohlo být i WHERE. Pro jednoduchost jsme vybrali všechny uživatele.

Řadit můžeme podle několika kritérií (sloupců). Pojďme si uživatele seřadit podle napsaných článků a ty se stejným počtem řaďme ještě podle abecedy:

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

Výsledek:

jmeno	prijmeni	pocet_clanku
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
...	...	...

Směr řazení

Určit můžeme samozřejmě i směr řazení. Můžeme řadit vzestupně (výchozí směr) klíčovým slovem ASC a sestupně klíčovým slovem DESC.
Zkusme si udělat žebříček uživatelů podle počtu článků. Ti první jich tedy mají nejvíce, řadit budeme sestupně. Ty se stejným počtem článků budeme řadit ještě podle abecedy:

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

Výsledek:

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

DESC je třeba vždy uvést. Vidíme, že řazení podle příjmení je normálně sestupné, protože jsme DESC napsali jen k pocet_clanku.

Limit

Zůstaňme ještě u našeho žebříčku uživatelů podle počtu článků. Takto budeme chtít vypsat 10 nejlepších uživatelů. Když jich bude ale milion, asi není dobrý nápad je všechny vybrat a pak jich v aplikaci použít jen 10 a těch 999 990 zahodit.

Dáme databázi limit, tedy maximální počet záznamů, které chceme vybrat. Zároveň uvedeme i řazení. Limit píšeme vždy na konec dotazu:

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

LIMIT a ORDER BY lze používat i u dalších příkazů, např. u DELETE nebo UPDATE. Můžeme si tak pojistit, aby byl vymazán nebo editován vždy jen jeden záznam.

Agregační funkce

Databáze nám nabízí spoustu tzv. agregačních funkcí. To jsou funkce, které nějakým způsobem zpracují více hodnot a jako výsledek vrátí hodnotu jednu.

COUNT

Příkladem takové funkce je funkce COUNT(), která vrátí počet řádků v tabulce, splňující nějaká kritéria.

Spočítejme, kolik z uživatelů napsalo alespoň jeden článek:

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

Výsledek:

count
29

Na COUNT() se ptáme pomocí SELECT. COUNT() není příkaz, je to funkce, která se vykoná nad řádky a její výsledek je vrácen operátorem select. Funkce má stejně jako v jiných programovacích jazycích (alespoň ve většině z nich) závorky. Ta hvězdička v nich znamená, že nás zajímají všechny sloupce. Můžeme totiž počítat třeba jen uživatele, kteří mají vyplněné jméno (přesněji kteří ho nemají NULL, ale to nechme na další díly).

COUNT() přenáší jen jedno jediné číslo. Nikdy nepočítáme pomocí výběru hodnoty, čímž bychom zbytečně zatížili databázi. Vždy použijeme funkci COUNT().

AVG()

AVG() označuje průměr z daných hodnot.

Podívejme se, jaký je průměrný počet článků na uživatele:

SELECT AVG(pocet_clanku) FROM uzivatel;

Výsledek:

avg
5.3225806451612903

SUM()

SUM() vrací součet hodnot.

Podívejme se, kolik článků napsali dohromady lidé narození po roce 1980:

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

Výsledek:

avg
65

MIN()

Funkce MIN() vrátí minimum (nejmenší hodnotu).

Najděme nejnižší datum narození:

SELECT MIN(datum_narozeni) FROM uzivatel;

Výsledek:

min
1935-05-15

Pozor, pokud bychom chtěli vybrat i jméno a příjmení, tento kód nebude fungovat:

-- 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;
               ^
SQL state: 42803
Character: 8

Agregační funkce pracuje s hodnotami více sloupců a vybrané sloupce (jmeno a prijmeni) nebudou nijak souviset s hodnotou MIN(). Problém bychom mohli vyřešit poddotazem nebo ještě jednodušeji se funkcím MIN() a MAX() úplně vyhnout a použít místo nich řazení a LIMIT:

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

Výsledek:

jmeno	prijmeni	datum_narozeni
Alfons	Svoboda	1935-05-15

MAX()

Obdobně jako MIN() existuje i funkce MAX().

Najděme maximální počet článků od jednoho uživatele:

SELECT MAX(pocet_clanku) FROM uzivatel;

Výsledek:

max
18

Existuje ještě řada dalších agregačních funkcí, ale na ukázku prozatím budou stačit ty, které jsme si uvedli.

Seskupování (Grouping)

Položky v databázi můžeme seskupovat podle určitých kritérii. Seskupování používáme téměř vždy spolu s agregačními funkcemi.

Pojďme seskupit uživatele podle jména:

SELECT jmeno FROM uzivatel GROUP BY jmeno ORDER BY jmeno;

Výsledek:

jmeno
Alfons
Eva
František
Jan
Jana
...

Vidíme, že každé jméno je zde zastoupeno jen jednou, i když je v databázi vícekrát.

Přidejme nyní kromě jména i počet jeho zastoupení v tabulce pomocí agregační funkce COUNT(*):

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

Výsledek:

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

Vidíme, že třeba Jany máme v databázi dva.

AS

Pro zjednodušení si můžeme v dotazu vytvořit aliasy, tedy přejmenovat třeba nějaký dlouhý sloupec, aby byl dotaz přehlednější. S tímto se ještě setkáme u dotazů přes více tabulek, kde je to velmi užitečné. U tabulek používáme AS ke zjednodušení operací uvnitř dotazu. U sloupců se AS používá k tomu, aby aplikace viděla data pod jiným názvem, než jsou skutečně v databázi. To může být užitečné zejména u agregačních funkcí, protože pro ně v databázi není žádný sloupec a mohlo by se nám s jejich výsledkem špatně pracovat.

Upravme si poslední dotaz:

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

Výsledek:

Ve výsledku vidíme, že se nám název sloupce změnil na pocet.

V následujícím cvičení, Řešené úlohy k 7. lekci PostgreSQL, si procvičíme nabyté zkušenosti z předchozích lekcí.