Funkce
S funkcemi jste se již určitě setkali. Na každé kalkulačce máte
funkce, napřílad sínus, mocnina, odmocnina atp. V této kapitole se
budu věnovat chápání a využívání funkcí v SQL databázích.
Funkce si můžete dokonce naprogramovat vlastní, ale tomu se v této
kaptiole věnovat nebudu.
Co jsou funkce
Funkce jsou vlastně databázové objekty, které však neslouží k uchovávání dat jako tabulky, ale k manipulaci s daty nebo k získávání (výpočtu) dat. S funkcemi jste se již setkali. Například statistické funkce. Funkce mohou mít argumenty a návratovou hodnotu, nebo obojí.
Argumenty jsou data, se kterými se manipuluje nebo které
se používají pro získání výsledků funkce. Například funkce
MAX(argument) má jeden argument (kterým je název sloupce).
Argumenty se uvádějí v závorce za jménem funkce. Pokud jich je více, oddělují se
čárkou.
Funkcí která nemá argumenty je například funkce RANDOM,
která vrací náhodné číslo v intervalu <0,1>.
I funkce bez argumentu se musí volat se závorkami za svým jménem.
Návratová hodnota funkce může být číslo, řetězec, tabulka
atp. V případě agregační funkce MAX je to největší hodnota ze sloupce, který
je jí předán jako argument.
Pokud chcete funkce vyzkoušet „nanečisto“, pak je můžete spustit
pomocí příkazu SELECT (viz tabulka dual).
Druhy funkcí
Skalární funkce
Skalární funkce mají jednu hodnotu v jednom argumentu (jedno číslo,
jeden řetězec, nikoliv celý sloupec) a jednu hodnotu na výstupu.
Například funkce LENGTH(retezec).
Jejím argumentem je jeden
řetězec a návratovou hodnotou číslo (délka řetězce předaného jako
argument). Skalární funke může být součástí libovolného výrazu.
prijmeni, jmeno, LENGTH(prijmeni)+LENGTH(jmeno) AS "Délka jména"
FROM zamestnanci;
prijmeni | jmeno | Délka jména
----------+---------+-------------
Pavova | Lenka | 11
Pavova | Jana | 10
Mala | Jana | 8
Pavova | Lenka | 11
Jerry | Tom | 8
Luter | Martin | 11
King | Leopold | 11
Mann | Tomas | 9
Trn | Vasek | 8
Osel | Stary | 9
Novy | Pan | 7
(11 rows)
Agregační funkce
Výstupem (návratovou hodnotou) agregační funkce je také jedna
hodnota. Na vstupu je však hodnotou množina hodnot (sloupec).
Množinou hodnot jsou buď všechny řádky ve sloupci, nebo jejich část
seskupená pomocí GROUP BY. Tyto
funkce se mohou použít jednak v seznamu sloupců za klíčovým slovem
SELECT, nebo v podmínce pro skupinu řádků za klíčovým slovem
HAVING, i v klauzuli LIMIT.
Příklady použití agregačních funkcích jste viděli v předešlé kapitole.
max
-------
22000
(1 row)
rimmer1=> SELECT MAX(plat) FROM zamestnanci GROUP BY oddeleni_id;
max
-------
12000
15000
22000
(3 rows)
rimmer1=> SELECT oddeleni_id, MAX(plat) FROM zamestnanci GROUP BY oddeleni_id
HAVING AVG(plat) >= 11000 ORDER BY MAX(plat) DESC;
oddeleni_id | max
-------------+-------
3 | 22000
2 | 15000
(2 rows)
Funkce SQL
Kompletní seznam funkcí najdete v oficiální dokumentaci. Já jsem pro vás vybral pár těch nejzajímavějších.
Agregační funkce
Většinu agregačních funkcí jsem ukázal v kapitole SELECT II. Pro úplnost je zde vypíšu v tabulce.
| Název | Význam | Typ agrumentu |
|---|---|---|
| AVG() | Vrátí průměrnou hodnotu. | smallint, int, bigint, real, double precision, numeric, interval |
| COUNT() | Vrátí počet položek v argumentu (které nejsou NULL) | Všechny typy |
| MAX() | Vrátí největší hodnotu. | jakékoliv pole, číslo, řetězec, nebo datum/čas |
| MIN() | Vrátí nejmenší hodnotu. | viz MAX |
| SUM() | Vrátí součet hodnot v množině. | bigint,double precision, integer, interval, money, numeric, real, smallint |
| STDDEV() | Spočte směrodatnou odchylku. | smallint, int, bigint, real, double precision, or numeric |
| VARIANCE() | Rozptyl hodnot v množině. | smallint, int, bigint, real, double precision, or numeric |
Funkce pro práci s čísly
| Název | Návratová hodnota |
|---|---|
| ABS(x) | Absolutní hodnota. |
| CINT(x) | Převede jakýkoliv číselný datový typ nebo řetězec (ve kterém je zapsáno číslo) na celé číslo. |
| CBRT(x) | Třetí odmocnina x. |
| COS(x) | Cosínus zadaného úhlu v radiánech. |
| EXP(x) | e na x-tou, kde e=2.7182 a x je reálné číslo (nikoliv celé číslo!). Celé číslo můžete na reálné převést například pomocí funkce ROUND takto: EXP(ROUND(x,0)) (x nesmí být příliš velké číslo). |
| FACTORIAL(x) | faktoriál x, kde x je celé číslo. |
| FLOOR(x) | Vrátí nejbližší celé číslo menší nebo rovno argumentu. |
| MOD(x,y) | Zbytek po celočíselném dělení x/y (x a y jsou celá čísla). |
| POWER(x,y) | x na y |
| RANDOM() | Náhodné číslo v intervalu <0,1> (Bez argumentu, závorky jsou přesto nutné!). |
| ROUND(x,y) | Číslo x se zaokrouhlí na y desetinných míst. |
| SIN(x) | Sínus zadaného úhlu v radiánech. |
| SQRT(x) | Druhá odmocnina x, kde x je reálné číslo (nikoliv celé číslo! viz funkce EXP výše). |
| TAN(x) | Tangens zadaného úhlu v radiánech. |
Funkce pro práci s řetězci
| Název | Návratová hodnota |
|---|---|
| retezec || retezec |
|| - toto není funkce ale operátor, který spojí dva řetězce v
jeden. V jiných databázích (než PostgeSQL) se pro spojování řetězců
používá funkce CONCAT, nebo operátor +.Třeba MySQL používá || jako logický operátor „nebo“.
|
| CONCAT(str,str,…) | Spojení řetězců |
| INITCAP(x) | Převede u všech slov v řetězci x první písmeno na velké a všechna další ve slově na malé. |
| LOWER(x) | Převede všechna písmena v řetězci x na malá. |
| LENGTH(x) | Vrací počet znaků v řetězci. |
| TRIM([leading | trailing | both] [characters] from string) | Ořízne všechny bílé znaky (mezery, tabulátory, nová řádky) nebo characters z leva, nebo z prava, nebo zleva i prava z řetězce string. |
| UPPER(x) | Převede všechna pímsena v řetězci x na velká. |
Funkce pro práci s časem
| Název | Návratová hodnota |
|---|---|
| CURRENT_TIMESTAMP | Vrací aktuální datum s časem i časovou zónou (např. 2002-12-29 20:22:31.610797+00). Toto vlastně není funkce, ale makro, proto se nepoužívají závorky! Může se použít jako DEFAULT hodnota. |
| CURRENT_DATE | Vrací akutální datum. |
| CURRENT_TIME | Vrací aktuální čas. |
| DATE(x) | Převede x na datum. Argument x může být typu date, abstime, nebo text. (např. date('25.6.2002') vrátí datový typ date s hodnotou 2002-06-25). |
| - + * / | Toto nejsou funkce, ale operátory. PostgreSQL umožňuje data odečítat, sčítat, a násobit a dělit časové intervaly. Detaily uvidíte v dokumentaci. |
Funkce pro konverzi datových typů
| Název | Návratová hodnota |
|---|---|
| TO_CHAR(x, vzor) | Převede datum (s časem), interval nebo číslo x na řetězec podle vzoru v argumentu vzor. Jak může vypadat vzor si najdete v dokumentaci. A to jak pro data, tak pro čísla. |
| TO_DATE(text, vzor) | Převede řetězec text na datum podle vzoru. |
| TO_TIMESTAMP(x, vzor) | Převede řetězec nebo číslo x na timestamp s časovou zónou podle vzoru. |
| TO_NUMBER(text, vzor) | Převede řetězec text na číslo podle vzoru. |
PostgreSQL má ještě jiný mechanismus pro konverzi datových typů. A to dvě
dvojtečky ::. Pokud napíšete nějakou
hodnotu (nebo sloupeček), za ní dvě dvojtečky a název datového typu,
PostgreSQL se pokusí hodnotu převést na tento datový typ. Tento zápis
asi nebudete často potřebovat, protože PostgreSQL se pokusí převést
datový typ sám od sebe všude tam, kde je to potřeba.
Ale občas se s tímto zápisem můžete někde setkat (třeba v dokumentaci).
TO_CHAR(CURRENT_TIMESTAMP, 'dd.mm.YYYY HH12:MI:SS'),
TO_CHAR(interval '15h 2m 12s', 'HH24:MI:SS'),
TO_CHAR(-125.8, '999D99S'),
TO_NUMBER('12,454.8-', '99G999D9S'),
TO_TIMESTAMP('05 Dec 2000', 'DD Mon YYYY'),
123.5::integer
FROM dual;
to_char | to_char | to_char | to_number | to_timestamp | int4
---------------------+----------+---------+-----------+------------------------+------
07.12.2013 04:29:58 | 15:02:12 | 125,80- | -1254.8 | 2000-12-05 00:00:00+01 | 124
rimmer1=> SELECT '3' + 3 from dual;
?column?
----------
6
-- pokusí se převést '3.3' na int
rimmer1=> SELECT '3.3' + 3 from dual;
ERROR: invalid input syntax for integer: "3.3"
-- implicitně převede '3.3' na float
rimmer1=> SELECT '3.3' + 3.0 from dual;
?column?
----------
6.3
-- explicitně převede '3.3' na float
rimmer1=> SELECT '3.3'::float + 3 from dual;
?column?
----------
6.3
Všiměte si, jak se snaží PostgreSQL uhodnout datový typ pro převod textového řetězce podle typu čísla ve výrazu.
Ostatní funkce
PostgreSQL obshuje celou řadu dalších funkcí a operátorů. Vzpomeňte si na to, kolik obsahuje PostgreSQL zvláštních datových typů. K nim se vážou i funkce. Takže máte funkce pro práci s XML, funkce pro práci s IP adresami, geometrické funkce, funkce pro práci s JSONem atd. Všechny samozřejmě najdete v dokumentaci.
MySQL/MariaDB, SQLite, Oracle
Z předchozích kapitol už víte, že s funkcemi a operátory je
to v každé DBMS jinak. Například víte,
že MySQL nemá funkci TO_DATE.
Nebo že operátor ^ se používá v Postgresu
na umocňování, zatímco v MySQL jako logická operace XOR.
V této kapitole jste se už dozvěděli, že MySQL používá operátor ||
jako logické OR. Pro spojování řetězců umí používat funkci CONCAT, kterou ale
zase neumí SQLite.
Kromě toho, že DBMS podporují různé sady funkcí a operátorů a toho, že některé operátory občas dělají něco úplně jiného, existuje ještě jedna záludnost. Někdy funkce, i když se jmenují a dělají totéž, nedělají to stejně!
Tak třeba funkce CONCAT může mít v Oracle jen dva argumenty
(spojí jen dva řetězce do jednoho). SQLite pro jistotu CONCAT
vůbec nemá. MySQL i PostgreSQL umožňují zadat do CONCAT
libovolný počet argumentů (které spojí všechny v jeden řetězec).
Podívejte se na následující tabulku, která ukazuje výsledky
spojování řetězců, pokud je jeden ze spojovaných výrazů NULL.
| Operátor/Funkce | PostgreSQL | MySQL | SQLite | Oracle |
|---|---|---|---|---|
| CONCAT('text',NULL) | 'text' | NULL | 'text' | |
| 'text' || NULL | NULL | NULL | 'text' |
O tomhle chování se ani v dokumentaci nedočtete. Podle standardu a logiky věci by měl být výsledek vždy NULL. Jak je vidět, není …
Nebo funkce TRIM. Pokud budete chtít odebrat všechny
mezery a hvězdičky ze začátku a konce řetězce, dopadnete asi takto:
mysql> SELECT CONCAT('*',TRIM(' x' from ' xAx '),'*') as con FROM dual;
+--------+
| con |
+--------+
| *Ax * |
+--------+
-- MySQL: z leva se nic neodebere, MySQL bere 'x ' jako celý řetězec
mysql> SELECT CONCAT('*',TRIM('x ' from ' xAx '),'*') as con FROM dual;
+--------+
| con |
+--------+
| * xA* |
+--------+
V SQLite pro jistotu nejde určovat, které znaky se mají odstranit.
oracle> SELECT CONCAT(CONCAT('*',TRIM(' x' from ' xAx ')),'*') FROM dual;
SELECT CONCAT(CONCAT('*',TRIM(' x' from ' xAx ')),'*') FROM dual
*
ERROR at line 1:
ORA-30001: trim set should have only one character
oracle> SELECT CONCAT(CONCAT('*',TRIM(' ' from ' xAx ')),'*') FROM dual;
CONCA
-----
*xAx*
Co k tomu říct závěrem? Snad jen, že když používátě nějakou novou funkci, nebo „starou“ známou funkci (nebo operátor) ale v jiném DBMS, přečtěte si k ní pozorně dokumentaci!
A ještě jedna blbůstka: MySQL vyžaduje, aby mezi jménem funkce a úvodní závorkou před argumenty nebyla mezera. Jinak dostanete na první pohled nepravdivé chybové hlášení:
