SQL Agregace a funkce

Databázové

systémy

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

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í] [HAVING podmínka pro skupiny]

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.

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ů

Příklad: Agregace (seskupení

řádků)

Příklad: Mějme tabulku PRAVA_K_UCTU, která bude pro zjednodušení obsahovat data:

ucet klientU1 K1U2 K1U2 K2U3 K2U3 K3

Seskupení můžeme provést podle sloupce ucet nebo klient

a) - bez aplikace agregační

funkce Ve druhem sloupci by se objevili první z nalezených hodnot odpovídajících prvnímu sloupci:SELECT ucet, klient ucet klientFROM prava_k_uctu U1 K1GROUP BY ucet; U2 K1Výsledek: U1 K1 U2 K2

U2 K1 U3 K2U3 K2 U3 K3

SELECT klient, ucet klient ucetFROM prava_k_uctu K1 U1GROUP BY klient; K1 U2Výsledek: K1 U1 K2 U2

K2 U2 K2 U3K3 U3 K3 U3

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:SELECT ucet,COUNT(*) ucet klientFROM prava_k_uctu U1 K1 1GROUP BY ucet; U2 K1 2Výsledek: U1 1 U2 K2

U2 2 U3 K2 2U3 2 U3 K3

SELECT klient,COUNT(*) klient ucetFROM prava_k_uctu K1 U1 2GROUP BY klient; K1 U2Výsledek: K1 2 K2 U2 2

K2 2 K2 U3K3 1 K3 U3 1

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_uFROM klient k INNER JOIN prava_k_uctu p ON k.r_cislo=p.klientGROUP 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_uFROM klient k LEFT JOIN prava_k_uctu p ON k.r_cislo=p.klientGROUP BY k.r_cislo;

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:

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, SUM(stav)FROM ucetGROUP BY r_cislo;

SELECT r_cislo, AVG(stav)FROM ucet GROUP BY r_cislo;

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ů:

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, MIN(stav)FROM ucetGROUP BY r_cislo;

SELECT r_cislo, MAX(stav)FROM ucetGROUP BY r_cislo;

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ým mají přístup:

Další použití agregačních funkcí

SELECT k.jmeno, k.prijmeni, COUNT(*) pocet_uFROM klient k INNER JOIN prava_k_uctu p ON k.r_cislo=p.klientGROUP BY k.r_cisloHAVING count(*)>1;

Chceme-li výpis setřídit podle hodnoty agregační funkce, za klíčové slovo ORDER BY uvedeme agregační funkci:

Třídění dle výsledků agregační

funkce

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)FROM ucet GROUP BY r_cisloORDER BY AVG(stav);

SELECT r_cislo, AVG(stav) AS prumerFROM ucet GROUP BY r_cisloORDER BY prumer;

Pokud výchozí tabulka obsahuje úplná data a nehodláme rozlišovat jednotlivé řádky:

Agregace bez klíče

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

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.

Agregace s jednoduchý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 BY ná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:

Agregace se složeným klíčem

SELECT r_cislo, pobocka,MAX(stav)FROM ucetGROUP BY r_cislo,pobockaORDER BY r_cislo,pobocka

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

COUNT(*)

4

r_cislo COUNT(*)

580506/4891 1

826111/5267 2

MAX(castka) MIN(castka) AVG(castka) SUM(castka)

3000 -1500 780.00 7800

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

FROM Klient AS k LEFT JOIN Ucet AS u ON k.r_cislo=u.r_cislo

GROUP BY k.r_cislo

Kteří klienti mají na svých účtech více než 50000?

SELECT k.prijmeni, k.r_cislo, SUM(u.stav) AS celkem

FROM Klient AS k LEFT JOIN Ucet AS u ON k.r_cislo=u.r_cislo

GROUP BY k.r_cislo

HAVING SUM(u.stav)>50000;

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!

Práce s funkcemi

SELECT FUNKCE(sloupec) FROM Tabulka;

CONCAT(x, y,…) - zřetězení dvou nebo více řetězců (sloupců) do jednoho:

Funkce pro práci s textovými

řetězci

SELECT CONCAT(prijmeni," ",jmeno) AS cele_jmenoFROM 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 ).

SELECT upper(prijmeni), jmenoFROM Klient;

UPPER(sloupec) /LOWER(sloupec) - převedení řetězce na velká/malá písmena.

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;

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;

Výpis posledních tří znaků čísla účtu:

SELECT RIGHT(c_uctu,3)FROM Ucet;

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);

Funkce pro práci s čísly

MOD(X,Y) – vrací zbytek z podílu X/YRAND(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ístSELECT ROUND(1.864,1); → 1.9

CEILING(X) – vrací nejvyšší celé číslo založené na hodnotě čísla X (tzn. zaokrouhlení na celé číslo nahoru)

SELECT CEILING(1.864); → 2FLOOR(X) – vrací celočíselnou část čísla X (tzn. zaokrouhlení na

celé číslo dolu)SELECT FLOOR(1.864); → 1TRUNCATE(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.8SELECT TRUNCATE(122,-2); → 100

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,

Funkce pro práci s datumem a

časem

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');

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:

%e den v měsíci %M název měsíce anglický %H dvoumíst. hodina- 24 h

%d dvoumístný den %b zkrácený název měsíce %i minuty

%D den s příponou %Y rok %S sekundy

%W název dne v týdnu %y dvoumístní rok %r čas

%a zkrácený název dne %I hodina %T čas ve 24h formátu

%c číslo měsíce %h dvoumístná hodina %p AM nebo PM

%m dvojmost. číslo měsíce %k hodina ve 24 h

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

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'); → 2008SELECT EXTRACT(YEAR_MONTH FROM NOW());

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'; → 2Chceme-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átuSELECT STR_TO_DATE('2008-10-31', '%d.%m.%Y');

Konverzní funkce