7.8. Consultas WITH (Expresiones de tabla comunes)

7.8.1. SELECT en WITH

El valor básico de SELECT en WITH es descomponer 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 sólo en las regiones de mayores ventas. La cláusula WITH define dos sentencias auxiliares denominadas regional_sales y top_regions, donde la salida de regional_sales se utiliza en top_regions y la salida de top_regions se utiliza en la consulta SELECT primaria. Este ejemplo podría haberse escrito sin WITH, pero habríamos necesitado dos niveles de sub-SELECTs anidados. Es un poco más fácil de seguir de esta manera.

El modificador opcional RECURSIVE hace que WITH deje de ser una mera conveniencia sintáctica y se convierta en una característica que logra cosas que no son posibles de otra manera en el SQL estándar. Usando RECURSIVE, una consulta WITH puede referirse a su propia salida. Un ejemplo muy sencillo 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 WITH recursiva es siempre un término no recursivo, luego UNION (o UNION ALL), luego un término recursivo, donde sólo el término recursivo puede contener una referencia a la propia salida de la consulta. Tal consulta se ejecuta como sigue:

En el ejemplo anterior, la tabla de trabajo tiene una sola fila en cada paso, y toma los valores del 1 al 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 forma de árbol. Un ejemplo útil es esta consulta para encontrar todas las subpartes directas e indirectas de un producto, dada sólo una tabla que muestra las 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 finalmente no devolverá ninguna tupla, o de lo contrario la consulta hará un bucle indefinido. A veces, el uso de UNION en lugar de UNION ALL puede lograr esto al descartar las filas que duplican las filas de salida anteriores. Sin embargo, a menudo un ciclo no implica filas de salida completamente duplicadas: puede ser necesario comprobar sólo uno o unos pocos campos para ver si se ha llegado antes al mismo punto. El método estándar para manejar estas situaciones es calcular un array de los valores ya visitados. Por ejemplo, considere la siguiente consulta que busca en un gráfico de tabla utilizando un campo de enlace:

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 hará un bucle si las relaciones de enlace contienen ciclos. Dado que necesitamos una salida «profunda», cambiar UNION ALL por UNION no eliminaría el bucle. En su lugar, necesitamos reconocer si hemos llegado a la misma fila de nuevo al seguir una ruta particular de enlaces. Añadimos dos columnas path y cycle a la consulta propensa a los 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;

Además de evitar los ciclos, el valor de la matriz suele ser útil por sí mismo, ya que representa el «camino» que se ha seguido para llegar a una fila concreta.

En el caso general de que haya que comprobar más de un campo para reconocer un ciclo, utilice una matriz de filas. Por ejemplo, si necesitamos comparar los 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 las consultas cuando no se está seguro de si pueden formar un bucle es colocar un LIMIT en la consulta padre. Por ejemplo, esta consulta se repetiría para siempre sin el 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 sólo tantas filas de una consulta WITH como las que realmente obtiene la consulta padre. No se recomienda utilizar este truco en producción, ya que otros sistemas podrían funcionar de forma diferente. Además, normalmente no funcionará si hace que la consulta externa ordene los resultados de la consulta recursiva o los una a alguna otra tabla, porque en estos casos la consulta externa intentará obtener toda la salida de la consulta WITH de todos modos.

Una propiedad útil de las consultas WITH es que se evalúan sólo una vez por ejecución de la consulta padre, incluso si son referidas más de una vez por la consulta padre o las consultas WITH hermanas. Así, los cálculos costosos que se necesitan en varios lugares pueden colocarse dentro de una consulta WITH para evitar el trabajo redundante. Otra posible aplicación es evitar las evaluaciones múltiples no deseadas de funciones con efectos secundarios. Sin embargo, la otra cara de la moneda es que el optimizador es menos capaz de empujar las restricciones de la consulta principal hacia abajo en una consulta WITH que una subconsulta ordinaria. La consulta WITH se evaluará generalmente tal y como está escrita, sin suprimir las filas que la consulta principal podría descartar después. (Pero, como se mencionó anteriormente, la evaluación puede detenerse antes de tiempo si la(s) referencia(s) a la consulta demandan sólo un número limitado de filas.)

Los ejemplos anteriores sólo muestran el uso de WITH con SELECT, pero puede adjuntarse de la misma manera a INSERT, UPDATE o DELETE. En cada caso, proporciona efectivamente una tabla(s) temporal(es) a la que se puede hacer referencia en el comando principal.