Otro ejemplo de uso de tablas agregadas

Deja un comentario

En este artículo habíamos visto como crear tablas agregadas y como insertarles datos:

https://firebird21.wordpress.com/2013/12/29/creando-y-actualizando-tablas-agregadas-de-uso-general/

Aquí tenemos otro ejemplo, éste está relacionado con los impuestos.

Tenemos una tabla llamada IMPUESTOS con esta estructura:

IMPUESTOS1

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

Y que contiene estos datos:

IMPUESTOS2

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

Una tabla llamada MOVIMIMP (movimientos de impuestos) donde se guardan los datos de todos los impuestos que se deben pagar y que tiene esta estructura:

IMPUESTOS3

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

Y que contiene estos datos:

IMPUESTOS4

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

En el archivo MOVIMCAB (cabecera de movimientos) guardamos la Fecha, el Número del Documento y otros datos. Creamos una vista llamada V_IMPUESTOS para obtener los datos que necesitamos:

CREATE VIEW V_IMPUESTOS(
   MOI_CODSUC,
   MOI_IDECAB,
   MOI_IDEIMP,
   MOI_NOMIMP,
   MOI_MONTOX)
AS
   SELECT
      MOI_CODSUC,
      MOI_IDECAB,
      MOI_IDEIMP,
      IMP_NOMBRE AS MOI_NOMIMP,
      MOI_MONTOX
   FROM
      MOVIMIMP
   JOIN
      IMPUESTOS
         ON MOI_CODSUC = IMP_CODSUC AND
            MOI_IDEIMP = IMP_IDENTI;

Y listo, ahora ya podemos utilizar las tablas agregadas. Primero creamos la tabla agregada MISIMPUESTOS1

EXECUTE PROCEDURE
   CREAR_TABLA_PIVOT (
      'MISIMPUESTOS1',
      'V_IMPUESTOS',
      'MOI_IDECAB INTEGER',
      'MOI_CODSUC INTEGER',
      'MOI_NOMIMP',
      'NUMERIC(17, 4)')

IMPUESTOS5

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

Y luego le insertamos datos a la tabla MISIMPUESTOS1

EXECUTE PROCEDURE
   ACTUALIZAR_TABLA_PIVOT (
      'MISIMPUESTOS1',
      'V_IMPUESTOS',
      'MOI_IDECAB INTEGER',
      'MOI_CODSUC INTEGER',
      'MOI_NOMIMP',
      'SUM(MOI_MONTOX)')

IMPUESTOS6

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

Y ya está, eso es todo. Ahora tenemos en la tabla MISIMPUESTOS1 todo lo que necesitamos saber sobre los impuestos. Y lo más importante es que nuestras tablas están normalizadas. Por lo tanto, si el Gobierno decide crear un nuevo impuesto no necesitaremos tocar las estructuras de nuestras tablas ni de nuestras vistas ni de nuestros stored procedures ni de nuestros triggers. Simplemente agregamos el nombre del nuevo impuesto en la tabla IMPUESTOS y el porcentaje del impuesto en la tabla CAMBIOS_IMP y listo, asunto solucionado.

Conclusión:

Gracias a los stored procedures CREAR_TABLA_PIVOT y ACTUALIZAR_TABLA_PIVOT desde ahora ya es demasiado fácil tener tablas agregadas. Lo único que debes hacer es escribir una vista que contenga los datos que pasarán a la tabla agregada y luego llamar a esos stored procedures con los parámetros adecuados, como viste en los ejemplos anteriores. Y listo, eso es todo. Muy fácil y muy sencillamente podrás tener todas las tablas agregadas que necesites.

Artículos relacionados:

Consideraciones a tener en cuenta al diseñar una Base de Datos

Diseño de bases de datos. 1FN

Diseño de bases de datos. 2FN

Diseño de bases de datos. 3FN

Más ejemplos de tablas que no cumplen con la 1FN

El manejo de los impuestos

Creando y actualizando tablas agregadas de uso general

El índice del blog Firebird21

Anuncios

Creando tablas agregadas e insertándoles datos

3 comentarios

En el artículo:

https://firebird21.wordpress.com/2013/04/28/tablas-agregadas/

habíamos visto lo que son las tablas agregadas y la gran utilidad que podemos obtener de ellas. En éste veremos una forma de crearlas (no la única forma, sino una forma) y también como insertarles datos.

Necesitaremos escribir dos stored procedures para conseguir nuestro objetivo. ¿Por qué? porque el Firebird no nos permite tener un COMMIT dentro de un stored procedure y no podemos insertarle filas a una tabla antes de hacerle un COMMIT exitoso, por lo tanto deberemos crear dos stored procedures:

  • Uno para crear la tabla y su Primary Key
  • Otro para insertarle filas a esa tabla

Ejemplo:

Tenemos una tabla llamada PRODUCTOS, en la cual se guardan el Identificador de cada producto, su Nombre y otros datos. Una tabla llamada MOVIMCAB (cabecera de movimientos) en la cual se guardan el Identificador del movimiento, la Fecha en que ocurrió, el Tipo de Documento que lo avala, el Número del Documento y otros datos. Y una tabla llamada MOVIMDET (detalles de movimientos) en la cual se guarda el Identificador del detalle, el Identificador de la cabecera, el Identificador del producto, la Cantidad vendida y otros datos.

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

En la Captura 1. vemos el Identificador y el Nombre de algunos productos.

AGRUPADAS2

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

Y en la Captura 2. vemos el Identificador del detalle, el Identificador de la cabecera, el Identificador del producto y la Cantidad vendida.

Y lo que queremos obtener es lo siguiente:

AGRUPADAS3

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

En la columna TAB_IDENTI se guarda el Identificador de la cabecera, y en las demás columnas la Cantidad vendida de cada uno de esos productos. Para hacer el ejemplo simple no se agregaron más columnas pero si tú quieres además del Identificador de la cabecera podrías agregarle la Fecha de la Venta, el Tipo de Documento, el Número del Documento, el Nombre del Cliente, etc., o sea que cualquier columna de la tabla MOVIMCAB o sus referenciadas podrías agregar.

Un stored procedure para crear la tabla agregada

CREATE PROCEDURE CREAR_TABLA_AGREGADA(
   tcNombreTabla VARCHAR(32))
AS
   DECLARE VARIABLE lcCreate   VARCHAR(4096);
   DECLARE VARIABLE lcColumna  VARCHAR(28);
BEGIN

 -- Primero, creamos la tabla

   lcCreate = 'CREATE TABLE ' || tcNombreTabla || '(';

   lcCreate = lcCreate || 'TAB_IDENTI INTEGER NOT NULL, ';

   FOR SELECT
      DISTINCT
      PRD_NOMBRE AS MOV_NOMPRD
   FROM
      MOVIMDET
   JOIN
      PRODUCTOS
         ON MOV_IDEPRD = PRD_IDENTI
   ORDER BY
      MOV_IDECAB
   INTO
      :lcColumna
   DO BEGIN
      lcColumna = Left(lcColumna, 28);
      lcColumna = Replace(lcColumna, ' ', '_');
      lcColumna = Replace(lcColumna, '.', '_');
      lcColumna = Replace(lcColumna, '/', '_');
      lcCreate = lcCreate || lcColumna || ' VARCHAR(28), ' ;
   END

   lcCreate = Left(lcCreate, Char_Length(lcCreate) - 2);

   lcCreate = lcCreate || ');';

   EXECUTE STATEMENT lcCreate;

 -- Segundo, le agregamos una Primary Key

   EXECUTE STATEMENT 'ALTER TABLE ' || tcNombreTabla || ' ADD CONSTRAINT PK_' || tcNombreTabla || ' PRIMARY KEY (TAB_IDENTI)';

END;

Este stored procedure crea una tabla con el nombre que quieras asignarle y también una Primary Key para esa tabla. Fíjate que se usa DISTINCT porque los nombres de las columnas no pueden estar repetidos. Y el nombre de la columna se obtiene editando el Nombre del Producto y reemplazando algunos caracteres por guiones bajos. Por supuesto que si no te gustan los guiones bajos podrías usar otros caracteres para el reemplazo.

Un ejemplo de llamada sería el siguiente:

EXECUTE PROCEDURE CREAR_TABLA_AGREGADA('PRUEBA2');


AGRUPADAS5

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

Y así hemos creado una tabla que tiene columnas con ¡¡¡los nombres de los productos!!!. Muy interesante, ¿verdad?. Desde luego que en tu caso podrías poner nombres de alumnos, de profesores, de clientes, de países, de lo que se te ocurra.

Un stored procedure para insertarle filas a la tabla agregada

CREATE PROCEDURE ACTUALIZAR_TABLA_AGREGADA(
   tcNombreTabla VARCHAR(32))
AS
   DECLARE VARIABLE lcUpdate VARCHAR(4096);
   DECLARE VARIABLE lcColumna VARCHAR(28);
   DECLARE VARIABLE lnIdenti INTEGER;
   DECLARE VARIABLE lnCantidad SMALLINT;
BEGIN

   FOR SELECT
      MOV_IDECAB,
      PRD_NOMBRE,
      SUM(MOV_CANTID)
   FROM
      MOVIMDET
   JOIN
      PRODUCTOS
         ON MOV_IDEPRD = PRD_IDENTI
   GROUP BY
      MOV_IDECAB,
      PRD_NOMBRE
   INTO
      :lnIdenti,
      :lcColumna,
      :lnCantidad
   DO BEGIN
      lcColumna = Left(lcColumna, 28);
      lcColumna = Replace(lcColumna, ' ', '_');
      lcColumna = Replace(lcColumna, '.', '_');
      lcColumna = Replace(lcColumna, '/', '_');
      lcUpdate = 'UPDATE OR INSERT INTO ' ||
                 tcNombreTabla || ' (' ||
                 'TAB_IDENTI, ' || lcColumna || ') VALUES (' ||
                 lnIdenti || ', '||
                 lnCantidad || ')';
      EXECUTE STATEMENT lcUpdate;
   END

END;

Aquí se editó el Nombre del Producto de la misma forma que habíamos hecho en el stored procedure CREAR_TABLA_AGREGADA porque los nombres de las columnas deben coincidir. Para poder usar el comando UPDATE OR INSERT se requiere de una Primary Key y por eso la tabla tiene una Primary Key. Este stored procedure debe recibir como parámetro de entrada el mismo nombre que se usó para crear la tabla agregada. Por ejemplo:

EXECUTE PROCEDURE ACTUALIZAR_TABLA_AGREGADA('PRUEBA2')

AGRUPADAS3

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

Y listo, eso es todo. Escribiendo dos stored procedures similares a los mostrados arriba podrás tener todas las tablas agregadas que quieras. Como recordarás, lo que generalmente se coloca en las tablas agregadas son agrupamiento de datos (sumas, cantidades totales, promedios, máximos, mínimos, etc.) porque sirven para analizar datos, no para verificar datos.

Artículos relacionados:

Tablas agregadas

Creando tablas dentro de un stored procedure o de un trigger

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

Tablas agregadas

9 comentarios

Antes de continuar leyendo este artículo es altamente recomendable que leas:

https://firebird21.wordpress.com/2013/04/27/aplicaciones-oltp-y-aplicaciones-olap/

Si no leíste el artículo de arriba, el actual no tendrá tanto valor.

¿Qué es una tabla agregada?

Es una tabla que no guarda datos en bruto sino datos agrupados. Por ejemplo, en una tabla agregada no se guardan los datos de cada venta realizada sino los totales (diarios/semanales/mensuales/anuales) de esas ventas

¿Para qué se usan las tablas agregadas?

Para que los usuarios puedan obtener los resultados de sus consultas muy rápidamente

¿Cómo se insertan datos a las tablas agregadas?

A través de un programa que realiza un proceso de ETL (Extraction, Transformation, Loading, o en castellano: Extracción, Transformación, Carga)

Primero, se extraen los datos. Eso normalmente implica escribir un SELECT que contiene las funciones agrupadas: COUNT(), SUM(), AVG(), MAX(), MIN()

Segundo, se realizan algunas operaciones con esos datos (si es necesario, claro)

Tercero, se insertan dichos datos en la tabla agregada, la cual se encuentra en otra Base de Datos

¿Cuál es la principal tarea de una tabla agregada?

Guardar los resultados obtenidos al consultar otra/s tabla/s pero en muchísimas menos filas, por lo tanto las consultas a la tabla agregada serán mucho más rápidas que la respectiva consulta a la/s tabla/s original/es

¿Cómo se nombra a las tablas agregadas?

Aunque no es obligatorio hacerlo así, lo normal es que empiecen con el prefijo AGG_ para que todos los programadores puedan saber que se trata de una tabla agregada. Por ejemplo: AGG_VENTAS_MENSUALES, AGG_VENTAS_POR_VENDEDOR

 ¿Se deben usar tablas agregadas para todas las tablas de una Base de Datos OLTP?

No, solamente se justifica hacer el trabajo cuando las tablas OLTP tienen cientos de miles o millones de filas. Si las tablas OLTP tienen pocos miles de filas no se justifica el esfuerzo

¿Qué importante consideración debemos tener siempre en mente?

Que para que las tablas agregadas sean útiles deben tener sus filas actualizadas con los de la tabla (o tablas) OLTP correspondientes. Cada vez que los datos de una tabla OLTP son cambiados (porque se agregaron/borraron/modificaron filas) la tabla agregada relacionada con esa tabla OLTP estará automáticamente desactualizada. Esto implica que consultando a la tabla agregada se podría obtener un resultado diferente que consultando a la tabla OLTP. Y eso muchas veces no es admisible.

¿Cuáles son las estrategias para insertar, borrar o actualizar datos en una tabla agregada?

 En la literatura sobre OLAP y Data Warehousing se nombran tres estrategias:

    • Snapshot
    • Eager
    • Lazy

En snapshot (instantánea, en castellano) los datos de la tabla agregada son totalmente borrados y luego se les insertan los nuevos datos. O sea que cada vez se agregan los datos desde cero. Implementar esta estrategia es muy simple pero tiene un gran problema: la utilización del Servidor durante el tiempo que dura la inserción de las filas será muy intensiva haciendo que el tráfico en la red se vuelva muy lento para las demás aplicaciones. Por ese motivo se lo suele realizar en los horarios en que menos usuarios están conectados a las bases de datos.

En eager (ansioso, en castellano) hay un trigger para cada operación de INSERT/DELETE/UPDATE en la tabla OLTP. Cada vez que en la tabla OLTP se realiza un DELETE o un UPDATE dicho trigger debe recalcular los valores que pondrá en la tabla agregada. Esto significa que no se agregarán los datos desde cero como se hace en snapshot pero implementar esta estrategia es más complicado. La ventaja de usar esta estrategia es que la tabla agregada siempre está actualizada, la desventaja es que las operaciones en la tabla OLTP son más lentas porque cada INSERT/DELETE/UPDATE en ella debe actualizar a la tabla agregada también.

En lazy (perezozo, en castellano) hay un trigger en la tabla OLTP que guarda los cambios en otra tabla, en una tabla separada. Cada una de las filas de la tabla separada tiene una columna que nos indica si ya fue procesada o aún no. Periódicamente un stored procedure lee el contenido de la tabla separada y por cada fila que aún no fue procesada realiza el eager y marca a la fila como ya procesada. Esto implica que al igual que en la estrategia eager se necesita de operaciones de escritura pero la actualización se puede realizar en un mejor momento, normalmente cuando hay pocos usuarios conectados. Esta estrategia es una mezcla de las dos anteriores: no se satura al Servidor porque nunca se procesan todas las filas de la tabla OLTP y las filas de la tabla agregada están casi, casi, actualizadas con los de la tabla OLTP.

Conclusión:

Usar tablas agregadas es muy útil para aumentar la velocidad de respuesta a las consultas. En Firebird las podemos implementar utilizando stored procedures y triggers. También podemos utilizar cualquiera de las tres estrategias (snapshot, eager, lazy) mencionadas más arriba. Por supuesto que tendremos datos redundantes pero en general no deberíamos tener problemas por eso.