Consulta recursiva de MySQL

1. Consulta MySQL recursiva
2. Implementar consulta recursiva en MySQL

En esta guía, aprenderemos sobre la consulta recursiva de MySQL. En esta guía se explicará cómo escribir una consulta recursiva en SQL y cómo funciona para su mejor comprensión.

Consulta MySQL recursiva

La consulta recursiva en SQL es una subconsulta; como su nombre indica, funciona de forma recursiva. Tiene un caso base, un nombre definido por el usuario y un caso recursivo con una condición de terminación.

with [Recursive] CTE(user_defined name) AS
(
    SELECT query (Non Recursive query or the Base query)
    UNION
    SELECT query (recursive query using the name [with a termination condition])
)
SELECT * from CTE_name;

Lo anterior es el pseudocódigo de la consulta SQL recursiva. Profundicemos más en ello.

la cláusula con

with [Recursive] CTE(user_defined name) AS

La cláusula with de SQL se utiliza al principio, junto con la palabra clave Recursiva de SQL. A continuación, se utiliza la palabra clave AS, seguida de CTE, un nombre definido por el usuario.

Esta sintaxis de palabras clave constituye el caso base de nuestra consulta.

‘CTE’ se llama ’expresión de tabla común’, un conjunto de resultados con nombre temporal definido por el usuario para ser utilizado más tarde en una instrucción ‘SELECT’ subsiguiente.

la consulta base

SELECT query (Non Recursive query or the Base query)

Esta es la primera de las dos consultas denominadas Consulta base. Es una consulta no recursiva, y la entrada base se proporcionará aquí en función de la cual ocurriría la recursividad.

la Cláusula Unión

UNION

El operador Unión se usa en el medio para fusionar nuestra primera y segunda consulta.

la consulta recursiva

SELECT query (recursive query using the name [with a termination condition])

Esta es la consulta recursiva en la que necesitamos proporcionar la tabla CTE que habíamos creado anteriormente usando la cláusula with y también proporcionar la condición de terminación que, cuando se vuelve verdadera, terminaría la recursividad.

Las consultas Base y Recursiva se escriben entre paréntesis (), como se muestra en el pseudocódigo anterior.

SELECT * from CTE_name

Esta consulta verá la tabla creada usando esta técnica recursiva.

Implementar consulta recursiva en MySQL

Comprendamos el funcionamiento de esta consulta implementándola correctamente.

with recursive number_printer AS
(
    SELECT 1 AS digit
    UNION
    SELECT digit+1 FROM number_printer WHERE digit<5
)
SELECT * FROM number_printer

La tabla anterior es el resultado de la ejecución de nuestro programa. Así es como se ejecutó.

La consulta comienza usando la cláusula with y la palabra clave recursive, y usamos print_number como nuestro CTE. Tan pronto como ejecutamos el código, el programa busca un caso base ya que entiende que es una consulta recursiva.

En la consulta base, comenzamos el caso base desde 1, y el alias utilizado aquí es dígito. En la primera iteración de ejecución, la salida será el resultado devuelto por la consulta base.

Los registros iniciales serían el resultado de la consulta base.

El resultado devuelto por la consulta base se convertiría en la entrada de la consulta recursiva. En la segunda iteración, la consulta recursiva utiliza los datos de salida de la consulta anterior y verifica la condición de terminación.

Si se cumple la condición de terminación, la iteración se detiene; de lo contrario, entra en la tercera iteración. La tercera iteración usa la salida devuelta en la segunda iteración como entrada.

Esta es la única lógica basada en la cual ocurre la recursividad.

Se ejecuta la tercera iteración y la salida resultante de esta iteración se usa como entrada para la cuarta iteración. La cuarta iteración luego agrega la salida de la consulta base con su entrada.

Sigue ocurriendo recursivamente hasta que se cumple una condición de terminación.

La tabla, a saber, number_printer, es de gran importancia aquí. Es obligatorio utilizar la tabla para que esta consulta sea recursiva. Para la finalización de la consulta, la condición de terminación se escribe utilizando la cláusula WHERE.

Ahora, veamos otro ejemplo con respecto a la consulta recursiva.

Ejemplo 2: Jerarquía

Supongamos que tenemos una organización que tiene cierta jerarquía. Hay un gerente en la parte superior, y dos gerentes están bajo la supervisión directa de ese gerente, y cada uno de estos dos gerentes tiene un gerente bajo su supervisión directa.

Ahora, hemos creado una tabla llamada red con columnas como id, nombre y ManagerID. Estos definen la identificación del empleado, su nombre y la identificación del gerente que tiene, respectivamente.

Se muestra en la siguiente tabla:

En la tabla anterior, obtuvimos los nombres y las identificaciones de los gerentes de los empleados, pero queremos asignar correctamente rangos a estos gerentes en función de la jerarquía que discutimos anteriormente. Por esa razón, usamos el siguiente código:

WITH RECURSIVE hierarchy AS
( SELECT id, name, 1 AS level FROM network WHERE id = 1
UNION
SELECT n.id, n.name, h.level+1 FROM hierarchy h
JOIN network n on h.id = n.ManagerID
)
SELECT * FROM hierarchy

El código de consulta recursivo anterior resolvería el problema como queríamos. Profundicemos y comprendamos qué hace este código.

La cláusula With se usa junto con la palabra clave Recursive según la sintaxis, y el CTE aquí se denomina jerarquía.

En la consulta base, selecciona el id y el nombre de la tabla network y obtiene el valor con id igual a 1. Luego, establecerá su valor AS nivel en el número entero 1.

El nivel se utiliza aquí para mostrar la posición del gerente en la jerarquía.

Hemos terminado con la consulta base; ahora, el código fusiona la consulta base con la consulta recursiva utilizando la cláusula UNION.

La consulta recursiva selecciona id y nombre de la tabla red. La n aquí es un alias para la tabla network, y lo que hace este punto (.) aquí es que limita el valor que se obtendrá de la tabla específica que le damos como entrada.

h es un alias para la tabla jerarquía, y la cláusula level+1 asegura la adición de 1 al valor entero obtenido de la tabla jerarquía.

Esta parte de la consulta recursiva toma nombre e id de la tabla red, el valor entero proviene de jerarquía, se le agrega 1 y la salida se guarda en nivel . Esta consulta actualiza y define el nivel de los administradores.

En la siguiente parte de una consulta, se utiliza la cláusula JOIN, que funciona como Inner join por valor predeterminado. Aquí, el ManagerID de la tabla red se compara con el id de la tabla jerarquía y se une internamente con la consulta anterior.

La lógica es que cuando la identificación del administrador coincide con la identificación, el nivel de esa entidad se asigna agregando 1 al nivel del administrador. De esta forma, se asigna el rango de supervisión.