CTE a analytické funkce v PostgreSQL
V tomto článku bych rád ukázal cestu ke dvěma novým funkcím PostgreSQL - CTE a analytickým funkcím. Z pohledu SQL o nic nového nejde - tyto funkce jsou dobře známé z komerčních databází. Z pohledu OSS databází se jedná o významný krok k plné implementaci ANSI SQL 200x a k znatelnému rozšíření komfortu vývojářů používajících tyto databáze.
Common Table Expression
CTE nejsou jen rekurzivní dotazy, přesto si dovedu představit, že většina vývojářů použije tuto funkcionalitu právě na rekurzivní dotaz. Co jsem si mohl všimnout, tak pro řešení rekurzivních dotazů CTE soupeří se syntaxí Oraclu CONNECT BY. CONNECT BY je zavedená syntaxe - byť na mne působí trochu cizorodě (v SQL) a trochu jako rychlý hack. Přes své rozšíření se do standardu tato klauzule nedostala a proto také nebyla implementována v PostgreSQL (přestože několik let je dostupný patch, který CONNECT BY implementuje do PostgreSQL). CONNECT BY jsem popisoval v článku stromy.
Vůči CONNECT BY je syntaxe CTE univerzálnější a nesoustřeďuje se pouze na rekurzivní dotazy. Zatímco pro CONNECT BY je základem určení sloupců identifikátoru řádku a identifikátoru předka (plus určení řádku s kořenem), pro CTE jsou důležité množiny, které získáme jako výsledek konkrétních dotazů. CTE, v podstatě, umožňuje, v rámci jednoho dotazu, vytvořit dočasnou tabulku, tuto tabulku naplnit, a tuto tabulku (resp. odkaz na ni) použít v jiné části dotazu. CONNECT BY si můžete představit jako jakýsi hodně zvláštní JOIN, - vůči tomu CTE je vlastně neprocedurální zápis následující funkce (rekurzivní varianta):
i := 0;
Si := SELECT0;
while Si <> {}
do
i := i + 1;
Si := SELECT1 WHERE .. IN S(i-1); -- rekurze!
done;
SELECT2 FROM S;
Příklad - data jsou převzata z výše uvedeného článku (každý uzel má dva potomky):
CREATE TABLE data (
id integer PRIMARY KEY,
parent integer REFERENCES data(id) NULL,
value varchar
);
INSERT INTO data
VALUES (1, NULL, 'root'),
(2, 1, 'A'), (3, 1, 'B'),
(4, 2, 'AA'), (5, 2, 'AB'),
(6, 3, 'BA'), (7, 3, 'BB'),
(8, 4, 'AAA'), (9, 4, 'AAB'),
(10, 7, 'BBA'), (11, 7, 'BBB');
postgres=# SELECT * FROM data;
id | parent | value
----+--------+-------
1 | | root
2 | 1 | A
3 | 1 | B
4 | 2 | AA
5 | 2 | AB
6 | 3 | BA
7 | 3 | BB
8 | 4 | AAA
9 | 4 | AAB
10 | 7 | BBA
11 | 7 | BBB
(11 rows)
Dotaz s CTE:
postgres=# WITH RECURSIVE t AS (SELECT *, 0 AS level
FROM data
WHERE value = 'root'
UNION ALL
SELECT data.*, level + 1
FROM data
JOIN
t
ON data.parent = t.id)
SELECT t.*, repeat(' ',level) || value AS formated_value
FROM t
ORDER BY CASE WHEN value = 'root' THEN '' ELSE repeat(' ',level) || value END;
id | parent | value | level | formated_value
----+--------+-------+-------+----------------
1 | | root | 0 | root
2 | 1 | A | 1 | A
4 | 2 | AA | 2 | AA
8 | 4 | AAA | 3 | AAA
9 | 4 | AAB | 3 | AAB
5 | 2 | AB | 2 | AB
3 | 1 | B | 1 | B
6 | 3 | BA | 2 | BA
7 | 3 | BB | 2 | BB
10 | 7 | BBA | 3 | BBA
11 | 7 | BBB | 3 | BBB
(11 rows)
Zásadní je UNION ALL uvnitř CTE. Pokud je CTE rekurzivní, pak musí obsahovat klauzuli UNION ALL. Výsledek prvního dotazu v této klauzuli se použije pro inicializaci. Druhý dotaz se pak periodicky opakuje. Během dotazu vypočítávám sloupec level, který se použije pro odsazení v položce formated_value.
V jazyce PL/pgSQL by výše uvedenému dotazu odpovídala funkce:
CREATE OR REPLACE FUNCTION tree_list()
RETURNS TABLE(id integer, parent integer, value varchar, level integer, formated_value varchar) AS $$
DECLARE
_s integer := 0;
_rc integer;
BEGIN
/* vytvoří a naplní tabulku t */
CREATE TEMPORARY TABLE t AS
SELECT *, 0 AS __level, _s AS step
FROM data
WHERE data.value = 'root';
GET DIAGNOSTICS _rc = ROW_COUNT;
/*
* rekurzivní volání nad množinou, tj. výsledek dotazu použijeme, v následující iteraci,
* jako parametr téhož dotazu
*/
WHILE _rc > 0
LOOP
_s := _s + 1;
INSERT INTO t
SELECT data.*, __level + 1, _s
FROM data
JOIN
t
ON data.parent = t.id AND step = _s - 1;
GET DIAGNOSTICS _rc = ROW_COUNT;
END LOOP;
/* závěrečné formátování */
RETURN QUERY SELECT t.id, t.parent, t.value, t.__level, (repeat(' ',__level) || t.value)::varchar
FROM t
ORDER BY CASE WHEN t.value = 'root' THEN '' ELSE repeat(' ',__level) || t.value END;
DROP TABLE t;
RETURN;
END;
$$ LANGUAGE plpgsql;
Je to věcí názoru, ale mně přijde, že CTE je určitě snazší napsat než napsat odpovídající funkci. Vyhodnocení dotazu s CTE je také rychlejší než volání funkce. Jinak, to je, k CTE, prakticky vše. Implementace CTE je, v PostgreSQL, není kompletní. Ale to, co je důležité, implementováno je.
Kromě rekurzivní varianty CTE můžeme ještě použít nerekurzivní - princip je plus/mínus stejný. Nerekurzivní forma CTE zastupuje dočasné tabulky - viz příklad v článku PostgreSQL v roce 2009 (PostgreSQL 8.4).
Analytické dotazy (Analytické funkce, funkce nad okny (window functions))
Zatímco CTE je, díky své "procedurální povaze" relativně snadno pochopitelné, pochopení analytických dotazů je obtížnější. Analytické dotazy jsou možná na první pohled složité, na druhý pohled snadné, na třetí pak zrádné a teprve na čtvrtý pokus je možné porozumět tomu, co s nimi bylo zamýšleno. Kdesi v dějinách SQL se striktně dělily funkce serveru a funkce klienta. Server se staral o uložení a vyhledání dat. Klient, pak, o prezentaci dat. Proto např. SQL neobsahovalo žádné funkce pro formátování hodnot číselných typů nebo typů datum a čas. A také SQL neobsahovalo žádnou podporu pro číslování řádků. Po čase se ukázalo, že to prostě nefunguje, a že uživatelé chtějí některé funkce (typické pro prezentační vrstvu) i na serveru. Případně, že jsou ochotni tyto chybějící funkce suplovat výkonnostně šílenými SQL příkazy (založených na CROSS JOINu). Na tyto požadavky zareagovali i dodavatelé RDBMS. Různě. K dispozici jsou session proměnné (MSSQL, MySQL), případně uložené procedury (všechny vyspělejší RDBMS) a konečně analytické dotazy. Ty vznikly ve spolupráci Oracle a DB2 - a dostaly se i do standardu.
Začnu příkladem - mějme tabulku report, která obsahuje 4 řádky.
CREATE TABLE report(castka int, popis varchar);
INSERT INTO report VALUES(10,'housky'),(20, 'rohliky'),( 4,'mleko'),(30,'maslo');
postgres=# SELECT * FROM report;
castka | popis
--------+---------
10 | housky
20 | rohliky
4 | mleko
30 | maslo
(4 rows)
Budeme požadovat takové zobrazení tohoto reportu, kdy řádky budou v pořadí určeném cenou, očíslované včetně sloupce podílu částky ku součtu všech částek.
Podle otců zakladatelů bychom měli na takovýto report použít odpovídající software (Crystal reports, ...). Samotné SQL příliš nepomůže (bez podpory analytických dotazů).
Procedurální řešení:
CREATE OR REPLACE FUNCTION print_report()
RETURNS TABLE (no integer, castka integer, popis varchar, pomer float) AS $$
DECLARE
_celkem float := (SELECT sum(report.castka) FROM report)::float;
BEGIN
no := 1;
FOR print_report.castka, print_report.popis
IN SELECT report.castka, report.popis FROM report ORDER BY report.castka DESC
LOOP
pomer := print_report.castka / _celkem;
RETURN NEXT;
no := no + 1;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
postgres=# SELECT no, castka, popis, to_char(pomer*100, '90.99')
FROM print_report();
no | castka | popis | to_char
----+--------+---------+---------
1 | 30 | maslo | 46.88
2 | 20 | rohliky | 31.25
3 | 10 | housky | 15.62
4 | 4 | mleko | 6.25
(4 rows)
Přestože to není složitý kód, dají se v něm udělat chyby (hlavní riziko je v kolizi jmen sloupců a proměnných). Pokud by databáze podporovala analytické dotazy, lze pro získání stejného reportu použít následující SQL příkaz:
postgres=# SELECT row_number() OVER (ORDER BY castka DESC) AS no, castka, popis, to_char(castka/sum(castka::float) OVER ()*100,'90.99')
FROM report
ORDER BY castka DESC;
no | castka | popis | to_char
----+--------+---------+---------
1 | 30 | maslo | 46.88
2 | 20 | rohliky | 31.25
3 | 10 | housky | 15.62
4 | 4 | mleko | 6.25
(4 rows)
Výsledek je stejný, ale bez procedurálního programování (a z toho plynoucích potenciálních chyb). Ve výše uvedeném SQL dotazu je použitá analytická funkce row_number() a agregační funkce sum() jako analytická funkce (agregační funkce můžeme použít jako analytické funkce). Analytickou funkci poznáme podle zápisu func(params) OVER (specifikace okna). Analytická funkce se počítá nad definovaným oknem a výsledek se zapisuje zpět do téhož okna. Okno nám zobrazuje určitý výsek relace (podobně jako u agregací, nedochází tu však k redukci řádků). Pro funkci row_number určujeme, že vstupem funkce má být celá tabulka report řazená podle částky od největšího k nejmenšímu. Pozor - tady je zádrhel - jestli jsem to správně pochopil, tak ORDER BY se v analytických funkcích neváže na výstup, ale na vstup funkce. Analytické funkce se vyhodnocují před seřazením výstupu, tj. před klauzulí ORDER BY!
postgres=# SELECT row_number() OVER (), castka FROM report; -- dostaneme co chceme
row_number | castka
------------+--------
1 | 10
2 | 20
3 | 4
4 | 30
(4 rows)
Jenomže, pokud přidáme klasickou klauzuli ORDER BY,
postgres=# SELECT row_number() OVER (), castka FROM report ORDER BY castka;
row_number | castka
------------+--------
3 | 4
1 | 10
2 | 20
4 | 30
(4 rows)
tak dostaneme výsledek, který asi nebudeme chtít. Klauzule ORDER BY se aplikovala nejen na skutečná data, ale i na výsledek analytické funkce. Pokud budeme požadovat "správný výsledek" musíme:
a) změnit pořadí vyhodnocování jednotlivých klauzulí:
postgres=# SELECT row_number() OVER (), castka FROM (SELECT castka FROM report ORDER BY castka) x;
row_number | castka
------------+--------
1 | 4
2 | 10
3 | 20
4 | 30
(4 rows)
b) určit funkci row_number() nad seřazenými daty:
postgres=# SELECT row_number() OVER (ORDER BY castka DESC), castka FROM report ORDER BY castka DESC;
row_number | castka
------------+--------
1 | 30
2 | 20
3 | 10
4 | 4
(4 rows)
Je poměrně důležité si uvědomit, že ORDER BY pro analytickou funkci (v definici okna) znamená trochu něco jiného než klauzule ORDER BY příkazu SELECT. Přičemž ovšem nedochází k více násobné (a v tomto případě, zbytečné) operaci sort. Viz prováděcí plán:
postgres=# EXPLAIN VERBOSE SELECT row_number() OVER (ORDER BY castka DESC), castka FROM report ORDER BY castka DESC;
QUERY PLAN
------------------------------------------------------------------
WindowAgg (cost=1.08..1.15 rows=4 width=4)
Output: row_number() OVER (?), castka
-> Sort (cost=1.08..1.09 rows=4 width=4)
Output: castka
Sort Key: castka
-> Seq Scan on report (cost=0.00..1.04 rows=4 width=4)
Output: castka
(7 rows)
Pro ukázky složitějších dotazů potřebujeme o něco málo komplexnější data. Znovu použiji tabulku report rozšířenou o sloupec kategorie:
CREATE TABLE report(castka int, popis varchar, kategorie varchar);
INSERT INTO report
VALUES (10,'housky','pecivo'),(20, 'rohliky','pecivo'),(15,'chleb','pecivo'),
(4,'mleko','mlecne vyr.'),(30,'maslo','mlecne vyr.'),
(40,'spekacky','uzeniny'),(50,'klobasy','uzeniny'),(80,'polican','uzeniny');
postgres=# SELECT * FROM report;
castka | popis | kategorie
--------+----------+-------------
10 | housky | pecivo
20 | rohliky | pecivo
15 | chleb | pecivo
4 | mleko | mlecne vyr.
30 | maslo | mlecne vyr.
40 | spekacky | uzeniny
50 | klobasy | uzeniny
80 | polican | uzeniny
(8 rows)
V definici okna můžeme použít klauzuli PARTITION BY. Je to něco na způsob GROUP BY. Vytváří to určité oblasti definované shodou vybrané hodnoty, příp. vybraných hodnot.
Začnu známou funkcí row_number.
postgres=# SELECT row_number() OVER (PARTITION BY kategorie), * FROM report;
row_number | castka | popis | kategorie
------------+--------+----------+-------------
1 | 4 | mleko | mlecne vyr.
2 | 30 | maslo | mlecne vyr.
1 | 15 | chleb | pecivo
2 | 10 | housky | pecivo
3 | 20 | rohliky | pecivo
1 | 40 | spekacky | uzeniny
2 | 50 | klobasy | uzeniny
3 | 80 | polican | uzeniny
(8 rows)
Přidám agregační funkci count() použitou jako analytickou (window) funkci:
postgres=# SELECT row_number() OVER (PARTITION BY kategorie), count(*) OVER (PARTITION BY kategorie), * FROM report;
row_number | count | castka | popis | kategorie
------------+-------+--------+----------+-------------
1 | 2 | 4 | mleko | mlecne vyr.
2 | 2 | 30 | maslo | mlecne vyr.
1 | 3 | 15 | chleb | pecivo
2 | 3 | 10 | housky | pecivo
3 | 3 | 20 | rohliky | pecivo
1 | 3 | 40 | spekacky | uzeniny
2 | 3 | 50 | klobasy | uzeniny
3 | 3 | 80 | polican | uzeniny
(8 rows)
Agregační funkce "počítá" tak, jak bychom čekali. Jenomže, výsledkem, není jeden řádek, nýbrž několik řádků - výsledek agregační funkce se rozepíše do příslušných buněk. Dotaz se poměrně natahuje - opakuje se mi definice okna "OVER (PARTITION BY kategorie)". Obranou je použití tzv. pojmenovaného okna:
SELECT row_number() OVER w, count(*) OVER w, * FROM report WINDOW w AS (PARTITION BY kategorie);
Zajímavé jsou funkce, které "hodnotí" jednotlivé řádky. Začnu funkcemi rank() a dense_rank(). Abych demonstroval rozdíl mezi nimi, tak přidám ještě jeden řádek do tabulky report. Ten záměrně obsahuje částku odpovídající některé existující částce v určené kategorii:
INSERT INTO report VALUES (80,'herkules','uzeniny');
postgres=# SELECT * FROM report WHERE kategorie = 'uzeniny';
castka | popis | kategorie
--------+----------+-----------
40 | spekacky | uzeniny
50 | klobasy | uzeniny
80 | polican | uzeniny
80 | herkules | uzeniny
(4 rows)
postgres=# SELECT row_number() OVER w, rank() OVER w, dense_rank() OVER w, *
FROM report
WHERE kategorie = 'uzeniny'
WINDOW w AS (ORDER BY castka DESC);
row_number | rank | dense_rank | castka | popis | kategorie
------------+------+------------+--------+----------+-----------
1 | 1 | 1 | 80 | polican | uzeniny
2 | 1 | 1 | 80 | herkules | uzeniny
3 | 3 | 2 | 50 | klobasy | uzeniny
4 | 4 | 3 | 40 | spekacky | uzeniny
(4 rows)
O první pozici (podle ceny) se dělí poličan a herkules. Funkce row_number() tento fakt vůbec nebere v potaz (na rozdíl od rank() a dense_rank()). rank i dense_rank umístí obě zmíněné uzeniny na první místo. Pro nás zajímavé je druhé a třetí místo. Když je něco na prvním místě, tak logicky, to co je za tím musí být na druhém místě (pro funkci dense_rank). Pokud bychom ale chtěli vědět, kolikátý je to řádek, pak smysluplnější výsledek nám poskytne funkce rank. Funkce rank se perfektně hodí pro řešení klasické úlohy nej podle kategorie viz článek Korelované vnořené dotazy: proč nepoužívat a čím nahradit.
CREATE TABLE zamestnanci(prijmeni varchar, profese varchar, mzda integer);
INSERT INTO zamestnanci
VALUES('Stěhule','analytik',10000),
('Kůs','analytik',15000),
('Nováková','asistentka',8000),
('Vlčková','asistentka', 10000),
('Kabuďa','PR',16000),
('Jirkovský','analytik',7000);
Dotaz na nejlépe placeného zaměstnance v každé kategorii (je nezbytné si pomoci derivovanou tabulkou, window funkce nelze použít v klauzuli WHERE):
postgres=# SELECT prijmeni, profese, mzda
FROM (SELECT rank() OVER (PARTITION BY profese ORDER BY mzda DESC), *
FROM zamestnanci) x
WHERE x.rank = 1;
prijmeni | profese | mzda
----------+------------+-------
Kůs | analytik | 15000
Vlčková | asistentka | 10000
Kabuďa | PR | 16000
(3 rows)
Pokud by mne zajímali dva nejlépe placení zaměstnanci, pak použiji funkci dense_rank a podmínku dense_rank <= 2.
Další zajímavou funkcí jsou mezisoučty:
postgres=# SELECT sum(castka) OVER () AS total, sum(castka) OVER (PARTITION BY kategorie) AS sum_groupby,
sum(castka) OVER (ORDER BY kategorie ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_cum,
sum(castka) OVER (PARTITION BY kategorie ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum,
castka, popis, kategorie
FROM report ;
total | sum_groupby | total_cum | cum | castka | popis | kategorie
-------+-------------+-----------+-----+--------+----------+-------------
329 | 34 | 4 | 4 | 4 | mleko | mlecne vyr.
329 | 34 | 34 | 34 | 30 | maslo | mlecne vyr.
329 | 45 | 49 | 15 | 15 | chleb | pecivo
329 | 45 | 59 | 25 | 10 | housky | pecivo
329 | 45 | 79 | 45 | 20 | rohliky | pecivo
329 | 250 | 119 | 40 | 40 | spekacky | uzeniny
329 | 250 | 169 | 90 | 50 | klobasy | uzeniny
329 | 250 | 249 | 170 | 80 | polican | uzeniny
329 | 250 | 329 | 250 | 80 | herkules | uzeniny
(9 rows)
V posledním příkladu se uplatnila klauzule ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, která určuje, že okno má být od začátku oblasti, do aktuálně zpracovávaného řádku. Podpora analytických funkcí není kompletní (v 8.4), proto, pozor, pokud byste chtěli portovat aplikace s DB2 nebo Oracle. Určitě doporučuji si projít dokumentaci, kde je seznam všech aktuálně implementovaných window funkcí. Pokud byste si chtěli napsat vlastní funkce tohoto typu, pak jedině v jazyce C.
Podpora analytických funkcí je v PostgreSQL "žhavou" novinkou. Nepochybně bude pár měsíců trvat, možná let, trvat než se s ní aplikační vývojáři sžijí. Přesto věřím, že si své příznivce najde a řadě lidí ušetří práci a čas.
Co je mi známo, tak CTE a analytické funkce podporují všechny velké komerční databáze - Oracle a DB2 dlouho, MSSQL od verze 2005. PostgreSQL je první open source databází, kde jsou tyto funkce dostupné. Aktuálně v betě 8.4, kterou si můžete volně stáhnout a nainstalovat (pro většinu operačních systémů) z adresy http://www.postgresql.org/ftp/binary/v8.4beta/http (Linux - hledejte v rpm repozitory). Přehledu nových funkcí verze 8.4 se věnoval článek PostgreSQL v roce 2009 (PostgreSQL 8.4). Plná verze 8.4 by měla být dostupná do začátku prázdnin.