Wednesday, November 23, 2011

Postgresql - Transposing a table with crosstab

Today we were asked to accomplish a simple task, show the count of  "tramites" over time for each "oficina registral" ; spago expects a single query to generate the related chart. My first approximation to the issue was a series of virtual tables (19) full joined over the date_key field, which isn't very pretty. RAF came with a neat solution in no time: to use the tablefunc functions contained in the postgres' contrib module.

Once installed the contrib module, you enable the tablefunc functions with something like:
psql template1 < /usr/share/postgresql/8.4/contrib/tablefunc.sql
so they're available for every new database you create. If the database keen to use them exists already, replace the template1 with the database name.
The resulting query isn't that pretty either, but it's very efficient. (at least runs faster than my original solution)

select * from crosstab('SELECT t.date_key::varchar(8) row_name,t.oficina_registral_key category, sum(cantidades_tramites) as value FROM 
fact_tramite_historico t, dim_oficina_registral dor
WHERE t.oficina_registral_key = dor.oficina_registral_key
group by  t.date_key,t.oficina_registral_key order by 1,2','select oficina_registral_key from dim_oficina_registral order by 1') as tab(fecha varchar(8),cat1 bigint,cat2 bigint,cat3 bigint,cat4 bigint,cat5 bigint,cat6 bigint,cat7 bigint,cat8 bigint,cat9 bigint,cat10 bigint,cat11 bigint,cat12 bigint,cat13 bigint,cat14 bigint,cat15 bigint,cat16 bigint,cat17 bigint,cat18 bigint,cat19 bigint,cat20 bigint);

It only remains to replace catN for its correspondent "oficina registral" name.
Clarification: each catN is declared as bigint because the aggregate function sum returns that data type.

Source:
http://www.postgresql.org/docs/8.3/static/tablefunc.html

Displaying aggregate function attributes,example
postgres=#\da count
http://archives.postgresql.org/pgsql-es-ayuda/2010-07/msg00020.php

http://stackoverflow.com/questions/2099198/sql-transpose-rows-as-columns

No comments:

Post a Comment