Tablas cruzadas en PostgreSQL: PivotMyTable

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, se crean dos consultas y una especificación de campos:

  • La primera consulta especifica la categorización de la tabla original.
  • La segunda consulta obtiene la lista de columnas sumarizadas.
  • La especificación de campos determina el tipo de columnas de salida final.

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.

  • Instalación de la extensión tablefunc: Visita las páginas de documentación de  PostgreSQL sobre instalación de extensiones en http://www.postgresql.org/docs/9.3/static/contrib.html. Comprueba que la versión de esta ayuda coincide con tu versión de PostgreSQL.
  • Instalación de la extensión tablefunc: Visita las páginas de documentación de  PostgreSQL sobre instalación del lenguaje PL/Python en  http://www.postgresql.org/docs/9.3/static/plpython.html. Comprueba que la versión de esta ayuda coincide con tu versión de PostgreSQL.

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:

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.

  • input_table (varchar): Nombre de la tabla origen.
  • output_table (varchar): Nombre de la tabla cruzada de salida.
  • group_fields(varchar): Nombre de la columna(s) para categorización de los datos. Al contrario que la función crosstab del módulo tablefunc, acepta múltiples columnas, separándolas por comas.
  • pivot_field(varchar): Columna pivote. Debe ser una única columna.
  • value_field(varchar): Datos a ser agragados. Se espera una columna de tipo numérico (int, numeric…)
  • agg_func(varchar): Función de agragación que se aplicará a los datos de la columna especificada en value_field. Debe ser especificado su nombre sin paréntesis. Es responsabilidad del usuario comprobar los resultados y la compatibilidad de tipo de dato. Las funciones disponibles/testadas son: sum, count, and avg.
  • as_percentage(bool): Esta opción habilita la salida como porcentaje de cada columna sobre la suma de cada fila para las columnas de salida. Inhabilitado por defecto, pasar este parámetro TRUE para habilitarlo el cálculo de porcentajes. Esta opción sólo está disponible seleccionando la función de agregación sum mediante el parámetro agg_func.
  • sort_order(varchar): Esta opción permite ordenar las columnas pivote resultante. Se puede especificar ‘asc’, ‘desc’ or ‘no_sorting’ (Por defecto)
  • drop_ex_tbl (boolean): PivotMyTable comprueba si la tabla de salida especificada mediante el parámetro output_table ya existe en la base de datos. Si este parámetro se ajusta a TRUE, en el caso de que exista una tabla con el mismo nombre pivotMyTable la eliminará automáticamente Este parámetro está ajustado por defecto a FALSE, lo que producirá una salida del proceso y una advertencia señalando este hecho.
  • as_view (boolean): Esta opción habilita la salida como vista en lugar de como tabla. Para habilitarla salida a vista, establecer en TRUE. El valor por defecto es FALSE.

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