SQL Agregace a funkce

1. SQL Agregace a funkce Databázové systémy

2. Agregace • agregační klauzule, díky které se řádky dosavadní „tabulky“ výsledku dotazu (tj. po fázi FROM a WHERE) poslučují podle totožných hodnot v definovaných sloupcích do skupin „superřádků“ • výstupem je potom tabulka „superřádků“, kde slučující sloupce mají definované hodnoty původních řádků, ze kterých vznikly (protože byly pro všechny řádky v superřádku stejné), kdežto v ostatních sloupcích by hodnota superřádku byla nejednoznačná (různé hodnoty v původních řádcích), takže pro tyto řádky jsou dvě možnosti • buď se ve výsledku dotazu vůbec nebudou vyskytovat • anebo se jim jedinečná hodnota vyrobí nějakou agregací z hodnot původních

3. Agregace Agregaci si lze představit jako shluknutí několika řádků do jednoho. Pro tuto "skupinu" řádků můžeme pomocí agregačních funkcí používat různé matematické operace. K tomu je zapotřebí rozšířit příkaz SELECT o části GROUP BY případně HAVING. Syntax příkazu SELECT : SELECT seznam sloupců FROM seznam tabulek [WHERE podmínky] [GROUP BY výrazy pro seskupení] [HAVINGpodmínka pro skupiny]

4. Agregační funkce SUM( ) - součet numerických hodnot ve sloupci MIN( ) - minimální hodnota ve sloupci MAX( ) - maximální hodnota ve sloupci COUNT( ) - počet numerických hodnot ve sloupci AVG( ) - aritmetický průměr numerických hodnot ve sloupci Vnořování uvedených funkcí do sebe, např. MIN(SUM()), není ve většině implementací SQL povoleno.

5. Agregační funkce • zobecnění použití agregačních funkcí uvedených dříve (COUNT, MAX, MIN, AVG, SUM), kde výsledkem není jednořádková tabulka (jako v případě nepoužití klauzule GROUP BY), ale tabulka s tolika řádky, kolik je superřádků vzniklých po fázi GROUP BY • z této „superřádkové“ tabulky lze pomocí klauzule HAVING odfiltrovat nezajímavé řádky, podobně jako se pomocí WHERE filtrovaly výsledky pocházející z „FROM-fáze“ • pozor, lze používat pouze agregované hodnoty sloupců

6. Příklad: Agregace (seskupení řádků) Příklad: Mějme tabulku PRAVA_K_UCTU, která bude pro zjednodušení obsahovat data: ucet klient U1K1 U2K1 U2K2 U3 K2 U3 K3 Seskupení můžeme provést podle sloupce ucet nebo klient

7. a) - bez aplikace agregační funkce Ve druhem sloupci by se objevili první z nalezených hodnot odpovídajících prvnímu sloupci: SELECTucet, klient ucet klient FROMprava_k_uctu U1 K1 GROUP BYucet; U2 K1 Výsledek:U1 K1 U2 K2 U2 K1U3 K2 U3 K2 U3 K3 SELECTklient, ucet klient ucet FROMprava_k_uctu K1 U1 GROUP BYklient; K1 U2 Výsledek:K1 U1K2 U2 K2 U2 K2 U3 K3 U3K3 U3

8. b) - s aplikací agregační funkce Použitím funkce, která by sečetla počet záznamů odpovídajících prvnímu sloupci, bychom dostali tabulku: SELECTucet,COUNT(*)ucet klient FROMprava_k_uctuU1 K1 1 GROUP BYucet; U2 K1 2 Výsledek:U1 1 U2 K2 U2 2U3 K2 2 U3 2 U3 K3 SELECTklient,COUNT(*)klient ucet FROMprava_k_uctu K1U1 2 GROUP BYklient; K1 U2 Výsledek:K1 2K2 U2 2 K2 2 K2 U3 K3 1K3 U3 1

9. Součet záznamů - COUNT( ) COUNT(*)je agregační funkcí, která slouží jenom k získání počtu záznamů v rámci jedné skupiny agregovaných řádků. Zajímá-li nás ke kolika účtům má přístup každý z klientů: SELECT k.jmeno, k.prijmeni,COUNT(*) pocet_u FROM klientk INNER JOIN prava_k_uctup ON k.r_cislo=p.klient GROUP BY k.r_cislo; Takovýto výpis není ale pravdivý. Chceme-li získat kompletní výpis, použijeme k tomu např. levé vnější spojení tabulek: SELECT k.jmeno,k.prijmeni,COUNT(p.ucet) pocet_u FROM klientk LEFT JOIN prava_k_uctup ONk.r_cislo=p.klient GROUP BY k.r_cislo;

10. SUM(sloupec) Agregační funkce SUM( ) vrátí součet hodnot v uvedeném sloupci v rámci shluknuté skupiny záznamů. Pokud bychom chtěli součet stavů všech účtů jednotlivých klientů, napsali bychom následující příkaz: SELECT r_cislo,SUM(stav) FROM ucet GROUP BY r_cislo; AVG(sloupec) Funkce AVG( ) počítá aritmetický průměr z vybraných hodnot zadaného sloupce. Chceme-li zjistit průměr stavu účtů jednotlivých klientů: SELECT r_cislo, AVG(stav) FROM ucet GROUP BY r_cislo;

11. MIN(sloupec) Agregační funkce MIN( ) slouží pro vrácení minima z hodnot ve sloupci ze seskupených záznamů. Chceme-li zjistit nejnižší stav účtů klientů: SELECT r_cislo, MIN(stav) FROM ucet GROUP BY r_cislo; MAX(sloupec) Funkce MAX( ) vrací maximum z hodnot zadaného sloupce. Příklad vypíše seznam. Chceme-li zjistit nevyšší stav účtů klientů: SELECT r_cislo, MAX(stav) FROM ucet GROUP BY r_cislo;

12. Další použití agregačních funkcí Na výstup dostaneme všechny skupiny agregovaných záznamů, ale někdy potřebujeme v rámci takových záznamů vybrat jen ty záznamy, které ještě dále budou splňovat další podmínky. Dodatečnou restrikci zapisujeme v části HAVING: Chceme seznam všech klientů, kteří mají přístup k více než jedomuúčtu. Přehled vypište ve formě jméno a příjmení klienta následované počtem účtů, ke kterýmmají přístup: SELECT k.jmeno, k.prijmeni,COUNT(*) pocet_u FROM klientk INNER JOIN prava_k_uctup ON k.r_cislo=p.klient GROUP BY k.r_cislo HAVING count(*)>1;

13. Třídění dle výsledků agregační funkce Chceme-li výpis setřídit podle hodnoty agregační funkce, za klíčové slovo ORDER BY uvedeme agregační funkci: SELECT r_cislo, AVG(stav) FROM ucet GROUP BY r_cislo ORDER BY AVG(stav); Některé databázové systémy mají však omezení, v takovém případě použijeme modifikátor AS: SELECT r_cislo, AVG(stav) AS prumer FROM ucet GROUP BY r_cislo ORDER BY prumer;

14. Agregace bez klíče Pokud výchozí tabulka obsahuje úplná data a nehodláme rozlišovat jednotlivé řádky: SELECT COUNT(s1),SUM(s1),AVG(s1),MIN(s1),MAX(s1) FROM Tabulka; vznikne tabulka obsahující právě jeden řádek s hodnotami agregačních funkcí. Agregace s jednoduchým klíčem Chceme-li použít jeden sloupec tabulky jako klíč ke shlukování řádků do agregátů, stačí uvést klíčové slovo GROUP BY a za ním název agregačního sloupce.

15. Agregace se složeným klíčem Chceme-li použít více sloupců tabulky jako klíč ke shlukování řádků do agregátů, stačí uvést za klíčové slovo GROUP BYnázvy příslušných sloupců oddělené čárkou. Nejde o nic jiného než o popis složeného klíče pro agregaci. Pokud nás zajímá jaké jsou maximální stavy účtů jednotlivých klientů na pobočkách, stačí napsat příkaz: SELECT r_cislo, pobocka,MAX(stav) FROM ucet GROUP BY r_cislo,pobocka ORDER BY r_cislo,pobocka

16. Příklady Kolik klientů má spořitelná? SELECT COUNT(*)FROM Klient Kolik účtů mají jednotliví klienti? SELECT r_cislo, COUNT(*)FROM Ucet GROUP BY r_cislo Jaký byl počet transakcí, maximální, minimální, průměrný a celkový pohyb peněz na účtech? SELECT MAX(castka), MIN(castka), AVG(castka), SUM(castka)FROM Transakce

17. Příklady Kolik peněz mají na svých účtech jednotliví klienti? SELECT k.prijmeni, k.r_cislo, COUNT(u.c_uctu) AS počet,SUM(u.stav) AS celkem FROMKlient AS kLEFT JOIN Ucet AS u ON k.r_cislo=u.r_cislo GROUP BYk.r_cislo Kteří klienti mají na svých účtech více než 50000? SELECT k.prijmeni, k.r_cislo, SUM(u.stav) AS celkem FROMKlient AS kLEFT JOIN Ucet AS u ON k.r_cislo=u.r_cislo GROUP BYk.r_cislo HAVING SUM(u.stav)>50000;

18. Práce s funkcemi Dvě základní skupiny funkcí: Skalárnífunkce, které se většinou aplikují na jeden argument (pole) a vrací opět jednu hodnotu (pole). Agregační funkce, jejichž vstupem jsou množiny řádků (záznamů) a z nich je počítána výsledná hodnota. Funkce můžeme volat ve všech SQL příkazech, zejména pak v dotazech. Chceme-li nějakou funkci použít, musíme dotaz upravit tak, aby bylo jasné, na které sloupce bude použita: V názvech funkcí se nerozlišují velká a malá písmena. Mezi názvem funkce a následující závorkou nesmí být mezera! SELECT FUNKCE(sloupec) FROM Tabulka;

19. Funkce pro práci s textovými řetězci CONCAT(x, y,…) - zřetězení dvou nebo více řetězců (sloupců) do jednoho: SELECT CONCAT(prijmeni," ",jmeno) AS cele_jmeno FROM Klient; V některých systémech se používá možnost zřetězení použitím znaménka „+“ (např. prijmeni+" "+jmeno). LENGTH(sloupec) - vrací délku řetězce; (v některých systémech se můžeme setkat s názvem LEN ). UPPER(sloupec) /LOWER(sloupec) - převedení řetězce na velká/malá písmena. SELECT upper(prijmeni), jmeno FROM Klient;

20. LEFT(sloupec, x) / RIGHT(sloupec, x) - vrací x znaků umístěných úplně vlevo/vpravo v hodnotě uložené ve vybraném sloupci. Výpis prvních dvou znaků rodného čísla : SELECT prijmeni, jmeno, LEFT(r_cislo,2) FROM Klient; Výpis posledních tří znaků čísla účtu: SELECT RIGHT(c_uctu,3) FROM Ucet; SUBSTRING(sloupec, počátek, délka) - funkce vracející nějaký podřetězec, nejtypičtější jejich použití je se třemi parametry. Výpis z každého rodného čísla den narozeni (pátý až šestý znak): SELECT prijmeni,jmeno,SUBSTRING(r_cislo,5,2) FROM Klient;

21. Funkce pro práci s čísly Základní funkce pro práci s čísly: ABS(X) – vrací absolutní hodnota z čísla X, SIN(X) - vrací sinus úhlu X(zadaného v radiánech), COS(X) - vrací kosinus úhlu X, TAN(X) - vrací tangens úhlu X, COT(X) - vrací kotangens úhlu X, EXP(X) - vrací e na X-tou, POWER(X,Y) - vrací X na Y-tou, SELECT POWER(2,3); LN(X) – vrací přirozený logaritmus čísla X – stejné jako LOG(X) SELECT LN(2); LOG(B,X) – vrací logaritmus čísla X pří základu B – existují i LOG2(X), LOG10(X) SELECT LOG(2,65536);SELECT LOG(10,100);

22. MOD(X,Y) – vrací zbytek z podílu X/Y RAND(X,Y) – vrací náhodné číslo (0, 1) Výpis výsledků v náhodném pořadí: SELECT * FROM tabulka ORDER BY RAND(); SIGN(X) - vrací hodnotu určující, zda je číslo kladné (1), záporné (-1), nebo nula (0) SQRT(X) – vypočítá druhou odmocninu čísla X, FORMAT(X, Y) - vrací číslo X formátované jako číslo s Y desetinnými místy. Jako oddělovač je použitá“,“. SELECT CONCAT(FORMAT(stav,2),” Kč”) FROM Ucet; PI( ) – vrací hodnotu konstanty „pi“, ROUND(X,Y) - vrací číslo X zaokrouhlené Y desetinných míst SELECT ROUND(1.864,1); → 1.9

23. CEILING(X) – vrací nejvyšší celé číslo založené na hodnotě čísla X (tzn. zaokrouhlení na celé číslo nahoru) SELECT CEILING(1.864); → 2 FLOOR(X) – vrací celočíselnou část čísla X (tzn. zaokrouhlení na celé číslo dolu) SELECT FLOOR(1.864); → 1 TRUNCATE(X,Y) – vrací číslo X oříznuté na Y desetinných míst. Jestliže Y je 0, pak výsledek nemá desetinnou část. Y může být i záporné. SELECT TRUNCATE(1.864,1); → 1.8 SELECT TRUNCATE(122,-2); → 100

24. Funkce pro práci s datumem a časem NOW( ) – vrací aktuální datum a čas, CURDATE( ) – vrací aktuální datum, CURTIME( ) – vrací aktuální čas, HOUR(sloupec) – vrací z uložené hodnoty pouze údaj o hodině, MINUTE(sloupec) – vrací z uložené hodnoty pouze údaj o minutě, SECOND(sloupec) – vrací z uložené hodnoty pouze údaj o sekundě, DAYNAME(sloupec) – vrací z uložené hodnoty pouze název dne, DAYOFMONTH(sloupec) – vrací z uložené hodnoty pouze údaj o dnu v měsíci, MONTH(sloupec) – vrací z uložené hodnoty pouze údaj o měsíci, MONTHNAME(sloupec) – vrací z uložené hodnoty pouze název měsíce, YEAR(sloupec) – vrací z uložené hodnoty pouze údaj o roku,

25. ADDDATE(sloupec, INTERVAL x typ) – vrací hodnotu sloupce zvýšenou o x jednotek - jako typ se může použít SECOND, MINUTE, HOUR, DAY, MONTH, YEAR (nebo je kombinovat) Např. přidejte k datumu transakce 3 dny: ADDDATE(datum, INTERVAL 3 DAY); - nebo DATE_ADD() SUBDATE(sloupec, INTERVAL x typ) – vrací hodnotu sloupce sníženou o x jednotek (DATE_SUB()) DATEDIFF(datum1,datum2) – vrací počet dnů mezi dvěma datumy. Počítá se pouze s datumem. SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30'); SELECT DATEDIFF(NOW(),'2008-01-01');

26. DATE_FORMAT(sloupec, ‘formátovací_řetězec‘) – vrací naformátovaný datum a čas. TIME_FORMAT(sloupec, ‘formátovací řetězec‘) – vrací naformátovaný čas Formátovací_řetězec může obsahovat libovolnou kombinaci formátovacích kódů a znaků procenta:

27. SELECT c_uctu,castka,DATE_FORMAT(datum,"%d.%m.%Y") FROM Transakce ORDER BY datum;

28. GET_FORMAT(datový_typ, ‘formátovací konstanta‘) – vrací ‘formátovací řetězec‘. - datový_typ může být pouze: : DATE, TIME, DATETIME a TIMESTAMP - ‘formátovací konstanta‘: 'EUR', 'USA', 'JIS', 'ISO', and 'INTERNAL'. Např. GET_FORMAT(DATE,'EUR') '%d.%m.%Y' SELECT DATE_FORMAT('2008-10-03' , GET_FORMAT(DATE,'EUR')); EXTRACT(sloupec, INTERVAL x typ) – vrací hodnotu vyextrahovanou z datumu. SELECT EXTRACT(YEAR FROM '2008-07-02'); → 2008 SELECT EXTRACT(YEAR_MONTH FROM NOW());

29. Konverzní funkce Obecně se jedná o převodní funkce z některých typů na číslo nebo řetězec. MySQL automaticky mění čísla na řetězce (a naopak). SELECT 1+'1'; → 2 Chceme-li změnit číslo na řetězec explicitně, můžeme použít funkce CAST() anebo CONCAT(): SELECT 38.8, CAST(38.8 AS CHAR); → 38.8, '38.8‚ SELECT 38.8, CONCAT(38.8); → 38.8, '38.8' CONV(N,z_báze,do_báze) – mění čísla s různou bázi (základem). SELECT CONV('a',16,2); → '1010‚ Pro změnu řetězce na datum se používá funkce: STR_TO_DATE(řetězec, formát) – vrací datum ve specifikovaném formátu SELECT STR_TO_DATE('2008-10-31', '%d.%m.%Y');