Ejemplo de recursión (7). Números faltantes en una serie

1 comentario

Si en una tabla o en una vista o en un procedimiento almacenado seleccionable, tenemos una columna numérica y queremos saber si están todos los números o si falta alguno, podemos usar la técnica mostrada en este artículo para averiguarlo.

Para ello, mediante recursión crearemos una tabla virtual, que en nuestro ejemplo llamaremos RANGO_NUMEROS. Esa tabla virtual contendrá todos los números que nos interesan, de forma consecutiva. Es decir: 1, 2, 3, 4, 5, 6, 7, 8, 9, …

Desde luego que podemos empezar con cualquier número, no es obligatorio que empecemos con el número 1.

La tabla es virtual porque solamente existe en la memoria de la computadora y mientras dure la ejecución del SELECT principal, no existe dentro de la Base de Datos ni tampoco se guarda en el disco duro, solamente existe hasta que el SELECT principal finaliza, luego … desaparece totalmente.

Listado 1. Un SELECT para averiguar si hay números consecutivos faltantes

WITH RECURSIVE RANGO_NUMEROS AS (

   SELECT
      1 AS NUMERO
   FROM
      RDB$DATABASE

   UNION ALL

   SELECT
      NUMERO + 1 AS NUMERO
   FROM
      RANGO_NUMEROS
   WHERE
      NUMERO <= 36
)

SELECT
   NUMERO
FROM
   RANGO_NUMEROS
LEFT JOIN
   REVALUOSCAB
      ON NUMERO = RVC_IDENTI
WHERE
   RVC_IDENTI IS NULL

Como siempre que usamos recursión, debemos asignar el valor inicial (en nuestro ejemplo, es el número 1, pero puedes elegir otro número si quieres) y un valor final (en nuestro ejemplo, es 36).

El valor final es absolutamente necesario establecerlo porque de lo contrario la recursión continuaría indefinidamente. Bueno, en realidad, hasta que llegues al límite de recursiones permitidas o hasta que la computadora se quede sin memoria RAM.

Siempre que uses recursión debes establecer una condición de salida, es decir, una condición para que la recursión finalice. No tendría sentido de otro modo.

¿Que hicimos en el Listado 1.?

Primero, hemos creado una tabla virtual llamada RANGO_NUMEROS, cuyo contenido es una sola columna, llamada NUMERO, y cuyos valores van desde el 1 hasta el 37 de forma consecutiva, es decir sin que falte algún número. Están todos. Va hasta el 37 porque en el SELECT pusimos NUMERO + 1. Y como en el WHERE pusimos 36, entonces obtendremos un número más, en este caso 37.

Segundo, hemos hecho un LEFT JOIN de nuestra tabla virtual llamada RANGO_NUMEROS con la tabla REVALUOSCAB, la cual tiene los números que queremos verificar.

Tercero, pusimos la condición RVC_IDENTI IS NULL para que solamente nos muestre los números que están en la tabla virtual RANGO_NUMEROS y que no están en la tabla REVALUOSCAB. De esta manera, solamente los números que se encuentren en la tabla virtual RANGO_NUMEROS y que no se encuentren en la tabla REVALUOSCAB obtendremos en el resultado.

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

En la Captura 1. vemos el contenido de la columna RVC_IDENTI de la tabla REVALUOSCAB. Como puedes notar, faltan los números que van del 26 al 36.

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

Después de ejecutar el Listado 1. obtenemos como resultado lo que vemos en la Captura 2., es decir, todos los números faltantes.

Conclusión:

Hay varias técnicas para mostrar los números que faltan en una serie, en este artículo hemos visto una de esas técnicas, la cual emplea recursión. Saber usar recursión puede ayudarte en muchos casos, por lo tanto es muy bueno conocer como usarla.

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

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

Ejemplo de recursión (5). Saldos acumulados

Ejemplo de recursión (6). Repitiendo las filas

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

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

Stored procedures recursivos

Deja un comentario

Con Firebird podemos escribir stored procedures recursivos, si los necesitamos.

¿Qué significa la recursividad?

Que el mismo stored procedure se ejecuta una vez, y otra vez, y otra vez, y otra vez … hasta que se cumple una condición que lo detiene.

¿Por qué usar la recursividad?

Todos los algoritmos recursivos pueden escribirse también sin usar recursividad, no es obligatorio usarla, en ningún caso. La ventaja es que en muchos casos los algoritmos recursivos son mucho más cortos y más fáciles de entender. También pueden ser más rápidos. En contrapartida casi siempre ocupan más memoria que sus equivalentes no recursivos.

Hay gente a quien le cuesta mucho pensar en forma recursiva y hay gente a quien le resulta muy fácil pensar así. Si para tí no es difícil pensar en forma recursiva entonces que el Firebird te provea de esta característica te puede ser muy útil.

¿Qué se debe tener en cuenta al escribir un stored procedure recursivo?

  1. Que debe existir una condición para que la recursividad se detenga
  2. Que cada vez que se ejecuta el stored procedure se debe estar más cerca de esa condición

Ejemplo 1:

Un ejemplo clásico para mostrar la recursividad es el utilizado para hallar el factorial de un número. Como quizás recordarás, el factorial de un número es ese número multiplicado por todos los anteriores números naturales, desde el 1.

El factorial de 1 es 1

El factorial de 2 es 1 * 2 = 2

El factorial de 3 es 1 * 2 * 3 = 6

El factorial de 4 es 1 * 2 * 3 * 4 = 24

El factorial de 5 es 1 * 2 * 3 * 4 * 5 = 120

El factorial de 6 es 1 * 2 * 3 * 4 * 5 * 6 = 720

y así sucesivamente.

SET TERM ^ ;

CREATE PROCEDURE FACTORIAL(
   tnNumero INTEGER)
RETURNS(
   ftnResultado DOUBLE PRECISION)
AS
BEGIN

   IF (tnNumero = 1) THEN BEGIN
      ftnResultado = 1;
      SUSPEND;
   END ELSE BEGIN
      EXECUTE PROCEDURE FACTORIAL (tnNumero - 1) RETURNING_VALUES ftnResultado;
      ftnResultado = ftnResultado * tnNumero;
      SUSPEND;
   END

END^

SET TERM ; ^

Aquí la condición de fin es que el número recibido como parámetro de entrada sea 1. El stored procedure se va ejecutando cada vez con el número anterior, hasta que se llega al 1 y allí se termina. Por ejemplo, si le envías el número 6, el stored procedure se ejecutará 6 veces y sus parámetros de entrada serán: 6, 5, 4, 3, 2, 1

Ejemplo 2:

Otro ejemplo clásico es hallar el número de Fibonacci. Esta es una serie infinita de números naturales. La serie empieza con los números 0 y 1. A partir de allí, cada número es la suma de los dos anteriores. Esto nos da:

0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, 377, etc.

SET TERM ^ ;

CREATE PROCEDURE FIBONACCI(
   tnNumero INTEGER)
RETURNS(
   ftnResultado DOUBLE PRECISION)
AS
   DECLARE VARIABLE lnNumero1 INTEGER;
   DECLARE VARIABLE lnNumero2 INTEGER;
BEGIN

   IF (tnNumero < 2) THEN BEGIN
      ftnResultado = tnNumero;
      SUSPEND;
   END ELSE BEGIN
      EXECUTE PROCEDURE FIBONACCI(tnNumero - 1) RETURNING_VALUES lnNumero1;
      EXECUTE PROCEDURE FIBONACCI(tnNumero - 2) RETURNING_VALUES lnNumero2;
      ftnResultado = lnNumero1 + lnNumero2;
      SUSPEND;
   END

END^

SET TERM ; ^

En este caso la recursividad se detendrá cuando el parámetro de entrada recibido sea menor que 2.

CUIDADO:

Siempre antes de llamar a un stored procedure recursivo hay que validar que el número (o los números) que se envía como parámetro de entrada no provoque una recursividad infinita. En el ejemplo del factorial algo así ocurriría si se envía como parámetro de entrada a un número negativo. ¿Por qué? porque en ese caso en lugar de acercarse a  la condición de fin, cada vez se alejará más. Por ejemplo, si le enviamos como parámetro de entrada al número -6, sus siguientes valores serán -7, -8, -9, -10, – 11, -12, etc., o sea que en lugar de acercarse a la condición de fin (que el parámetro de entrada sea 1) se aleja cada vez más. Al enviarle como parámetro de entrada a un número negativo, cada recursión se aleja más de la condición de fin.

Artículo relacionado:

El índice del blog Firebird21