Ordenando las columnas variables de las tablas agregadas

Deja un comentario

En artículos anteriores habíamos visto como crear tablas agregadas, de una forma muy fácil y sencilla pero  había un pequeño problema que quizás habrás notado: los nombres de las columnas variables aparecían en orden alfabético.

Y eso a veces puede ser lo que quieres y a veces no. Por ejemplo, si las columnas variables se refieren a nombres de productos, de clientes, de países, de alumnos, etc., entonces está bien mostrarlas ordenadas alfabéticamente, pero si se refieren a nombres de meses eso no es lo usual. No se acostumbra ver a los meses así: ABR, AGO, DIC, ENE, FEB, etc. sino que se acostumbra verlos así: ENE, FEB, MAR, ABR, MAY, etc.

Por ese motivo le hice una pequeña modificación al stored procedure CREAR_TABLA_PIVOT el cual ahora permite especificar el orden en el cual se mostrarán las columnas variables. La nueva versión es la siguiente:

CREATE PROCEDURE CREAR_TABLA_PIVOT(
   tcNombreTabla            VARCHAR(  28),
   tcVista                  VARCHAR(  28),
   tcPrimeraColumnaCabecera VARCHAR(  64),
   tcOtrasColumnasCabecera  VARCHAR(4096),
   tcColumnaDatos           VARCHAR(  28),
   tcTipoDatos              VARCHAR(  64),
   tcOrden                  VARCHAR(  28))
AS
   DECLARE VARIABLE lcCreate       VARCHAR(4096);
   DECLARE VARIABLE lcColumna      VARCHAR(  28);
   DECLARE VARIABLE lcMensajeError VARCHAR(1024);
BEGIN

   -- Primero, creamos la tabla

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

   lcCreate = lcCreate || tcPrimeraColumnaCabecera || ' NOT NULL, ';

   IF (Char_Length(tcOtrasColumnasCabecera) > 0) THEN BEGIN
      tcOtrasColumnasCabecera = tcOtrasColumnasCabecera || ',';
      WHILE (Char_Length(tcOtrasColumnasCabecera) > 0) DO BEGIN
         EXECUTE PROCEDURE Parser(tcOtrasColumnasCabecera, ',') RETURNING_VALUES :lcColumna;
         lcCreate = lcCreate || Trim(lcColumna) || ' , ' ;
         tcOtrasColumnasCabecera = Replace(tcOtrasColumnasCabecera, lcColumna || ',', '');
      END
   END

   FOR EXECUTE STATEMENT
      'SELECT DISTINCT ' || tcColumnaDatos || ' FROM ' || tcVista || IIF(Char_Length(:tcOrden) > 0, ' ORDER BY ' || :tcOrden, '')
   INTO
      :lcColumna
   DO BEGIN
      lcColumna = Left(lcColumna, 28);
      lcColumna = Replace(lcColumna, ' ', '_');
      lcColumna = Replace(lcColumna, '.', '_');
      lcColumna = Replace(lcColumna, '/', '_');
      lcColumna = Replace(lcColumna, '%', '_');
      lcCreate = lcCreate || lcColumna || ' ' || tcTipoDatos || ', ' ;
   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 (' || Left(:tcPrimeraColumnaCabecera, Position(' ', :tcPrimeraColumnaCabecera)) || ')';

END;

donde como puedes ver se agregó el parámetro de entrada tcOrden.

La vista V_VENTAS

CREATE VIEW V_VENTAS(
   MVC_IDECLI,
   MVC_NOMCLI,
   MVC_NUMANO,
   MVC_NUMMES,
   NOMBRE_MES,
   MVC_NOMCLI1,
   MVC_TOTALX)
AS
   SELECT
      MVC_IDECLI,
      C.CLI_NOMBRE AS MVC_NOMCLI,
      EXTRACT(YEAR  FROM MVC_FECHAX) AS MVC_NUMANO,
      EXTRACT(MONTH FROM MVC_FECHAX) AS MVC_NUMMES,
      DECODE(
         EXTRACT(MONTH FROM MVC_FECHAX),
             1, 'ENE',
             2, 'FEB',
             3, 'MAR',
             4, 'ABR',
             5, 'MAY',
             6, 'JUN',
             7, 'JUL',
             8, 'AGO',
             9, 'SEP',
            10, 'OCT',
            11, 'NOV',
            12, 'DIC') AS NOMBRE_MES,
      C.CLI_NOMBRE AS MVC_NOMCLI,
      M.MVC_TOTALX
   FROM
      MOVIMCAB M
   JOIN
      CLIENTES C
         ON M.MVC_IDECLI = C.CLI_IDENTI;

En esta vista tenemos una columna llamada MVC_NUMMES que usaremos para ordenar los nombres de los meses según el número de los meses.

Creando la tabla agregada AGR_VENTAS_MENSUALES

EXECUTE PROCEDURE
   CREAR_TABLA_PIVOT (
      'AGR_VENTAS_MENSUALES',
      'V_VENTAS',
      'MVC_NOMCLI VARCHAR(40)',
      '',
      'NOMBRE_MES',
      'INTEGER',
      'MVC_NUMMES')

El último parámetro nos indica el orden en el cual queremos ver a las columnas variables. Si no se especifica ese orden entonces las veremos ordenadas alfabéticamente. Como la columna MVC_NUMMES es de tipo numérico y en ella guardamos los números de los meses (1, 2, 3, 4, 5, etc.) entonces los nombres de los meses estarán ordenados según ese criterio y serán ENE, FEB, MAR, ABR, MAY, etc.

Estructura de la tabla AGR_VENTAS_MENSUALES

AGR1

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

Insertándole datos a la tabla AGR_VENTAS_MENSUALES

EXECUTE PROCEDURE
   ACTUALIZAR_TABLA_PIVOT (
      'AGR_VENTAS_MENSUALES',
      'V_VENTAS',
      'MVC_NOMCLI VARCHAR(40)',
      '',
      'NOMBRE_MES',
      'SUM(MVC_TOTALX)')

AGR2

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

Y listo, ya obtuvimos lo que queríamos obtener: los nombres de los clientes y a continuación los nombres de los meses. Si además del nombre del mes también quisiéramos tener el número del año sería muy fácil hacerlo: en la vista V_VENTAS reemplazamos la línea:

12, 'DIC') AS NOMBRE_MES,

por:

12, 'DIC') || '/' || EXTRACT(YEAR FROM MVC_FECHAX) AS NOMBRE_MES,

y después de ejecutar el stored procedure CREAR_TABLA_PIVOT esta será la estructura de nuestra tabla AGR_VENTAS_MENSUALES:

AGR3

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

Y después de insertarle datos con el stored procedure ACTUALIZAR_TABLA_PIVOT obtendremos esto:

AGR4

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

Así que ahora el nombre de cada columna variable está compuesto por el nombre del mes y el número del año, generalmente es mejor tenerlas de esta forma para que no se preste a confusión.

Artículos relacionados:

Aplicaciones OLTP y aplicaciones OLAP

Tablas agregadas

Creando tablas dentro de un stored procedure o de un trigger

Creando tablas agregadas e insertándoles datos

Creando y actualizando tablas agregadas de uso general

Otro ejemplo de uso de tablas agregadas

Ventas mensuales a cada cliente usando tablas agregadas

El índice del blog Firebird21

Ventas mensuales a cada cliente usando tablas agregadas

Deja un comentario

En este artículo hay otro ejemplo del uso de las tablas agregadas. Ahora, las usaremos para ver en columnas las ventas realizadas durante cada mes del año.

NOTA IMPORTANTE:

Los stored procedures CREAR_TABLA_PIVOT y ACTUALIZAR_TABLA_PIVOT han sido levemente modificados. Como seguramente sabes siempre el código fuente puede ser mejorado, y eso es justamente lo que sucedió con ellos. Las más nuevas versiones podrás descargar desde:

http://www.mediafire.com/view/ol58gyejul1t43a/CREAR_TABLA_PIVOT.txt

http://www.mediafire.com/view/1uebv8uerc5kwpe/ACTUALIZAR_TABLA_PIVOT.txt

Necesitarás de las nuevas versiones para que te funcionen los ejemplos que vienen a continuación.

Listado 1. La vista V_VENTAS

CREATE VIEW V_VENTAS(
   MVC_IDECLI,
   NOMBRE_MES,
   MVC_NOMCLI,
   MVC_TOTALX)
AS
   SELECT
      MVC_IDECLI,
      'MES_' ||
      LPAD(EXTRACT(MONTH FROM MVC_FECHAX), 2, '0') || '_' ||
      DECODE(
         EXTRACT(MONTH FROM MVC_FECHAX),
          1, 'ENE',
          2, 'FEB',
          3, 'MAR',
          4, 'ABR',
          5, 'MAY',
          6, 'JUN',
          7, 'JUL',
          8, 'AGO',
          9, 'SEP',
         10, 'OCT',
         11, 'NOV',
         12, 'DIC') AS NOMBRE_MES,
         C.CLI_NOMBRE AS MVC_NOMCLI,
         M.MVC_TOTALX
      FROM
         MOVIMCAB M
      JOIN
         CLIENTES C
            ON M.MVC_IDECLI = C.CLI_IDENTI;

AGREGADAS1

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

Ahora creamos la tabla agregada VENTAS1:

Listado 2. La tabla agregada VENTAS1

EXECUTE PROCEDURE
   CREAR_TABLA_PIVOT (
      'VENTAS1',
      'V_VENTAS',
      'MVC_IDECLI INTEGER',
      'MVC_NOMCLI VARCHAR(40)',
      'NOMBRE_MES',
      'NUMERIC(17, 4)')

AGREGADAS2

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

Y a continuación le agregamos datos:

Listado 3. Insertando filas a la tabla VENTAS1

EXECUTE PROCEDURE
   ACTUALIZAR_TABLA_PIVOT (
      'VENTAS1',
      'V_VENTAS',
      'MVC_IDECLI INTEGER',
      'MVC_NOMCLI VARCHAR(40)',
      'NOMBRE_MES',
      'SUM(MVC_TOTALX)')

AGREGADAS3

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

Y listo, ya está. Ya tenemos nuestra tabla agregada con un Cliente por cada fila y las ventas mensuales en las siguientes columnas. Este tipo de tablas puede ser muy útil para la toma de decisiones.

Fíjate que lo único que necesitamos hacer es escribir una vista que contenga los datos que utilizaremos, los stored procedures CREAR_TABLA_PIVOT y ACTUALIZAR_TABLA_PIVOT se encargan de todo lo demás.

Si no queremos ver los Identificadores de los clientes entonces podríamos crear la tabla de esta manera:

Listado 4. La tabla VENTAS1 sin los identificadores de los clientes

EXECUTE PROCEDURE
   CREAR_TABLA_PIVOT (
      'VENTAS1',
      'V_VENTAS',
      'MVC_NOMCLI VARCHAR(40)',
      '',
      'NOMBRE_MES',
      'NUMERIC(17, 4)')

La actualizaríamos así:

Listado 5. Insertando filas a la tabla VENTAS1 sin los identificadores de los clientes

EXECUTE PROCEDURE
   ACTUALIZAR_TABLA_PIVOT (
      'VENTAS1',
      'V_VENTAS',
      'MVC_NOMCLI VARCHAR(40)',
      '',
      'NOMBRE_MES',
      'SUM(MVC_TOTALX)')

Y obtendríamos este resultado:

AGREGADAS4

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

Que es bastante similar al anterior pero no idéntico ¿por qué no? porque en el anterior las filas aparecían ordenadas por Identificador del Cliente y en el actual aparecen ordenadas por Nombre del Cliente. Es decir que las filas siempre aparecen ordenadas por el contenido de la primera columna.

Artículos relacionados:

Creando y actualizando tablas agregadas de uso general

Otro ejemplo de uso de tablas agregadas

El índice del blog Firebird21

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
     M.MOI_CODSUC,
      M.MOI_IDECAB,
      M.MOI_IDEIMP,
      I.IMP_NOMBRE AS MOI_NOMIMP,
      M.MOI_MONTOX
   FROM
      MOVIMIMP M
   JOIN
      IMPUESTOS I
         ON M.MOI_CODSUC = I.IMP_CODSUC AND
            M.MOI_IDEIMP = I.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

Creando y actualizando tablas agregadas de uso general

1 comentario

En este artículo ya habíamos visto una forma de crear tablas agregadas y de insertarles datos:

https://firebird21.wordpress.com/2013/12/28/creando-tablas-agregadas-e-insertandoles-datos/

sin embargo, tenía un problema: para cada tabla agregada que decidieras crear tendrías que escribir dos stored procedures:

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

Si solamente vas a necesitar una o dos tablas agregadas eso no sería un problema pero si necesitarás 30 ó 40 tablas agregadas entonces ya es otro tema porque tendrías que escribir 60 u 80 stored procedures. Muy aburrido.

Por ese motivo escribí dos stored procedures de uso general. Siempre se necesitan al menos dos porque dentro de un stored procedure no se puede escribir un COMMIT y si no se le hizo el COMMIT a una tabla entonces no se le pueden insertar datos.

También escribí otro stored procedure llamado PARSER cuya misión es extraer un subtexto dentro de un texto.

El stored procedure PARSER


CREATE PROCEDURE PARSER(
      tcTexto     VARCHAR(18192),
      tcSeparador VARCHAR(12))
   RETURNS(
      ftcNombre    VARCHAR(1024))
AS
   DECLARE VARIABLE lnPosicion SMALLINT;
BEGIN

   lnPosicion = Position(tcSeparador IN tcTexto);

   ftcNombre = Left(tcTexto, lnPosicion - 1) ;

END;

Este stored procedure extrae de un texto un subtexto, para saber cual es el subtexto que debe extraer se usa un separador o delimitador. Algunos ejemplos:

PARSER de ‘hoy,es,un,día,soleado’ usando como delimitador la coma nos dará como resultado ‘hoy’

PARSER de ‘mañana*será*otro*día’ usando como delimitador el asterisco nos dará como resultado ‘mañana’

El delimitador puede tener más de un caracter si lo deseas. Está definido VARCHAR(12) así que podrías tener delimitadores de hasta 12 caracteres. Nunca necesitarás tanto, pero bueno, ahí está.

Las vista V_MOVIMIENTOS

Los datos que se insertarán en la tabla agregada deben tener un origen, o sea que deben salir de algún lado. Ese origen puede ser una tabla o una vista, en estos ejemplos usé una vista llamada V_MOVIMIENTOS.

CREATE VIEW V_MOVIMIENTOS(
   MOV_IDECAB,
   MOV_FECHAX,
   MOV_TIPDOC,
   MOV_NRODOC,
   MOV_IDEPRD,
   MOV_NOMPRD,
   MOV_CANTID)
AS
   SELECT
      D.MOV_IDECAB,
      C.MVC_FECHAX AS MOV_FECHAX,
      C.MVC_TIPDOC AS MOV_TIPDOC,
      C.MVC_NRODOC AS MOV_NRODOC,
      D.MOV_IDEPRD,
      P.PRD_NOMBRE AS MOV_NOMPRD,
      D.MOV_CANTID
   FROM
      MOVIMDET D
   JOIN
      MOVIMCAB C
         ON D.MOV_IDECAB = C.MVC_IDENTI
   JOIN
      PRODUCTOS P
         ON D.MOV_IDEPRD = P.PRD_IDENTI;

En la tabla PRODUCTOS tenemos los datos de los productos, en la tabla MOVIMDET (detalles de movimientos) los detalles de las ventas y en la tabla MOVIMCAB (cabecera de movimientos) los datos de cabecera.

PIVOT1

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

El stored procedure CREAR_TABLA_PIVOT

CREATE PROCEDURE CREAR_TABLA_PIVOT(
   tcNombreTabla            VARCHAR(28),
   tcVista                  VARCHAR(28),
   tcPrimeraColumnaCabecera VARCHAR(64),
   tcOtrasColumnasCabecera  VARCHAR(4096),
   tcColumnaDatos           VARCHAR(28),
   tcTipoDatos              VARCHAR(64))
AS
   DECLARE VARIABLE lcCreate VARCHAR(4096);
   DECLARE VARIABLE lcColumna VARCHAR(28);
   DECLARE VARIABLE lcMensajeError VARCHAR(1024);
BEGIN

   -- Primero, creamos la tabla

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

   lcCreate = lcCreate || tcPrimeraColumnaCabecera || ' NOT NULL, ';

   IF (Char_Length(tcOtrasColumnasCabecera) > 0) THEN BEGIN
      tcOtrasColumnasCabecera = tcOtrasColumnasCabecera || ',';
      WHILE (Char_Length(tcOtrasColumnasCabecera) > 0) DO BEGIN
         EXECUTE PROCEDURE Parser(tcOtrasColumnasCabecera, ',') RETURNING_VALUES :lcColumna;
         lcCreate = lcCreate || Trim(lcColumna) || ' , ' ;
         tcOtrasColumnasCabecera = Replace(tcOtrasColumnasCabecera, lcColumna || ',', '');
      END
   END

   FOR EXECUTE STATEMENT
      'SELECT DISTINCT ' || tcColumnaDatos || ' FROM ' || tcVista INTO :lcColumna
   DO BEGIN
      lcColumna = Left(lcColumna, 28);
      lcColumna = Replace(lcColumna, ' ', '_');
      lcColumna = Replace(lcColumna, '.', '_');
      lcColumna = Replace(lcColumna, '/', '_');
      lcColumna = Replace(lcColumna, '%', '_');
      lcCreate = lcCreate || lcColumna || ' ' || tcTipoDatos || ', ' ;
   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 (' || Left(:tcPrimeraColumnaCabecera, Position(' ', :tcPrimeraColumnaCabecera)) || ')';

END;

Este stored procedure te permite crear una tabla y es totalmente general, esa tabla puede tener la cantidad de columnas fijas que desees. Puedes crear una tabla con 1 columna fija, otra tabla con 2 columnas fijas, otra tabla con 3 columnas fijas, etc. Las columnas variables son ilimitadas pero debes mantener un número que sea manejable. En estos ejemplos si tienes 4 productos entonces tendrás 4 columnas, si tienes 20 productos entonces tendrás 20 columnas. Pero si tienes 300 productos nadie se molestará en mirar todas las columnas de la tabla, así que sería una pérdida de tiempo crearla.

Mi regla cuando uso tablas agregadas es nunca crearlas con más de 12 columnas variables porque simplemente nadie las utiliza cuando tienen más columnas, así que ni vale la pena crearlas.

El stored procedure ACTUALIZAR_TABLA_PIVOT

CREATE PROCEDURE ACTUALIZAR_TABLA_PIVOT(
   tcTablaPivot             VARCHAR(  28),
   tcVista                  VARCHAR(  28),
   tcPrimeraColumnaCabecera VARCHAR(  64),
   tcOtrasColumnasCabecera  VARCHAR(4096),
   tcColumnaDatos           VARCHAR(  28),
   tcValoresDatos           VARCHAR(1024))
AS
   DECLARE VARIABLE lcOtrasColumnasCabecera VARCHAR(4096);
   DECLARE VARIABLE lcSelect                VARCHAR(4096);
   DECLARE VARIABLE lcColumna               VARCHAR(  28);
   DECLARE VARIABLE lcGrupo                 VARCHAR(4096);
   DECLARE VARIABLE lcInto1                 VARCHAR(1024);
   DECLARE VARIABLE lcInto2                 VARCHAR(1024);
   DECLARE VARIABLE lcInto3                 VARCHAR(1024);
   DECLARE VARIABLE lcActualizar            VARCHAR(1024);
BEGIN

   lcOtrasColumnasCabecera = tcOtrasColumnasCabecera;

   lcSelect = 'SELECT ' ||
              Left(tcPrimeraColumnaCabecera, Position(' ', tcPrimeraColumnaCabecera) - 1) || ', ' ;

   lcGrupo = Left(tcPrimeraColumnaCabecera, Position(' ', tcPrimeraColumnaCabecera) - 1) || ', ' ;

   IF (Char_Length(lcOtrasColumnasCabecera) > 0) THEN BEGIN
      lcOtrasColumnasCabecera = lcOtrasColumnasCabecera || ',' ;
      WHILE (Char_Length(lcOtrasColumnasCabecera) > 0) DO BEGIN
         EXECUTE PROCEDURE Parser(lcOtrasColumnasCabecera, ',') RETURNING_VALUES :lcColumna;
         lcGrupo = lcGrupo || Left(lcColumna, Position(' ', lcColumna) - 1) || ',' ;
         lcOtrasColumnasCabecera = Replace(lcOtrasColumnasCabecera, lcColumna || ',', '') ;
      END
   END

   lcSelect = lcSelect ||
              tcColumnaDatos || ', ' ||
              tcValoresDatos ||
              ' FROM ' || tcVista ||
              ' GROUP BY ' || lcGrupo ||
              tcColumnaDatos ;

   FOR EXECUTE STATEMENT
      lcSelect
   INTO
      :lcInto1,
      :lcInto2,
      :lcInto3
   DO BEGIN
      -- Aquí se insertan o actualizan la primera columna y los datos agrupados
      lcInto2 = Left(lcInto2, 28);
      lcInto2 = Replace(lcInto2, ' ', '_');
      lcInto2 = Replace(lcInto2, '.', '_');
      lcInto2 = Replace(lcInto2, '/', '_');
      lcInto2 = Replace(lcInto2, '%', '_');
      lcActualizar = 'UPDATE OR INSERT INTO ' ||
                     tcTablaPivot || ' (' ||
                     Left(tcPrimeraColumnaCabecera, Position(' ', tcPrimeraColumnaCabecera) - 1) || ',' ||
                     lcInto2 ||
                     ') VALUES(' ||
                     lcInto1 || ',' ||
                     lcInto3 || ')' ;
      EXECUTE STATEMENT lcActualizar;
      IF (Char_Length(Trim(tcOtrasColumnasCabecera)) > 0) THEN BEGIN
         lcOtrasColumnasCabecera = tcOtrasColumnasCabecera || ',' ;
         lcActualizar = 'UPDATE ' || tcTablaPivot || ' SET ' ;
         WHILE (Char_Length(lcOtrasColumnasCabecera) > 0) DO BEGIN
            -- Aquí se actualizan todas las demás columnas de la cabecera
            EXECUTE PROCEDURE Parser(lcOtrasColumnasCabecera, ',') RETURNING_VALUES :lcColumna;
            lcActualizar = lcActualizar ||
                           Left(lcColumna, Position(' ', lcColumna) - 1) ||
                           ' = (SELECT ' || Left(lcColumna, Position(' ', lcColumna) - 1) ||
                           ' FROM ' || tcVista ||
                           ' WHERE ' ||
                           Left(tcPrimeraColumnaCabecera, Position(' ', tcPrimeraColumnaCabecera) - 1) ||
                           '=' || lcInto1 ||
                           ' ROWS 1),' ;
            lcOtrasColumnasCabecera = Replace(lcOtrasColumnasCabecera, lcColumna || ',', '') ;
         END
         lcActualizar = Left(lcActualizar, Char_Length(lcActualizar) - 1) ||
                       ' WHERE ' ||
                       Left(tcPrimeraColumnaCabecera, Position(' ', tcPrimeraColumnaCabecera) - 1) || '=' ||
         lcInto1;
         EXECUTE STATEMENT lcActualizar;
      END
   END

END;

Este stored procedure es el encargado de insertarle datos a la tabla agregada. No importa cuantas columnas tenga la tabla, él se encargará de la inserción y actualización de los datos.

Ejemplo

EXECUTE PROCEDURE
   CREAR_TABLA_PIVOT(
      'PRUEBA1',
      'V_MOVIMIENTOS',
      'MOV_IDECAB INTEGER',
      'MOV_FECHAX DATE,MOV_TIPDOC SMALLINT,MOV_NRODOC VARCHAR(15)',
      'MOV_NOMPRD',
      'NUMERIC(17, 4)')

PIVOT3

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

Como puedes ver, la tabla PRUEBA1 se creó exactamente como se le indicó.

EXECUTE PROCEDURE ACTUALIZAR_TABLA_PIVOT(
   'PRUEBA1',
   'V_MOVIMIENTOS',
   'MOV_IDECAB INTEGER',
   'MOV_FECHAX DATE,MOV_TIPDOC SMALLINT,MOV_NRODOC VARCHAR(15)',
   'MOV_NOMPRD',
   'SUM(MOV_CANTID)')

Y este es el stored procedure encargado de insertarle y de actualizarle las filas a la tabla PRUEBA1. Al ejecutarlo esto es lo que obtenemos:

PIVOT2

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

Donde tenemos 4 columnas fijas (MOV_IDECAB, MOV_FECHAX, MOV_TIPDOC, MOV_NRODOC) y 4 columnas variables (todas las demás).

Los parámetros de entrada de los stored procedures

tcNombreTabla. Es el nombre que queremos darle a la tabla que vamos a crear, o el nombre que tiene la tabla a la cual le insertaremos datos. Para saber si la tabla ya existe y no intentar crearla en ese caso podrías escribir:

lcTablaExiste = IIF((SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'PRUEBA1') = 'PRUEBA1', 'S', 'N')

Si el valor de la variable lcTablaExiste es igual a ‘S’ entonces ya existe una tabla con el nombre ‘PRUEBA1’.

tcVista. Es el nombre de la vista o de la tabla que contiene los datos que serán agrupados.

tcPrimeraColumnaCabecera. Es el nombre que veremos en la primera columna. Este dato siempre debe especificarse porque no se puede crear ni usar la tabla si no se lo especifica. Las filas de la vista tcvista son agrupadas según esta columna. Fíjate que debes escribir el nombre de una columna de tcvista y también su tipo de datos (SMALLINT, INTEGER, etc.).

tcOtrasColumnasCabecera. Esto es opcional, no es obligatorio tener más de una columna fija. Si las usarás entonces deberás escribir el nombre de la columna, su tipo de datos (INTEGER, DATE, NUMERIC, etc.) y luego una coma para separar a una columna de la siguiente.

tcColumnaDatos. Es la columna que tiene los datos variables. En nuestro ejemplo, es la columna donde se tienen registrados los nombres de los productos. En otros casos podría tener nombres de alumnos, de profesores, de países, de libros, etc.

tcTipoDatos. Es el tipo de los datos variables. Debe ser uno numérico (SMALLINT, INTEGER, DECIMAL, NUMERIC, etc.) y se lo necesita cuando se crea la tabla.

tcValoresDatos. En general se trata de una función agregada. El resultado de ejecutar la función agregada es el que se guardará en las columnas variables. Ejemplos: SUM(MOV_CANTID), SUM(MOV_CANTID * MOV_PRECIO), MAX(MOV_PRECIO), etc.

Manejo de errores

Para que los stored procedures no sean muy largos no les escribí código que se encargue de atrapar los errores, pero es algo que tú deberías hacer para evitar errores como los siguientes:

  • No existe la tabla
  • No existe el nombre de la columna
  • No existe el tipo de datos
  • El tipo de datos es incorrecto
  • etc.

Conclusión:

Si usamos tablas agregadas nuestras aplicaciones se potenciarán muchísimo porque ellas les permiten a los gerentes analizar lo que está ocurriendo y por lo tanto son de una gran ayuda para la toma de decisiones.

Los stored procedures que has visto en este artículo son de uso general, o sea que te servirán con cualquier tabla y con cualquier vista. Además, puedes tener todas las columnas fijas y todas las columnas variables que desees. Sin embargo, debes recordar que la mayoría de las personas no mirarán tablas que tengan más de 12 columnas variables y eso implica que en tu vista debes poner condiciones que limiten la cantidad de filas con nombres de productos (o de lo que sea) distintos.

Artículos relacionados:

Creando tablas agregadas e insertándoles datos

El índice del blog Firebird21

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
      P.PRD_NOMBRE AS MOV_NOMPRD
   FROM
      MOVIMDET D
   JOIN
      PRODUCTOS P
         ON D.MOV_IDEPRD = P.PRD_IDENTI
   ORDER BY
      D.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
      D.MOV_IDECAB,
      P.PRD_NOMBRE,
      SUM(D.MOV_CANTID)
   FROM
      MOVIMDET D
   JOIN
      PRODUCTOS P
         ON D.MOV_IDEPRD = P.PRD_IDENTI
   GROUP BY
      D.MOV_IDECAB,
      P.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

¿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
   C.MVC_FECHAX,
   C.MVC_NRODOC,
   P.PRD_NOMBRE,
   D.MOV_CANTID,
   D.MOV_PRECIO,
   D.MOV_CANTID * D.MOV_PRECIO AS TOTALPRODUCTO
FROM
   MOVIMDET D
JOIN
   MOVIMCAB C
      ON D.MOV_IDECAB = C.MVC_IDENTI
JOIN
   PRODUCTOS P
      ON D.MOV_IDEPRD = P.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
   C.MVC_FECHAX,
   P.PRD_NOMBRE,
   SUM(D.MOV_CANTID * D.MOV_PRECIO) AS TOTALPRODUCTO
FROM
   MOVIMDET D
JOIN
   MOVIMCAB C
      ON D.MOV_IDECAB = C.MVC_IDENTI
JOIN
   PRODUCTOS P
      ON D.MOV_IDEPRD = P.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
      D.MOV_IDEPRD,
      C.MVC_FECHAX,
      SUM(D.MOV_CANTID * D.MOV_PRECIO) AS TOTALPRODUCTO
   FROM
      MOVIMDET D
   JOIN
      MOVIMCAB C
         ON D.MOV_IDECAB = C.MVC_IDENTI
   GROUP BY
      1, 2
)

SELECT
   P.PRD_IDENTI,
   P.PRD_NOMBRE,
   V1.TOTALPRODUCTO AS "15/02/2011",
   V2.TOTALPRODUCTO AS "16/02/2011",
   V3.TOTALPRODUCTO AS "17/02/2011"
FROM
   PRODUCTOS P
LEFT JOIN
   VENTASDIA V1
      ON P.PRD_IDENTI = V1.MOV_IDEPRD AND
         V1.MVC_FECHAX = '02/15/2011'
LEFT JOIN
   VENTASDIA V2
      ON P.PRD_IDENTI = V2.MOV_IDEPRD AND
         V2.MVC_FECHAX = '02/16/2011'
LEFT JOIN
   VENTASDIA V3
      ON P.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

Older Entries