Zobrazení zdroje stránky CTE a analytické funkce v PostgreSQL

[[Category:Články]]
V tomto článku bych rád ukázal cestu ke dvěma novým funkcím PostgreSQL - <i>CTE</i> a <i>analytickým funkcím</i>. 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==
<i>CTE</i> 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 <tt>CONNECT BY</tt>. <tt>CONNECT BY</tt> 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). <tt>CONNECT BY</tt> jsem popisoval v článku [http://www.root.cz/clanky/stromy/ stromy]. 

Vůči <tt>CONNECT BY</tt> 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. <tt>CONNECT BY</tt> 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):

<pre>   i := 0;
   Si := SELECT0;
   while Si <> {}
   do
     i := i + 1;
     Si := SELECT1 WHERE .. IN S(i-1); -- rekurze!
   done;
   SELECT2 FROM S;</pre>

Příklad - data jsou převzata z výše uvedeného článku (každý uzel má dva potomky):

<pre>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)</pre>

Dotaz s CTE:

<pre>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)</pre>

Zásadní je <tt>UNION ALL</tt> uvnitř CTE. Pokud je CTE rekurzivní, pak musí obsahovat klauzuli <tt>UNION ALL</tt>. 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 <tt>level</tt>, který se použije pro odsazení v položce <tt>formated_value</tt>.

V jazyce PL/pgSQL by výše uvedenému dotazu odpovídala funkce:

<pre>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;</pre>

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)]].

<h2><i>Analytické dotazy</i> (Analytické funkce, funkce nad okny (<i>window functions</i>))</h2>

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. 

<pre>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)</pre>

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

<pre>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)</pre>

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:

<pre>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)</pre>

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 <tt>row_number()</tt> a agregační funkce <tt>sum()</tt> jako analytická funkce (agregační funkce můžeme použít jako analytické funkce). Analytickou funkci poznáme podle zápisu <tt>func(params) OVER (specifikace okna)</tt>. 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 <tt>ORDER BY</tt> 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í <tt>ORDER BY</tt>!

<pre>postgres=# SELECT row_number() OVER (), castka FROM report; -- dostaneme co chceme
 row_number | castka 
------------+--------
          1 |     10
          2 |     20
          3 |      4
          4 |     30
(4 rows)</pre>

Jenomže, pokud přidáme klasickou klauzuli <tt>ORDER BY</tt>,

<pre>postgres=# SELECT row_number() OVER (), castka FROM report ORDER BY castka;
 row_number | castka 
------------+--------
          3 |      4
          1 |     10
          2 |     20
          4 |     30
(4 rows)</pre>

tak dostaneme výsledek, který asi nebudeme chtít. Klauzule <tt>ORDER BY</tt> 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í:

<pre>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)</pre>

b) určit funkci <tt>row_number()</tt> nad seřazenými daty:
<pre>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)</pre>

Je poměrně důležité si uvědomit, že <tt>ORDER BY</tt> pro analytickou funkci (v definici okna) znamená trochu něco jiného než klauzule <tt>ORDER BY</tt> příkazu <tt>SELECT</tt>. 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:

<pre>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)</pre>

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:

<pre>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)</pre>

V definici okna můžeme použít klauzuli <i><tt>PARTITION BY</tt></i>. Je to něco na způsob <tt>GROUP BY</tt>. Vytváří to určité oblasti definované shodou vybrané hodnoty, příp. vybraných hodnot.

Začnu známou funkcí row_number. 

<pre>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)</pre>

Přidám agregační funkci count() použitou jako analytickou (window) funkci:
<pre>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)</pre>

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 "<tt>OVER (PARTITION BY kategorie)</tt>". Obranou je použití tzv. pojmenovaného okna:

<pre>SELECT row_number() OVER w, count(*) OVER w, * 
   FROM report 
  WINDOW w AS (PARTITION BY kategorie);</pre>

Zajímavé jsou funkce, které "hodnotí" jednotlivé řádky. Začnu funkcemi <tt>rank()</tt> a <tt>dense_rank()</tt>. 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:

<pre>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)</pre>

O první pozici (podle ceny) se dělí poličan a herkules. Funkce <tt>row_number()</tt> tento fakt vůbec nebere v potaz (na rozdíl od <tt>rank()</tt> a <tt>dense_rank()</tt>). 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 [http://www.postgres.cz/index.php/Korelovan%C3%A9_vno%C5%99en%C3%A9_dotazy#V.C3.BDb.C4.9Br_prvn.C3.ADch_.28posledn.C3.ADch.29_n_produkt.C5.AF_s_ka.C5.BEd.C3.A9_skupiny Korelované vnořené dotazy: proč nepoužívat a čím nahradit].

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

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 <tt>WHERE</tt>):
<pre>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)</pre>

Pokud by mne zajímali dva nejlépe placení zaměstnanci, pak použiji funkci dense_rank a podmínku <tt>dense_rank &lt;= 2</tt>.

Další zajímavou funkcí jsou mezisoučty:
<pre>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)</pre>

V posledním příkladu se uplatnila klauzule <i><tt>ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</tt></i>, 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 [http://www.postgresql.org/docs/8.4/static/functions-window.html#FUNCTIONS-WINDOW-TABLE 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 [http://www.postgresql.org/about/news.1074 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.