CTE y WITH RECURSIVE SQL: 6 ejercicios para GSI A2

Una consulta larga no tiene por qué ser una sola frase difícil de leer. Una CTE (common table expression) permite poner nombre a un resultado intermedio y continuar desde él. En SQLite se introduce con WITH; y cuando ese resultado debe alimentarse de sí mismo se usa WITH RECURSIVE. La documentación de SQLite sobre WITH es la fuente primaria para esta sintaxis y sus reglas: una CTE pertenece a una única sentencia, no persiste como tabla y puede ser ordinaria o recursiva.

Para quien prepara el GSI A2, este es entrenamiento de razonamiento SQL: separar el dato base, el cálculo y la presentación del resultado. La Resolución enlazada en el BOE, en su Anexo IX, incluye SQL dentro del programa de referencia. No dice que las CTE sean obligatorias ni que estos sean ejercicios oficiales; se han elegido como práctica útil para entender consultas y relaciones de datos. No sustituyen las bases de una convocatoria ni prometen aprobar.

Si aún no dominas JOIN, agregados y valores NULL, conviene empezar por ejercicios de JOIN, NULL y GROUP BY. Para conjuntos que se combinan sin recursión, también ayuda el artículo de UNION, INTERSECT y EXCEPT. Aquí el motor es exclusivamente SQLite: no se afirma compatibilidad universal ni una mejora automática de rendimiento en otros motores.

Datos ficticios para ejecutar todos los ejemplos

Abre SQLite, por ejemplo con sqlite3 practica.db, y ejecuta este bloque una vez. Son datos inventados; la unidad 7 es una segunda raíz sin parentesco para evitar confundir “todos los registros” con “el árbol de Dirección”. En cada ejercicio se muestra una consulta independiente que puede ejecutarse después de esta preparación.

CREATE TABLE gastos (area TEXT, concepto TEXT, importe INTEGER);
INSERT INTO gastos VALUES
  ('Sistemas','licencias',80), ('Sistemas','red',45), ('Sistemas','copias',30),
  ('Desarrollo','pruebas',60), ('Desarrollo','repositorio',50), ('Desarrollo','formacion',40);

CREATE TABLE unidades (id INTEGER PRIMARY KEY, nombre TEXT, padre_id INTEGER);
INSERT INTO unidades VALUES
  (1,'Direccion',NULL), (2,'Sistemas',1), (3,'Desarrollo',1),
  (4,'Infraestructura',2), (5,'Datos',2), (6,'Calidad',3), (7,'Archivo',NULL);

CREATE TABLE tickets (id INTEGER PRIMARY KEY, unidad_id INTEGER, estado TEXT, minutos INTEGER);
INSERT INTO tickets VALUES
  (1,2,'cerrado',30), (2,2,'cerrado',70), (3,3,'cerrado',50),
  (4,3,'abierto',20), (5,5,'cerrado',40), (6,5,'cerrado',60);

CREATE TABLE enlaces (origen INTEGER, destino INTEGER);
INSERT INTO enlaces VALUES (1,2), (2,3), (3,1), (2,4);

El patrón recursivo tiene tres piezas. El ancla crea las primeras filas; el paso recursivo toma una fila ya obtenida y produce la siguiente; el mecanismo de fin hace que deje de producir filas: por una condición explícita, por falta de coincidencias o porque UNION descarta filas idénticas ya vistas. Antes de memorizar sintaxis, localiza esas tres piezas: es la defensa más simple contra una consulta que nunca termina.

Nota de comprobación: las seis consultas y sus salidas se ejecutaron el 11 de noviembre de 2026 con SQLite 3.46.1 mediante el módulo sqlite3 de la biblioteca estándar de Python.

1. CTE ordinaria: total por área

Pregunta. ¿Qué áreas tienen gasto total y cuántos conceptos registran, de mayor a menor gasto?

WITH resumen AS (
  SELECT area, COUNT(*) AS conceptos, SUM(importe) AS total
  FROM gastos
  GROUP BY area
)
SELECT area, conceptos, total
FROM resumen
ORDER BY total DESC, area ASC;

Resultado exacto.

Sistemas|3|155
Desarrollo|3|150

Razonamiento. La CTE ordinaria resumen agrupa primero. La consulta exterior decide qué columnas enseñar y cómo ordenarlas. Podría escribirse sin CTE, pero al separarlo se ve que el agregado no depende de la presentación. El ORDER BY final importa: SQL no garantiza un orden de filas si no se solicita expresamente.

2. Dos CTE encadenadas: filtrar después de resumir

Pregunta. Entre los tickets cerrados, ¿qué unidades acumulan al menos 60 minutos y cuántos tickets aportan ese tiempo?

WITH cerrados AS (
  SELECT unidad_id, minutos
  FROM tickets
  WHERE estado = 'cerrado'
), carga AS (
  SELECT u.nombre, COUNT(*) AS tickets_cerrados, SUM(c.minutos) AS minutos_cerrados
  FROM cerrados AS c
  JOIN unidades AS u ON u.id = c.unidad_id
  GROUP BY u.id, u.nombre
)
SELECT nombre, tickets_cerrados, minutos_cerrados
FROM carga
WHERE minutos_cerrados >= 60
ORDER BY minutos_cerrados DESC, nombre ASC;

Resultado exacto.

Datos|2|100
Sistemas|2|100

Razonamiento. cerrados fija el universo correcto antes del agregado; carga lo agrupa y traduce el identificador al nombre. Solo entonces tiene sentido aplicar el umbral sobre SUM. Las CTE encadenadas se leen de arriba abajo, y cada una puede usar las anteriores declaradas en la misma cláusula WITH.

3. Serie finita del 1 al 5

Pregunta. Genera los números del 1 al 5 sin tabla auxiliar.

WITH RECURSIVE numeros(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1
  FROM numeros
  WHERE n < 5
)
SELECT n
FROM numeros
ORDER BY n ASC;

Resultado exacto.

1
2
3
4
5

Razonamiento. El ancla es SELECT 1. El paso es SELECT n + 1 FROM numeros. El fin está en WHERE n < 5: cuando llega a 5, no se genera 6. Se usa UNION ALL porque cada número se genera una vez y no hace falta pagar el trabajo lógico de eliminar duplicados.

4. Jerarquía descendente desde la unidad 1

Pregunta. Partiendo de Dirección (id = 1), lista la propia unidad y sus descendientes con el nivel jerárquico.

WITH RECURSIVE arbol(id, nombre, nivel) AS (
  SELECT id, nombre, 0
  FROM unidades
  WHERE id = 1
  UNION ALL
  SELECT u.id, u.nombre, a.nivel + 1
  FROM unidades AS u
  JOIN arbol AS a ON u.padre_id = a.id
)
SELECT id, nombre, nivel
FROM arbol
ORDER BY nivel ASC, id ASC;

Resultado exacto.

1|Direccion|0
2|Sistemas|1
3|Desarrollo|1
4|Infraestructura|2
5|Datos|2
6|Calidad|2

Razonamiento. El ancla selecciona solo la raíz 1. El paso busca unidades cuyo padre_id coincide con un nodo ya alcanzado y aumenta el nivel. La terminación es natural: las hojas 4, 5 y 6 no tienen hijos. Archivo no aparece porque su raíz es 7, no 1. El orden final muestra el nivel de forma estable; el orden de evaluación recursiva no es un contrato de presentación.

5. Ancestros de una hoja

Pregunta. Desde Datos (id = 5), sube hasta la raíz e indica la distancia de cada ancestro.

WITH RECURSIVE ancestros(id, nombre, padre_id, nivel) AS (
  SELECT id, nombre, padre_id, 0
  FROM unidades
  WHERE id = 5
  UNION ALL
  SELECT u.id, u.nombre, u.padre_id, a.nivel + 1
  FROM unidades AS u
  JOIN ancestros AS a ON u.id = a.padre_id
)
SELECT nombre, nivel
FROM ancestros
ORDER BY nivel ASC, nombre ASC;

Resultado exacto.

Datos|0
Sistemas|1
Direccion|2

Razonamiento. Ahora el ancla es la hoja y el paso invierte el recorrido: busca la fila cuyo id es el padre_id de la fila actual. La fila Dirección tiene padre_id NULL; al intentar enlazar con NULL no hay coincidencia y termina. Conservar padre_id dentro de la CTE es necesario para que la siguiente iteración sepa hacia dónde subir.

6. Grafo con ciclo: nodos únicos y visitados

Pregunta. El grafo de enlaces contiene el ciclo 1 → 2 → 3 → 1. ¿Qué nodos son alcanzables desde 1 sin repetir nodos?

WITH RECURSIVE alcanzables(nodo) AS (
  SELECT 1
  UNION
  SELECT e.destino
  FROM enlaces AS e
  JOIN alcanzables AS a ON e.origen = a.nodo
)
SELECT nodo
FROM alcanzables
ORDER BY nodo ASC;

Resultado exacto.

1
2
3
4

Razonamiento. Aquí el ancla es 1 y el paso sigue cada arista saliente. UNION, no UNION ALL, descarta una fila nodo ya vista: al volver de 3 a 1 no aparece un 1 nuevo y la exploración termina. Esto funciona porque la identidad de una fila es solo nodo.

Error frecuente y precaución. Si se añade nivel a la CTE, la fila deja de ser solo el nodo: (1,0) y (1,3) son distintas. UNION ya no corta el ciclo y tampoco garantiza el final. Para conservar caminos con UNION ALL, hay que limitar profundidad o llevar visitados. Una versión segura para este ejemplo usa una ruta con separadores:

WITH RECURSIVE rutas(nodo, ruta) AS (
  SELECT 1, ',1,'
  UNION ALL
  SELECT e.destino, r.ruta || e.destino || ','
  FROM enlaces AS e JOIN rutas AS r ON e.origen = r.nodo
  WHERE instr(r.ruta, ',' || e.destino || ',') = 0
)
SELECT nodo, ruta
FROM rutas
ORDER BY nodo ASC, ruta ASC;

La condición instr es el final por anti-visitados: ninguna ruta puede repetir un nodo. No es una receta universal para todo identificador o todo motor; ilustra por qué el diseño de la fila recursiva determina si UNION basta.

Método de repaso y límites honestos

Antes de ejecutar, predice ancla, paso y final con lápiz. Después cambia un dato y vuelve a predecir: añade un hijo a Datos, o un enlace 4 → 2, y explica por qué la consulta 4 o 6 se comporta como se comporta. Ese hábito entrena más que copiar la solución. Comprueba además cada salida contra la tabla de resultados y deja siempre el ORDER BY en la consulta final; una salida aparentemente “igual” pero sin orden solicitado no es una salida reproducible.

Las CTE no materializan por sí mismas una tabla permanente y no son un botón de rendimiento. SQLite puede decidir cómo ejecutar una consulta según el plan y los datos; primero se busca corrección, después se mide con el contexto real. La sintaxis concreta de este artículo se ha comprobado con SQLite y no se presenta como garantía para otros gestores.

Preguntas frecuentes

¿Qué es una CTE en SQL?

Una CTE es un resultado con nombre definido al inicio de una única sentencia mediante WITH. No es una tabla persistente: sirve para organizar esa consulta.

¿Para qué usamos WITH RECURSIVE en estos ejemplos?

Lo usamos para expresar que la CTE se consulta a sí misma, por ejemplo al generar una serie o recorrer una jerarquía. SQLite permite omitir la palabra RECURSIVE; incluirla no convierte por sí solo una CTE ordinaria en recursiva.

¿UNION y UNION ALL son equivalentes en una CTE recursiva?

No. UNION elimina filas repetidas y puede ayudar a terminar si la fila recursiva es solo el nodo; UNION ALL conserva repeticiones y exige una condición de parada o una estrategia de visitados.

¿Puedo reutilizar la CTE en otra sentencia?

No. Su ámbito es la sentencia que empieza con WITH. Si se necesita persistir un resultado, hay que usar el mecanismo de almacenamiento adecuado de la base de datos.

¿Estos ejercicios son preguntas oficiales del GSI A2?

No. Son ejercicios originales de entrenamiento elegidos para practicar SQL. El BOE citado incluye SQL en el programa de referencia, pero no impone CTE ni convierte estos enunciados en examen oficial.

¿El Pack GSI A2 2026 cubre todo el programa?

No. Es práctica y apoyo parcial: declara qué partes trabaja y cuáles no cubre. Conviene contrastarlo con la convocatoria y completar el estudio del programa aplicable.

Siguiente práctica

Puedes pedir la muestra gratuita del Pack GSI A2 para revisar una parte del material antes de decidir. Es una muestra limitada, no el pack completo. El Pack GSI A2 2026 es práctica y apoyo parcial; no cubre todo el programa ni se presenta como un curso que “enseñe CTE”. Úsalo junto con el temario y las bases que correspondan a tu convocatoria.