Tablas cruzadas en PostgreSQL: PivotMyTable

Feb. 5, 2014

Tablas cruzadas en  PostgreSQL: PivotMyTable

PivotMyTable es una función escrita en PL/Python para su uso en servidores PostgreSQL. Su objetivo es obtener tablas cruzadas en PostgreSQL de una manera más amigable que lo que posibilita el módulo tablefunc de PostgreSQL con su serie de funciones crosstab, siendo en el fondo una suerte de intermediario entre el usuario y éstas.

PivotMyTable hace posible crear tablas cruzadas en PostgreSQL del mismo modo que otras soluciones disponibles, automatizando la creación de las consultas que las funciones crosstab de tablefunc necesitan para funcionar. Más concretamente,

Crosstabs in PostgreSQL: PivotMyTable

PivotMyTable is a PL/Python function for use in PostgreSQL servers. Its aim is to get crosstab  tables in PostgreSQL in a more friendly way that PostgreSQL module tablefunc does with its crosstab series functions and in fact it behaves ,at last, as a proxy for tablefunc functions.

PivotMyTable makes possible to create crosstabs in PostgreSQL in the same way that other available solutions, automating the creation of the queries that the tablefunc crosstab functions need to work.

se crean dos consultas y una especificación de campos:

El problema de otras soluciones es que, hasta lo que yo sé, no son suficientemente dinámicas para que la obtención de tablas cruzadas en PostgreSQL sea sencilla. Y muchas de ellas simplemente te devuelven el texto de la consulta para ser ejecutada posteriormente. PivotMyTable, sin embargo, crea la tabla para el usuario y hace un buen trabajo en lo que respecta al tipo de columnas de salida, escogiendo el tipo correcto sin que sea responsabilidad del usuario especificarlos.

Cat besides table

Otra buena funcionalidad es la capacidad de PivotMyTable de hacer uso de más de una columna para categorizar los datos. Esto es posible creando una especie de columna agrupada previo a la transposición de la tabla y posteriormente recreando las columnas que originaron la columna agrupada.

Además, PivotMyTable hace posible obtener porcentajes en las tablas cruzadas, así como olvidarse de los valores nulos en las tablas obtenidas. Esto último es un funcionalidad deseable, por ejemplo si se usan los datos en otras aplicaciones como R. Ten en cuenta que actualizar las columnas obtenidas una a una para eliminar los valores nulos y sustituirlos por, por ejemplo, 0, puede ser tedioso si tu tabla tiene 20 o 30 columnas… ¡¡¡imagínate si tu tabla tiene 1000 columnas!!!

PivotMyTable se ha liberado bajo licencia GPL v3 y puede ser descargada libremente desde mi repositotio en GitHub. Contacta conmigo para cualquier sugerencia o para comunicar errores, etc.

PIvotMyTable requiere la extensión PostgreSQL tablefunc y el lenguage PL/Python instalados en tu base de datos para funcionar.

Uso de PivotMyTable para crear tablas cruzadas en PostgreSQL

El uso de PivotMyTable es muy sencillo. Partiendo de una tabla como esta:

player tool round hits
Pepito Hammer Rd1 12
Pepito Hammer Rd2 13
Pepito Hammer Rd2 4
Pepito Wrench Rd5 1
Manu Wrench Rd1 12
Manu Wrench Rd1 16
Manu Hammer Rd2 3
Richal Hammer Rd3 42
Richal Hammer Rd1 17
Richal Hammer Rd4 22
Richal Hammer Rd2 15
Richal Hammer Rd1 17

Es posible ejecutar la siguiente consulta:

select * from pivotmytable('myinfo','pivotedinfo','player,tool','round','hits','sum',sort_order:='asc');

Para crear una tabla cruzada en PostgreSQL como esta:

player tool Rd1 Rd2 Rd3 Rd4 Rd5
Pepito Hammer 12 17 0 0 0
Pepito Wrench 0 0 0 0 1
Manu Hammer 0 3 0 0 0
Manu Wrench 28 0 0 0 0
Richal Hammer 34 15 42 22 0

Opciones y parámetros de la función

Todos los parámetros del tipo varchar deben ser pasados entre comillas simples para que PivotMyTable funcione.

¡¡Espero que te sea de utilidad!! Ponme un twitter o deja un comentario si lo deseas con tu opinión.

More precisely, two queries and one column specification are created:

The problem with another solutions is that they area, as far as I know, not enough dynamic to make crosstabs with ease. And also some of them just return you the query to be executed. PivotMyTable creates the table for you and makes a nice work regarding the type of output fields choosing its correct type, what makes unnecessary for the user to specify it.

[caption id=“attachment_659” align=“alignleft” width=“236”]Cat besides table Well, not a crosstable in PostgreSQL at all…[/caption]

Yet another nice feature is the ability of PivotMyTable to use more than one column to categorize the data. This is accomplished by creating a joined column prior to the crosstabulation, and subsequently re-creating the fields which origined the joined column.

Also, PivotMyTablee makes possible to get percentages in the pivoted tables, as well as get rid of null values in the oputput tables. This is a desirable feature, for instance to use the data in other application like R. Bear in mind that updating the pivoted columns one by one can be an ordeal if your table has more than maybe 20 or 30 columns… not to think about having 1K output columns!!!

PivotMyTable is released under GPL v3 license and can be downloaded from my GitHub repository. Feel free to contact me for suggestion or bug/error reporting.

PivotMyTable requires the PostgreSQL extension tablefunc and the language PL/Python installed in the database to work.

The usage of PivotMyTable is simple: Providing that you have a table myinfo like:

player tool round hits
Pepito Hammer Rd1 12
Pepito Hammer Rd2 13
Pepito Hammer Rd2 4
Pepito Wrench Rd5 1
Manu Wrench Rd1 12
Manu Wrench Rd1 16
Manu Hammer Rd2 3
Richal Hammer Rd3 42
Richal Hammer Rd1 17
Richal Hammer Rd4 22
Richal Hammer Rd2 15
Richal Hammer Rd1 17

You can issue this query:

select * from pivotmytable('myinfo','pivotedinfo','player,tool','round','hits','sum',sort_order:='asc');

To to create a crosstab in PostgreSQL like this:

player tool Rd1 Rd2 Rd3 Rd4 Rd5
Pepito Hammer 12 17 0 0 0
Pepito Wrench 0 0 0 0 1
Manu Hammer 0 3 0 0 0
Manu Wrench 28 0 0 0 0
Richal Hammer 34 15 42 22 0

Function Parameters/Options

All the parameters of the type varchar must be assigned between single quotes for PivotMyTable to work.

I hope this work helps you!!! Please feel free to drop me a tweet for feedback!!!