Uso de CTE para mejorar la legibilidad de consultas complejas

Imagina que tienes que escribir una consulta SQL enorme que hace varias operaciones relacionadas entre sí. Podrías simplemente anidar un montón de subconsultas una dentro de otra, pero el resultado sería un código espagueti. Un auténtico laberinto SQL en el que hasta el propio autor se pierde fácil.

¡CTE es tu salvavidas! CTE te permite dividir una consulta compleja en partes lógicas, cada una como una sección nombrada aparte. Así tu consulta es mucho más clara y fácil de mantener.

Comparación: Subconsulta vs CTE

A primera vista, ambos enfoques hacen lo mismo — filtran las notas por curso y calculan la media para cada estudiante. Pero fíjate bien: en la versión con subconsulta la lógica está "escondida" entre paréntesis, mientras que en el CTE está fuera y tiene un nombre claro, filtered_grades. ¡Ahora imagina que hay diez pasos intermedios en vez de dos!

Subconsulta:

SELECT student_id, AVG(grade) AS avg_grade
FROM (
    SELECT student_id, grade
    FROM grades
    WHERE course_id = 101
) subquery
GROUP BY student_id;

CTE:

WITH filtered_grades AS (
    SELECT student_id, grade
    FROM grades
    WHERE course_id = 101
)
SELECT student_id, AVG(grade) AS avg_grade
FROM filtered_grades
GROUP BY student_id;

Encuentra las 10 diferencias. ¡Por supuesto, el CTE gana en facilidad de lectura!

Dividir consultas complejas en etapas usando CTE

CTE te permite construir la consulta paso a paso, de modo que en cada etapa el resultado sea lo más claro posible. Por ejemplo, si quieres obtener una lista de estudiantes con su nota media por curso y añadir los datos de sus profesores, divide la tarea en varias partes.

Ejemplo:

WITH avg_grades AS (
    SELECT student_id, course_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id, course_id
),
course_teachers AS (
    SELECT course_id, teacher_id
    FROM courses
)

SELECT ag.student_id, ag.avg_grade, ct.teacher_id
FROM avg_grades ag
JOIN course_teachers ct ON ag.course_id = ct.course_id;

¿A que es legible? Incluso si vuelves a esta consulta dentro de un mes, su estructura seguirá siendo obvia.

Uso de varios CTE para un informe grande

Vamos a ver un ejemplo de un informe más complejo. Imagina que tienes una base de datos de una universidad y quieres crear un informe sobre los estudiantes más exitosos, sus cursos y profesores. El plan es:

1. Primero encontramos a los estudiantes con una nota media alta (más de 90).
2. Luego los relacionamos con sus cursos.
3. Por último, añadimos los datos de los profesores.

Consulta con varios CTE:

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name, c.teacher_id
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
),
teachers AS (
    SELECT teacher_id, name AS teacher_name
    FROM teachers
)

SELECT ha.student_id, ha.avg_grade, sc.course_name, t.teacher_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN teachers t ON sc.teacher_id = t.teacher_id;

Lo bueno de esta consulta es que cada parte de la tarea la pusimos como un bloque lógico separado. ¿Quieres saber quiénes son los estudiantes con logros impresionantes? Mira el CTE high_achievers. ¿Te interesa su relación con los cursos? Eso está en student_courses. ¿Los profes que necesitas? Todo en teachers. Este enfoque hace mucho más fácil mantener y modificar el código.

Dividir en etapas los cálculos complejos

A veces tus consultas incluyen cálculos o filtros complicados. En vez de intentar meterlo todo en una consulta larguísima, divídela en varios CTE.

Ejemplo:

WITH course_stats AS (
    SELECT course_id, COUNT(student_id) AS student_count, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY course_id
),
popular_courses AS (
    SELECT course_id
    FROM course_stats
    WHERE student_count > 50
)
SELECT c.course_name, cs.student_count, cs.avg_grade
FROM popular_courses pc
JOIN course_stats cs ON pc.course_id = cs.course_id
JOIN courses c ON c.course_id = pc.course_id;

Aquí primero recogemos estadísticas de los cursos en course_stats, luego filtramos los cursos populares en popular_courses, y después lo juntamos con la tabla de cursos. Así puedes separar los pasos intermedios y entender la consulta mucho mejor.

¿Cuándo es imprescindible usar CTE?

Aquí tienes algunos escenarios donde el CTE es especialmente útil:

1. Analítica e informes. Por ejemplo, calcular métricas complejas con filtros por grupos.
2. Trabajar con estructuras jerárquicas. CTE recursivos para construir árboles de categorías o estructuras organizativas.
3. Reutilización de datos. Por ejemplo, si el mismo conjunto de datos se usa en varias etapas de la consulta.

Errores típicos al usar CTE

Por supuesto, como cualquier herramienta potente, el CTE tiene sus trampas ocultas.

Materialización excesiva de datos. En PostgreSQL los CTE por defecto se "materializan", es decir, su resultado se calcula y se guarda temporalmente. Esto puede ralentizar la ejecución si los datos son muy grandes. Para evitarlo, usa índices y selecciona solo las columnas que realmente necesitas.

Uniones incorrectas. A veces las consultas complejas con varios CTE son difíciles de optimizar. Siempre revisa tus consultas con EXPLAIN o EXPLAIN ANALYZE.

Uso excesivo de CTE. Si tus CTE se hacen demasiado largos y confusos, puede que sea mejor dividir la consulta en varias operaciones separadas.