Tablas cruzadas en PostgreSQL: PivotMyTable
Feb. 5, 2014Tablas 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:
- 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.
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:
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.
- 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.
More precisely, two queries and one column specification are created:
- First query specifies the categorization/grouping of the original table
- Second query gets the list of summarized columns from pivot column.
- The column specification determines the type of the output columns.
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”] 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.
- Installation of tablefunc extension: Please check PostgreSQL documentation pages for extensions installation in http://www.postgresql.org/docs/9.3/static/contrib.html. Check for the correct manual page for your version of PostgreSQL.
- Installation of PL/Python language: Please check PostgreSQL documentation pages for the installation of PL/Python http://www.postgresql.org/docs/9.3/static/plpython.html. Check for the correct manual page for your version of PostgreSQL.
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.
- input_table (varchar): Name of the table to get data to pivot.
- output_table (varchar): Name for the output table.
- group_fields(varchar): Name of the column(s) for categorizing the data. Unlike the native _tablefunc_module functions crosstab, it accepts multiple columns, separating them with commas.
- pivot_field(varchar): Column to be pivoted, must be a single column.
- value_field(varchar): Data to aggregate for the pivot field. It is expected for value_field to be a numeric (int, float, etc) field.
- agg_func(varchar): Aggregate function to apply to the data from value_field. It must be specified without brackets. It’s user responsability to check for function/data type compatibility and resulting data. The available(tested) functions are: sum, count and avg.
- as_percentage(bool): This option enables the output as percentage of each output columns over its overall sum BY ROW. It defaults to FALSE, so to enable percentage calculation set it to TRUE. This option is only available with sum as the selected agg_func.
- sort_order(varchar): This option enables sorting the resulting pivoted columns. You can specify ‘asc’, ‘desc’. It defaults to ‘no_sorting’.
- drop_ex_tbl (boolean): pivotMyTablee checks if the output table specified with the parameter output_table exists before further processing. Setting this option to TRUE makes possible to automatically drop the existing table. By default, drop_ex_tbl is set to FALSE so the function exists with a warning if output_table already exists in the database.
- as_view (boolean): This option enables the output to a view instead of to a table. To enable view output, set it to TRUE. Defaults to FALSE.
I hope this work helps you!!! Please feel free to drop me a tweet for feedback!!!