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 (column1, column2, …): 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: Referencia o CTE pelo seu nome, usando-o como uma tabela.

Vamos ver o seguinte exemplo de criação de um CTE usando uma abordagem em camadas. Vamos assumir que temos uma tabela Employees, 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

Usar 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

Finalmente, referir-se 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
)
-- Usar o CTE para selecionar funcionários com altos salários
SELECT EmployeeID, FirstName, LastName
FROM HighEarningEmployees;

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

Por que CTEs em SQL são Úteis

Do exemplo acima, você pode estar se perguntando por que usamos CTEs quando até 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 parece 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
-- Junção para obter tabela 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 outros usos.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 da consulta pode ser representada por seu próprio CTE, tornando a consulta inteira mais fácil de seguir.
• Travessia de Dados Hierárquicos: Os CTEs podem ajudar na navegação de 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 percorrer os níveis de forma iterativa.
• Agregações de Vários Níveis: Os CTEs podem ajudar a realizar agregações em vários 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.
• Combinação de Dados de Múltiplas Tabelas: Vários 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 uma legibilidade aprimorada.

Técnicas Avançadas de CTE no 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 vários CTEs em uma única consulta, o que permite transformações e cálculos complexos. Este método é útil quando um problema requer múltiplos estágios de processamento de dados, onde cada CTE representa um estágio distinto.

Suponha que tenhamos dados de vendas em uma tabela chamada Vendas e desejamos calcular as vendas totais 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 (
    -- Passo 1: Calcular as vendas totais para cada produto
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
),
AverageSales AS (
    -- Passo 2: Calcular a média das vendas totais de todos os produtos
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
),
HighSalesProducts AS (
    -- Passo 3: Filtrar produtos com vendas totais acima da média
    SELECT ProductID, TotalSales
    FROM ProductSales
    WHERE TotalSales > (SELECT AverageTotalSales FROM AverageSales)
)
-- Passo 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 (VendasProduto) calcula as vendas totais por produto.

• O segundo CTE (AverageSales) calcula a média das 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 em operações UPDATE, DELETE e MERGE, 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. Desejamos dar um aumento de 10% a todos os funcionários que trabalham na empresa há mais de 5 anos.

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

A CTE LongTermEmployees identifica funcionários que trabalham há mais de cinco anos. A instrução UPDATE utiliza essa CTE para aumentar seletivamente os salários.

Usando CTE com uma instrução DELETE

Agora, vamos supor 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 DATEADD para encontrar produtos com uma data de última venda há mais de 2 anos atrás
    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 em SQL permite atualizações condicionais, inserções ou exclusões em uma tabela de destino com base em dados de 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)

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

Introdução às CTEs recursivas

As CTEs recursivas são um tipo especial de CTE que faz referência a si mesma dentro de sua definição, permitindo que a consulta execute 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 os dados de forma iterativa, 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 realize 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 para 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 os 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;

Potenciais Problemas ou Limitações de CTEs em SQL

Compreender as funcionalidades 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 em SQL ao trabalhar com 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 é de 100, o qual 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 uma na outra ou definidas dentro de outra CTE.

• Azure Synapse Analytics: As CTEs têm 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 executar certas operações de dados hierárquicos.

Se você estiver trabalhando com o SQL Server, saiba que a DataCamp tem 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 a trilha de carreira Desenvolvedor SQL Server, que abrange 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: CTEs Recursivas podem se tornar intensivas 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 CTEs sejam apropriadas para simplificar consultas envolvendo tarefas repetidas, tabelas derivadas, visualizações e tabelas temporárias também servem a propósitos semelhantes. 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 in 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 mostrar sua maestria em usar SQL para resolver problemas de negócios e se destacar entre outros profissionais.