Tablas dinámicas en PostgreSQL usando la función Crosstab

Hace algunos años, cuando se lanzó la versión 8.3 de PostgreSQL, se introdujo una nueva extensión llamada tablefunc. Esta extensión proporciona un conjunto de funciones realmente interesantes. Una de ellas es la función crosstab, que se utiliza para la creación de tablas pivote. Eso es lo que cubriremos en este artículo.

La forma más sencilla de explicar cómo funciona esta función es utilizando un ejemplo con una tabla dinámica. Primero, explicaremos nuestro punto inicial desde una perspectiva práctica, y luego definiremos la tabla dinámica que queremos.

Nuestro punto inicial: Datos en bruto

Mientras lee este artículo, imagínese como un profesor de una escuela primaria (elemental). Supondremos que enseñas todas las asignaturas (lengua, música, etc.). La escuela le proporciona un sistema para registrar todos los resultados de las evaluaciones o pruebas. La siguiente sentencia SQL mostraría los resultados de las evaluaciones que has cargado previamente en el sistema:

SELECT *
FROM evaluations

Nuestro objetivo: una tabla dinámica

La siguiente tabla podría seguir fácilmente el progreso de sus alumnos. En informática, llamamos a este tipo de cuadrícula una tabla dinámica. Si analiza cómo se construye la tabla dinámica, encontrará que utilizamos valores de los datos brutos como encabezados de columna o nombres de campo (en este caso, geografía, historia, matemáticas, etc.)

Activación de la función de tabulación cruzada

Como hemos mencionado anteriormente, la función crosstab es parte de una extensión de PostgreSQL llamada tablefunc. Para llamar a la función crosstab, primero debe habilitar la extensión tablefunc ejecutando el siguiente comando SQL:

CREATE extension tablefunc;

Cómo funciona la función crosstab

La función crosstab recibe como parámetro un comando SQL SELECT, que debe cumplir con las siguientes restricciones:

• El SELECT debe devolver 3 columnas.
• La primera columna del SELECT será el identificador de cada fila de la tabla pivotante o resultado final. En nuestro ejemplo, es el nombre del estudiante. Observe cómo los nombres de los estudiantes (John Smith y Peter Gabriel) aparecen en la primera columna.
• La segunda columna del SELECT representa las categorías de la tabla dinámica. En nuestro ejemplo, estas categorías son las asignaturas escolares. Es importante tener en cuenta que los valores de esta columna se expandirán en muchas columnas de la tabla pivotante. Si la segunda columna devuelve cinco valores diferentes (geografía, historia, etc.) la tabla dinámica tendrá cinco columnas.
• La tercera columna del SELECT representa el valor que se asignará a cada celda de la tabla dinámica. Estos son los resultados de la evaluación en nuestro ejemplo.

Si pensamos en nuestra tabla pivotante como una matriz bidimensional, entonces la primera columna de SELECT es la primera dimensión de la matriz, la segunda columna de SELECT es la segunda dimensión, y la tercera es el valor del elemento de la matriz .Como rejilla [valor_de_primera_columna, valor_de_segunda_columna] = valor_de_tercera_columna.

En nuestro ejemplo, el parámetro SELECT será:

SELECT student, subject, evaluation_result FROM evaluations ORDER BY 1,2

La función crosstab se invoca en la cláusula FROM de la sentencia SELECT. Debemos definir los nombres de las columnas y los tipos de datos que irán en el resultado final. Para nuestros propósitos, el resultado final se define como:

AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC)

Uniendo todas estas piezas, nuestra consulta final será:

SELECT * 
FROM crosstab( 'select student, subject, evaluation_result from evaluations order by 1,2') 
     AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

Y podemos ver el resultado aquí:

Un conjunto de datos en bruto, muchas tablas dinámicas

A partir de un único conjunto de datos, podemos producir muchas tablas dinámicas diferentes. Continuemos con el ejemplo del profesor y la clase mientras vemos algunas de nuestras opciones.

Ejemplo 1: Promedios de evaluación mensual

Como profesores, también podemos necesitar un informe de los resultados de la evaluación de un alumno para el año hasta la fecha. Por ejemplo, supongamos que queremos obtener los promedios de las evaluaciones de Juan Pérez desde marzo hasta julio. En una cuadrícula como la siguiente, la tabla tendría el siguiente aspecto:

El SQL para esta tabla pivotante es:

SELECT * 
FROM crosstab( 'select extract(month from period)::text, subject.name,
		     trunc(avg(evaluation_result),2) 
     from evaluation, subject  
     where evaluation.subject_id = subject.subject_id and student_id = 1 
     group by 1,2 order by 1,2')  AS final_result(Month TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

Compare este resultado con la tabla anterior:

Ejemplo 2: Encontrar registros de estudiantes incompletos

También podríamos llamar a esta sección "Una limitación de Crosstab y cómo solucionarla". Antes de entrar en materia, vamos a preparar la escena:

Supongamos que quiere ver si algunos estudiantes no tienen una nota de evaluación para ciertas asignaturas. Quizás intente nuestra consulta anterior, añadiendo una cláusula WHERE para julio. El código quedaría así:

SELECT * 
FROM crosstab( 'select student, subject, evaluation_result from evaluations where extract (month from evaluation_day) = 7 order by 1,2') 
     AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

La siguiente tabla dinámica es el resultado de esta consulta. Podemos ver rápidamente que no tenemos ninguna calificación para lengua, matemáticas y música para Pedro.

Sin embargo, si intentamos una consulta normal para obtener las calificaciones de Pedro en julio ...

SELECT * from evaluations 
where extract ( month from evaluation_day)=7 and student like 'Gabriel, Peter'

... obtenemos resultados diferentes. Aquí tenemos las notas de geografía y lengua:

Por supuesto, la segunda consulta es la correcta porque muestra los datos en bruto. El problema está en el proceso de construcción de la tabla dinámica: a algunas categorías les falta información. Para solucionar esto, podemos utilizar la función crosstab con un segundo parámetro, que representa la lista completa de categorías. Si faltan valores, la tabla dinámica se construirá correctamente. (He resaltado la consulta del segundo parámetro en rojo).

SELECT * 
FROM crosstab( 'select student, subject, evaluation_result from evaluations 
                where extract (month from evaluation_day) = 7 order by 1,2',
                'select name from subject order by 1') 
     AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

Ahora tenemos una tabla dinámica correcta con valores vacíos en los lugares adecuados.

Ejercicio

Las tablas dinámicas nos dan una forma diferente de ver nuestros datos. Además, podemos crear diferentes tablas dinámicas basadas en los mismos datos brutos utilizando la función de tabulación cruzada. Intenta crear una tabla dinámica que muestre la temperatura máxima de cada ciudad y mes basándose en los datos brutos de la siguiente tabla.

CREATE TABLE weather (city text, when timestamp, temperature float);

La tabla dinámica debe tener una fila para cada ciudad y una columna para cada mes. Si quieres, puedes pensar en otras tablas pivotantes que podrían hacerse con los mismos datos.