Diversión con SQL: Las CTE recursivas en Postgres

Las expresiones de tabla comunes (CTE) son una construcción poderosa dentro de SQL. En la conversación diaria, es posible que escuche CTE referidas como cláusulas WITH. Puede pensar que los CTE son similares a una vista que se materializa solo mientras se ejecuta esa consulta y no existe fuera de esa consulta. Los CTE pueden ser bloques de construcción muy útiles para permitir que sus consultas SQL de gran tamaño sean más legibles. Pero también se pueden usar recursivamente, lo que le permite crear algunas consultas muy complejas sin tener que desplegarse en un lenguaje de procedimientos como plpgsql o plv8.

Los CTE recursivos se permiten llamar hasta que se cumpla alguna condición. Entremos y exploremos un CTE recursivo, uno básico y, por supuesto, usando PostgreSQL—y luego diseccionemos el CTE recursivo un poco más para ver para qué podemos usarlo:

WITH RECURSIVE tens (n) AS ( SELECT 10 UNION ALL SELECT n+10 FROM tens WHERE n+10<= 100 ) SELECT n FROM tens; 

Cuando se ejecute lo anterior, obtendremos el siguiente resultado:

 n ----- 10 20 30 40 50 60 70 80 90 100 (10 rows) 

Con lo anterior también podríamos hacer esto fácilmente con generate_series. Pero quédate con nosotros y verás las cosas más complejas que podemos hacer que no son posibles con generate_series. Primero echemos un vistazo más de cerca a cómo funciona.

La primera parte que notarás es WITH RECURSIVE. Esto le dice a Postgres que el CTE puede llamarse recursivamente a sí mismo. La siguiente parte que notará es que toma algunos parámetros en ella. En este caso (n), también puede tomar más de uno si lo necesita.

Avanzando hacia el CTE, tenemos la primera consulta que se ejecuta, SELECT 10, que genera el primer valor. La segunda parte es donde comienza toda la diversión. El UNION ALL especifica que vamos a devolver todos los registros que se producen desde el bucle. Entonces SELECT n+10 FROM tens WHERE n+10<= 100 seguirá llamando al tens CTE que se crea hasta que se cumpla la condición.

Así que esos son los conceptos básicos, pero la pregunta interesante es: ¿cuándo usarías un CTE recursivo? Cuando tiene un árbol o una estructura jerárquica para sus datos, los CTE recursivos pueden hacer la vida mucho más fácil que cargar todos sus datos y ejecutar un bucle en su código. Para aplicaciones que se ocupan de categorías de comercio electrónico y compras, los CTE recursivos son de gran ayuda.

Intentemos hacer los beneficios de los CTE recursivos en Postgres un poco más concretos, con un ejemplo. Primero vamos a crear una tabla de empleados, luego vamos a cargar algunos empleados de ejemplo. (Sí, verá algunos de los nombres de nuestro equipo aquí en Citus Data en esta lista. Fueron los primeros nombres que me vinieron a la mente.)

CREATE TABLE employees ( id serial, name varchar(255), manager_id int ); INSERT INTO employees VALUES (1, 'Umur', null); INSERT INTO employees VALUES (2, 'Craig', 1); INSERT INTO employees VALUES (3, 'Daniel', 2); INSERT INTO employees VALUES (4, 'Claire', 1); INSERT INTO employees VALUES (5, 'Lindsay', 2); INSERT INTO employees VALUES (6, 'Will', 2); INSERT INTO employees VALUES (7, 'Burak', 2); INSERT INTO employees VALUES (8, 'Eren', 2); INSERT INTO employees VALUES (9, 'Katie', 3); INSERT INTO employees VALUES (10, 'Teresa', 4); 

Ahora voy a escribir una consulta que me dé todos los informes que se acumulan en una organización determinada dentro de la empresa. En este caso, obtendré todos mis informes y los míos, junto con la identificación del gerente de cada persona:

WITH RECURSIVE managertree AS ( SELECT id, name, manager_id FROM employees WHERE id = 2 UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e INNER JOIN managertree mtree ON mtree.id = e.manager_id ) SELECT * FROM managertree; id | name | manager_id ----+---------+------------ 2 | Craig | 1 3 | Daniel | 2 5 | Lindsay | 2 6 | Will | 2 7 | Burak | 2 8 | Eren | 2 9 | Katie | 3 (7 rows) 

La próxima vez que necesite realizar algún cálculo recursivo en sus datos, considere hacerlo directamente en SQL en lugar de cargar todos esos datos en su aplicación. Para leer más, considere echar un vistazo a algunos de estos recursos útiles:

• Documentos de PostgreSQL en CTEs
• Resolver el problema del vendedor ambulante con un CTE
• Obtener un árbol con todos sus hijos

Como nota, los CTEs en este momento son una valla de optimización en PostgreSQL, aunque hay esperanzas de que eso cambie en el futuro. Las expresiones de tabla comunes son una herramienta increíblemente útil para generar informes. A veces, la legibilidad de los CTE supera el impacto en el rendimiento, pero tenga en cuenta las ventajas y desventajas al usarlos

• diversión con sql
• Postgres
• consejos