Restando fechas que se encuentran en distintas filas (2)

Deja un comentario

Ya habíamos visto como conseguir restar fechas que se encuentran en distintas filas:

Restando fechas que se encuentran en distintas filas

Ahora en este artículo veremos otro método para conseguir el mismo resultado. Para ello usaremos tablas autoreferenciadas.

Una tabla autoreferenciada es una tabla que tiene un JOIN a sí misma, y es algo que puede ser muy útil a veces.

Listado 1.


CREATE GENERATOR
   GEN_NUMERAR;

UPDATE
   PRUEBA1
SET
   PRU_NUMREG = GEN_ID(GEN_NUMERAR, 1);

DROP GENERATOR
   GEN_NUMERAR;

SELECT
   T1.PRU_NUMREG,
   T1.PRU_FECINI,
   T2.PRU_FECFIN,
   T2.PRU_FECFIN - T1.PRU_FECINI AS DIFERENCIA
FROM
   PRUEBA1 T1
LEFT JOIN
   PRUEBA1 T2
      ON T1.PRU_NUMREG = T2.PRU_NUMREG - 1

Como puedes ver en el Listado 1., la primera parte es igual a la que habíamos escrito en el otro artículo, lo único que cambia es el SELECT.

Usamos un LEFT JOIN para que siempre se muestre la Fecha Inicial, haya o no haya una Fecha Final correspondiente. Podrías usar solamente JOIN si únicamente te interesan las filas que tienen una Fecha Inicial y su correspondiente Fecha Final.

Nuestra tabla PRUEBA1 tiene estas filas:

FECHA02

Captura 1.

Y después de ejecutar el Listado 1. obtendremos lo que vemos en la Captura 2.

FECHA01

Captura 2.

Artículos relacionados:

Ejemplo Nº 004 – Usando un INNER JOIN para autoreferenciar una tabla

Autoreferenciar una tabla. Algunos ejemplos

Entendiendo las tablas autoreferenciadas

Usando una tabla autoreferenciada para averiguar números consecutivos faltantes

Restando fechas que se encuentran en distintas filas

El índice del blog Firebird21

El foro del blog Firebird21

Ejemplo de recursión (4). Actualizando filas recursivamente

3 comentarios

Así como podemos consultar filas de forma recursiva, también podemos actualizar esas filas recursivamente si es lo que necesitamos.

Veamos un caso:

Tenemos una tabla llamada CUENTAS que tiene estos datos:

UPDATE1

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

En la columna CUE_IDENTI guardamos el identificador de cada fila; en la columna CUE_IDEPAD guardamos el identificador de su fila padre; en la columna CUE_TIMEST guardamos la fecha y la hora de la última actualización de una cuenta. Ahora supongamos que queremos que la cuenta CRÉDITOS y todas las cuentas que sean sus descendientes tengan la fecha y la hora actuales.

Listado 1.

UPDATE
   CUENTAS
SET
   CUE_TIMEST = CURRENT_TIMESTAMP
WHERE
   CUE_IDENTI IN (
      WITH RECURSIVE MiCTE AS (
         
         SELECT
            CUE_IDENTI
         FROM
            CUENTAS
         WHERE
            CUE_IDENTI = 6
         
         UNION ALL
         
         SELECT
            T1.CUE_IDENTI
         FROM
            CUENTAS T1
         JOIN
            MiCTE 
               ON T1.CUE_IDEPAD = MiCTE.CUE_IDENTI
         
      )
      
      SELECT CUE_IDENTI FROM MiCTE

   );

UPDATE2

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

Tal y como podemos ver en la Captura 2. se actualizó la columna CUE_TIMEST de la cuenta que tiene CUE_IDENTI =6 y también de todos sus descendientes. En el Listado 1. podemos ver que la primera cuenta a ser actualizada es la que tiene CUE_IDENTI = 6, a continuación se actualizan las demás filas.

Desde luego que no solamente podemos cambiar la fecha y la hora al actualizar, podemos cambiar cualquier dato que necesitemos.

Explicación:

Como en la cláusula WHERE del UPDATE estamos usando IN eso significa que queremos actualizar varias filas. ¿Qué filas actualizaremos? Aquellas cuya columna CUE_IDENTI se encuentre en la tabla virtual que crearemos. En nuestra tabla virtual la primera fila es la que tiene CUE_IDENTI = 6, así que esa fila sí o sí será actualizada. A continuación hallamos el CUE_IDENTI de las demás filas que queremos actualizar. Como la tabla virtual es recursiva entonces se le irán agregando filas hasta que la condición del JOIN deje de cumplirse. El último SELECT es el que devuelve todas las filas de la tabla virtual, y todas esas filas tienen una sola columna, llamada CUE_IDENTI.

Entonces, nuestro UPDATE recursivo sería el equivalente a este UPDATE no recursivo.

Listado 2.

UPDATE
   CUENTAS
SET
   CUE_TIMEST = CURRENT_TIMESTAMP
WHERE
   CUE_IDENTI IN (6, 7, 8, 9, 10)

¿Cuál es la diferencia? Que en el Listado 2. debemos conocer  todos los identificadores que deseamos actualizar, en cambio en el Listado 1., no. Allí solamente necesitamos conocer el identificador de la primera cuenta, nada más. Todas las cuentas que sean sus descendientes serán actualizadas, sean 4 como en este caso o sean 500 o sean 1000. La única limitación es que pueden ser como máximo 1024 porque el Firebird permite hasta 1024 recursiones.

El Listado 2. es más sencillo, pero requiere conocer los identificadores de todas las cuentas que queremos actualizar y si los usuarios están constantemente agregando cuentas tendremos que estar cambiando nuestro UPDATE a cada rato. Por el contrario con el Listado 1. lo único que debemos conocer es el identificador de la primera cuenta que deseamos actualizar y todas las cuentas que sean sus descendientes se actualizarán también.

Artículos relacionados:

Stored procedures recursivos

Entendiendo a las tablas autoreferenciadas

Usando CTE (Common Table Expression)

Otro ejemplo de CTE: ventas semanales

Usando varias CTE en una vista o en un stored procedure

FOR SELECT y tablas CTE

Usando recursividad con CTE

Ejemplo de recursión (1). Filas salteadas

Ejemplo de recursión (2). Numerar filas

Ejemplo de recursión (3). Fechas consecutivas

El índice del blog Firebird21

El foro del blog Firebird21

Ejemplo de recursión (3). Fechas consecutivas

Deja un comentario

Ya hemos visto varios ejemplos de tablas virtuales recursivas, pero como es un tema que le interesa a mucha gente entonces ahora veremos otro ejemplo de lo que podemos hacer.

Problema:

Queremos ver todas las fechas desde hoy hasta los siguientes 10 días.

Solución:

Listado 1.

WITH RECURSIVE FECHAS_SIGUIENTES AS (
   
   SELECT
      CURRENT_DATE AS FECHA
   FROM
      RDB$DATABASE

   UNION ALL
   
   SELECT
      DATEADD(DAY, 1, FECHA)
   FROM
      FECHAS_SIGUIENTES
   WHERE
      FECHA < DATEADD(DAY, 10, CURRENT_DATE)

)

SELECT
   *
FROM
   FECHAS_SIGUIENTES

EJEMPLO3-1

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

Como siempre, nuestra tabla virtual recursiva empieza con una fila no recursiva y luego se le agregan las filas recursivas. La filas recursivas siempre deben colocarse después del UNION ALL.

El algoritmo es muy sencillo. Primero, insertamos a nuestra tabla virtual la primera fila que nos interesa, luego le insertamos otra fila conteniendo la fecha del día siguiente y continuamos insertando filas mientras la fecha obtenida sea menor que la fecha actual + 10.

Desde luego que podemos usar otro número, 10 es solamente un ejemplo. Podríamos obtener 30 fechas, 60 fechas, 365 fechas, o las que necesitemos, siempre y cuando su cantidad no sea mayor a 1024 porque ese es el límite de llamadas recursivas que permite el Firebird.

¿Y para qué nos podría servir tener una tabla virtual de fechas?

Por ejemplo, para listar todas las ventas entre dos fechas, y si en una fecha no se ha realizado ventas que muestre cero. Así nos aseguraríamos que estén todas las fechas, sin que existan fechas faltantes.

RECURSION1

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

Como puedes ver en la Captura 1. no hubo ventas todos los días, por ejemplo no hay ventas el 1 de agosto ni el 3 de agosto. En nuestra consulta queremos que esas dos fechas también aparezcan, aunque no hayamos vendido.

Listado 2.

WITH RECURSIVE RANGO_FECHAS AS (

   SELECT
      CAST('2015/08/01' AS DATE) AS FECHA
   FROM
      RDB$DATABASE

   UNION ALL

   SELECT
      DATEADD(DAY, 1, FECHA)
   FROM
      RANGO_FECHAS
   WHERE
      FECHA < CAST('2015/08/20' AS DATE)

)

SELECT
   FECHA,
   COALESCE(SUM(FAC_MONTOX), 0) AS TOTAL_VENTA
FROM
   RANGO_FECHAS
LEFT JOIN
   FACTURAS
      ON FECHA = FAC_FECVEN
GROUP BY
   FECHA

RECURSION2

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

Y listo. ¡¡¡Solucionado!!!

Como puedes ver en la Captura 2. se muestran todas las fechas entre el 1 de agosto y el 20 de agosto (porque esas fueron las que elegimos, podríamos haber elegido cualquier otro rango de fechas) con el total vendido cada uno de esos días. Si en una fecha no hubo ventas, entonces se muestra cero.

Conclusión:

Poder listar todo un rango de fechas es muy útil cuando queremos ver a todas esas fechas, sin importar que en la tabla relacionada haya o no haya fechas que se puedan emparejar. Tal como nos muestra la Captura 2. si en una fecha no hubo ventas, igualmente esa fecha es mostrada.

Artículos relacionados:

Stored procedures recursivos

Entendiendo a las tablas autoreferenciadas

Usando CTE (Common Table Expression)

Otro ejemplo de CTE: ventas semanales

Usando varias CTE en una vista o en un stored procedure

FOR SELECT y tablas CTE

Usando recursividad con CTE

Ejemplo de recursión (1). Filas salteadas

Ejemplo de recursión (2). Numerar filas

El índice del blog Firebird21

El foro del blog Firebird21

Los alias de las columnas

3 comentarios

En Firebird tenemos la posibilidad de darles nombres alternativos, normalmente conocidos como “alias”, a las columnas de un SELECT pero ¿dónde podemos usar esos alias?

SELECT
   MiColumna1 AS UnAlias,
   MiColumna2 AS OtroAlias
FROM
   MiTabla

Según el estándar SQL (que Firebird cumple) los alias se usan para darle títulos a las columnas. Entonces ¿en qué momento Firebird conoce cuál es el alias de una columna y puede usarlo?

Solamente después de que conoce el valor de todas las columnas, nunca antes. Por lo tanto un alias no puede ser usado en otra columna, ni en la cláusula WHERE ni en la cláusula JOIN pero sí en las cláusulas GROUP BY y ORDER BY.

Ejemplo 1. No se puede usar ALIAS aquí.

Listado 1.

SELECT
   MOV_CANTID * MOV_PRECIO AS TOTAL_PRODUCTO,
   TOTAL_PRODUCTO / 11 AS TOTAL_IMPUESTO
FROM
   MOVIMDET

En el Listado 1. tenemos un alias llamado TOTAL_PRODUCTO y luego quisimos usarlo en la siguiente columna. Eso no está permitido, fallará.

Ejemplo 2. No se puede usar ALIAS aquí

Listado 2.

SELECT
   MOV_CANTID * MOV_PRECIO AS TOTAL_PRODUCTO
FROM
   MOVIMDET
WHERE
   TOTAL_PRODUCTO >= 100000

En el Listado 2. tenemos un alias llamado TOTAL_PRODUCTO y luego quisimos usarlo en la cláusula WHERE. Tampoco está permitido y fallará.

Ejemplo 3. No se puede usar ALIAS aquí

Listado 3.

SELECT
   MOV_CANTID * MOV_PRECIO AS TOTAL_PRODUCTO
FROM
   MOVIMDET
JOIN
   MOVIMCAB
      ON MVC_TOTALX > TOTAL_PRODUCTO * 2

En el Listado 3. tenemos un alias llamado TOTAL_PRODUCTO y quisimos usarlo en un JOIN. Eso no está permitido y fallará.

Ejemplo 4. Sí se puede usar ALIAS aquí.

Listado 4.

SELECT
   MOV_CANTID * MOV_PRECIO AS TOTAL_PRODUCTO
FROM
   MOVIMDET
GROUP BY
   TOTAL_PRODUCTO

En el Listado 4. tenemos un alias llamado TOTAL_PRODUCTO y queremos usarlo en la cláusula GROUP BY. Eso sí está permitido porque los nombres de todas las columnas ya están definidos.

Ejemplo 5. Sí se puede usar ALIAS aquí

Listado 5.

SELECT
   MOV_CANTID * MOV_PRECIO AS TOTAL_PRODUCTO
FROM
   MOVIMDET
ORDER BY
   TOTAL_PRODUCTO

En el Listado 5. tenemos un alias llamado TOTAL_PRODUCTO y queremos usarlo en la cláusula ORDER BY. Eso sí está permitido porque los nombres de todas las columnas ya están definidos.

Conclusión:

Solamente se puede usar un alias previamente definido después que todas las columna del SELECT han sido evaluadas, nunca antes. Por ese motivo no se puede usar usar dentro de la lista de columnas ni en la cláusula WHERE ni en la cláusula JOIN pero sí puede usarse en la cláusula GROUP BY y en la cláusula ORDER BY.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Obteniendo las primeras o las últimas Facturas de cada Cliente

4 comentarios

Ante una consulta en el foro me pareció interesante mostrar aquí en el blog como podemos obtener las primeras o las últimas Facturas vendidas a un Cliente. Por supuesto que no es el único caso, hay miles de casos similares, que pueden involucrar a productos, proveedores, vendedores, cobradores, o lo que sea. Lo importante es conocer la técnica para poder responder a esta clase de preguntas.

Veamos el problema:

En nuestra tabla cabecera de ventas (llamada VENTASCAB) guardamos el Identificador del Cliente, el Número de la Factura de venta y otros datos más. Quisiéramos obtener:

  1. Las 3 primeras Facturas vendidas a cada Cliente
  2. Las 3 últimas Facturas vendidas a cada Cliente

FACTURAS01

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

En la Captura 1. vemos algunos identificadores y algunos números de Factura, para que se entienda el ejemplo.

Caso 1. Obteniendo las 3 primeras Facturas vendidas a cada Cliente

Listado 1.

SELECT
   A.VTC_IDECLI,
   A.VTC_NRODOC
FROM
   VENTASCAB A
WHERE
   A.VTC_NRODOC IN (SELECT 
                       FIRST 3 
                       B.VTC_NRODOC 
                    FROM 
                       VENTASCAB B 
                    WHERE 
                       A.VTC_IDECLI = B.VTC_IDECLI
                    ORDER BY
                       B.VTC_IDECLI,
                       B.VTC_NRODOC
                   )
ORDER BY
   A.VTC_IDECLI,
   A.VTC_NRODOC

FACTURAS02

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

Caso 2. Obteniendo las 3 últimas Facturas vendidas a cada Cliente

Listado 2.

SELECT
   A.VTC_IDECLI,
   A.VTC_NRODOC
FROM
   VENTASCAB A
WHERE
   A.VTC_NRODOC IN (SELECT 
                       FIRST 3 
                       B.VTC_NRODOC 
                    FROM 
                       VENTASCAB B 
                    WHERE 
                       A.VTC_IDECLI = B.VTC_IDECLI
                    ORDER BY
                       B.VTC_IDECLI DESCENDING,
                       B.VTC_NRODOC DESCENDING
                   )
ORDER BY
   A.VTC_IDECLI,
   A.VTC_NRODOC

FACTURAS03

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

Explicación:

Cuando en la cláusula WHERE usamos el predicado IN lo que hacemos es un filtrado múltiple, o sea cuando varias filas pueden cumplir la condición de filtro. Aquí lo que necesitamos son 3 filas por cada cliente, entonces mediante una subconsulta obtenemos esas 3 filas. Como la subconsulta está ordenada por Identificador del Cliente y Número de la Factura entonces en el Caso 1. obtenemos las primeras 3 Facturas de cada cliente. Para obtener las 3 últimas Facturas del cliente ordenamos a la subconsulta de mayor a menor, o sea de forma descendente, como vemos en el Caso 2.

Fíjate que la palabra DESCENDING debe colocarse a continuación de cada columna, porque si no se coloca a continuación de una columna entonces esa columna estará ordenada de forma ascendente, que es lo normal, pero no es lo que necesitamos. Aquí necesitamos que ambas columnas se ordenen de forma descendente.

Importantísimo: Debemos tener un índice ascendente compuesto por las columnas VTC_IDECLI y VTC_NRODOC y otro índice descendente también compuesto por las columnas VTC_IDECLI y VTC_NRODOC. ¿Por qué? porque si no tenemos esos índices entonces el Firebird se verá obligado a ordenar la tabla y eso en tablas grandes puede demorarse una eternidad. Así que, si escribes una consulta similar a la del Caso 1. o a la del Caso 2. y los minutos pasan y pasan y no obtienes el resultado de la consulta, es seguro que te falta crear uno de los índices o ambos.

Conclusión:

Cuando queremos filtrar una tabla para ver solamente algunas de las filas, una de las técnicas es usar el predicado IN en la cláusula WHERE.

El predicado IN es una forma abreviada de escribir el operador de comparación OR y es muy útil para escribir menos y entender mejor.

Si no tenemos índices, consultas como las del Caso 1. y del Caso 2. pueden demorarse muchísimo, así que debemos crear esos índices para obtener una buena velocidad de respuesta.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Poniendo los JOIN en el orden correcto

1 comentario

Muchas veces cuando escribimos un SELECT que no es muy sencillo necesitamos relacionar a una tabla con otra u otras tablas. Eso lo hacemos con las cláusulas JOIN.

Como sabemos, hay dos clases de JOIN:

  • INNER JOIN
  • OUTER JOIN

La palabra INNER puede omitirse si se desea, ya que es la que por defecto asumirá el Firebird si no se escribe algo antes de la palabra JOIN. Los OUTER a su vez pueden ser: LEFT, RIGHT, FULL

Entonces, la pregunta es: ¿importa si se escribe primero un INNER o un OUTER? ¿O es lo mismo?

Consulta 1. Poniendo primero el INNER JOIN

SELECT
   *
FROM
   MiTabla1
JOIN
   MiTabla2
      ON MiCondición1
LEFT JOIN
   MiTabla3
      ON MiCondición2

Aquí se escribió primero el INNER JOIN (la palabra INNER no es obligatorio escribir) y luego el OUTER JOIN (la palabra OUTER tampoco es obligatorio escribir, al escribir LEFT ya el Firebird sabe que se trata de un OUTER)

Consulta 2. Poniendo primero el OUTER JOIN

SELECT
   *
FROM
   MiTabla1
LEFT JOIN
   MiTabla3
      ON MiCondición2
JOIN
   MiTabla2
      ON MiCondición1

Aquí se escribió primero el OUTER JOIN (en este caso, fue un LEFT) y luego el INNER JOIN (la palabra INNER es opcional, no se requiere escribirla)

¿Cuál consulta es mejor?

¿La Consulta 1 es más eficiente que la Consulta 2? ¿La Consulta 2 es más eficiente que la Consulta 1? ¿O son igual de eficientes?

El optimizador del Firebird solamente reordena las tablas en el PLAN hasta que encuentra el primer OUTER JOIN (sea LEFT, RIGHT, o FULL).

Por lo tanto, la correcta es la Consulta 1.

Conclusión:

El orden de los JOIN sí importa en Firebird porque el optimizador reordena las tablas para obtener un mejor PLAN solamente hasta que encuentra el primer OUTER JOIN.

¿Consejo?

Revisa todos tus SELECTs y todas tus vistas para asegurarte de que siempre los INNER JOIN se encuentren antes de los OUTER JOIN, conseguirás consultas más eficientes de esa manera.

Artículos relacionados:

INNER JOIN y OUTER JOIN

Ejemplo Nº 001 – Usando INNER JOIN

Ejemplo Nº 002 – Usando INNER JOIN eficientemente

Ejemplo Nº 003 – Escribiendo varios INNER JOIN

Ejemplo Nº 004 – Usando un INNER JOIN para autoreferenciar una tabla

Ejemplo Nº 006 – Usando LEFT JOIN e INNER JOIN

JOIN implícito y JOIN explícito

NATURAL JOIN y CROSS JOIN

Relacionando dos tablas: la forma vieja y la forma nueva

Teoría de Conjuntos: Unión, Intersección, Diferencia

Entendiendo las tablas autoreferenciadas

Autoreferenciar una tabla. Algunos ejemplos

El índice del blog Firebird21

El foro del blog Firebird21

Almacenamiento de las columnas de tipo BLOB

Deja un comentario

En Firebird tenemos la posibilidad de almacenar (guardar) archivos dentro de las bases de datos. Podemos usar esta característica para guardar documentos de texto, hojas de cálculo, gráficos, fotografías, canciones, vídeos, etc.

Para ello, declaramos a la columna como de tipo BLOB (Binary Large OBjets), o sea: “objetos binarios de gran tamaño”.

¿Dónde se guardan esos objetos binarios de gran tamaño?

Si declaramos a una columna como siendo de tipo CHAR, VARCHAR, SMALLINT, INTEGER, etc., es en esa misma columna donde se guardan sus valores. Pero con las columnas de tipo BLOB no sucede así. En este caso en la columna se guarda un puntero (un número que indica una dirección) a la ubicación física del archivo BLOB.

(Algo similar ocurre en los xBase: dBase, Visual FoxPro, etc., allí se les llama “campos memo” y se guardan en archivos distintos a las tablas .DBF)

Firebird hace lo siguiente:

  • Si el archivo BLOB cabe en la misma página que su fila (registro), se guarda en esa página. Recuerda que los tamaños de página pueden ser de 4.096, 8.192, 16.384 bytes. Como en general los archivos BLOB tienen un tamaño mayor a 16.384 bytes entonces es muy raro que sean guardados en la misma página que su fila respectiva.
  • Si el archivo BLOB no cabe en la misma página que su fila (registro) entonces se guarda en otra o en otras páginas. Estas páginas son del tipo “overflow”. Recordarás que todo en Firebird se guarda dentro de páginas. Los archivos BLOB no son la excepción. Esas “páginas de overflow” siempre están relacionadas con una tabla y por lo tanto son localizadas a través de las filas (registros) de esa tabla. El Firebird sabe a cual tabla pertenece cada “página de overflow” porque en la cabecera de la “página de overflow” coloca ese dato, así también como la fila (registro) que le corresponde y la secuencia en que se encuentra (si un archivo BLOB ocupa varias páginas el Firebird debe poder saber cual es la primera página, cual la segunda, cual la tercera, etc.)

Conclusión:

Las columnas de tipo BLOB normalmente se utilizan para guardar archivos dentro de ellas. Esos archivos como todo en Firebird se guardan dentro de páginas de la Base de Datos. Si el archivo BLOB es pequeño entonces podría guardarse en la misma página que su fila respectiva, pero lo normal es que el archivo BLOB sea más grande que el tamaño de una página y en ese caso se guarda en páginas llamadas de “overflow”. En la cabecera de las páginas de overflow se encuentran los datos que permiten saber a cual  tabla pertenecen, a cual fila de esa tabla, y la posición de esa página con relación a las demás páginas de ese archivo BLOB.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Older Entries