Analýza domén

Převzato z http://www.depesz.com/index.php/2008/12/01/getting-list-of-most-common-domains/#comment-27066

Následující kód je v podstatě první ukázkou jedné z vlastností PostgreSQL a to možnosti volání SRF funkcí v normálním kontextu. Tuto vlastnost nemám rád, ale v tomto případě má smysl. Představte si, že máte seznam (log) domén - a nyní budete chtít dohledat četnost jednotlivých domén. Tedy uložené domény musíme transformovat (např. psql.cz na psql.cz a cz). Tj. musíme namnožit řádky - což v čistém SQL nelze - (v ANSI SQL 200x by bylo možné použít CTE, patrně by se nejednalo o žádný zázrak). Možnosti jsou 2 - buďto napíšeme klasickou SRF funkci nad konkrétní tabulkou (což je metoda, kterou preferuji) nebo funkci, která převede hodnotu domény na hierarchický seznam poddomén.

a) připravím si pomocné funkce:

postgres=# create or replace function domain_list(varchar[]) 
           returns setof varchar as $$
              select array_to_string($1,’.') 
              union all 
              select domain_list($1[2:array_upper($1,1)]) 
                 where array_upper($1,1) > 1 
           $$ language sql immutable strict;
CREATE FUNCTION
postgres=# select domain_list(array['a','b','c','d']);
domain_list
————-
a.b.c.d
b.c.d
c.d
d
(4 rows)

postgres=# create or replace function domain_list(varchar) 
           returns setof varchar as $$ 
              select domain_list(string_to_array($1, ‘.’))
           $$ language sql immutable strict;
CREATE FUNCTION
postgres=# select domain_list(’a.b.c.d’);
domain_list
————-
a.b.c.d
b.c.d
c.d
d

b) pro tabulku domains si funkcí domain_list namnožíme řádky:

postgres=# select * from domains;
       d        
----------------
 pgsql.cz
 lmc.cz
 postgresql.org
 gnome.org
(4 rows)

postgres=# select domain_list(d), d from domains;
  domain_list   |       d        
----------------+----------------
 pgsql.cz       | pgsql.cz
 cz             | pgsql.cz
 lmc.cz         | lmc.cz
 cz             | lmc.cz
 postgresql.org | postgresql.org
 org            | postgresql.org
 gnome.org      | gnome.org
 org            | gnome.org
(8 rows)

Výsledek po agregaci:

postgres=# select domain_list(d), count(*) 
              from domains 
             group by domain_list(d) 
             order by 2 desc, 1;
  domain_list   | count 
----------------+-------
 cz             |     2
 org            |     2
 gnome.org      |     1
 lmc.cz         |     1
 pgsql.cz       |     1
 postgresql.org |     1
(6 rows)

Docela hezký trik.