Crear una tabla dinámica en PostgreSQL

Una tabla dinámica tiende a agregar algunos valores dados en una tabla original que ya se usa para resumir grandes flujos de datos. En PostgreSQL, es una tabla devuelta con columnas de valor N personalizadas, que es el tipo de datos de la fila resumida en nuestro resultado.

Hoy aprenderemos cómo crear una tabla dinámica en PostgreSQL utilizando una tabla predefinida con valores existentes.

Utilice crosstab() para crear una tabla dinámica en PostgreSQL

Puede encontrar crosstab() bajo el encabezado TABLE_FUNC en la documentación de PostgreSQL. Es una función que devuelve una tabla con varias filas.

Sintaxis:

crosstab ( sql text ) ? setof record

Esta consulta produce una tabla dinámica que contiene nombres de fila más columnas de valor N, donde N está determinado por el tipo de fila especificado en la consulta de llamada.

crosstabN ( sql text ) , setof table_crosstab_N

Esta consulta produce una tabla dinámica que contiene nombres de fila más columnas de valor N. crosstab2, crosstab3 y crosstab4 están predefinidos.

Ahora, veamos cómo podemos usarlo. Crearemos una tabla simple llamada APARTAMENTO con columnas; ID, UNIDAD, PRECIO, ZONA.

Ejemplo:

CREATE TABLE apartment (
   ID int PRIMARY KEY,
   UNIT int,
   PRICE int,
   AREA int
)

Ahora, agreguemos algunos valores a nuestra tabla:

INSERT INTO apartment VALUES (1, 20, 200, 10) , (2, 20, 200, 9), (3, 50, 190, 8);

Entonces, si miramos nuestra tabla ahora, sería algo como lo siguiente.

Producción :

id    unit   price  area
1     20     200     10
2     20     200     9
3     50     190     8

Y en nuestra tabla dinámica, esta tabla se representaría como:

unit    ..180    190        200          210...
20         -      -      19/2 = 9.5       -
50         -      8         -             -

Así que aquí, estamos tomando el promedio del ÁREA para cada UNIDAD a sus PRECIOS específicos. Queremos ver el ÁREA promedio que obtenemos sobre el PRECIO mencionado.

Entonces, si encuentra el ÁREA promedio para un PRECIO de 200 para una UNIDAD de 20, obtendrá el promedio como (10 + 9) / 2 = 8. De esta manera, encuentra la tabla dinámica con los agregados.

Ahora, ha entendido el concepto, así que sigamos adelante e implementémoslo. Podemos escribir una consulta de la siguiente manera:

Select UNIT, PRICE, avg(area)
from apartment
group by UNIT, PRICE

Esta consulta implementará la funcionalidad básica de lo que queremos. Utiliza la cláusula GROUP BY para tomar aquellas columnas de la tabla contra las que deseamos agregar.

Deseamos encontrar el promedio del ÁREA para una determinada UNIDAD y PRECIO; por lo tanto, agrupamos usando estas dos columnas. Se devuelve una tabla de la siguiente manera.

Producción :

unit   price        avg
50     190     8.0000000000000000
20     200     9.5000000000000000

Ahora, esta tabla también funciona correctamente, pero sigamos adelante y usemos la tab cruzada(). Tienes que ver cómo funciona y las limitaciones.

Para usar crosstab(), tenemos que asegurarnos de dos puntos cruciales:

1. crosstab() debe tener valores distintos para cada fila.
2. crosstab() debe tener los mismos tipos de datos para cada columna.

Por lo tanto, asegúrese de que ninguna columna tenga un tipo de datos diferente. Ahora, para pivotar nuestros resultados de esta consulta GROUP BY, avancemos y escribamos algo como lo siguiente:

select *
from crosstab
(
'Select UNIT::float, PRICE::float, avg(AREA)::float
from apartment
group by UNIT, PRICE') as ct(
    UNIT float,
    avge float
);

Hay algunos puntos críticos a considerar aquí. Seleccionamos las columnas de la tabla cruzada devuelta como la tabla de resultados CT tal como la definimos con dos columnas: UNIT y avge.

Después de agrupar, obtenemos las columnas del APARTAMENTO y encontramos el promedio. Luego, esta tabla de consulta se pivota para obtener nuestro resultado final.

Además, tenga en cuenta que nos aseguramos de convertir cada columna en el mismo tipo de datos. Por supuesto, un promedio será de precisión doble o flotante.

Por lo tanto, es mejor hacer que las columnas int floten, incluso si no es necesario. Si ejecuta la consulta sin enviar, devolverá un error como se muestra a continuación.

Producción :

ERROR:  return and sql tuple descriptions are incompatible
SQL state: 42601

Por lo tanto, para evitar esto, nos aseguramos de convertir en flotante o crear una nueva tabla con valores insertados en ella con el tipo flotante. La diferencia en los tipos de datos es lo que hace que la tupla sea incompatible.

Producción :

unit    avg
50       8
20      9.5

En otros casos, no es necesario utilizar crosstab(). Incluso usar el GROUP BY es efectivo ya que nos devuelve una tabla con valores únicos para obtener un promedio.

Sin usar la función crosstab(), aún habríamos obtenido el siguiente resultado:

unit   price   avg
50     190      8
20     200     9.5

Puede notar en crosstab() que usar una lista de retorno con tres columnas como nos gustaría, todavía nos devuelve solo valores que contienen la UNIDAD y el promedio del área. ¿Por qué?

La declaración SQL toma un conjunto de categorías y valores, y en nuestro caso, la categoría se convierte en la UNIDAD, y los valores tienden a ser la columna promedio. Por lo tanto, solo se mantienen dos columnas en nuestra tabla de retorno.

Podemos usarlo ahora que sabemos cómo funciona crosstab() para nuestras necesidades. Si tuviéramos UNIDADES con diferentes PRECIOS y luego tomáramos el PROMEDIO(), tendríamos un número N de columnas, dependiendo de nuestros datos.

Cambiemos los valores de nuestra tabla para que sean los siguientes:

id    unit   price  area
1      20    200     10
2      20    170     9
3      50    190     8

Ahora, ejecutar la consulta crosstab() en esto devolverá algo como lo siguiente.

Producción :

unit   avge   avge1
50      8    [NULL]
20     10      9

Puede notar que cambiar el PRECIO tiende a distribuir los valores del ÁREA en diferentes promedios. Esto sucede porque también agrupamos nuestro promedio por precios.

Los mismos valores de PRECIO tendrán un promedio de diferentes valores si están presentes para todos ellos, pero otros valores de PRECIO solo consistirán en promedios de ÁREAS respectivas a cada PRECIO. Por lo tanto, la tabla está ordenada de esta manera.