Lekce 7 - SQLite - Řazení, Limit a agregační funkce
V minulé lekci, SQLite - Import, jsme si ukázali import databáze.
V tomto SQLite tutoriálu se podíváme na řazení a agregační funkce.
Pracovat budeme s tabulkou uživatelů z
databaze_pro_web, kterou jsme si připravili v lekci SQLite - Výběr dat
(vyhledávání).
Ř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 data 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 "uzivatele" ORDER BY "prijmeni";
Výsledek:
Václav Blažek Ondřej Bohatý Vítezslav Churý Pavel Dušín Petr Dvořák ...
V dotazu by samozřejmě mohlo být i WHERE, pro jednoduchost
jsme vybrali všechny uživatele.
Určitě si všimnete, že nám tam chybí Marie Černá a další, kteří se nacházejí až na konci. Je to dáno pouze částečnou podporou UTF-8, aby SQLite zůstalo malé. Buď si to můžeme dopsat, nebo necháme řazení až na kódu v aplikaci.
Ř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 "uzivatele" ORDER BY "pocet_clanku", "prijmeni";
Výsledek:
Matěj Horák 0 Michal Krejčí 0 Miroslav Kučera 1 Vladimír Pokorný 1 Jana Veselá 1 František Veselý 1 Petr Černý 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 "uzivatele" ORDER BY "pocet_clanku" DESC, "prijmeni";
Výsledek:
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ě vzestupné, 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í. Příkaz LIMIT píšeme vždy na konec dotazu:
SELECT "jmeno", "prijmeni", "pocet_clanku" FROM "uzivatele" 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.
Funkce 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 "uzivatele" WHERE "pocet_clanku" > 0;
Výsledek:
29
Na COUNT() se ptáme pomocí SELECT, není to
příkaz, je to funkce, která se vykoná nad řádky a její výsledek je
vrácen příkazem 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).
Určitě bychom našli i jiný způsob, jak tohoto výsledku
docílit. Jednoduše bychom vybrali nějakou hodnotu jako doposud (třeba
jméno), tyto řádky si přenesli do své aplikace a spočítali, kolik jmen
je. Data bychom poté zahodili. Takový přenos je ale zbytečně náročný na
databázi a zpomaloval by aplikaci. Funkce COUNT() přenáší jen
jedno jediné číslo. Nikdy nepočítejte pomocí výběru hodnoty, pouze
funkcí COUNT()!
Funkce 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 "uzivatele";
Výsledek:
5.32258
Funkce SUM()
SUM() vrací součet hodnot. Podívejme se, kolik článků
napsali dohromady lidé narození po roce 1980:
SELECT SUM("pocet_clanku") FROM "uzivatele" WHERE "datum_narozeni" > '1980-1-1';
Výsledek:
65
Funkce MIN()
Funkce MIN() vrátí minimum (nejmenší hodnotu). Najděme
nejnižší datum narození:
SELECT MIN("datum_narozeni") FROM "uzivatele";
Výsledek:
1935-5-15
Funkce 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 "uzivatele";
Výsledek:
18
SQLite má ještě nějaké agregační funkce, ale ty pro nás již nejsou zajímavé.
Seskupování (Grouping)
Položky v databázi můžeme seskupovat podle určitých kritérií. 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 "uzivatele" GROUP BY "jmeno";
Výsledek:
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, uděláme to pomocí agregační funkce COUNT():
SELECT "jmeno", COUNT(*) FROM "uzivatele" GROUP BY "jmeno";
Výsledek:
Alfons 1 Eva 1 František 1 Jan 2 Jana 1 ...
Vidíme, že třeba Jany máme v databázi dva.
Aliasy
Pro zjednodušení si můžeme v dotazu vytvořit aliasy pomocí klíčového
slova AS, 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 AS používáme 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 "uzivatele" GROUP BY "jmeno";
Výsledek:
jmeno pocet ================ Alfons 1 Eva 1 František 1 Jan 2 Jana 1 Jaroslav 1 Jiří 1 Josef 1 Kateřina 1
V následujícím cvičení, Řešené úlohy k 7. lekci SQLite, si procvičíme nabyté zkušenosti z předchozích lekcí.
