¿Por qué usar una cross-table?

Deja un comentario

Porque podemos tener la información resumida y la información resumida es la que preferentemente nos solicitan los gerentes. A ellos no les suele interesar los detalles de cada venta, por ejemplo, sino los totales vendidos. Una cross-table sirve justamente para eso.

Si nos solicitan las ventas de cada producto en cada día del mes podríamos mostrar esa información usando la cláusula GROUP BY pero tendríamos muchas fechas repetidas, algo como:

Producto 1, fecha 1, total ventas del día 1
Producto 1, fecha 2, total ventas del día 2
Producto 1, fecha 3, total ventas del día 3
Producto 2, fecha 1, total ventas del día 1
Producto 2, fecha 2, total ventas del día 2
Producto 2, fecha 3, total ventas del día 3

etc.

Los nombres de los productos (Producto 1, Producto 2, etc.) se repiten y las fechas (fecha 1, fecha 2, fecha 3, etc.) también se repiten.

Si en la consulta deben mostrarse las ventas de 40 productos durante cada uno de los días del mes entonces tendríamos una consulta con 40 x 30 = 1.200 filas. Y si los productos fueran 500 entonces tendríamos 500 * 30 = 15.000 filas. Una exageración, nadie mira una consulta tan larga.

En cambio, si usamos una cross-table el nombre de cada producto se muestra una sola vez y cada fecha también es mostrada una sola vez. Para el caso anterior tendríamos 40 filas (una por cada producto) y 30 columnas (una para cada fecha del mes). Por lo tanto una cross-table es más parecida a una hoja de cálculo (una planilla Excel)

CROSS6

Captura 1. Si haces clic en la imagen la verás más grande

Como puedes ver en la Captura 1. cada fila representa a un producto (y el nombre de cada producto es mostrado una sola vez) y en cada columna se encuentra el total vendido en ese día (y cada fecha se muestra una sola vez).

Por lo tanto, es muy fácil visualizar las ventas de cada producto en cada uno de los días. Esto es mucho más comprensible que su equivalente usando la cláusula GROUP BY.

Conclusión:

Aunque Firebird no dispone nativamente de la opción de crear una cross-table es demasiado fácil crear una como vimos en este artículo:

https://firebird21.wordpress.com/2013/11/18/usando-una-cross-table/

y debemos aprovecharnos de esta herramienta para que nuestras aplicaciones sean más útiles.

Artículos relacionados:

Usando una cross-table

El índice del blog Firebird21

 

Usando una cross-table

Deja un comentario

En Firebird nativamente no tenemos la posibilidad de crear cross-tables pero es muy fácil simular una.

¿Qué es una cross-table?

Una tabla que puede ser leída de arriba hacia abajo o de izquierda a derecha y en ambos casos nos provee de información relevante. También se la conoce como pivot table y como tabla multidimensional.

Una tabla normal solamente se lee de arriba hacia abajo ya que si la leemos de izquierda a derecha nos estamos refiriendo siempre al mismo registro. En cambio en una cross-table podemos pensar que cada fila es un registro y también que cada columna es un registro.

¿Cuál es la ventaja de usar una cross-table?

Sumarizar y mostrar grandes cantidades de datos. Una cross table siempre tiene menos filas (generalmente muchísimas menos) que las tablas de donde se obtuvieron sus datos.

Ejemplo:

Queremos mostrar las ventas de cada producto que ocurrieron los días 15/02/2011, 16/02/2011 y 17/02/2011. Por supuesto que algunos productos pudieron haberse vendido muchas veces durante esos días, con Facturas diferentes, y algunos productos no haberse vendido.

Tenemos una tabla PRODUCTOS con los datos de los productos que nuestra empresa vende.

CROSS2

Captura 1. Si haces clic en la imagen la verás más grande

Tenemos una tabla MOVIMCAB (cabecera de movimientos) con estos datos, correspondientes a las ventas realizadas:

CROSS1

Captura 2. Si haces clic en la imagen la verás más grande

Y una tabla MOVIMDET (detalles de movimientos) con los datos de los productos vendidos:

CROSS3

Captura 3. Si haces clic en la imagen la verás más grande

Esta tabla tiene 6 filas porque cada una corresponde a una línea de una Factura. Si nos fijamos en la columna MOV_IDEPRD (identificador del producto) el número 13 está en 3 filas, lo cual significa que ese producto se vendió 3 veces.

Una consulta normal para saber los datos de los productos vendidos sería como ésta:

SELECT
   MVC_FECHAX,
   MVC_NRODOC,
   PRD_NOMBRE,
   MOV_CANTID,
   MOV_PRECIO,
   MOV_CANTID * MOV_PRECIO AS TOTALPRODUCTO
FROM
   MOVIMDET
JOIN
   MOVIMCAB
      ON MOV_IDECAB = MVC_IDENTI
JOIN
   PRODUCTOS
      ON MOV_IDEPRD = PRD_IDENTI

Y así obtendríamos este resultado:

CROSS4

Captura 4. Si haces clic en la imagen la verás más grande

O sea, 6 productos vendidos, 6 filas en el resultado. Si un producto se vendió más de una vez entonces se mostrará más de una vez. Podríamos agrupar esos datos por producto y fecha, como muestra la siguiente consulta:

SELECT
   MVC_FECHAX,
   PRD_NOMBRE,
   SUM(MOV_CANTID * MOV_PRECIO) AS TOTALPRODUCTO
FROM
   MOVIMDET
JOIN
   MOVIMCAB
      ON MOV_IDECAB = MVC_IDENTI
JOIN
   PRODUCTOS
      ON MOV_IDEPRD = PRD_IDENTI
GROUP BY
   2, 1

y éste sería el resultado:

CROSS5

Captura 5. Si haces clic en la imagen la verás más grande

Pero el día 15/02/2011 se vendieron 4 productos distintos y por lo tanto hay 4 filas. Y el producto COCA COLA 350 C.C. se vendió en los dos días y por lo tanto está repetido. Podríamos agregarle la cláusula HAVING a la consulta anterior pero en ese caso las filas estarían agrupadas por fechas o por nombres de los productos y tampoco obtendríamos lo que estamos buscando.

¿Cuál es la solución?

Usar una cross-table.

WITH VENTASDIA AS (
   SELECT
      MOV_IDEPRD,
      MVC_FECHAX,
      SUM(MOV_CANTID * MOV_PRECIO) AS TOTALPRODUCTO
   FROM
      MOVIMDET
   JOIN
      MOVIMCAB
         ON MOV_IDECAB = MVC_IDENTI
   GROUP BY
      1, 2
)

SELECT
   PRD_IDENTI,
   PRD_NOMBRE,
   V1.TOTALPRODUCTO AS "15/02/2011",
   V2.TOTALPRODUCTO AS "16/02/2011",
   V3.TOTALPRODUCTO AS "17/02/2011"
FROM
   PRODUCTOS
LEFT JOIN
   VENTASDIA V1
      ON PRD_IDENTI = V1.MOV_IDEPRD AND
         V1.MVC_FECHAX = '02/15/2011'
LEFT JOIN
   VENTASDIA V2
      ON PRD_IDENTI = V2.MOV_IDEPRD AND
         V2.MVC_FECHAX = '02/16/2011'
LEFT JOIN
   VENTASDIA V3
      ON PRD_IDENTI = V3.MOV_IDEPRD AND
         V3.MVC_FECHAX = '02/17/2011'
WHERE
   V1.TOTALPRODUCTO IS NOT NULL OR
   V2.TOTALPRODUCTO IS NOT NULL OR
   V3.TOTALPRODUCTO IS NOT NULL

Al ejecutar esa consulta, éste será el resultado:

CROSS6

Captura 6. Si haces clic en la imagen la verás más grande

Conclusión:

Las cross-tables pueden ser extremadamente útiles cuando necesitamos mostrar datos sumarizados porque la información que contienen está sumarizada tanto si se lee de arriba hacia abajo como si se lee de izquierda a derecha. Al leer de izquierda a derecha generalmente el valor de las columnas es obtenido a través de alguna función agregada (SUM(), MAX(), MIN(), COUNT(), AVG() aunque por supuesto no siempre debe ser así).

Artículos relacionados:

Tablas agregadas

El índice del blog Firebird21