CTE em SQL: Um Guia Completo com Exemplos

Como Criar um CTE SQL

Ao criar um CTE, usamos a palavra-chave WITH para iniciar a definição do CTE. A sintaxe geral de um CTE é a seguinte:

WITH cte_name (column1, column2, ...)
AS (
    -- Consulta que define o CTE
    SELECT ...
    FROM ...
    WHERE ...
)
-- Consulta principal
SELECT ...
FROM cte_name;

Onde:

• WITH: Inicia a definição do CTE, indicando que o nome seguinte representa um conjunto de resultados temporário.

• cte_name: O nome é atribuído ao CTE para referenciá-lo na consulta principal.

• Lista de colunas opcional (coluna1, coluna2, …): Especifica os nomes das colunas para o conjunto de resultados do CTE. Isso é útil quando os nomes das colunas precisam ser ajustados.

• Consulta que define o CTE: A consulta interna que seleciona dados e molda o conjunto de resultados temporário.

• Consulta principal: Faz referência ao CTE pelo seu nome, utilizando-o como uma tabela.

Vamos ver o seguinte exemplo de criação de um CTE usando uma abordagem em camadas. Vamos supor que temos uma tabela Funcionários e queremos criar um CTE que selecione funcionários que ganham um salário acima de $50.000.

Passo 1: Escrever a consulta base

Começamos escrevendo a consulta básica SELECT:

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

Passo 2: Envolver a consulta usando a palavra-chave WITH para criar um CTE

Utilize a palavra-chave WITH para dar um nome ao CTE.

WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)

Passo 3: Usar o CTE na consulta principal

Por fim, faça referência ao CTE em uma instrução SELECT chamando o nome do CTE definido acima.

-- Definir uma Expressão de Tabela Comum (CTE)
WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)
-- Utilizar o CTE para selecionar funcionários com altos rendimentos
SELECT EmployeeID, FirstName, LastName
FROM HighEarningEmployees;

Para resumir os passos acima, utilizamos a palavra-chave WITH para definir o CTE chamado FuncionariosComAltosRendimentos. A consulta interna foi usada para gerar o conjunto de dados temporário. A consulta principal faz referência ao FuncionariosComAltosRendimentos para exibir as colunas especificadas IDFuncionario, Nome e Sobrenome.

Por que as CTEs em SQL são úteis

Do exemplo acima, você pode estar se perguntando por que usamos CTEs quando até mesmo consultas simples produzem os mesmos resultados. As seguintes são as razões:

Simplificar consultas complexas

CTEs dividem declarações SQL complexas em partes menores e mais gerenciáveis, tornando o código mais fácil de ler, escrever e manter. 

Suponha que temos três tabelas: Pedidos, Clientes e Produtos. Queremos encontrar a receita total gerada por cada cliente que comprou em 2024. Quando escrevemos a consulta sem usar CTE, ela fica confusa e difícil de ler e entender.

-- Selecionar nomes de clientes e receita total de seus pedidos
SELECT c.CustomerName, SUM(p.Price * o.Quantity) AS TotalRevenue
FROM Orders o
-- Juntar para obter tabelas de clientes e produtos
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON o.ProductID = p.ProductID
WHERE YEAR(o.OrderDate) = 2024
GROUP BY c.CustomerName
HAVING SUM(p.Price * o.Quantity) > 1000;

Ao usar um CTE, podemos separar a lógica em um formato mais legível:

-- Definir o CTE
WITH OrderDetails AS (
    SELECT o.OrderID, c.CustomerName, p.Price, o.Quantity, o.OrderDate
    FROM Orders o
    JOIN Customers c ON o.CustomerID = c.CustomerID
    JOIN Products p ON o.ProductID = p.ProductID
    WHERE YEAR(o.OrderDate) = 2024
)
--Consulta principal
SELECT CustomerName, SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

Reutilização de código

CTEs ajudam a evitar a duplicação permitindo que o mesmo conjunto de resultados seja reutilizado em diferentes partes de uma consulta. Se múltiplos cálculos ou operações são baseados no mesmo conjunto de dados, você pode defini-lo uma vez em um CTE e fazer referência a ele conforme necessário.

Suponha que precisamos calcular a média e o total de vendas para cada categoria de produto em um banco de dados de comércio eletrônico. Podemos usar um CTE para definir os cálculos uma vez e reutilizá-los em consultas subsequentes.

-- Definir um CTE para calcular o total e a média de vendas para cada categoria
WITH CategorySales AS (
    SELECT Category, SUM(SalesAmount) AS TotalSales, AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- Selecionar categoria, total de vendas e média de vendas do CTE
SELECT Category, TotalSales, AverageSales
FROM CategorySales
WHERE TotalSales > 5000;

Outras aplicações

Além de simplificar consultas e reutilização de código, os CTEs também têm outras utilidades.Não consigo cobrir todos os possíveis usos de CTEs em detalhes. Nosso curso Manipulação de Dados em SQL é uma ótima opção se você quiser continuar praticando. No entanto, vou documentar aqui alguns dos principais outros motivos:

• Organização e Legibilidade da Consulta: Os CTEs melhoram a legibilidade do código SQL dividindo as consultas em etapas lógicas e sequenciais. Cada etapa no processo de consulta pode ser representada por seu próprio CTE, tornando toda a consulta mais fácil de seguir.
• Travessia de Dados Hierárquicos: Os CTEs podem ajudar a navegar em relacionamentos hierárquicos, como estruturas organizacionais, relacionamentos pai-filho ou qualquer modelo de dados que envolva níveis aninhados. Os CTEs recursivos são úteis para consultar dados hierárquicos, pois permitem atravessar os níveis de forma iterativa.
• Agregações em Múltiplos Níveis: Os CTEs podem ajudar a realizar agregações em múltiplos níveis, como calcular números de vendas em diferentes granularidades (por exemplo, por mês, trimestre e ano). Usar CTEs para separar essas etapas de agregação garante que cada nível seja calculado de forma independente e lógica.
• Combinando Dados de Múltiplas Tabelas: Múltiplos CTEs podem ser usados para combinar dados de diferentes tabelas, tornando a etapa final de combinação mais estruturada. Essa abordagem simplifica junções complexas e garante que os dados de origem estejam organizados de forma lógica para melhor legibilidade.

Técnicas Avançadas de CTE SQL

Os CTEs suportam técnicas avançadas de SQL, tornando-os versáteis e úteis para diferentes casos de uso. A seguir estão algumas das aplicações avançadas de CTEs.

Múltiplos CTEs em uma única consulta

Você pode definir múltiplos CTEs em uma única consulta, o que permite transformações e cálculos complexos. Esse método é útil quando um problema requer várias etapas de processamento de dados, onde cada CTE representa uma etapa distinta.

Suponha que temos dados de vendas em uma tabela chamada Sales e queremos calcular o total de vendas para cada produto, identificar produtos com vendas totais acima da média e classificar esses produtos com base em suas vendas totais.

WITH ProductSales AS (
    -- Etapa 1: Calcular o total de vendas para cada produto
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
),
AverageSales AS (
    -- Etapa 2: Calcular a média das vendas totais entre todos os produtos
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
),
HighSalesProducts AS (
    -- Etapa 3: Filtrar produtos com vendas totais acima da média
    SELECT ProductID, TotalSales
    FROM ProductSales
    WHERE TotalSales > (SELECT AverageTotalSales FROM AverageSales)
)
-- Etapa 4: Classificar os produtos com altas vendas
SELECT ProductID, TotalSales, RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
FROM HighSalesProducts;

No exemplo acima;

• O primeiro CTE (ProductSales) calcula o total de vendas por produto.

• O segundo CTE (AverageSales) calcula a média de vendas totais de todos os produtos.

• O terceiro CTE (HighSalesProducts) filtra os produtos cujas vendas totais excedem a média.

• A consulta final classifica esses produtos com base em suas vendas totais.

CTEs em instruções UPDATE, DELETE e MERGE

Ao serem incorporados nas operações UPDATE, DELETE e MERGE, os CTEs podem simplificar tarefas de manipulação de dados, especialmente ao lidar com filtros complexos ou dados hierárquicos.

Usando CTE com uma instrução UPDATE

Suponha que temos uma tabela Employees com uma coluna EmployeeSalary. Queremos dar um aumento de 10% a todos os funcionários que trabalham na empresa há mais de 5 anos.

-- Definir um CTE para encontrar funcionários contratados há mais de 5 anos
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5
)
-- Atualizar salários em 10% para funcionários de longo prazo identificados no CTE
UPDATE Employees
SET EmployeeSalary = EmployeeSalary * 1.1
WHERE EmployeeID IN (SELECT EmployeeID FROM LongTermEmployees);

O CTE LongTermEmployees identifica funcionários que trabalharam mais de cinco anos. A instrução UPDATE usa este CTE para aumentar seletivamente os salários.

Usando CTE com uma instrução DELETE

Agora, suponha que temos uma tabela chamada Produtos e queremos excluir todos os produtos que não foram vendidos nos últimos 2 anos. Podemos usar uma CTE para filtrar os produtos:

-- Definir uma CTE para identificar produtos não vendidos nos últimos 2 anos
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- Usar o DATEADD para encontrar produtos com uma data de última venda há mais de 2 anos
    WHERE LastSoldDate < DATEADD(YEAR, -2, GETDATE())
)
-- Excluir produtos identificados como antigos da tabela principal
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

A CTE ProdutosAntigos identifica produtos que não foram vendidos nos últimos dois anos e, em seguida, a instrução DELETE usa essa CTE para remover esses produtos.

Usando CTE com uma instrução MERGE

A instrução MERGE no SQL permite atualizações condicionais, inserções ou exclusões em uma tabela de destino com base em dados em uma tabela de origem. No exemplo a seguir, a CTE InventarioMesclado combina dados de inventário novos e existentes. A instrução MERGE então atualiza quantidades para produtos existentes ou insere novos produtos com base nos dados da CTE.

-- CTE para mesclar dados de inventário novos e existentes
WITH MergedInventory AS (
    SELECT ni.ProductID, ni.Quantity AS NewQuantity, i.Quantity AS CurrentQuantity
    FROM NewInventoryData ni
    -- Use LEFT JOIN para incluir todos os novos dados, mesmo que não estejam no inventário atual
    LEFT JOIN Inventory i ON ni.ProductID = i.ProductID
)
-- Mesclar os dados preparados na tabela de Inventário
MERGE INTO Inventory AS i
USING MergedInventory AS mi
ON i.ProductID = mi.ProductID
-- Atualizar produtos existentes com novas quantidades
WHEN MATCHED THEN
    UPDATE SET i.Quantity = mi.NewQuantity
-- Inserir novos produtos se eles não existirem no inventário
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, Quantity) VALUES (mi.ProductID, mi.NewQuantity);

Expressões Comuns de Tabela Recursiva (CTEs)

CTEs recursivas ajudam a realizar operações avançadas e repetidas.

Introdução às CTEs recursivas

CTEs recursivas são um tipo especial de CTE que se referencia em sua definição, permitindo que a consulta realize operações repetidas. Isso as torna ideais para trabalhar com dados hierárquicos ou em forma de árvore, como organogramas, estruturas de diretórios ou montagens de produtos. A CTE recursiva processa iterativamente os dados, retornando resultados passo a passo até que uma condição de término seja atendida.

Membros âncora e recursivos

Uma CTE recursiva consiste em duas partes principais:

• Membro Âncora: A parte que define a consulta base que inicia a recursão.
• Membro Recursivo: A parte que faz referência à CTE em si, permitindo que ela execute operações “recursivas”.

Suponha que temos uma tabela Employees, onde cada linha contém um EmployeeID, EmployeeName e ManagerID. Se quisermos encontrar todos os relatórios diretos e indiretos de um gerente específico, começamos com o membro âncora identificando o gerente de nível superior. O membro âncora começa com o funcionário com EmployeeID = 1.

O membro recursivo encontra funcionários cujo ManagerID corresponde ao EmployeeID da iteração anterior. Cada iteração recupera o próximo nível da hierarquia.

WITH EmployeeHierarchy AS (
    -- Membro âncora: selecionar o gerente de nível superior
    SELECT EmployeeID, EmployeeName, ManagerID, 1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- Começando com o gerente de nível superior
    UNION ALL
    -- Membro recursivo: encontrar funcionários que se reportam aos gerentes atuais
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, EmployeeName, Level
FROM EmployeeHierarchy;

Questões ou Limitações Potenciais de CTEs no SQL

Compreender as características e limitações de CTEs é importante para escrever consultas lógicas e legíveis. Vamos analisar algumas limitações e possíveis problemas ao usar CTEs em diferentes bancos de dados.

Limitações do SQL Server e Azure

Há algumas limitações específicas do ambiente para CTEs SQL ao trabalhar com o SQL Server ou Azure Synapse Analytics. Elas incluem o seguinte:

• SQL Server: O nível máximo de recursão padrão para CTEs recursivas é 100, o que pode ser modificado usando a dica OPTION (MAXRECURSION). Se esse limite for excedido sem ajuste, um erro ocorre. CTEs não podem ser aninhadas diretamente umas dentro das outras ou definidas dentro de outra CTE.

• Azure Synapse Analytics: As CTEs possuem suporte limitado para certas operações SQL como INSERT, UPDATE, DELETE e MERGE. Além disso, CTEs recursivas não são suportadas nos ambientes baseados em nuvem do Azure Synapse Analytics, restringindo a capacidade de realizar certas operações de dados hierárquicos.

Se você está trabalhando com o SQL Server, saiba que a DataCamp possui muitos recursos excelentes para ajudar. Para começar, recomendo fazer o curso Introdução ao SQL Server da DataCamp para dominar os conceitos básicos do SQL Server para análise de dados. Você pode experimentar nossa trilha de carreira SQL Server Developer, que abrange tudo, desde transações e tratamento de erros até análise de séries temporais. Nosso curso Consultas Hierárquicas e Recursivas no SQL Server vai direto ao ponto de como escrever consultas avançadas no SQL Server, incluindo métodos envolvendo CTEs.

Outras questões potenciais

Embora os CTEs sejam úteis para simplificar consultas complexas, existem algumas armadilhas comuns das quais você deve estar ciente. Elas incluem o seguinte:

• Laços Infinitos em CTEs Recursivos: Se a condição de término de um CTE recursivo não for atendida, pode resultar em um laço infinito, fazendo com que a consulta seja executada indefinidamente. Para evitar que o CTE recursivo seja executado infinitamente, use a dica OPTION (MAXRECURSION N) para limitar o número máximo de iterações recursivas, onde N é um limite especificado.

• Considerações de Desempenho: Os CTEs recursivos podem se tornar intensivos em recursos se a profundidade da recursão for alta ou se grandes conjuntos de dados estiverem sendo processados. Para otimizar o desempenho, limite os dados processados em cada iteração e garanta filtragem apropriada para evitar níveis excessivos de recursão.

Quando Usar CTEs vs. Outras Técnicas

Embora os CTEs sejam apropriados para simplificar consultas envolvendo tarefas repetidas, tabelas derivadas, views e tabelas temporárias também servem a propósitos similares. A tabela a seguir destaca as vantagens e desvantagens de cada método e quando usar cada um.

Conclusão

Dominar CTEs requer prática, como qualquer coisa: recomendo experimentar a trilha de carreira Associate Data Analyst em SQL da DataCamp para se tornar um analista de dados proficiente. O curso Reporting in SQL também irá ajudá-lo a se tornar proficiente na construção de relatórios complexos e painéis para uma apresentação eficaz de dados. Por fim, você deve obter a SQL Associate Certification para demonstrar sua maestria em utilizar SQL para resolver problemas de negócios e se destacar entre outros profissionais.