Ejemplo de recursión (5). Saldos acumulados

Deja un comentario

En el Ejemplo 4. de recursión ya habíamos visto como actualizar una fila y todas sus filas descendientes, ahora veremos otro ejemplo muy útil, el cual nos permitirá obtener los saldos acumulados de forma sencilla y rápida.

UPDATE3

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

  • CUE_IDENTI es el identificador de cada fila
  • CUE_NUMERO es el número de la cuenta contable
  • CUE_NOMBRE es el nombre de la cuenta contable
  • CUE_IDEPAD es el identificador de la fila padre
  • CUE_ASENTA indica si una cuenta es asentable, también llamada imputable (‘T’) o no lo es (‘F’)
  • CUE_MONTOX es el saldo de la cuenta

Inicialmente, solamente las cuentas asentables (es decir, las que pueden escribirse en el Libro Diario) tienen saldo diferente que cero. Todas las cuentas no asentables tienen saldo cero.

Listado 1.

UPDATE 
   CUENTAS 
SET 
   CUE_MONTOX = 0
WHERE
   CUE_ASENTA = 'F';

UPDATE
   CUENTAS T1
SET
   CUE_MONTOX = CUE_MONTOX + 
                  (WITH RECURSIVE MiCTE AS (

                       SELECT
                          T2.CUE_IDENTI,
                          T2.CUE_MONTOX AS CUE_MONTOX
                       FROM 
                          CUENTAS T2
                       WHERE
                          T2.CUE_IDENTI = T1.CUE_IDENTI

                       UNION ALL

                       SELECT
                          T3.CUE_IDENTI,
                          MiCTE.CUE_MONTOX + T3.CUE_MONTOX AS CUE_MONTOX
                       FROM
                          CUENTAS T3
                       JOIN
                          MiCTE
                             ON T3.CUE_IDEPAD = MiCTE.CUE_IDENTI
                             
                    )
                    
                    SELECT SUM(CUE_MONTOX) FROM MiCTE
                  
                  )
WHERE
   CUE_ASENTA = 'F';

UPDATE4

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

Como puedes ver, cada cuenta no asentable (CUE_ASENTA = ‘F’) guarda la suma acumulada de sus cuentas hijas. Así por ejemplo “ACTIVO CORRIENTE” es la suma de sus 2 cuentas hijas: “DISPONIBILIDADES” y “CRÉDITOS”. A su vez “DISPONIBILIDADES” es la suma de sus cuentas hijas: “Caja” y “Mi Banco Cta.Cte”. “ACTIVO” tiene una sola cuenta hija, que es “ACTIVO CORRIENTE” entonces sus saldos son idénticos. “CRÉDITOS” tiene una sola cuenta hija, que es “CLIENTES”, por lo tanto sus saldos también son idénticos. En cambio “CLIENTES” tiene tres cuentas hijas y su saldo es la suma de los saldos de sus cuentas hijas.

Explicación:

El primer UPDATE pone en cero los saldos acumulados de todas las cuentas no asentables para no sumar por accidente los saldos que pudieron haberse quedado allí de una ejecución anterior.

El segundo UPDATE lo que hace es sumar al saldo actual de una cuenta, el saldo acumulado de todas sus cuentas hijas. ¿Cómo se halla el saldo acumulado de todas sus cuentas hijas? Mediante la creación de una tabla virtual recursiva llamada MiCTE que le va sumando al saldo actual de una cuenta el saldo de todas sus cuentas hijas.

Recuerda que en la tabla virtual recursiva no se pueden usar las funciones agregadas: COUNT(), MAX(), MIN(), SUM(), AVG() ni tampoco se puede usar la cláusula GROUP BY, por ese motivo se tuvo que escribir: MiCTE.CUE_MONTOX + T3.CUE_MONTOX AS CUE_MONTOX para obtener la suma manualmente.

Fuera de la tabla virtual recursiva sí se puede usar la función SUM(), por eso se la usó en el último SELECT.

Más allá de este caso particular también es importante aprender la técnica de actualizar a una columna de una tabla mediante la creación de una tabla virtual (recursiva o no recursiva). Lo que debemos hacer es lo siguiente: crear a la tabla virtual y luego mediante un SELECT devolver la columna que nos interesa. Lo más frecuente es que la columna que nos interesa se obtenga mediante una función agregada: COUNT(), SUM(), AVG(), MAX(), MIN(), tal y como podemos ver en el último SELECT del Listado 1.

¿Y si queremos ver los saldos acumulados, pero sin actualizar la tabla de CUENTAS?

También es muy sencillo de lograrlo, el algoritmo es casi el mismo.

Listado 2.

SELECT
   T1.CUE_IDENTI,
   T1.CUE_NUMERO,
   T1.CUE_NOMBRE,
   T1.CUE_IDEPAD,
   T1.CUE_ASENTA,
      (WITH RECURSIVE MiCTE AS (
          
          SELECT
             T2.CUE_IDENTI,
             T2.CUE_MONTOX AS CUE_MONTOX
          FROM 
             CUENTAS T2
          WHERE
             T2.CUE_IDENTI = T1.CUE_IDENTI
          
          UNION ALL
          
          SELECT
             T3.CUE_IDENTI,
             MiCTE.CUE_MONTOX + T3.CUE_MONTOX AS CUE_MONTOX
          FROM
             CUENTAS T3
          JOIN
             MiCTE
                ON T3.CUE_IDEPAD = MiCTE.CUE_IDENTI
          
         )
         
         SELECT SUM(CUE_MONTOX) FROM MiCTE
         
   ) AS CUE_MONTOX
FROM
   CUENTAS T1

Y si ejecutas el Listado 2. entonces obtendrás exactamente el mismo resultado que ves en la Captura 2. así que la actualización de la tabla CUENTAS es una posibilidad, pero no es obligatorio hacerlo.

Quizás habrás notado que en el Listado 2. no se pusieron inicialmente a cero los saldos de las cuentas no asentables, como sí se había hecho en el Listado 1. ¿por qué no? porque se supuso que el saldo inicial de cada cuenta no asentable era cero. Pero si ese no es (o podría no ser) el caso entonces también tendrían que haberse cerado los saldos de las cuentas no asentables antes de ejecutar el SELECT, tal y como se hizo en el Listado 1. Como medida preventiva y para asegurarnos de no obtener resultados erróneos, es lo mejor que se puede hacer: cerar los saldos de las cuentas no asentables antes de ejecutar el UPDATE o el SELECT.

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

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

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

Ejemplo de recursión (2). Numerar filas

Deja un comentario

Aquí tenemos otro ejemplo de una tabla virtual recursiva. Este ejemplo es muy, muy sencillo, pero ilustra una técnica que podemos usar para numerar filas.

Listado 1.

WITH RECURSIVE CONSECUTIVOS AS (
   
   SELECT 
      1 AS CONTADOR 
   FROM 
      RDB$DATABASE
   
   UNION ALL
   
   SELECT 
      CONTADOR + 1 
   FROM 
      CONSECUTIVOS 
   WHERE 
      CONTADOR < 10

)

SELECT
   *
FROM
   CONSECUTIVOS

EJEMPLO2-1

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

Como siempre, la primera fila no es recursiva, las filas recursivas son las que se encuentran a continuación de UNION ALL (recuerda que una tabla virtual recursiva es una UNION entre una o más filas no recursivas y una o más filas recursivas).

Según el valor que le asignemos a la columna CONTADOR (ese nombre es sólo un ejemplo, tú puedes llamarlo como quieras) podemos numerar de 1 en 1 (como hicimos en el Listado 1.) o numerar de 2 en 2, de 3 en 3, o como se nos ocurra.

Numerando las filas de un SELECT

Para que se entienda mejor la utilidad de este algoritmo ahora numeraremos las filas de un SELECT. Tenemos una tabla llamada FACTURAS que tiene estos datos:

NUMERAR1

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

Y lo que deseamos es numerar a sus filas, ¿cómo lo podemos hacer?

Listado 2.

WITH RECURSIVE CONSECUTIVOS AS (

   SELECT 
      1 AS CONTADOR,
      FAC_NUMERO
   FROM 
      FACTURAS
   WHERE
      FAC_NUMERO = '001-001-0001234'

   UNION ALL

   SELECT 
      FIRST 1
      CONTADOR + 1,
      FAC_NUMERO
   FROM 
      FACTURAS     F
   JOIN 
      CONSECUTIVOS C
         ON F.FAC_NUMERO > C.FAC_NUMERO
   WHERE 
      CONTADOR < 1024

)

SELECT
   C.CONTADOR,
   F.FAC_NUMERO,
   F.FAC_FECVEN,
   F.FAC_MONTOX
FROM
   FACTURAS     F
JOIN
   CONSECUTIVOS C
      ON C.FAC_NUMERO = F.FAC_NUMERO

NUMERAR2

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

Y como puedes ver, ahora tenemos ¡¡¡a todas las filas numeradas!!!

Puede ser muy útil a veces, pero debes recordar algo importante: este algoritmo solamente funcionará si la cantidad de filas es como máximo 1024 porque ese es el límite de llamadas recursivas del Firebird, no permite más que 1024.

Como en todos los casos, nuestra tabla virtual recursiva empieza con una fila no recursiva, a la cual conocemos y desde la cual vamos a partir. Esa es nuestra fila “ancla”. Las demás filas (o sea, las filas recursivas) se van agregando a la tabla virtual mientras nuestra tabla de FACTURAS tenga filas y la cantidad de esas filas sea menor que 1024.

Si las filas de la tabla FACTURAS son más que 1024, para evitar que sobrepasen ese límite de 1024 se podría filtrar con la cláusula WHERE.

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)

Numerando las filas de un SELECT

El índice del blog Firebird21

El foro del blog Firebird21

Ejemplo de recursión (1). Filas salteadas

Deja un comentario

En otros artículos ya habíamos visto como usar recursividad en los stored procedures y en los SELECTs del Firebird, así que ahora es tiempo de mostrar algunos ejemplos para que se entienda mejor lo que podemos hacer.

Problema:

Queremos mostrar las filas de nuestra tabla CUENTAS de 3 en 3, o sea las filas 1, 4, 7, 10, etc.

EJEMPLO1-1

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

Solución:

Listado 1.

WITH RECURSIVE SALTEAR AS (
   SELECT
      1 AS NUMERO,
      T1.CUE_IDENTI,
      T1.CUE_NOMBRE,
      (SELECT FIRST 1 SKIP 2 T2.CUE_IDENTI FROM CUENTAS T2 WHERE T2.CUE_IDENTI > T1.CUE_IDENTI ORDER BY T2.CUE_IDENTI) AS SIGUIENTE
   FROM
      (SELECT FIRST 1 T1.CUE_IDENTI, T1.CUE_NOMBRE FROM CUENTAS T1 ORDER BY T1.CUE_IDENTI) T1

   UNION ALL

   SELECT
      NUMERO + 1,
      T1.CUE_IDENTI,
      T1.CUE_NOMBRE,
      (SELECT FIRST 1 SKIP 2 T2.CUE_IDENTI FROM CUENTAS T2 WHERE T2.CUE_IDENTI > T1.CUE_IDENTI ORDER BY T2.CUE_IDENTI) AS SIGUIENTE
   FROM
      SALTEAR T3
   JOIN
      CUENTAS T1 
         ON T1.CUE_IDENTI = T3.SIGUIENTE
   WHERE
      NUMERO < 1024

)

SELECT
   NUMERO,
   CUE_IDENTI,
   CUE_NOMBRE,
   SIGUIENTE
FROM
   SALTEAR

EJEMPLO1-2

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

Como puedes ver, las filas se muestran de 3 en 3

Explicación:

Creamos una tabla virtual recursiva llamada SALTEAR, cuya primera fila no es recursiva. Las filas recursivas son las que se van insertando en el SELECT que se encuentra después del UNION ALL.

Como nuestra tabla virtual es recursiva, entonces la podemos usar en el segundo SELECT. Usamos SKIP 2 para “saltar” dos lugares más, eso en la práctica significa que iremos de 3 en 3 (no te confundas, el SKIP es 2, pero el salto es de 3).

Desde luego que tu podrás saltar de 4 en 4, de 5 en 5, de 10 en 10, o de lo que quieras, saltar de 3 en 3 es solamente un ejemplo, para que se entienda el algoritmo.

¿Y por qué pusimos en el WHERE que NUMERO sea menor que 1024?

Porque el Firebird tiene un límite a la cantidad de llamadas recursivas que podemos realizar y ese límite es de 1024. O sea que hasta 1023 veces una tabla virtual recursiva puede llamarse a sí misma (la primera vez fue llamada desde afuera).

La columna NUMERO es muy útil para saber la cantidad de veces que se está ejecutando nuestra tabla virtual. Debes aprender esta técnica para aplicarla cuando no sepas de antemano la cantidad máxima de repeticiones que pueden ocurrir (desde luego que a tu columna puedes llamarla como quieras, llamarla NUMERO es solamente un ejemplo).

Como nuestra tabla SALTEAR es virtual (o sea que solamente existe en la memoria de la computadora pero no en el disco duro) entonces debemos usarla enseguida después de haberla creado. Eso fue justamente lo que hicimos en el último SELECT del Listado 1., es decir: usar la tabla virtual que habíamos creado.

Conclusión:

Si necesitamos ver a las filas de una tabla o de una vista de “x en x” entonces podemos crear una tabla virtual recursiva para obtener lo que estamos necesitando.

La técnica para numerar a las filas es muy útil para asegurarnos de nunca pasar la cantidad máxima de recursiones que permite el Firebird, esa cantidad es de 1024.

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

El índice del blog Firebird21

El foro del blog Firebird21

Creando una subconsulta que devuelva muchas columnas

6 comentarios

A veces necesitamos tener en un SELECT varias columnas que provienen de la misma subconsulta. Hay dos formas de conseguir algo así: la forma mala y la forma buena. He visto que muchas personas usan la “forma mala”, así que ahora explicaré la “forma buena”.

Veamos el caso:

Listado 1. La “forma mala”

SELECT
   MiColumna1,
   MiColumna2,
   (SELECT MiSubColumna1 FROM MiTabla2 JOIN MiEnlace WHERE MiCondición) AS MiColumna3,
   (SELECT MiSubColumna2 FROM MiTabla2 JOIN MiEnlace WHERE MiCondición) AS MiColumna4,
   (SELECT MiSubColumna3 FROM MiTabla2 JOIN MiEnlace WHERE MiCondición) AS MiColumna5
FROM
   MiTabla1

Si te fijas en las subconsultas del Listado 1. verás que son muy, muy, similares, la única diferencia es la columna que devuelven a la consulta principal, el resto es idéntico.

¿Cuál es el defecto de hacerlo así?

Que la subconsulta se ejecuta muchas veces (3 veces en el Listado 1., pero en otros casos podría ser más) y eso puede llegar a ser muy lento en tablas grandes.

Además, si la consulta es muy larga, deberás escribir mucho y te complicará la lectura.

Así que veamos una forma alternativa (y mejor) de obtener el mismo resultado:

Listado 2. La “forma buena”

WITH MiSubConsulta AS (
   SELECT
      MiSubColumna1,
      MiSubColumna2,
      MiSubColumna3
   FROM
      MiTabla2
   JOIN
      MiEnlace
   WHERE
      MiCondición
)

SELECT
   T1.MiColumna1,
   T1.MiColumna2,
   T2.MiSubColumna1 AS MiColumna3,
   T2.MiSubColumna2 AS MiColumna4,
   T2.MiSubColumna3 AS MiColumna5
FROM
   MiTabla1      T1
LEFT JOIN
   MiSubConsulta T2
      ON T1.MiColumna1 = T2.MiSubColumna1

¿Cuáles son las ventajas de hacerlo así?

  1. La tabla virtual CTE (es virtual porque solamente existe en la memoria de la computadora) es creada una sola vez y sus columnas pueden ser usadas muchísimas veces. En este ejemplo sus columnas (llamadas MiSubColumna1, MiSubColumna2, y MiSubColumna3) fueron usadas 3 veces pero en otros casos podrían usarse muchas más veces. Como se la crea una sola vez eso es mucho más rápido que crearla 3 veces que era el caso en el Listado 1.
  2. Es muy fácil de entender. Si miramos a la subconsulta muy fácilmente entenderemos lo que hace.

¿Qué fue lo que hicimos?

  1. Creamos una tabla virtual llamada “MiSubConsulta” (el nombre puede ser cualquiera, “MiSubConsulta” es solamente un ejemplo)
  2. En la tabla virtual colocamos todas las columnas que nos interesan
  3. En el SELECT principal hicimos un LEFT JOIN a la tabla virtual y por eso ya pudimos acceder a todas sus columnas. Si la condición de enlace no se cumple entonces tendremos NULL en las columnas que provienen de la tabla virtual.

Observación: La condición de enlace del Listado 2. (T1.MiColumna1 = T2.MiSubColumna1) es solamente un ejemplo, allí tú pondrás la condición de enlace adecuada a tu caso.

Conclusión:

Si necesitamos tener varias subconsultas en la lista de columnas de un SELECT y esas subconsultas son muy similares lo mejor es crear una tabla CTE (o sea, una tabla virtual) y así conseguiremos una mejor velocidad de respuesta y también que nuestro código sea más fácil de entender.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Entendiendo la recursividad en los SELECT

Deja un comentario

En un artículo anterior ya vimos un ejemplo de un SELECT recursivo, el cual fue muy útil, así que ahora explicaremos mejor como crear SELECTs recursivos.

  1. Se construye usando CTE (Common Table Expression)
  2. Un SELECT recursivo es una UNION entre miembros recursivos y no recursivos
  3. Debe tener al menos un miembro no recursivo al cual se le llama anchor (ancla)
  4. El miembro (o miembros) no recursivo/s debe colocarse antes que el miembro (o miembros) recursivo/s
  5. El enlace entre el miembro (o miembros) no recursivo/s y el miembro (o miembros) recursivo/s se hace mediante UNION ALL
  6. Las uniones entre los miembros no recursivos puede ser de cualquier tipo
  7. Después de la palabra WITH hay que escribir la palabra RECURSIVE
  8. El nombre de la tabla virtual puede usarse solamente en un FROM o en un INNER JOIN
  9. En el miembro (o miembros) recursivo/s no puede usarse un OUTER JOIN (LEFT JOIN, RIGHT JOIN, FULL JOIN). En el miembro (o miembros) no recursivo/s sí pueden usarse los OUTER JOIN
  10. Un SELECT recursivo usa mucha menos memoria y ciclos de CPU que un stored procedure recursivo equivalente
  11. En el miembro (o miembros) recursivo/s no se pueden usar agregados (DISTINCT, GROUP BY, HAVING). En el miembro (o miembros) no recursivos, sí
  12. En el miembro (o miembros) recursivo/s no se pueden usar funciones agregadas (COUNT(), SUM(), AVG(), MAX(), MIN(), LIST()). En el miembro (o miembros) no recursivo/s, sí
  13. La máxima profundidad admitida es 1024

Listado 1.

WITH RECURSIVE MiTablaVirtual AS (
   SELECT <datos_padre>
   UNION ALL
   SELECT <datos_hijo> JOIN <MiTablaVirtual> ON <enlace_con_el_padre>
)

SELECT * FROM MiTablaVirtual
  1. La ejecución se inicia en el primer SELECT
  2. El conjunto resultado obtenido del primer SELECT se llama “MiTablaVirtual” (en este ejemplo) y como cuando finaliza ya existe “MiTablaVirtual” entonces a ésta se la puede referenciar en el segundo SELECT.
  3. A “MiTablaVirtual” se le unen los conjuntos resultados obtenidos del segundo SELECT
  4. Cuando de la ejecución del segundo SELECT se obtiene un conjunto resultado vacío (es decir, ninguna fila) se regresa un nivel y se obtiene la siguiente fila, si es posible
  5. Cuando ya no se pueden obtener filas, termina la recursividad
  6. A continuación del CTE hay que usar la tabla virtual creada porque de lo contrario todo lo que se hizo en ella se perderá
  7. Los nombres de las columnas que pueden usarse externamente de la tabla virtual son los nombres de columnas que especificamos en el segundo SELECT. O sea que en el “SELECT * FROM MiTablaVirtual” del Listado 1. veremos los nombres de las columnas del segundo SELECT.

Tipos de recursión

La recursión generalmente es útil cuando podemos representar a los datos en forma de árbol, como en el Gráfico 1., que es el caso más común. También es muy útil si los datos se pueden representar como pilas, colas o listas; cuando no es así en general no se puede o no se debe usar recursión.

RECURSIÓN01

Gráfico 1. Si haces clic en la imágen la verás más grande

Hay dos tipos de recursión:

  • De arriba hacia abajo
  • De abajo hacia arriba

Cuando la recursión es de abajo hacia arriba, puede reemplazarse con iteración, es decir con un ciclo WHILE que vaya subiendo al nodo superior hasta descubrir que ya no hay más nodos.

El Firebird tiene actualmente un límite de 1024 llamadas recursivas. O sea que en el Gráfico 1. podríamos tener como máximo 1024 nodos. Esto es mucho más que suficiente para la gran mayoría de las aplicaciones … pero no para todas.

Artículos relacionados:

Usando recursividad con CTE

Stored procedures recursivos

El índice del blog Firebird21

El foro del blog Firebird21

Older Entries