Consultas avanzadas en postgres con tablas de referencia cruzada (pivot table)

Algunas veces tenemos la necesidad de crear reportes con una estructura diferente a como los tenemos almacenados en nuestra base de datos, más precisamente en diseño de referencia cruzada o también llamados informes de matriz, los cuales son utilizados para mostrar la relación entre tres o más datos, en donde se muestran los datos en filas y columnas y en los puntos de intersección los valores.

Cuando investigamos como realizar este tarea en base de datos, nos encontramos con un cantidad de desafíos que muchas veces no logramos solucionar y terminamos desistiendo, así que en este post te enseñaré como realizar este tipo de reporte en Postgres y no morir en el intento.

Nuestro objetivo será crear un reporte de referencia cruzada para visualizar las ventas de cada mes por país y tipo de venta, dados los datos de la tabla de la izquierda obtener el resultado de la tabla de la derecha.

Para poder trabajar con nuestro ejemplo primero crearemos la tabla e insertaremos los registros de las ventas.

CREATE TABLE sales(
   id serial, 
   country VARCHAR(3),
   type VARCHAR(15),
   month int, 
   amount numeric(10,2)
);

INSERT INTO sales(country,type,month,amount) 
VALUES ('PEN','Course',1,16),('PEN','Course',2,12),('PEN','Course',3,24),
       ('PEN','Subscription',4,30),('PEN','Subscription',5,30),('PEN','Subscription',6,30),
       ('PEN','Course',7,16),('PEN','Subscription',8,30),('PEN','Course',9,16),
       ('PEN','Subscription',10,30),('PEN','Subscription',11,30),('PEN','Course',12,12),
       ('COL','Subscription',1,30),('COL','Course',4,24),('COL','Subscription',6,30),
       ('COL','Subscription',12,30),('BOL','Course',1,12),('BOL','Course',3,12),
       ('MXN','Course',5,16);

Postgres nos provee de forma nativa algunas extensiones que podemos utilizar para agregar funcionalidades a nuestra base de datos, así que nosotros usaremos la extensión tablefunc, la cual nos permite generar tablas de referencia cruzada.

Para agregar la extensión a nuestra base de datos solo debemos ejecutar la siguiente instrucción:

CREATE EXTENSION IF NOT EXISTS tablefunc; 

Esta extensión incluye la función crosstab(source\_sql,category\_sql) que genera una tabla de referencia cruzada con las columnas de valores especificadas por una segunda consulta. Para poder entender claramente los parámetros de la función, nos apoyaremos en la siguiente imagen con el fin de entender la posición de los datos en la tabla:

crosstab(source_sql,category_sql)

Esta consulta producirá el siguiente resultado:

+-----------+--------------+---------+-------+
| country   | type         |   month | sum   |
|-----------+--------------+---------+-------|
| BOL       | Course       |       1 | 12.00 |
| BOL       | Course       |       3 | 12.00 |
| COL       | Course       |       4 | 24.00 |
| COL       | Subscription |       1 | 30.00 |
| COL       | Subscription |       6 | 30.00 |
| COL       | Subscription |      12 | 30.00 |
| MXN       | Course       |       5 | 16.00 |
| PEN       | Course       |       1 | 16.00 |
| PEN       | Course       |       2 | 12.00 |
| PEN       | Course       |       3 | 24.00 |
| PEN       | Course       |       7 | 16.00 |
| PEN       | Course       |       9 | 16.00 |
| PEN       | Course       |      12 | 12.00 |
| PEN       | Subscription |       4 | 30.00 |
| PEN       | Subscription |       5 | 30.00 |
| PEN       | Subscription |       6 | 30.00 |
| PEN       | Subscription |       8 | 30.00 |
| PEN       | Subscription |      10 | 30.00 |
| PEN       | Subscription |      11 | 30.00 |
+-----------+--------------+---------+-------+
SELECT 19

Sin embargo la documentación oficial de Postgres nos indica:

"La función espera que las columnas extras sean las mismas para todas las filas con el mismo valor de row\_name".

Sí verificamos los datos únicos de nuestra columna row\_name (country) y extra (type) identificaremos que no estamos cumpliendo con esta condición, debido a que tanto para PER como COL nuestra columna extra no tiene el mismo dato para todos los registros del mismo país, ya que tenemos dos datos diferentes course y subscription, para los países de BOL y MXN no tenemos este problema ya que solo tenemos ventas de cursos.

+-----------+--------------+
| country   | type         |
|-----------+--------------|
| BOL       | Course       | -> OK
| COL       | Course       | -> NO cumple
| COL       | Subscription | -> NO cumple
| MXN       | Course       | -> OK
| PEN       | Course       | -> NO cumple
| PEN       | Subscription | -> NO cumple
+-----------+--------------+

Entonces, ¿cómo hacemos para resolver este problema? sí nuestro objetivo es poder obtener tanto las ventas de cursos cómo de suscripción, y esta restricción nos impide crear el reporte adecuadamente, y que pasaría si tuviéramos mas tipos de ventas, sería prácticamente imposible generar el reporte deseado. Así que en este punto es donde muchos pensaríamos en desistir, yo también lo pensé ?, sin embargo se me ocurrió usar una técnica que utilizo cuando trabajo en Excel la cuál llamamos llaves, que consiste simplemente en concatenar una serie de columnas con el fin de producir un nuevo valor único, así que lo que haremos será no incluir columnas extra, y concatenar los campos country y type con el fin de producir la columna row\_name, de esta manera nos saltamos la restricción y tendremos el detalle del país y el tipo de venta, usando esta técnica podremos incluir la cantidad de columnas que necesitemos para el área de filas del reporte, entonces nuestro argumento source\_sql de nuestra función será:

SELECT CONCAT(country,'-',type) AS key, month, SUM(amount) FROM sales GROUP BY key, month ORDER BY 1,2

SELECT month from generate_series(1,12) as month

Esta instrucción producirá el siguiente resultado:

---------+
|   month |
|---------|
|       1 |
|       2 |
|       3 |
|       4 |
|       5 |
|       6 |
|       7 |
|       8 |
|       9 |
|      10 |
|      11 |
|      12 |
+---------+
SELECT 12

• source_sql: Es una declaración SQL que produce el conjunto de fuente de datos. Esta instrucción debe devolver una columna row\_name, una columna category y una columna values, también podemos tener una o más columnas extras. La columna row\_name debe ser la primera, las columnas de category y values deben ser las dos últimas columnas. Las columnas entre row\_name y category se tratan como extra. En nuestro ejemplo la declaración SQL será:
• **category_sql:**Es una declaración SQL que produce el conjunto de datos de la sección de columnas category. Esta declaración debe devolver solo una columna, además debe producir al menos una fila y no debe producir valores duplicados o se generará un error, de manera que sí deseamos agregar una consulta SQL para generar este argumento siempre debemos asegurarnos de incluir la palabra reservada DISTINCT, en nuestros ejemplo la sección category contendrá los meses del año, por lo tanto haremos uso de la función generate\_series para generarlos, nuestro argumento category\_sql será:

Por último, la función crosstab es declarada para retornar un conjunto de registros, así que los nombres reales y tipo de datos de salida de las columnas deben ser definidos en la clausula FROM de la instrucción SELECT, por lo tanto le daremos el nombre key al dato de la fila y el tipo de dato será TEXT, y para los datos de columna le asignaremos el nombre de cada mes con tipo de dato NUMERIC(10,2).

Finalmente nuestra instrucción para generar el reporte será:

SELECT *
    FROM crosstab(
      'SELECT CONCAT(country,''-'',type) AS key, month, SUM(amount) FROM sales GROUP BY key, month ORDER BY 1,2',
      'SELECT month FROM generate_series(1,12) AS month'
) AS (
    key text,
    Jan NUMERIC(10,2),
    Feb NUMERIC(10,2),
    Mar NUMERIC(10,2),
    Apr NUMERIC(10,2),
    May NUMERIC(10,2),
    Jun NUMERIC(10,2),
    Jul NUMERIC(10,2),
    Aug NUMERIC(10,2),
    Sep NUMERIC(10,2),
    Oct NUMERIC(10,2),
    Nov NUMERIC(10,2),
    Dec NUMERIC(10,2)
);

Esta instrucción producirá el siguiente resultado:

+------------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| key              | jan    | feb    | mar    | apr    | may    | jun    | jul    | aug    | sep    | oct    | nov    | dec    |
|------------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------|
| BOL-Course       | 12.00  |        | 12.00  |        |        |        |        |        |        |        |        |        |
| COL-Course       |        |        |        | 24.00  |        |        |        |        |        |        |        |        |
| COL-Subscription | 30.00  |        |        |        |        | 30.00  |        |        |        |        |        | 30.00  |
| MXN-Course       |        |        |        |        | 16.00  |        |        |        |        |        |        |        |
| PEN-Course       | 16.00  | 12.00  | 24.00  |        |        |        | 16.00  |        | 16.00  |        |        | 12.00  |
| PEN-Subscription |        |        |        | 30.00  | 30.00  | 30.00  |        | 30.00  |        | 30.00  | 30.00  |        |
+------------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+

Si queremos separar los datos de la sección de fila en varias columnas, solo debemos usar la función split\_part, por lo que nuestra consulta final será:

SELECT split_part(key,'-',1) AS country, split_part(key,'-',2) AS type, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec 
    FROM crosstab(
      'SELECT CONCAT(country,''-'',type) AS key, month, SUM(amount) FROM sales GROUP BY key, month ORDER BY 1,2',
      'SELECT month FROM generate_series(1,12) AS month'
) AS (
    key text,
    Jan NUMERIC(10,2),
    Feb NUMERIC(10,2),
    Mar NUMERIC(10,2),
    Apr NUMERIC(10,2),
    May NUMERIC(10,2),
    Jun NUMERIC(10,2),
    Jul NUMERIC(10,2),
    Aug NUMERIC(10,2),
    Sep NUMERIC(10,2),
    Oct NUMERIC(10,2),
    Nov NUMERIC(10,2),
    Dec NUMERIC(10,2)
);

+-----------+--------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| country   | type         | jan    | feb    | mar    | apr    | may    | jun    | jul    | aug    | sep    | oct    | nov    | dec    |
|-----------+--------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------|
| BOL       | Course       | 12.00  |        | 12.00  |        |        |        |        |        |        |        |        |        |
| COL       | Course       |        |        |        | 24.00  |        |        |        |        |        |        |        |        |
| COL       | Subscription | 30.00  |        |        |        |        | 30.00  |        |        |        |        |        | 30.00  |
| MXN       | Course       |        |        |        |        | 16.00  |        |        |        |        |        |        |        |
| PEN       | Course       | 16.00  | 12.00  | 24.00  |        |        |        | 16.00  |        | 16.00  |        |        | 12.00  |
| PEN       | Subscription |        |        |        | 30.00  | 30.00  | 30.00  |        | 30.00  |        | 30.00  | 30.00  |        |
+-----------+--------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+

Puedes ver el ejemplo completo y funcional en db<>fiddle.

Espero que esta información pueda ser de utilidad y puedas ponerla en practica, ¡Nos vemos pronto!