mysql | CTE recursiva (expresiones de tabla comunes)

¿Qué es un CTE?

En MySQL cada consulta genera un resultado o relación temporal. Para dar un nombre a esos conjuntos de resultados temporales, se utiliza CTE.

• Un CTE se define mediante la cláusula WITH .
• Usando la cláusula WITH podemos definir más de un CTE en una sola declaración.
• Se puede hacer referencia a un CTE en los otros CTE que forman parte de la misma cláusula WITH, pero esos CTE deben definirse antes.
• Los alcances de cada CTE existen dentro de la declaración en la que se define.

Una CTE recursiva es una subconsulta que se refiere a sí misma usando su propio nombre.

• Los CTE recursivos se definen mediante la cláusula WITH RECURSIVE .
• Debe haber una condición de terminación para el CTE recursivo.
• Los CTE recursivos se utilizan para la generación de series y el recorrido de datos jerárquicos o estructurados en árbol.

Sintaxis:

WITH RECURSIVE 
cte_name [(col1, col2, ...)]
AS ( subquery )
Select col1, col2, .. from cte_name;


cte_name: Name given to recursive subquery written in subquery block.
col1, col2, ...colN: The name given to columns generated by subquery.
subquery: A MySql query that refer to itself using cte_name as its own name.

Aquí, los nombres de columna proporcionados en la instrucción SELECT deben coincidir con los nombres proporcionados en la lista seguida de cte_name .

La estructura CTE recursiva proporcionada en el bloque de subconsulta :

Select col1, col2, ... coln from table_name        
UNION [ALL, DISTINCT]
Select col1, col2, ...coln from cte_name          
WHERE clause

El CTE recursivo consta de una subconsulta no recursiva seguida de una subconsulta recursiva:

• La primera declaración de selección es una declaración no recursiva, que proporciona filas iniciales para el conjunto de resultados.
• UNION [TODO, DISTINTO] se usa para agregar filas adicionales al conjunto de resultados anterior. El uso de las palabras clave ALL y DISTINCT se utiliza para incluir o eliminar filas duplicadas en el último conjunto de resultados.
• La segunda declaración de selección es una declaración recursiva que produce un conjunto de resultados iterativamente hasta que la condición provista en la cláusula WHERE sea verdadera.
• El conjunto de resultados producido en cada iteración toma el conjunto de resultados producido en la iteración anterior como tabla base.
• La recursividad finaliza cuando la declaración de selección recursiva no produce filas adicionales.

Ejemplos:

1. Considere la siguiente consulta que genera una serie de los primeros 5 números impares –
   Consulta:

   WITH RECURSIVE 
   odd_no (sr_no, n) AS
   (
   SELECT 1, 1 
   union all
   SELECT sr_no+1, n+2 from odd_no where sr_no < 5 
   )
   SELECT * FROM odd_no;  
   

   Producción:

   +---------+-------+
   | sr_no   |  n    |
   +---------+-------+
   | 1       |  1    |
   | 2       |  3    |
   | 3       |  5    |
   | 4       |  7    |
   | 5       |  9    |
   +---------+-------+
   

   Explicación:

   La consulta anterior consta de dos partes: recursiva y no recursiva.

   Parte no recursiva: producirá filas iniciales que constan de dos columnas denominadas «sr_no» y «n» y una sola fila.

   Query:
   SELECT 1, 1 
   
   Output:
   +---------+-------+
   | sr_no   |  n    |
   +---------+-------+
   | 1       |  1    |
   +---------+-------+
   

   parte recursiva-

   SELECT sr_no+1, n+2 from cte where odd_no < 5 
   

   Agregará filas a la salida anterior hasta que se cumpla la condición de terminación, es decir ( sr_no < 5 ).

   Cuando sr_no se convierte en 5, la condición se vuelve falsa y la recursión finaliza.

2. Considere la siguiente tabla «bst»:

   mysql> SELECT * FROM bst order by node;
   +------+-----------+
   | node | parent    | 
   +------+-----------+
   |  1   |  NULL     |
   |  2   |   1       |
   |  3   |   1       |
   |  4   |   2       |
   |  5   |   2       | 
   |  6   |   3       |
   |  7   |   3       |
   +------+-----------+
   

   La tabla anterior «bst» consta de dos columnas «Node» y «principal» que dan el valor de un Node en el árbol de búsqueda binaria y su valor principal respectivo.

   Descripción del problema: Tenemos que encontrar las rutas de todos los Nodes en el «bst» dado.

   Consulta:

   WITH RECURSIVE
   cte ( node, path )
   AS
   ( SELECT node, cast ( 1 as char(30) )  
             FROM bst WHERE parent IS NULL
     UNION ALL
     SELECT bst.node,  CONCAT ( cte.path, '-->', bst.node ) 
            FROM cte JOIN bst ON cte.node = bst.parent
   )
   SELECT * FROM cte ORDER BY node;
   

   Producción:

   +------+-----------+
   | node |   path    |
   +------+-----------+
   |  1   | 1         |
   |  2   | 1-->2     |
   |  3   | 1-->3     |
   |  4   | 1-->2-->4 |
   |  5   | 1-->2-->5 |
   |  6   | 1-->3-->6 |
   |  7   | 1-->3-->7 |
   +------+-----------+
   

   Explicación:

   Aquí, la parte no recursiva en CTE anterior dará solo una fila que consta de un Node raíz junto con su ruta que se establece en 1.

   SELECT node, cast ( 1 as char(30) )  
             FROM bst WHERE parent IS NULL
   
   Output:
   +------+-----------+
   | node |   path    |
   +------+-----------+
   |  1   | 1         |
   +------+-----------+
   

   parte recursiva-

   SELECT bst.node,  CONCAT ( cte.path, '-->', bst.node ) 
            FROM cte JOIN bst ON cte.node = bst.parent
   

   La instrucción recursiva SELECT encontrará todos aquellos Nodes en bst cuyo padre sea el Node producido en la iteración anterior.

   Tal iteración finaliza cuando el Node ie (Node de hoja) producido en la iteración anterior no consiste en ningún Node secundario en bst.