PostgreSQL: Documentación: 13: 7.8. CON las Consultas (Expresiones de Tabla Comunes)

El valor básico de SELECT en WITH es dividir las consultas complicadas en partes más simples. Un ejemplo es:

WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales))SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_salesFROM ordersWHERE region IN (SELECT region FROM top_regions)GROUP BY region, product;

que muestra los totales de ventas por producto solo en las regiones de mayor venta. La cláusula WITH define dos sentencias auxiliares llamadas regional_sales y top_regions, donde la salida de regional_sales se usa en top_regions y la salida de top_regions se usa en la consulta primaria SELECT. Este ejemplo podría haberse escrito sin WITH, pero habríamos necesitado dos niveles de sub-SELECTanidados. Es un poco más fácil de seguir de esta manera.

El modificador opcional RECURSIVE cambia WITH de una mera conveniencia sintáctica a una función que logra cosas que de otro modo no serían posibles en SQL estándar. Usando RECURSIVE, una consulta WITH puede hacer referencia a su propia salida. Un ejemplo muy simple es esta consulta para sumar los enteros del 1 al 100:

WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100)SELECT sum(n) FROM t;

La forma general de una consulta recursiva WITH es siempre un término no recursivo, luego UNION (o UNION ALL), luego un término recursivo, donde solo el término recursivo puede contener una referencia a la salida propia de la consulta. Dicha consulta se ejecuta de la siguiente manera:

En el ejemplo anterior, la tabla de trabajo tiene una sola fila en cada paso, y toma los valores de 1 a 100 en pasos sucesivos. En el paso 100, no hay salida debido a la cláusula WHERE, por lo que la consulta termina.

Las consultas recursivas se utilizan normalmente para tratar datos jerárquicos o estructurados en árbol. Un ejemplo útil es esta consulta para encontrar todas las subpartes directas e indirectas de un producto, dada solo una tabla que muestra inclusiones inmediatas:

WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part)SELECT sub_part, SUM(quantity) as total_quantityFROM included_partsGROUP BY sub_part

Cuando se trabaja con consultas recursivas, es importante asegurarse de que la parte recursiva de la consulta eventualmente no devuelva tuplas, o de lo contrario la consulta se repetirá indefinidamente. A veces, usar UNION en lugar de UNION ALL puede lograr esto descartando filas que duplican filas de salida anteriores. Sin embargo, a menudo un ciclo no implica filas de salida que estén completamente duplicadas: puede ser necesario verificar solo uno o algunos campos para ver si se ha alcanzado el mismo punto antes. El método estándar para manejar tales situaciones es calcular una matriz de los valores ya visitados. Por ejemplo, considere la siguiente consulta que busca en una tabla graph utilizando un campo link :

WITH RECURSIVE search_graph(id, link, data, depth) AS ( SELECT g.id, g.link, g.data, 1 FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph g, search_graph sg WHERE g.id = sg.link)SELECT * FROM search_graph;

Esta consulta se repetirá si las relaciones link contienen ciclos. Debido a que requerimos una salida de “profundidad”, simplemente cambiar UNION ALL a UNION no eliminaría el bucle. En su lugar, necesitamos reconocer si hemos llegado a la misma fila de nuevo mientras seguimos un camino particular de enlaces. Añadimos dos columnas path y cycle a la consulta propensa a bucles:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY, false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || g.id, g.id = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle)SELECT * FROM search_graph;

Aparte de evitar ciclos, el valor de la matriz a menudo es útil por derecho propio como representación de la” ruta ” tomada para llegar a cualquier fila en particular.

En el caso general en el que se necesita verificar más de un campo para reconocer un ciclo, use una matriz de filas. Por ejemplo, si necesitáramos comparar campos f1 y f2:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY, false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || ROW(g.f1, g.f2), ROW(g.f1, g.f2) = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle)SELECT * FROM search_graph;

Un truco útil para probar consultas cuando no está seguro de que puedan repetirse es colocar un LIMIT en la consulta principal. Por ejemplo, esta consulta se repetiría para siempre sin la LIMIT:

WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t)SELECT n FROM t LIMIT 100;

Esto funciona porque la implementación de PostgreSQL evalúa solo tantas filas de una consulta WITH como se obtienen en realidad por la consulta padre. No se recomienda usar este truco en producción, porque otros sistemas podrían funcionar de manera diferente. Además, por lo general no funcionará si haces que la consulta externa ordene los resultados de la consulta recursiva o los unes a otra tabla, porque en tales casos la consulta externa generalmente intentará recuperar todos los resultados de la consulta WITH de todos modos.

Una propiedad útil de las consultas WITH es que normalmente se evalúan solo una vez por ejecución de la consulta principal, incluso si la consulta principal o las consultas del hermano WITH hacen referencia a ellas más de una vez. Por lo tanto, los cálculos costosos que se necesitan en varios lugares se pueden colocar dentro de una consulta WITH para evitar el trabajo redundante. Otra posible aplicación es evitar evaluaciones múltiples no deseadas de funciones con efectos secundarios. Sin embargo, la otra cara de esta moneda es que el optimizador no es capaz de empujar restricciones de la consulta padre hacia abajo a una consulta WITH con referencia múltiple, ya que eso podría afectar a todos los usos de la salida de la consulta WITH cuando solo debería afectar a uno. La consulta WITH referenciada a múltiples se evaluará tal como está escrita, sin supresión de filas que la consulta padre pueda descartar posteriormente. (Pero, como se mencionó anteriormente, la evaluación podría detenerse antes si las referencias a la consulta exigen solo un número limitado de filas.)

Sin embargo, si una consulta WITH no es recursiva y no tiene efectos secundarios (es decir, es una SELECT que no contiene funciones volátiles), puede plegarse en la consulta principal, lo que permite la optimización conjunta de los dos niveles de consulta. De forma predeterminada, esto sucede si la consulta padre hace referencia a la consulta WITH una sola vez, pero no si hace referencia a la consulta WITH más de una vez. Puede anular esa decisión especificando MATERIALIZED para forzar el cálculo separado de la consulta WITH, o especificando NOT MATERIALIZED para forzar que se fusione en la consulta principal. Esta última opción corre el riesgo de duplicar el cálculo de la consulta WITH, pero aún puede proporcionar un ahorro neto si cada uso de la consulta WITH necesita solo una pequeña parte de la salida completa de la consulta WITH.

Un ejemplo simple de estas reglas es

WITH w AS ( SELECT * FROM big_table)SELECT * FROM w WHERE key = 123;

Esta consulta WITH se plegará, produciendo el mismo plan de ejecución que

SELECT * FROM big_table WHERE key = 123;

En particular, si hay un índice en key, probablemente se utilizará para obtener solo las filas que tengan key = 123. Por otro lado, en

WITH w AS ( SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;

se materializará la consulta WITH, produciendo una copia temporal de big_table que luego se unirá consigo misma, sin beneficio de ningún índice. Esta consulta se ejecutará de manera mucho más eficiente si se escribe como

WITH w AS NOT MATERIALIZED ( SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;

para que las restricciones de la consulta principal se puedan aplicar directamente a escaneos de big_table.

Un ejemplo en el que NOT MATERIALIZED podría ser indeseable es

WITH w AS ( SELECT key, very_expensive_function(val) as f FROM some_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;

Aquí, la materialización de la consulta WITH garantiza que very_expensive_function se evalúe solo una vez por fila de tabla, no dos veces.

Los ejemplos anteriores solo muestran que WITH se usa con SELECT, pero se puede adjuntar de la misma manera a INSERT, UPDATE o DELETE. En cada caso, proporciona tablas temporales a las que se puede hacer referencia en el comando principal.