SELECT II

• WHERE
• ORDER BY
  • NULLS FIRST
• COUNT, AVG, MIN, MAX, SUM, STDDEV
• GROUP BY
• HAVING
• LIMIT a OFFSET
• MySQL/MariaDB
• SQLite
• Oracle
  • LIMIT A OFFSET

WHERE

Podmínku WHERE jste se naučili používat již v kaptiole Úprava tabulky. Tuto podmínku, na rozdíl od následujících, využívají i příkazy jako DELETE FROM a UPDATE. Odkaz na tuto podmínku je tady jen pro úplnost.

V této kapitole využiji tabulky z příkladu kapitoly Vytváření relací. Dozvíte se tu spoustu zajímavých a potřebných věcí, které byste měli velmi dobře znát.

ORDER BY

ORDER BY způsobí setřídění řádků podle nějakého sloupce (abecedně nebo podle velikosti čísel). ORDER BY se píše až za podmínku WHERE (pokud ji použijete).

Na začátek ještě vložím jeden řádek do databáze, který se bude v této lekci později hodit.

A teď už příklad k ORDER BY.

Setřídění probíhá implicitně vzestupně – ASC, nebo můžete třídit sestupně – DESC.

Třídit lze podle více sloupců než podle jednoho. V následujícím SELECTu setřídím výsledek nejdříve podle platu (setstupně) a tam kde je plat stejný setřídím záznam podle jména (vzestupně).
Priorita třídění je dána pořadím sloupců za klíčovými slovy ORDER BY.

NULLS FIRST

Podívejte se na následující příklad:

Dá se nějak rozumě vysvětlit, proč je Pan Novy poslední, když jeho plat je NULL? Jestli vás napadlo, že je NULL menší než všechny čísla, tak jste nedávali pozor. NULL znamená, že nevím jaký má Pan Novy plat. Důvot je ten, že Postgres prostě implicitně hodnoty s NULL řadí na konec.

To se dá změnit pomocí klauzule NULLS FIRST:

Pozor! Pokud použijete NULLS FIRST, je jedno jestli řadíte sloupec sestupně (DESC) nebo vzestupně (ASC), hodnoty s NULL budou vždy první.

Za domácí úkol zjistětě z dokumentace, jak se dá explicitně říct, že mají být hodnoty NULL na konci. (Buďte raději paranoidní a implicitnímu nastavení nevěřte. Nikdy nevíte, kdy se může změnit!)

COUNT, AVG, MIN, MAX, SUM, STDDEV

Příkaz SELECT může získat některé „statistické“ údaje pomocí následujících agregačních funkcí: Průměr AVG, minimum MIN, maximum MAX, součet SUM, počet položek COUNT, variace VARIACE a směrodatná odchylka STDDEV. Celý výčet agregačních funkcí najdete v dokumentaci PostgreSQL.

Příkaz vrátil počet řádků, kde je uveden plat, tj. kde plat není NULL. Pokud vás zajímá počet řádků (tj. bez ohledu na to, zda je nebo není v nějakém sloupci NULL), použijte funkci COUNT s hvězdičkou *.

Zkusím přidat zaměstnance, kde neuvedu jeho plat. Nezapomeňte, že 0 je také číslo, takže jej COUNT započítává. Proto je třeba uvést NULL. Počet „platů“ v tabulce se tím nezmění (přestože počet řádků ano).

Další příklad ukazuje funkci MAX:

Všimli jste si něčeho zajímavého? Když jsem popisoval speciální hodnotu NULL, psal jsem, že NULL znamená, že nevím, co tam je. Jak teda můžu vědět, když mám v sloupečku plat jednu hodnotu NULL, jaká je maximální hodnota? Neměl by být výsledek taky NULL (že nevím, jaký je výsledek)?
Věc se má tak, že MAX prostě NULL ignoruje. Jediný případ, kdy by MAX vrátilo NULL by bylo, kdyby bylo NULL ve všech řádcích.
Obdobně to funguje i s ostatními agregačními funkcemi.

Další příklad už asi nepotřebuje komentář:

GROUP BY

Předchozí (agregační) funkce pracují s celou tabulkou a vrátí jeden souhrný řádek. Pomocí podmínky WHERE můžete některé řádky ze zpracování vyřadit, ale výsledek pak bude stále jeden souhrný řádek pro všechny zbylé řádky.

Představte si, že chcete zjistit výsledky pro každé oddělení zvlášť. Jednou z možností je pro každé oddělení použít podmínku WHERE oddeleni_id = číslo oddělení. Tak ale budete muset spustit pro každé oddělení extra SELECT. Samozřejmě to jde jednodušeji (tak si nestěžujte že se musíte učit zase něco nového, ušetří vám to spoustu práce :-).

K tomu, abyste zjistili souhrnná data (maximum, minimum atd.) pro skupiny řádků, slouží klauzule GROUP BY, která řádky seskupí (vytvoří skupiny z řádků obsahujících stejnou hodnotu v seskupovaném sloupci).

V příkladu zobrazím minima, maxima, počet vyplněných platů a počet záznamů oddělení.

Z předchozího příkladu nepoznáte, kterému oddělení patří který řádek. Naštěstí sloupce podle kterých se seskupuje (jsou uvedeny v GROUP BY) můžete přidat do výpisu.

Ještě pár příkladů:

Pokud přeci jen chcete vypsat sloupec, který není v GROUP BY, protože víte že i tak je ve všech řádcích použitých pro výsledný řádek hodnota stejná, použijte třeba funkci MAX. Když víte, že jsou hodnoty stejné, tak MAX nemůže vrátit nic jiného, než tu hodnotu (stejně tak MIN).

V klauzuli GROUP BY můžete uvést více sloupců. Řekněme třeba, že mě zajímá jak často se která výše platu objevuje v závislosti na oddělení:

Poznámka: Pořadí klauzulí WHERE … GROUP BY … ORDER BY nelze zaměňovat.

HAVING

Podmínka HAVING je ekvivalentní podmínce WHERE jen s tím rozdílem, že podmínka WHERE se aplikuje před provedením příkazu GROUP BY (odstraní řádky před zgroupováním) a HAVING se aplikuje až na řádky vytvořené po zgroupování (tedy na řádky výsledku SELECT … GROUP BY).

V prvním příkladu vyberu z tabulky zamestnanci jen ty řádky, které neobsahují oddělení č. 1 a z nich získám souhrnné informace pro ostatní oddělení. V druhém příkladu získám souhrnné informace ze všech oddělení, ale výslednou tabulku omezím na oddělení, které nemají oddeleni_id č. 1.
Dvěma různými cestami tak získám stejný výsledek.

Zajímavější bude následující příklad, kde zobrazím jen ta oddělení, kde je maximální plat větší jak 15000. Vzhledem k tomu, že se omezení týká výsledku agregační funkce, lze toho dosáhnout pouze pomocí HAVING (WHERE se aplikuje před zjištěním tohoto výsledku).

Všiměte si, že zatímco WHERE se píše před GROUP BY, HAVING se píše až za. To není náhoda :-).
PS: Nic vám nebrání používat WHERE i HAVING v jednom SQL dotazu :-).

LIMIT a OFFSET

LIMIT a OFFSET jsou poslední dvě věci, které vám chyběli, abyste mohli pracovat s tabulkami o milionech řádků.
LIMIT omezí počet vrácených řádků a OFFSET přeskočí zadaný počet prvních řádek.

Jejich použití je tak jednoduché a jasné, že bych snad ani nemusel dělat příklad. Ale pro jistotu:

LIMIT i OFFSET musí být kladná celá čísla.
Poznámka: příkaz OFFSET lze použít i bez klauzule LIMIT.

MySQL/MariaDB

Výčet agregačních funkcí MySQL najdete v dokumentaci. Asi vás nepřekvapí, že se od PostgreSQL lyší.

MySQL má trochu jinou strategii pro pojmenování sloupců kde použijete agregační funkce než PostgreSQL. Raději vždy používejte vlastní jména pomocí AS.

MySQL také umožní přidat do SELECTu sloupce, které nejsou součástí GROUP BY. Hodnota v takovém sloupci se vybere náhodně z řádků, které jsou seskupené (v příkladě je hodnota třetího sloupce náhodně vybrána ze všech řádků tabulky). Že je to fuj a že byste měli používat trik s MAX (nebo MIN) asi nemusím dodávat.

MySQL nemá pro ORDER BY klauzuli NULL FIRST, ale může si pomoci jiným trikem: Nejdříve se seřadí sloupec plat podle toho, jestli je nebo není NULL (použije se funkce ISNULL) a pak už podle samotného sloupce plat.

MySQL umí LIMIT a OFFSET jako Postgres (jenom vyžaduje toto pořadí), ale umí ještě něco navíc, co je (bohužel) hojně používáno.
Pokud zapíšete za LIMIT dvě čísla (oddělená čárkou), první se bere jako offset a druhé jako limit.

Typický MySQLák píše takto:

Je to kratší, ale nestandardní.

SQLite

Agregační funkce SQLite také najdete v nápovědě. Zjistíte, že SQLite nemá STDDEV (standardní odchylku) a není moc, co byste stím mohli udělat (leda si tuto funkci doprogramovat – to nežertuji).

SQLite umožňuje přidat sloupeček do SELECTu, který není součástí GROUP BY stejně jako MySQL.

SQLite neumí NULLS FIRST. Můžete ale použít stejný trik, jako u MySQL.

SQLite umí, stejně jako MySQL, mimo standardního LIMIT a OFFSET (pouze v tomto pořadí, pokud použijete obé) i nestandardní offset s limitem pomocí LIMIT offset,limit.

Oracle

Agregační funkce najdete, jako vždy, v dokumentaci.

Oracle neumožňuje SELECTovat sloupeček, který není součástí GROUP BY. Trik s MAX samozřejmě funguje.

Oracle umí NULLS FIRST. Pokud byste ale chtěli použít trik s ORDER BY plat IS NULL, tak to neumí, protože neumí použít plat IS NULL jako součást klauzule SELECT (na rozdíl od ostatních databází).

Můžete použít jiný trik, který bude fungovat všude. Používá ale CASE, který budu vysvětlovat až v příští kapitole. Takže teď ten trik ukáži prozatím bez vysvětlení:

LIMIT a OFFSET

Oracle nemá LIMIT ani OFFSET. To je bezesporu šokující zjištění. Abyste mohli omezit výstupní řádky, budete muset použít trik, který využívá dvě novinky: pseudosloupeček ROWNUM a něco co už umíte, jen o tom nevíte – SELECT ze SELECTu.

Tak nejdřív co je to ten SELECT ze SELECTu. V povídání o CREATE VIEW jste se dozvěděli, že výsledkem každého SELECTu je tabulka. Pomocí CREATE VIEW jste si tuto „tabulku“ pojmenovali a mohli jste z ní SELECTovat. Ono to jde ale i přímo ze SELECTu!

ROWNUM je pseudosloupeček, který obsahuje číslo řádku výsledné tabulky SELECTu. ROWNUM umí ze zde zmiňovaných databází jen Oracle.

ROWNUM je právě to, co se dá použít pro omezení počtu řádků.
Následuje příklad LIMITu:

Pokud by vás napadlo, že by to šlo jednodušeji a vystačili byste si s jedním příkazem, tak nevystačili. Problém je v tom, že při použití ORDER BY se řádky SELECTují v zatím neseřazeném pořadí, už se jim přiděluje ROWNUM a porovnává se ve WHERE podmínce.

Výsledekem je, že sice vyberete 3 řádky seřazené podle příjmení, ale ne první 3 řádky. Vyberete náhodné 3 řádky, které projdou podmínkou WHERE. Až na nich se provede seřazení pomocí ORDER BY.

Jak simulovat OFFSET? Možná vás napadlo toto:

To ale nevrátí žádný řádek. Proč? ROWNUM se přiděluje řádkům, které jsou součástí výsledku dotazu. První řádek má číslo 1, druhý řádek č. 2 atd. Jenže, když první řádek neprojde podmínkou WHERE, stává se z dalšího řádku v řadě řádek první. Takže ten taky neprojde podmínkou WHERE atd. atd.
SELECT s podmínkou WHERE ROWNUM > 1 nikdy nevrátí žádný řádek.

Kvůli OFFSETu se musí provést ještě jeden vnořený SELECT navíc.

Funguje to asi takhle: Dva vnořené SELECTy fungují jako LIMIT. Prostřední SELECT navíc udělá z ROWNUM (toho nejvíce vnořeného SELECTu) regulérní non-pseudo sloupec rnum. V posledním, vnějším SELECTu už se s tímto sloupcem může pracovat jako s jakýmkoliv jiným reálným sloupcem.

Je to hnus, ale je to Oraclem doporučovaný oficiální postup pro LIMIT a OFFSET. Dobrá zpráva aspoň je, že ten úplně vnitřní SELECT nevybere úplně všechny data z disku do paměti, kde by se teprve vybrali požadované řádky. Díky optimalizaci dotazů dokáže Oracle takovéto SELECTy provádět opravdu rychle (jako jakákoliv jiná databáze s LIMITem a OFFSETem).