Usando CTE pra deixar consultas complexas mais legíveis

Imagina que você precisa escrever uma consulta SQL grandona, que faz várias operações conectadas. Dá pra só ir encaixando um monte de subqueries uma dentro da outra, mas o resultado vai ficar tipo um código espaguete. Um verdadeiro labirinto SQL, onde até quem escreveu se perde fácil.

CTE é tipo seu colete salva-vidas! Com CTE, dá pra quebrar uma consulta complicada em partes lógicas, cada uma com seu nome. Isso deixa sua query muito mais fácil de entender e de manter depois.

Comparando: Subquery vs CTE

De primeira, parece que os dois fazem a mesma coisa — filtram as notas por curso e calculam a média de cada estudante. Mas olha com mais atenção: na versão com subquery, a lógica tá "escondida" entre parênteses, já no CTE ela fica pra fora e ganha um nome maneiro, tipo filtered_grades. Agora imagina se não fossem dois, mas dez passos intermediários!

Subquery:

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;

Acha as 10 diferenças. Com certeza, CTE ganha de lavada na leitura!

Quebrando consultas complexas em etapas com CTE

CTE deixa você montar a consulta passo a passo, de um jeito que cada etapa fique o mais clara possível. Por exemplo, se você quer pegar a lista de estudantes com a média deles por curso e ainda juntar com os dados dos professores, separa a tarefa em partes.

Exemplo:

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;

Fala sério, ficou legível, né? Mesmo se você voltar nesse código daqui um mês, vai sacar rapidinho o que ele faz.

Usando vários CTE pra um relatório grandão

Bora ver um exemplo de relatório mais cabuloso. Imagina que temos um banco de dados da faculdade e queremos montar um relatório dos estudantes mais brabos, os cursos deles e os professores. O plano é:

1. Primeiro, achar os estudantes com média alta (acima de 90).
2. Depois, ligar eles com os cursos.
3. Por fim, juntar os dados dos professores.

Consulta com vários 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;

O legal dessa consulta é que cada parte da tarefa virou um bloco separado, bem lógico. Quer saber quem são os estudantes que mandaram bem? Olha no CTE high_achievers. Quer ver a ligação deles com os cursos? Tá no student_courses. E os professores? Tudo em teachers. Assim, fica muito mais fácil de manter e mexer no código depois.

Quebrando cálculos complexos em etapas

Às vezes, suas queries vão ter cálculos ou filtros mais pesados. Em vez de tentar enfiar tudo numa consulta gigante, separa em vários CTE.

Exemplo:

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;

Aqui, primeiro a gente pega as estatísticas dos cursos em course_stats, depois filtra os cursos populares em popular_courses, e só então junta com a tabela de cursos. Esse jeito de separar os passos intermediários deixa tudo muito mais fácil de entender.

Quando CTE é indispensável?

Olha alguns cenários onde CTE brilha:

1. Análise e relatórios. Tipo calcular métricas complicadas com filtros por grupo.
2. Trabalhar com estruturas hierárquicas. CTE recursivo pra montar árvore de categorias ou organograma.
3. Reutilizar dados. Por exemplo, quando o mesmo resultado é usado em várias partes da consulta.

Erros comuns ao usar CTE

Claro, como toda ferramenta poderosa, CTE tem suas pegadinhas.

Materialização desnecessária de dados. No PostgreSQL, CTE por padrão são "materializados", ou seja, o resultado é calculado e guardado temporariamente. Isso pode deixar tudo mais lento se os dados forem grandes demais. Pra evitar, usa índices e tenta selecionar só as colunas que realmente precisa.

Joins errados. Às vezes, consultas com muitos CTE ficam difíceis de otimizar. Sempre testa suas queries com EXPLAIN ou EXPLAIN ANALYZE.

Exagerar no uso de CTE. Se seus CTE estão ficando gigantes e confusos, talvez seja melhor dividir em várias consultas separadas.