sql - Cómo seleccionar el uso de la cláusula WITH RECURSIVE

postgresql recursive-query (1)

He buscado en Google y he leído algunos artículos como esta página de manual de postgreSQL o esta página de blog y he intentado hacer consultas con un éxito moderado (parte de ellos se cuelga, mientras que otros funcionan bien y rápido), pero hasta ahora no puedo entender completamente cómo la magia funciona

¿Alguien puede dar una explicación muy clara que demuestre dicha semántica de consulta y el proceso de ejecución, mejor basándose en muestras típicas como el cálculo factorial o la expansión de árbol completo desde la tabla (id,parent_id,name) ?

¿Y cuáles son los lineamientos básicos y los errores típicos que uno debe saber para cumplir with recursive consultas with recursive ?

En primer lugar, intentemos simplificar y aclarar la descripción del algoritmo que figura en la página del manual . Para simplificarlo, considere solo la union all with recursive cláusula with recursive por ahora (y union más adelante):

WITH RECURSIVE pseudo-entity-name(column-names) AS ( Initial-SELECT UNION ALL Recursive-SELECT using pseudo-entity-name ) Outer-SELECT using pseudo-entity-name

Para aclararlo, describamos el proceso de ejecución de consultas en pseudo código:

working-recordset = result of Initial-SELECT append working-recordset to empty outer-recordset while( working-recordset is not empty ) begin new working-recordset = result of Recursive-SELECT taking previous working-recordset as pseudo-entity-name append working-recordset to outer-recordset end overall-result = result of Outer-SELECT taking outer-recordset as pseudo-entity-name

O incluso más corto: el motor de la base de datos ejecuta la selección inicial, tomando sus filas de resultados como conjunto de trabajo. Luego, ejecuta repetidamente la selección recursiva en el conjunto de trabajo, cada vez que reemplaza el contenido del conjunto de trabajo con el resultado de la consulta obtenido. Este proceso finaliza cuando se devuelve un conjunto vacío mediante una selección recursiva. Y todas las filas de resultados proporcionadas primero por la selección inicial y luego por la selección recursiva se reúnen y se alimentan a la selección externa, cuyo resultado se convierte en el resultado general de la consulta.

Esta consulta está calculando factorial de 3:

WITH RECURSIVE factorial(F,n) AS ( SELECT 1 F, 3 n UNION ALL SELECT F*n F, n-1 n from factorial where n>1 ) SELECT F from factorial where n=1

La selección inicial SELECT 1 F, 3 n nos da valores iniciales: 3 para argumento y 1 para valor de función.
Selección recursiva SELECT F*n F, n-1 n from factorial where n>1 indica que cada vez que necesitamos multiplicar el valor de la última función por el valor del último argumento y el valor del argumento de disminución.
El motor de base de datos lo ejecuta así:

En primer lugar, ejecuta initail select, que proporciona el estado inicial del conjunto de registros de trabajo:

F | n --+-- 1 | 3

Luego transforma el conjunto de registros de trabajo con una consulta recursiva y obtiene su segundo estado:

F | n --+-- 3 | 2

Luego tercer estado:

F | n --+-- 6 | 1

En el tercer estado no hay una fila que siga la condición n>1 en la selección recursiva, por lo que el conjunto de trabajo es salidas de bucle.

El conjunto de registros externo ahora contiene todas las filas, devueltas por selección inicial y recursiva:

F | n --+-- 1 | 3 3 | 2 6 | 1

La selección externa filtra todos los resultados intermedios del conjunto de registros externo, mostrando solo el valor factorial final que se convierte en el resultado general de la consulta:

F -- 6

Y ahora consideremos el forest(id,parent_id,name) tablas forest(id,parent_id,name) :

'' Expandir el árbol completo '' aquí significa ordenar los elementos del árbol en primer orden de profundidad legibles para los humanos mientras se calculan sus niveles y (quizás) las rutas. Ambas tareas (de ordenación y nivel de cálculo o ruta correctas) no se pueden resolver en una (o incluso un número constante de) SELECCIONAR sin usar la cláusula WITH RECURSIVE (o la cláusula Oracle CONNECT BY, que no es compatible con PostgreSQL). Pero esta consulta recursiva hace el trabajo (bueno, casi lo hace, vea la nota a continuación):

WITH RECURSIVE fulltree(id,parent_id,level,name,path) AS ( SELECT id, parent_id, 1 as level, name, name||'''' as path from forest where parent_id is null UNION ALL SELECT t.id, t.parent_id, ft.level+1 as level, t.name, ft.path||'' / ''||t.name as path from forest t, fulltree ft where t.parent_id = ft.id ) SELECT * from fulltree order by path

El motor de base de datos lo ejecuta así:

En primer lugar, ejecuta initail select, que proporciona todos los elementos de nivel más alto (raíces) de forest tabla de forest :

Luego, ejecuta la selección recursiva, que proporciona todos los elementos de segundo nivel de forest tabla del forest :

id | parent_id | level | name | path ---+-----------+-------+------------------+---------------------------------------- 2 | 1 | 2 | subitem 1.1 | item 1 / subitem 1.1 3 | 1 | 2 | subitem 1.2 | item 1 / subitem 1.2 4 | 1 | 2 | subitem 1.3 | item 1 / subitem 1.3 7 | 6 | 2 | subitem 2.1 | item 2 / subitem 2.1

Luego, ejecuta de nuevo la selección recursiva, recuperando elementos de nivel 3d:

Y ahora vuelve a ejecutar la selección recursiva, intentando recuperar elementos de cuarto nivel, pero no hay ninguno de ellos, por lo que el bucle sale.

El SELECT externo establece el orden correcto de filas legibles por humanos, clasificando en la columna de ruta:

id | parent_id | level | name | path ---+-----------+-------+------------------+---------------------------------------- 1 | | 1 | item 1 | item 1 2 | 1 | 2 | subitem 1.1 | item 1 / subitem 1.1 3 | 1 | 2 | subitem 1.2 | item 1 / subitem 1.2 5 | 3 | 3 | subsubitem 1.2.1 | item 1 / subitem 1.2 / subsubitem 1.2.1 4 | 1 | 2 | subitem 1.3 | item 1 / subitem 1.3 6 | | 1 | item 2 | item 2 7 | 6 | 2 | subitem 2.1 | item 2 / subitem 2.1 8 | | 1 | item 3 | item 3

NOTA : El orden de las filas resultantes seguirá siendo correcto solo mientras no haya caracteres de puntuación preceder a la intercalación / en los nombres de los elementos. Si cambiamos el nombre del Item 2 en el Item 1 * , se interrumpirá el orden de la fila, quedando entre el Item 1 y sus descendientes.
Una solución más estable es utilizar el carácter de tabulación ( E''/t'' ) como separador de ruta en la consulta (que puede ser sustituido por un separador de ruta más legible más adelante: en la selección externa, antes de pasar a ser humano o etc.). Las rutas separadas por tabulaciones conservarán el orden correcto hasta que haya tabulaciones o caracteres de control en los nombres de los elementos, que fácilmente se pueden verificar y eliminar sin pérdida de utilidad.

Es muy sencillo modificar la última consulta para expandir cualquier subárbol arbitrario; solo necesita sustituir la condición parent_id is null con perent_id=1 (por ejemplo). Tenga en cuenta que esta variante de consulta devolverá todos los niveles y rutas en relación con el Item 1 .

Y ahora sobre los errores típicos . El error típico más notable específico de las consultas recursivas es la definición de condiciones de parada errónea en la selección recursiva, lo que da como resultado un bucle infinito.

Por ejemplo, si omitimos where n>1 condición en la muestra factorial anterior, la ejecución de la selección recursiva nunca dará un conjunto vacío (porque no tenemos una condición para filtrar una sola fila) y el bucle continuará infinitamente.

Esa es la razón más probable por la que se cuelgan algunas de sus consultas (la otra razón no específica pero aún así posible es una selección muy ineficaz, que se ejecuta en tiempo finito pero muy largo).

No hay muchas guías de consulta específicas RECURSIVAS para mencionar, que yo sepa. Pero me gustaría sugerir (bastante obvio) el procedimiento de creación de consultas recursivas paso a paso.

Por separado construir y depurar su selección inicial.
Envuélvalo con andamios CON estructura RECURSIVA
y comienza a construir y depurar tu selección recursiva.

La construcción recomendada de escaramuzas es así:

WITH RECURSIVE rec( <Your column names> ) AS ( <Your ready and working initial SELECT> UNION ALL <Recursive SELECT that you are debugging now> ) SELECT * from rec limit 1000

Esta selección externa más simple emitirá el conjunto de registros externo completo, que, como sabemos, contiene todas las filas de salida de la selección inicial y cada ejecución de la selección recusrive en un bucle en su orden de salida original, ¡como en las muestras anteriores! El limit 1000 partes evitará que se cuelgue, reemplazándolo con una salida de gran tamaño en la que podrá ver el punto de parada perdido.

Después de depurar la selección inicial y recursiva, construya y depure su selección externa.

Y ahora, lo último que hay que mencionar: la diferencia en el uso de union lugar de union all en conjunto with recursive cláusula with recursive . Introduce una restricción de unicidad de fila que da como resultado dos líneas adicionales en nuestro pseudocódigo de ejecución:

working-recordset = result of Initial-SELECT discard duplicate rows from working-recordset /*union-specific*/ append working-recordset to empty outer-recordset while( working-recordset is not empty ) begin new working-recordset = result of Recursive-SELECT taking previous working-recordset as pseudo-entity-name discard duplicate rows and rows that have duplicates in outer-recordset from working-recordset /*union-specific*/ append working-recordset to outer-recordset end overall-result = result of Outer-SELECT taking outer-recordset as pseudo-entity-name