Mostrando los resultados ordenados por cualquier criterio

8 comentarios

Cuando en un SELECT usamos la cláusula ORDER BY le estamos indicando al Firebird en cual orden queremos que las filas sean mostradas. En general ese orden es fijo y ya lo conocemos antes de escribir el SELECT. Sin embargo, en ocasiones no ocurre así sino que el orden de las filas depende de alguna condición.

Ejemplo:

Tenemos una tabla llamada PRODUCTOS la cual tiene las siguientes filas:

ORDEN1

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

Y queremos mostrar a esas filas ordenadas según un criterio muy particular, y no tenemos un índice creado que podamos usar. En este caso, lo que queremos es mostrar primero a todas las filas en cuyo nombre esté “350”, luego las filas en cuyo nombre está la palabra “LITRO” y luego las filas en cuyo nombre está la palabra “LITROS”.

Resumiendo:

  • Primero las filas que tienen “350”
  • Después las filas que tienen “LITRO”
  • Después las filas que tienen “LITROS”

Con el siguiente SELECT … ORDER BY obtendemos lo que buscamos:

SELECT
   PRD_IDENTI,
   PRD_NOMBRE,
   CASE
      WHEN PRD_NOMBRE CONTAINING '350'    THEN 1
      WHEN PRD_NOMBRE CONTAINING 'LITROS' THEN 3
      WHEN PRD_NOMBRE CONTAINING 'LITRO'  THEN 2
   END
FROM
   PRODUCTOS
ORDER BY
   CASE
      WHEN PRD_NOMBRE CONTAINING '350'    THEN 1
      WHEN PRD_NOMBRE CONTAINING 'LITROS' THEN 3
      WHEN PRD_NOMBRE CONTAINING 'LITRO'  THEN 2
   END

El primer CASE … END es opcional, no necesitamos escribirlo pero si lo escribimos nos ayuda a entender lo que sucede. El resultado será el siguiente:

ORDEN2

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

Entonces ¿por qué funciona lo que escribimos en la cláusula ORDER BY? porque hemos creado una columna virtual, y las filas se muestran ordenadas según esa columna virtual. O sea, primero todas las que tiene el valor 1, luego las que tienen el valor 2 y finalmente las que tienen el valor 3.

Desde luego que podríamos tener más valores si los necesitamos: 4, 5, 6, 7, …., etc.

¿Y por qué los WHEN que escribimos no están ordenados de menor a mayor?

Porque los caracteres “LITRO” está incluidos dentro de los caracteres “LITROS”. Si hubiéramos escrito primero el WHEN que tiene “LITRO” y luego el WHEN que tiene “LITROS” entonces no habríamos obtenido el resultado deseado. ¿Por qué? porque en ese caso “LITROS” habría tenido el valor 2 y no el valor 3, que es el que necesitamos.

Conclusión:

Es importante recordar que podemos mostrar a las filas por cualquier orden que se nos ocurra, y que no es necesario tener un índice para ello, y que la condición puede ser cualquiera. La técnica es crear una columna virtual (la cual por supuesto no es necesario mostrársela a los usuarios) y así las filas serán mostradas según el orden en que las hayamos colocado en esa columna virtual.

En este ejemplo se mostró la columna virtual, pero eso es para que se entienda la técnica, a los usuarios no necesitamos mostrársela.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

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,
      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,
      CLI_NOMBRE AS MVC_NOMCLI,
      MVC_TOTALX
   FROM
      MOVIMCAB
   JOIN
      CLIENTES
         ON MVC_IDECLI = 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

Columnas computadas

7 comentarios

Firebird además de las columnas normales nos permite tener columnas computadas.

¿Qué es una columna computada?

Una columna cuyo contenido depende de otras columnas, previamente definidas.

¿Para qué se usan las columnas computadas?

Para poder referenciar a los datos de varias formas, o sea que los datos pueden encontrarse en sus columnas originales o en las columnas computadas. También para escribir fórmulas que estén siempre accesibles.

¿Cómo se declara una columna computada?

Con la cláusula COMPUTED BY

Ejemplo:

Tenemos una tabla llamada ALUMNOS que tiene estas columnas (y varias más, pero que ahora no nos interesan):

CREATE TABLE ALUMNOS (
   ALU_IDENTI D_IDENTIFICADOR NOT NULL,
   ALU_NOMBRE D_NOMBRE25      NOT NULL,
   ALU_APELLD D_NOMBRE25      NOT NULL) ;

El dominio D_IDENTIFICADOR es un INTEGER y el dominio D_NOMBRE25 es un VARCHAR(25).

En la columna ALU_IDENTI se guarda el identificador del alumno, en ALU_NOMBRE sus nombres y en ALU_APELLD sus apellidos.

Pero en los informes queremos que aparezcan primero los apellidos, luego una coma, luego un espacio en blanco, y luego los nombres. Claro que podríamos conseguirlo realizando las concatenaciones correspondientes en cada caso pero lo más sencillo es tener una columna computada, como la siguiente:

ALU_APENOM COMPUTED BY (TRIM(ALU_APELLD) || ', ' || ALU_NOMBRE)

Entonces, la estructura de la tabla ALUMNOS quedaría así:

CREATE TABLE ALUMNOS (
   ALU_IDENTI D_IDENTIFICADOR NOT NULL,
   ALU_NOMBRE D_NOMBRE25      NOT NULL,
   ALU_APELLD D_NOMBRE25      NOT NULL,
   ALU_APENOM COMPUTED BY (TRIM(ALU_APELLD) || ', ' || ALU_NOMBRE)) ;

Si usas EMS SQL Manager, le dices que quieres agregar una columna a la tabla, como siempre lo haces, pero ahora eliges la opción “COMPUTED BY expression” y escribes la expresión que deseas:

COMPUTED3

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

 Si ahora escribimos un SELECT para ver los datos de los alumnos obtendremos algo similar a esto:

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

Fíjate en la columna ALU_APENOM, tiene los mismos datos que las columnas ALU_NOMBRE y ALU_APELLD, pero concatenados de la forma que determinamos anteriormente.

¿Cómo se cambia el valor de una columna computada?

La única forma es cambiando los valores de sus columnas base (en este ejemplo, de ALU_NOMBRE y de ALU_APELLD), si intentas algo como esto:

UPDATE
   ALUMNOS
SET
   ALU_APENOM = 'PRUEBA DE CAMBIO DE NOMBRE'
WHERE
   ALU_IDENTI = 1200

El Firebird se enojará contigo y te mostrará el mensaje:

This column cannot be updated because it is derived from an SQL function or expression.Attempted update of read-only column.

O sea que la columna no puede ser actualizada porque es derivada de una función o expresión SQL. Intentaste actualizar una columna que es read-only (sólo lectura).

¿Dónde se usa una columna computada?

  • En el comando SELECT para ver sus valores, para poner una condición de filtro (cláusula WHERE) o para mostrar a los datos ordenados (cláusula ORDER BY).
  • En el comando UPDATE y en el comando DELETE, cuando se coloca una condición de filtro (cláusula WHERE)

No se puede insertar un valor, ni actualizar un valor, ya que las columnas computadas obtienen sus valores de columnas previamente definidas.

SELECT
   ALU_APENOM
FROM
   ALUMNOS

Sí se puede (desde Firebird 2.0) indexar a una columna computada, pero hay que hacerlo de esta forma:

CREATE INDEX IDX_ALUMNOS1 ON ALUMNOS COMPUTED BY (TRIM(ALU_APELLD) || ', ' || ALU_NOMBRE);

Ya que si se intenta así:

CREATE INDEX IDX_ALUMNOS1 ON ALUMNOS ALU_APENOM

Se obtendrá un mensaje de error similar al siguiente:

Unsuccessful metadata update.
Attempt to index COMPUTED BY column in INDEX IDX_ALUMNOS1.

SQL Code: -607
IB Error Number: 335544351

El mensaje dice que la actualización de los metadatos no tuvo éxito, porque se intentó indexar una columna computada.


SELECT
   ALU_APENOM
FROM
   ALUMNOS
ORDER BY
   ALU_APENOM

COMPUTED3

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

¿Se puede cambiar una columna computada?

Se puede cambiar el tipo de datos y la expresión. No se puede cambiar una columna base en una columna computada o viceversa.

¿Las columnas computadas se usan solamente para concatenar columnas alfanuméricas?

No, también pueden usarse con columnas numéricas. Supongamos que tenemos una tabla de PRODUCTOS y deseamos saber cual es nuestro porcentaje de ganancia sobre el precio de costo, entonces podríamos escribir algo como esto:

PRD_PORCEN COMPUTED BY (PRD_PREVTA * 100 / PRD_PRECTO)

Donde PRD_PREVTA es el precio de venta del producto y PRD_PRECTO es el precio de costo del producto. En la columna PRD_PORCEN siempre tendremos los porcentajes de ganancia.

Desde luego que podríamos escribir cualquier otra fórmula matemática, no solamente esa.

Conclusión:

Las columnas computadas son muy útiles para que escribamos menos y podamos visualizar los datos en varias formas distintas, además las podemos usar con las cláusulas WHERE y ORDER BY.

No se puede indexar a una columna computada, pero sí se puede indexar las columnas que la componen.

Artículos relacionados:

Utilizando columnas computadas

Algunos ejemplos de uso de las columnas computadas

Usando un SELECT en una columna computada

Indexando una columna computada

El índice del blog Firebird21

El foro del blog Firebird21