SQL funkce a DML - 5. díl

Rychle prolétnu zajímavé funkce, příkazy z SQL a zbylé DML operace INSERT, UPDATE, DELETE a MERGE. Pokud se na to cítíte můžete určitě článek přeskočit. V příštím už se konečně dostaneme k věcem bližším DWH.

Funkce a příkazy SQL

Pro lepší práci s daty existují další klíčová slova v SQL a také funkce. Pravděpodobně velmi často budete používat větvení CASE. Je to typický switch/else if z programovacích jazyků. Vypadá následovně:

CASE
	WHEN condition1 THEN result1
	WHEN condition2 THEN result2
	…
	ELSE resultElse
END

Poměrně užitečné jsou také funkce nullif a coalesce.

/* Nullif vrátí hodnotu null, pokud jsou oba parametry shodné. Jinak vrátí první parametr */
SELECT nullif(0, 0) -- return null
SELECT nullif(1, 0) -- return 1
/* Coalesce vrátí první parametr, který není null */
SELECT coalesce(null, 0) -- return 0
SELECT coalesce(null, null, 0) -- return 0
SELECT coalesce(0, 1, 2) -- return 0
/* Kombinace obou funkcí je dobré preventivní opatření před dělení nulou */
SELECT 20 / 0 -- error
SELECT 20 / coalesce(nullif(0, 0), 1) -- return 20
SELECT 20 / coalesce(nullif(5, 0), 1) -- return 4

Matematické funkce v SQL

abs() /* Absolutní hodnota */
sin(), cos(), tan() /* Goniometrické funkce */
round(), ceil(), floor() /* Zaokrouhlování */
exp(), sqrt() /* Mocniny */
gcd(), lcm() /* Největší společný dělitel a nejmenší společný násobek - mohou být specifické pro konkrétní dialekt SQL */
ln(), log(), log10() /* Logaritmus */
mod() /* Modulo - v některých dialektech SQL lze zapsat jako operátor pomocí % */
pi() /* π = 3,14... */
radians() /* Převod ° na radiány */

Funkce pro práci s textem v SQL

concat() /* Spojuje texty */
chr() /* Konkrétní znak dle ASCII table - hodí se chr(10) = LF = line feed */
character_length() /* Délka textu */
replace() /* Najít & Nahradit */
ltrim(), rtrim(), trim() /* Odstranění zbytečných mezer (mezera defaultní znak - lze změnit) */
substring(), left(), right() /* Extrahuje část textu */
upper(), lower() /* Převod znaků na UPPER CASE nebo lower case */
format() /* Formátování */

Funkce pro práci s datumem v SQL

U práce s datumem se poměrně dost liší dialekt od dialektu. V PostgreSQL narazíte na extract() nebo date_trunc() pro získání konkrétní části datumu. Pro sčítání a odčítání datumů se používá klíčové slovo interval. Například takto:

/* Poslední den kvartálu */
(date_trunc('quarter', '2023-09-16'::date) + interval '3 months' - interval '1 day')::date AS QUARTER_LAST_DATE

Vlastní funkce (User Defined Functions)

Většina SQL databází umožňuje definovat vlastní funkce. To je poměrně užitečné a může se hodit například na funkce jako odstranění úvodních nul apod. V PostgreSQL takovou funkci definovat následujícím způsobem.

/*
	Remove Leading Zeroes
	Ex.: from '0001234567' returns '1234567'
*/
DROP FUNCTION IF EXISTS removeLeadingZeroes(varchar);
CREATE FUNCTION removeLeadingZeroes(varchar) RETURNS VARCHAR AS $$
	SELECT regexp_replace($1, '^0*', '');
$$ LANGUAGE SQL;

Nebudu se v této chvíli definováním vlastních funkcí věnovat příliš do hloubky, jen poznamenám, že do závorek se uvádí datový typ parametrů a přistupuje se k nim pomocí $ a čísla pořadí. Více lze nalézt v dokumentaci PostgreSQL. V odkazech ještě přidávám kód pro vytvoření základních funkcí.

Pozor na NULL

Podmínka COLUMN <> 'pivo' nezafunguje správně na NULL hodnoty. Pokud používáte negované podmínky je potřeba myslet na NULL hodnoty (COLUMN = 'pivo' bude fungovat správně).

Obecně je dobré v DWH NULL hodnoty nahradit zástupným textem. U textů to bývá NA, N/A, XNA (důležité je aby se Vám tento text nikde opravdu nevyskytoval – NA nedoporučuji – zkratka pro Non Alcoholic). Nahrazení NULL za XNA lze provést jednoduše pomocí coalesce(NULL, 'XNA') Pokud toto uděláte tak coalesce(COLUMN, 'XNA') <> 'pivo' bude fungovat. U jiných datových typů bývá nahrazení obtížnější kvůli volbě hodnoty, pro datumy to bývá hodnota ‚1900-01-01‘ apod.

UNION & EXCEPT

Může nastat situace, že máte data stejného typu a potřebujete je propojit na úrovni celých tabulek. Nechcete je propojovat přes primární klíč, prostě to jsou jen další záznamy. Důležité je, že mají stejnou strukturu = stejné sloupce. Pro takové propojení existuje UNION a UNION ALL. Rozdíl mezi nimi je pouze v tom, že pokud máte ve spojení duplicitní záznamy UNION je zde ponechá, UNION ALL rovnou záznamy deduplikuje.

Pro operaci průniku tu máme příkaz INTERSECT. Snad jsem ho ani nikdy v životě nepoužil.

No a pak tu máme EXCEPT (MINUS) a ten slouží k rozdílu. Sjednocení, průnik i rozdíl se dělá na všech sloupcích. Pomocí kombinace EXCEPT a count(1) se dá kontrolovat rovnost tabulek – # záznamů a rozdílnost dat. Hodí se například v situaci, kdy upravuji kriticky důležitou tabulku a udělám si její zálohu. Upravím současnou tabulku (například přidám sloupec) a zkontroluji, že se tabulky rovnají (bez přidaného sloupce). A vím, že při úpravách v tabulce jsem nic nerozbil u ostatních sloupců.

Obecná doporučení

LIKE – % znamená jakýkoli počet jakýchkoliv znaků, _ znamená právě jeden jakýkoliv znak.

Spojování logických podmínek – pokud použijete OR obalte ho důkladně závorkami – předejdete neočekávaným výsledkům

Nepoužívejte DISTINCT – napište raději GROUP BY – spousta lidí DISTINCTu nerozumí správně a předejdete dost často zmatení.

Nepoužívejte při použití IN uvnitř závorek SELECT (subquery). Udělejte to INNER JOINem. Původní je nečitelné a pomalé.

Na příkaz EXISTS taky zapomeňte, že existuje 🙂

Zbylé DML operace

INSERT

Příkaz pro vkládání záznamů. Potřeba je jen dodržet správné pořadí sloupů. Nejlepší je vždy vyjmenovat (do nevyjmenovaných se vyplní null).

INSERT INTO l0_ext.CONFIGURATION_REPORT (REPORT_ID, REPORT_NAME, PERIOD_FROM, PERIOD_TO) VALUES ('Report01', 'Report X01', '202201', '202212');

UPDATE

Slouží k úpravě hodnot konkrétních polí. Velmi důležité je uvést klauzuli WHERE. Jinak změníte všechny záznamy v tabulce – velmi nebezpečné.

UPDATE l0_ext.CONFIGURATION_REPORT
	SET REPORT_NAME = 'Report XX01', PERIOD_TO = '202312'
WHERE REPORT_ID = 'Report01'

DELETE

Slouží k mazání záznamů v tabulce. Velmi důležité je uvést klauzuli WHERE. Jinak změníte všechny záznamy v tabulce – velmi nebezpečné.

DELETE
FROM l0_ext.CONFIGURATION_REPORT
WHERE REPORT_ID = 'Report01'

Pokud chcete vymazat celou tabulku a nemáte v tabulce cizí klíče je lepší použít příkaz TRUNCATE, který bude pravděpodobně rychlejší.

TRUNCATE TABLE l0_ext.CONFIGURATION_REPORT

MERGE

MERGE je INSERT/UPDATE/DELETE na základě podmínky. Hodí se na komplexnější úpravy (například v historizaci). Vykoná danou operaci, pokud je splněna podmínka.

MERGE INTO <table>
USING <select>
	ON <condition>
WHEN MATCHED THEN
	UPDATE SET <column> = <value> /* Option to UPDATE */
	--INSERT (<columns>) VALUES (<values>) /* Option to INSERT */
	--DELETE /* Option to DELETE */
--WHEN NOT MATCHED THEN /* Option NOT MATCHED */

Shrnutí

Existuje mnoho pomocných funkcí. Mezi nejdůležitější patří coalesce()a nullif(). Podmínky pomocí CASE WHEN THEN ELSE END. Za zmínku stojí matematické funkce a funkce pro práci s řetězci a datumy.
Dbejte na existenci NULL, a že negované podmínky s NULL nemusí fungovat zcela tak jak očekáváte. Dobré je použít nahrazení NULL textovou hodnotou XNA.
Další operace s tabulkami (množinami) lze dělat pomocí UNION, UNION ALL, INTERSECT a EXCEPT.
Další důležité DML jsou INSERT, UPDATE, DELETE a komplexnější MERGE. INSERT ke vkládání, UPDATE ke změně a DELETE k mazání, MERGE si vybere jeden z předchozích na základě podmínky.