Ejemplos de CTE recursiva: tareas prácticas

Lección 6.6 · Tiempo de lectura: ~9 min · Tareas de práctica ↓

Las CTE recursivas sirven para mucho más que organigramas. En esta lección aplicarás WITH RECURSIVE a cuatro tareas comunes: generar una serie de fechas para rellenar huecos en un informe diario, totalizar las ventas en todos los niveles de un árbol de categorías, explotar una lista de materiales en la lista completa de piezas y encontrar todas las rutas entre dos ciudades en una red de carreteras con ciclos.

En la lección anterior aprendiste cómo funciona una CTE recursiva: el miembro ancla devuelve las filas iniciales, el miembro recursivo encuentra el nivel siguiente y la recursión se detiene cuando no aparece nada nuevo. Aquí el mismo patrón se convierte en una herramienta para problemas cotidianos. Cada ejemplo muestra una técnica reutilizable: una secuencia generada, pares ancestro–descendiente, cantidades multiplicadas y una ruta con control de ciclos.

El primer ejemplo se ejecuta en la base de datos de ejemplo Sakila. Los demás crean sus propias tablas, así que ejecútalos en el playground en una base sin datos de ejemplo. La sintaxis es la de MySQL 8.0; también funciona en MariaDB 10.2+.

1. Generar una serie de fechas para rellenar huecos

Un informe de "alquileres por día" construido con GROUP BY muestra solo los días que tienen alquileres. Los días sin alquileres simplemente desaparecen, y un gráfico hecho con esos datos oculta el hueco. La solución es generar un calendario con todos los días y unir los datos a él.

Una CTE recursiva genera el calendario: el ancla devuelve el primer día y cada iteración agrega un día hasta llegar al último:

WITH RECURSIVE calendar AS (
    SELECT DATE('2005-05-28') AS day
    UNION ALL
    SELECT day + INTERVAL 1 DAY
    FROM calendar
    WHERE day < '2005-06-16'
)
SELECT c.day, COUNT(r.rental_id) AS rentals
FROM calendar AS c
LEFT JOIN rental AS r
    ON r.rental_date >= c.day
   AND r.rental_date < c.day + INTERVAL 1 DAY
GROUP BY c.day
ORDER BY c.day;

Resultado: 20 filas, una por cada día del 28 de mayo al 16 de junio de 2005. Del 1 al 13 de junio no hay alquileres, y esos días muestran 0 en lugar de desaparecer.

Hay tres detalles importantes:

• LEFT JOIN conserva los días sin alquileres; COUNT(r.rental_id) cuenta solo los alquileres reales, así que los días vacíos obtienen 0.
• La unión usa un rango (>= day y < day + 1 día) en lugar de DATE(r.rental_date) = c.day, para que todavía se pueda usar un índice sobre rental_date.
• El mismo patrón genera cualquier secuencia: números, horas, meses. Una serie de más de 1000 pasos requiere aumentar cte_max_recursion_depth.

2. Totales de un subárbol completo

En un árbol de categorías, las ventas se registran en categorías concretas, pero un informe necesita los totales de cada rama: "Computers" debe incluir las laptops y las computadoras de escritorio, y "Electronics" debe incluirlo todo.

CREATE TABLE product_category (
    category_id INT PRIMARY KEY,
    parent_id INT,
    name VARCHAR(50) NOT NULL
);

INSERT INTO product_category VALUES
    (1, NULL, 'Electronics'),
    (2, 1, 'Computers'),
    (3, 1, 'Phones'),
    (4, 2, 'Laptops'),
    (5, 2, 'Desktops'),
    (6, 3, 'Smartphones');

CREATE TABLE sale (
    sale_id INT PRIMARY KEY,
    category_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO sale VALUES
    (1, 4, 1200), (2, 4, 800), (3, 5, 700),
    (4, 6, 500), (5, 6, 300), (6, 3, 150);

El truco es construir primero todos los pares ancestro–descendiente. El ancla empareja cada categoría consigo misma; cada iteración agrega los hijos de los descendientes ya encontrados:

WITH RECURSIVE subtree AS (
    SELECT category_id AS ancestor_id, category_id
    FROM product_category

    UNION ALL

    SELECT s.ancestor_id, c.category_id
    FROM subtree AS s
    INNER JOIN product_category AS c ON c.parent_id = s.category_id
)
SELECT a.name, SUM(sl.amount) AS total_sales
FROM subtree AS s
INNER JOIN product_category AS a ON a.category_id = s.ancestor_id
LEFT JOIN sale AS sl ON sl.category_id = s.category_id
GROUP BY a.category_id, a.name
ORDER BY a.category_id;

Resultado: Electronics 3650.00, Computers 2700.00, Phones 950.00, Laptops 2000.00, Desktops 700.00, Smartphones 800.00.

Cada venta se cuenta una vez para su propia categoría y una vez para cada ancestro. "Phones" obtiene 950: 150 vendidos directamente en esa categoría más 800 de "Smartphones". La agregación se hace en la consulta final, porque MySQL no permite GROUP BY ni funciones de agregación en el miembro recursivo.

3. Explotar una lista de materiales

Una lista de materiales (BOM, bill of materials) indica de qué está hecho un producto. Los componentes tienen sus propios componentes, y la misma pieza puede aparecer en varias ramas. Para pedir las piezas de una bicicleta, necesitas la cantidad total de cada pieza:

CREATE TABLE part (
    part_id INT PRIMARY KEY,
    part_name VARCHAR(50) NOT NULL
);

INSERT INTO part VALUES
    (1, 'Bicycle'), (2, 'Frame'), (3, 'Wheel'),
    (4, 'Rim'), (5, 'Spoke'), (6, 'Bolt');

CREATE TABLE bom (
    parent_part_id INT,
    child_part_id INT,
    quantity INT NOT NULL,
    PRIMARY KEY (parent_part_id, child_part_id)
);

INSERT INTO bom VALUES
    (1, 2, 1),   -- una bicicleta tiene 1 cuadro
    (1, 3, 2),   -- y 2 ruedas
    (2, 6, 4),   -- un cuadro tiene 4 pernos
    (3, 4, 1),   -- una rueda tiene 1 rin
    (3, 5, 36),  -- 36 rayos
    (3, 6, 2);   -- y 2 pernos

En cada nivel, la cantidad de un componente se multiplica por la cantidad de su padre:

WITH RECURSIVE explosion AS (
    SELECT child_part_id AS part_id, quantity
    FROM bom
    WHERE parent_part_id = 1

    UNION ALL

    SELECT b.child_part_id, e.quantity * b.quantity
    FROM explosion AS e
    INNER JOIN bom AS b ON b.parent_part_id = e.part_id
)
SELECT p.part_name, SUM(e.quantity) AS total_quantity
FROM explosion AS e
INNER JOIN part AS p ON p.part_id = e.part_id
GROUP BY p.part_id, p.part_name
ORDER BY p.part_id;

Resultado: Frame 1, Wheel 2, Rim 2, Spoke 72, Bolt 8.

Rayos: 2 ruedas × 36 = 72. Los pernos vienen de dos ramas: 4 en el cuadro y 2 × 2 = 4 en las ruedas, y SUM los suma para obtener 8. Para obtener solo las piezas que se compran y no las que se ensamblan, conserva las filas cuya pieza no tiene componentes propios: agrega WHERE NOT EXISTS (SELECT 1 FROM bom AS c WHERE c.parent_part_id = e.part_id).

4. Rutas en un grafo con ciclos

Un árbol tiene un único camino hacia cada nodo. Una red de carreteras es un grafo: las carreteras van en ambos sentidos y forman ciclos, así que una recursión ingenua daría vueltas para siempre. Busquemos todas las rutas de Berlín a Viena:

CREATE TABLE road (
    from_city VARCHAR(30),
    to_city VARCHAR(30),
    distance_km INT NOT NULL,
    PRIMARY KEY (from_city, to_city)
);

INSERT INTO road VALUES
    ('Berlin', 'Prague', 350),
    ('Berlin', 'Leipzig', 190),
    ('Leipzig', 'Prague', 250),
    ('Prague', 'Vienna', 330),
    ('Leipzig', 'Munich', 430),
    ('Munich', 'Vienna', 400);

Cada carretera se guarda una vez, así que una CTE normal edge agrega primero el sentido inverso. La CTE recursiva trip extiende cada ruta con una carretera más, salta las ciudades que ya están en la ruta y deja de extender una ruta cuando llega a Viena:

WITH RECURSIVE edge AS (
    SELECT from_city, to_city, distance_km FROM road
    UNION ALL
    SELECT to_city, from_city, distance_km FROM road
),
trip AS (
    SELECT
        to_city AS city,
        CAST(CONCAT(from_city, ',', to_city) AS CHAR(500)) AS route,
        distance_km AS total_km
    FROM edge
    WHERE from_city = 'Berlin'

    UNION ALL

    SELECT e.to_city, CONCAT(t.route, ',', e.to_city), t.total_km + e.distance_km
    FROM trip AS t
    INNER JOIN edge AS e ON e.from_city = t.city
    WHERE FIND_IN_SET(e.to_city, t.route) = 0
      AND t.city <> 'Vienna'
)
SELECT route, total_km
FROM trip
WHERE city = 'Vienna'
ORDER BY total_km;

Resultado: 4 rutas; la más corta es Berlín, Praga, Viena, con 680 km.

FIND_IN_SET(e.to_city, t.route) = 0 es el control de ciclos de la lección anterior: una ruta nunca vuelve a una ciudad ya visitada. Fíjate en que WITH RECURSIVE se escribe una sola vez, y las CTE que vienen después pueden ser normales o recursivas.

Esta consulta enumera todas las rutas posibles, y su número crece muy rápido con el tamaño de la red. Sirve para grafos pequeños, pero en redes de carreteras grandes los caminos más cortos se calculan con algoritmos especializados, por ejemplo en bases de datos de grafos o con la extensión de PostgreSQL pgRouting.

Cuándo una CTE recursiva no es la mejor herramienta

• Árboles profundos que se leen con frecuencia. Si una jerarquía se lee mucho más de lo que cambia, guarda una estructura adicional: una ruta materializada (/1/2/4/) o una tabla de clausura (closure table) con todos los pares ancestro–descendiente, como la CTE subtree de arriba, guardada en una tabla. Así un subárbol es un simple JOIN sin recursión.
• Grafos grandes. Enumerar rutas es exponencial; usa algoritmos de grafos.
• Un número pequeño y fijo de niveles. Para "empleado y su jefe", un solo self-join (lección 5.7) es más sencillo.

Conclusiones clave:

• Una CTE recursiva genera secuencias: un calendario de fechas unido con LEFT JOIN rellena los huecos de los informes.
• Los pares ancestro–descendiente construidos con una CTE recursiva permiten totalizar valores sobre todo un subárbol.
• En una lista de materiales, las cantidades se multiplican nivel a nivel y se suman con GROUP BY en la consulta final.
• En los grafos con ciclos, guarda los nodos visitados en una ruta y sáltalos con FIND_IN_SET().
• La agregación va en la consulta final: MySQL no la permite en el miembro recursivo.
• Para grafos grandes o árboles que se leen muy a menudo, considera las tablas de clausura o los algoritmos de grafos.

Preguntas frecuentes

¿Cómo genero una serie de fechas en MySQL?

Usa una CTE recursiva: WITH RECURSIVE calendar AS (SELECT DATE('2024-01-01') AS day UNION ALL SELECT day + INTERVAL 1 DAY FROM calendar WHERE day < '2024-01-31') SELECT day FROM calendar;. PostgreSQL además tiene la función integrada generate_series().

¿Cómo relleno las fechas faltantes en un informe SQL?

Genera con una CTE recursiva un calendario de todas las fechas, luego une los datos con LEFT JOIN y cuenta una columna de la tabla unida, como COUNT(r.rental_id). Las fechas sin datos obtienen 0 en lugar de desaparecer.

¿Cómo calculo un total para un nodo y todos sus descendientes?

Construye los pares ancestro–descendiente con una CTE recursiva: el ancla empareja cada nodo consigo mismo y el miembro recursivo agrega los hijos de cada descendiente. Después une los valores a los descendientes y agrupa por ancestro con GROUP BY.

¿Cómo evito los ciclos al recorrer un grafo con una CTE recursiva?

Guarda la ruta de nodos visitados en una columna de texto y agrega al miembro recursivo una condición como FIND_IN_SET(next_node, path) = 0. PostgreSQL 14+ también tiene una cláusula CYCLE que marca y detiene los ciclos automáticamente.