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.
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:
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:
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:
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:
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:
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:
Comentarios recientes