Una técnica muy poderosa aunque muy poco usada es la de la recursividad. ¿Qué significa recursividad? Que un código se ejecute y al finalizar se llame a sí mismo para volver a ejecutarse. Sin embargo, al volver a ejecutarse lo hace con algún parámetro cambiado y continúa llamándose a sí mismo hasta que se cumpla una condición de fin. Sin esa condición de fin se ejecutaría infinitas veces (o más propiamente, hasta que usara toda la memoria disponible de la computadora).
En Firebird podemos tener stored procedures recursivos y también SELECTs recursivos.
Los stored procedures recursivos ya los vimos en el artículo:
Así que ahora veremos algo también muy interesante, los SELECTs recursivos.
Para crear un SELECT recursivo debemos usar CTE (Common Table Expression) porque esa construcción permite recursividad.
Ejemplo:
Tenemos una tabla llamada GENEALOGIA donde guardamos los nombres de las personas y nos gustaría conocer quienes son los ascendientes de cada persona (o sea, sus padres, abuelos, bisabuelos, tatarabuelos, etc.)
Algo así podríamos conseguir con tablas autoreferenciadas, como vimos en el artículo:
Entendiendo las tablas autoreferenciadas
y también con recursividad, que es el tema que ahora nos ocupa.
Captura 1. Si haces clic en la imagen la verás más grande
Captura 2. Si haces clic en la imagen la verás más grande
En la columna GEN_IDEPAD se guarda el Identificador de la fila padre. Una fila puede no tener fila padre (como es el caso de «ALICIA» o puede tener una sola fila padre (los demás casos).
Gráficamente, lo que tenemos es esto:
Captura 3. Si haces clic en la imagen la verás más grande
Aquí podemos ver que BRIGIDA, BEATRIZ y BENITA son hijas de ALICIA; y que CORINA, CAROL, CARMEN y CLAUDIA son hijas de BRIGIDA y que DALILA y DIANA son hijas de CAROL.
Entonces, para ver el nombre de una persona y de todos sus ascendientes podríamos escribir algo como:
Listado 1.
WITH RECURSIVE PARIENTES AS ( SELECT * FROM GENEALOGIA WHERE GEN_IDENTI = 10 UNION ALL SELECT GENEALOGIA.* FROM GENEALOGIA JOIN PARIENTES ON GENEALOGIA.GEN_IDENTI = PARIENTES.GEN_IDEPAD ) SELECT * FROM PARIENTES ORDER BY GEN_IDENTI
Aquí hemos pedido la genealogía de la persona con Identificador igual a 10, entonces obtendremos lo siguiente:
Captura 4. Si haces clic en la imagen la verás más grande
Explicación:
Con la instrucción WITH RECURSIVE PARIENTES AS ( … ) hemos creado una tabla virtual, o sea una tabla que solamente existe en la memoria de la computadora, cuyo nombre es PARIENTES y que es recursiva. ¿Qué significa que sea recursiva? que mientras pueda irá ejecutando los comandos que hayamos colocado dentro suyo.
¿Y cuál es la condición de fin?
Siempre que usamos recursividad debemos colocar una condición de fin, o de lo contrario se ejecutará infinitas veces (o mejor dicho, mientras tenga memoria disponible y luego se «colgará»). En este caso nuestra condición de fin lo da el JOIN, mientras una fila tenga algún valor en la columna GEN_IDEPAD continuará la ejecución, cuando ya no tenga valor (o sea, cuando GEN_IDEPAD sea NULL) finalizará porque ya no se cumplicará la condición que habíamos establecido de que GENEALOGIA.GEN_IDENTI = PARIENTES.GEN_IDEPAD.
¿Y por qué se usó UNION ALL?
Porque el primer SELECT es el que nos dice desde donde debemos empezar y en una CTE recursiva siempre hay que usar UNION ALL para agregar las demás filas que necesitamos. El primer SELECT se ejecuta una sola vez, el segundo SELECT se ejecuta muchas veces. Y si te fijas, en el segundo SELECT se usó el nombre de la tabla CTE que estamos creando, que en este caso es PARIENTES. O sea que dentro de la tabla virtual PARIENTES se usó a la tabla virtual PARIENTES y a eso se le llama … RECURSIVIDAD.
En síntesis:
Para crear una CTE recursiva, debemos:
- Escribir la palabra RECURSIVE a continuación de la palabra WITH
- Escribir un SELECT que determine cual será la primera fila, o sea donde empezará la recursividad
- Escribir UNION ALL
- Escribir un SELECT que tenga una condición de fin. Esa condición de fin puede colocarse en la cláusula WHERE o en la cláusula JOIN
Una vez que hemos creado a nuestra tabla virtual (o sea, a nuestra tabla CTE) ya la podemos usar como a cualquier otra tabla. Podemos mostrar su contenido (como en el ejemplo de este artículo) o usarla en una subconsulta, en un JOIN, etc.
Conclusión:
La recursividad es una técnica muy poderosa aunque muy poco usada. Debemos pensar en usar recursividad cuando hay una relación directa entre dos filas, lo cual generalmente se da en las tablas que pueden ser autoreferenciadas.
Cuidado especial debemos tomar en asegurarnos de que siempre exista una condición de fin y que a esa condición de fin se llegue alguna vez, de lo contrario a nuestra tabla virtual se le irán agregando filas y más filas, hasta que la computadora se quede sin memoria disponible y en ese momento se «colgará» por falta de memoria.
Hay personas que tienen facilidad para pensar de forma recursiva (como el autor de este blog) y personas a quienes les cuesta mucho pensar así. El no pensar de forma recursiva no es un defecto, todos los algoritmos recursivos también pueden expresarse de otra manera, sin usar recursividad. La ventaja de la recursividad es que en algunos algoritmos (no en todos, en algunos) es muy sencilla de implementar, con pocas líneas se llega al resultado buscado.
Por ejemplo, si quieres obtener los mismos resultados que te muestra el Listado 1. sin usar recursividad desde luego que podrás hacerlo, pero sin dudas que escribirás mucho más. Se notará muchísimo la diferencia cuando los niveles no sean 4 como en nuestro ejemplo sino que sean 12, 20, ó más.
Si aprendemos a usar recursividad entonces tendremos una herramienta más para poder realizar nuestras tareas.
Artículos relacionados:
Entendiendo las tablas autoreferenciadas
Usando CTE (Common Table Expression)
Otro ejemplo de CTE: ventas semanales
ejgtuc
Ago 22, 2015 @ 12:09:58
Muy buen articulo Walter.
wrov
Ago 22, 2015 @ 22:52:33
Muchas gracias.
Saludos.
Walter.
Fabian
Ago 29, 2015 @ 09:19:44
Hola Walter, buen día, muy interesante el blog en general, aqui encontramos mucha informacion valiosa sobre Firebird y en castellano.
Tengo una duda con el tema de la recursividad, si tengo que realizar una búsqueda en un campo de texto donde necesitó que la consulta devuelva todos los registros que dicho campo contenga las palabras, por ejemplo, ‘casa’, ‘vivienda’, ‘habitacion’, ‘cuarto’ , no término de interpretar la forma de la consulta recursiva y si realmente es la recursividad la forma adecuada o mas eficiente de resolverlo.
Desde ya muchas gracias nuevamente por la gran cantidad de información sobre esta poderosa base de datos que tenemos al alcance de todos.
wrov
Ago 29, 2015 @ 12:05:59
Gracias Fabián por los comentarios positivos.
Para tu ejemplo, recursividad no es la forma más eficiente de resolverlo, salvo que tengas un diccionario de palabras ordenadas alfabéticamente y deseas realizar búsquedas allí. Pero para el caso normal de buscar una palabra dentro de un texto lo más conveniente es usar LIKE, STARTING WITH, CONTAINING, SIMILAR TO.
Hay varios artículos en el blog que enseñan como usarlas.
Saludos.
Walter.
integral2017ivand
Oct 24, 2018 @ 21:06:16
como se podria hacer para poder mostrar un balance contable tipo arbol pero desde enero a diciembre
ejemplo enero febrero marzo
1 activo 100 30 120
1.1. activo fijo 100 30 120
1.1.1 caja 100 30 120
Gracias por sus ideas
Tor
Nov 05, 2018 @ 13:12:13
se me ocurre algo así
SELECT nivel, nombre_nivel,
(select sum(loquesea) from Tabla where extract(month from fecha) = 1) AS TotalEnero,
(select sum(loquesea) from Tabla where extract(month from fecha) = 2) AS TotalFebreto,
……
ORDER BY nivel
HENRY
Nov 29, 2018 @ 11:00:39
Buenos dias a todos quiero agradecer por este excelente blog he aprendido muchisimo, tengo una inquietud tengo este sql
WITH RECURSIVE MiCTE AS (
SELECT
T2.CCON_ID,
T2.CCON_DDC,
T2.PUCC_ID,
T2.PUCC_PAD
FROM
CON_CCON T2
WHERE
T2.PUCC_ID = ‘11100501’
AND CCON_ANO = 2016 AND TRCR_ID IS NULL
UNION ALL
SELECT
T3.CCON_ID,
T3.CCON_DDC,
T3.PUCC_ID,
T3.PUCC_PAD
FROM
CON_CCON T3
JOIN
MiCTE
ON T3.PUCC_ID = MiCTE.PUCC_PAD
WHERE T3.CCON_ANO = 2016
)
SELECT CCON_ID, PUCC_ID, PUCC_PAD,CCON_DDC, (SELECT SUM(CCON_DDC) FROM con_ccon WHERE CON_CCON.PUCC_ID LIKE MiCTE.PUCC_ID ||’%’ AND CCON_ANO = 2016 AND TRCR_ID IS NOT NULL) SALDO FROM MiCTE;
me hace lo que necesito pero quiero saber si se puede utilizar el resultado y hacer un update por cada registro que me devuelve
ccon_id pucc_id pucc_pad ccon_ddc saldo
774 1 0 0 1009953672.14
773 11 1 0 215932821.14
778 1110 11 0 213575134.14
777 111005 1110 0 213575134.14
775 11100501 111005 0 207453134.14
este el el resultado que me devuelve la consulta y todo esta perfecto lo que necesito es porder hacer el campo ccon_ddc igual al saldo correspondiente donde ccon_id = al numero correspondiente
muchas gracias
HENRY
Nov 29, 2018 @ 11:05:08
ccon_id — pucc_id — pucc_pad — ccon_ddc — saldo
774 ——— 1 ————-0 —————0 ————–1009953672.14
773 ———-11————1 —————0 ————–215932821.14
778 ———-1110———11 ————–0 ————–213575134.14
777 ———-111005 —–1110 ———–0———– —213575134.14
775 ———-11100501–111005——– 0 ————–207453134.14
wrov
Nov 29, 2018 @ 11:29:55
Gracias por el comentario positivo.
Puedes escribir un stored procedure seleccionable y utilizarlo en tu INSERT o en tu UPDATE.
Saludos.
Walter.