CTEs: Common Table Expressions com WITH

1. O que são CTEs e Por Que Usá-las?

Uma CTE (Common Table Expression) é um conjunto de resultados temporário nomeado que existe apenas durante a execução de uma consulta. Criada com a cláusula WITH, a CTE permite definir uma "tabela virtual" que pode ser referenciada dentro da instrução SQL principal, seja um SELECT, INSERT, UPDATE ou DELETE.

A sintaxe básica é:

WITH nome_cte AS (
    SELECT coluna1, coluna2
    FROM tabela
    WHERE condicao
)
SELECT * FROM nome_cte;

As CTEs oferecem vantagens significativas sobre subconsultas tradicionais:

  • Legibilidade: Consultas complexas são divididas em blocos lógicos nomeados, facilitando a compreensão do fluxo de dados.
  • Modularidade: Cada CTE pode ser desenvolvida e testada independentemente.
  • Reutilização: Uma mesma CTE pode ser referenciada múltiplas vezes dentro da mesma consulta, evitando repetição de código.

Diferentemente de subconsultas tradicionais (que são executadas como parte de uma expressão), as CTEs são definidas no escopo da consulta principal e podem ser referenciadas por nome, tornando o código mais autodocumentado.

2. Sintaxe Fundamental e Exemplos Simples

A estrutura básica de uma CTE segue o padrão:

WITH nome_cte AS (
    consulta_interna
)
consulta_principal

Exemplo prático: filtrar vendas acima da média mensal.

WITH vendas_por_dia AS (
    SELECT 
        data_venda,
        SUM(valor) AS total_dia
    FROM vendas
    WHERE data_venda >= '2024-01-01'
    GROUP BY data_venda
),
media_vendas AS (
    SELECT AVG(total_dia) AS media_mensal
    FROM vendas_por_dia
)
SELECT v.data_venda, v.total_dia
FROM vendas_por_dia v
CROSS JOIN media_vendas m
WHERE v.total_dia > m.media_mensal
ORDER BY v.total_dia DESC;

Múltiplas CTEs são separadas por vírgula, e cada uma pode referenciar as anteriores, criando uma pipeline de transformação de dados.

3. CTEs Não Recursivas: Organizando Consultas Complexas

CTEs não recursivas são ideais para dividir lógica em etapas bem definidas. Considere um relatório de desempenho de vendas por cliente:

WITH total_pedidos AS (
    SELECT 
        cliente_id,
        COUNT(*) AS qtd_pedidos,
        SUM(valor_total) AS valor_total
    FROM pedidos
    WHERE status = 'CONCLUIDO'
    GROUP BY cliente_id
),
clientes_ativos AS (
    SELECT 
        c.nome,
        c.email,
        tp.qtd_pedidos,
        tp.valor_total
    FROM clientes c
    INNER JOIN total_pedidos tp ON c.id = tp.cliente_id
    WHERE tp.qtd_pedidos >= 5
),
ranking AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY valor_total DESC) AS posicao
    FROM clientes_ativos
)
SELECT * FROM ranking
WHERE posicao <= 10;

Cada CTE encapsula uma transformação específica: agregação, filtragem e classificação. O resultado é uma consulta clara e fácil de modificar.

4. CTEs Recursivas: Navegando Hierarquias e Grafos

CTEs recursivas são poderosas para trabalhar com estruturas hierárquicas ou grafos. A anatomia inclui uma parte âncora (que retorna o nível base), uma parte recursiva (que referencia a si mesma) e UNION ALL.

Exemplo: hierarquia de funcionários.

WITH RECURSIVE hierarquia AS (
    -- Parte âncora: funcionários sem gerente (nível 1)
    SELECT 
        id,
        nome,
        gerente_id,
        1 AS nivel,
        CAST(nome AS VARCHAR(500)) AS caminho
    FROM funcionarios
    WHERE gerente_id IS NULL

    UNION ALL

    -- Parte recursiva: subordinados
    SELECT 
        f.id,
        f.nome,
        f.gerente_id,
        h.nivel + 1,
        CAST(h.caminho || ' -> ' || f.nome AS VARCHAR(500))
    FROM funcionarios f
    INNER JOIN hierarquia h ON f.gerente_id = h.id
)
SELECT * FROM hierarquia
ORDER BY caminho;

Para evitar loops infinitos, o SGBD impõe um limite máximo de profundidade (tipicamente 100 níveis no PostgreSQL, configurável com SET max_recursive_iterations). Sempre use uma condição de parada na parte recursiva.

5. CTEs com Cláusulas de Modificação de Dados

CTEs podem ser usadas com comandos DML (INSERT, UPDATE, DELETE) para modificar dados de forma controlada.

Exemplo: atualizar saldo de estoque baseado em vendas recentes.

WITH vendas_ultima_semana AS (
    SELECT 
        produto_id,
        SUM(quantidade) AS total_vendido
    FROM itens_pedido
    WHERE data_pedido >= CURRENT_DATE - INTERVAL '7 days'
    GROUP BY produto_id
)
UPDATE produtos p
SET estoque_atual = estoque_atual - v.total_vendido
FROM vendas_ultima_semana v
WHERE p.id = v.produto_id;

Limitações importantes:
- Nem todos os SGBDs suportam CTEs modificáveis (MySQL só a partir da versão 8.0).
- A CTE deve ser referenciada diretamente no comando DML.
- Não é possível usar a mesma CTE em múltiplos comandos DML simultaneamente.

6. Boas Práticas e Armadilhas Comuns

Performance: Em alguns SGBDs (como PostgreSQL), CTEs são materializadas (executadas uma vez e armazenadas em memória), o que pode ser benéfico para CTEs referenciadas múltiplas vezes. No SQL Server e Oracle, o otimizador pode "inline" a CTE, tratando-a como subconsulta. Conheça o comportamento do seu SGBD.

Armadilhas comuns:
- CTEs excessivamente aninhadas: prefira dividir em CTEs separadas em vez de aninhar subconsultas dentro da CTE.
- CTEs desnecessárias: para consultas simples, subconsultas ou joins diretos são mais eficientes.
- Recursão sem condição de parada: sempre garanta que a parte recursiva eventualmente não retorne linhas.

Boas práticas:
- Nomeie CTEs com nomes descritivos do propósito.
- Mantenha CTEs curtas e focadas em uma única transformação.
- Documente CTEs recursivas com comentários explicando a lógica.

7. CTEs vs. Alternativas: Tabelas Temporárias e Subconsultas

Característica CTE Tabela Temporária Subconsulta
Escopo Consulta única Sessão/transação Expressão
Índices Não Sim Não
Reuso Múltiplas referências na mesma consulta Múltiplas consultas Apenas na subconsulta
Performance Materialização opcional Materialização garantida Inline

Quando usar CTE:
- Consultas complexas que exigem legibilidade.
- Quando a mesma transformação é referenciada múltiplas vezes.
- Para hierarquias e grafos (recursão).

Quando usar tabela temporária:
- Dados intermediários que serão usados em múltiplas consultas.
- Grandes volumes de dados que precisam de índices.
- Operações complexas que exigem modificação incremental.

Quando usar subconsulta:
- Consultas simples e diretas.
- Quando a lógica é trivial e não justifica a criação de um bloco nomeado.

Referências