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 (
    -- Query that defines the CTE
    SELECT ...
    FROM ...
    WHERE ...
)
-- Main query
SELECT ...
FROM cte_name;

Onde:

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

• cte_name: O nome é atribuído ao CTE para fazer referência a ele 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 os dados e forma o conjunto de resultados temporários.

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

Vejamos o seguinte exemplo de criação de um CTE usando uma abordagem em camadas. Suponhamos que você tenha uma tabela Employees e queira criar um CTE que selecione os funcionários com salário acima de US$ 50.000.

Etapa 1: Escreva a consulta básica

Começamos escrevendo a consulta básica SELECT:

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

Etapa 2: Envolva a consulta usando a palavra-chave WITH para criar um CTE

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

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

Etapa 3: Use 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.

-- Define a Common Table Expression (CTE)
WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)
-- Use the CTE to select high-earning employees
SELECT EmployeeID, FirstName, LastName
FROM HighEarningEmployees;

Para resumir as etapas acima, usamos 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 site HighEarningEmployees para exibir as colunas especificadas EmployeeID, FirstName e LastName.

Por que os CTEs do SQL são úteis

Com base no exemplo acima, você pode se perguntar por que usamos CTEs quando até mesmo consultas simples produzem os mesmos resultados. A seguir, você encontrará os motivos:

Simplificar consultas complexas

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

Suponhamos que você tenha três tabelas: Orders, Customers, e Products. Queremos descobrir a receita total gerada por cada cliente que comprou em 2024. Quando escrevemos a consulta sem usar o CTE, ela parece desordenada e difícil de ler e entender.

-- Select customer names and total revenue from their orders
SELECT c.CustomerName, SUM(p.Price * o.Quantity) AS TotalRevenue
FROM Orders o
-- Join to get customer and products table
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:

-- Define the 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
)
--Main query
SELECT CustomerName, SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

Reutilização de código

Os CTEs ajudam a evitar a duplicação, permitindo que o mesmo conjunto de resultados seja reutilizado em diferentes partes de uma consulta. Se vários cálculos ou operações forem baseados no mesmo conjunto de dados, você poderá defini-lo uma vez em um CTE e consultá-lo conforme necessário.

Suponha que precisemos calcular a média e o total de vendas de 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.

-- Define a CTE to calculate total and average sales for each category
WITH CategorySales AS (
    SELECT Category, SUM(SalesAmount) AS TotalSales, AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- Select category, total sales, and average sales from the CTE
SELECT Category, TotalSales, AverageSales
FROM CategorySales
WHERE TotalSales > 5000;

Outros aplicativos

Além de simplificar as consultas e a reutilização do código, os CTEs também têm outros usos. Não tenho condições de cobrir detalhadamente todos os usos possíveis dos CTEs. Nosso curso Manipulação de dados em SQL é uma ótima opção se você quiser continuar praticando. No entanto, documentarei alguns dos outros motivos principais aqui:

• Organização de consultas e legibilidade: Os CTEs melhoram a legibilidade do código SQL, dividindo as consultas em etapas lógicas e sequenciais. Cada etapa do processo de consulta pode ser representada por seu próprio CTE, o que facilita o acompanhamento de toda a consulta.
• Passagem 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 a consulta de dados hierárquicos porque permitem que você percorra os níveis iterativamente.
• Agregações em vários níveis: Os CTEs podem ajudar a realizar agregações em vários níveis, como o cálculo de números de vendas em diferentes granularidades (por exemplo, por mês, trimestre e ano). O uso de 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 várias 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 as uniões complexas e garante que os dados de origem sejam organizados logicamente para melhorar a legibilidade.

Técnicas avançadas de SQL CTE

Os CTEs suportam técnicas avançadas de SQL, o que os torna versáteis e úteis para diferentes casos de uso. A seguir, você encontrará alguns dos aplicativos avançados dos CTEs.

Vários CTEs em uma única consulta

Você pode definir vários CTEs em uma única consulta, o que permite transformações e cálculos complexos. Esse método é útil quando um problema exige vários estágios de processamento de dados, em que cada CTE representa um estágio distinto.

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

WITH ProductSales AS (
    -- Step 1: Calculate total sales for each product
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
),
AverageSales AS (
    -- Step 2: Calculate the average total sales across all products
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
),
HighSalesProducts AS (
    -- Step 3: Filter products with above-average total sales
    SELECT ProductID, TotalSales
    FROM ProductSales
    WHERE TotalSales > (SELECT AverageTotalSales FROM AverageSales)
)
-- Step 4: Rank the high-sales products
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 o total médio de vendas 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

Quando incorporados às operações UPDATE, DELETE e MERGE, os CTEs podem simplificar as tarefas de manipulação de dados, especialmente quando você lida com filtros complexos ou dados hierárquicos.

Usando CTE com uma instrução UPDATE

Suponha que você tenha 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.

-- Define a CTE to find employees hired more than 5 years ago
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5
)
-- Update salaries by 10% for long-term employees identified in the CTE
UPDATE Employees
SET EmployeeSalary = EmployeeSalary * 1.1
WHERE EmployeeID IN (SELECT EmployeeID FROM LongTermEmployees);

O CTE LongTermEmployees identifica os funcionários que trabalham há mais de cinco anos. A declaração UPDATE usa esse CTE para aumentar seletivamente os salários.

Usando CTE com uma instrução DELETE

Agora, suponha que você tenha uma tabela chamada Products e queira excluir todos os produtos que não foram vendidos nos últimos dois anos. Podemos usar um CTE para filtrar os produtos:

-- Define a CTE to identify products not sold in the last 2 years
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- Use DATEADD to find products with a LastSoldDate more than 2 years ago
    WHERE LastSoldDate < DATEADD(YEAR, -2, GETDATE())
)
-- Delete products identified as old from the main table
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

O CTE OldProducts identifica os produtos que não foram vendidos nos últimos dois anos e, em seguida, a declaração DELETE usa esse CTE para remover esses produtos.

Usando CTE com uma instrução MERGE

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

-- CTE to merge new and existing inventory data
WITH MergedInventory AS (
    SELECT ni.ProductID, ni.Quantity AS NewQuantity, i.Quantity AS CurrentQuantity
    FROM NewInventoryData ni
    -- Use LEFT JOIN to include all new data, even if not in current inventory
    LEFT JOIN Inventory i ON ni.ProductID = i.ProductID
)
-- Merge the prepared data into the Inventory table
MERGE INTO Inventory AS i
USING MergedInventory AS mi
ON i.ProductID = mi.ProductID
-- Update existing products with new quantities
WHEN MATCHED THEN
    UPDATE SET i.Quantity = mi.NewQuantity
-- Insert new products if they don't exist in the inventory
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, Quantity) VALUES (mi.ProductID, mi.NewQuantity);

Expressões de tabela comum (CTEs) recursivas

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

Introdução aos CTEs recursivos

Os CTEs recursivos são um tipo especial de CTE que faz referência a si mesmo em sua definição, permitindo que a consulta realize operações repetidas. Isso os torna ideais para trabalhar com dados hierárquicos ou estruturados em árvore, como organogramas, estruturas de diretório ou conjuntos de produtos. O CTE recursivo processa os dados de forma iterativa, retornando os resultados passo a passo até que uma condição de encerramento seja atendida.

Membros âncora e recursivos

Um CTE recursivo consiste em duas partes principais:

• Membro da âncora: A parte que define a consulta base que inicia a recursão.
• Membro recursivo: A parte que faz referência ao próprio CTE, permitindo que ele execute as operações "recursivas".

Suponha que tenhamos uma tabela Employees, em que 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çaremos com o membro âncora que identifica 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 (
    -- Anchor member: select the top-level manager
    SELECT EmployeeID, EmployeeName, ManagerID, 1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- Starting with the top-level manager
    UNION ALL
    -- Recursive member: find employees who report to the current managers
    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;

Problemas ou limitações potenciais dos CTEs no SQL

Compreender os recursos e as limitações dos CTEs é importante para que você escreva consultas lógicas e legíveis. Vamos examinar algumas limitações e possíveis problemas do uso de CTEs em diferentes bancos de dados.

Limitações do SQL Server e do Azure

Há algumas limitações específicas do ambiente para CTEs do SQL quando você trabalha com o SQL Server ou o Azure Synapse Analytics. Eles incluem o seguinte:

• SQL Server: O nível máximo de recursão padrão para CTEs recursivos é 100, que pode ser modificado usando a dica OPTION (MAXRECURSION). Se esse limite for excedido sem ajuste, ocorrerá um erro. Os CTEs não podem ser aninhados diretamente uns dentro dos outros ou definidos dentro de outro CTE.

• Azure Synapse Analytics: Os CTEs têm suporte limitado para determinadas operações SQL, como INSERT, UPDATE, DELETE e MERGE. Além disso, os CTEs recursivos não são compatíveis com os ambientes baseados na nuvem do Azure Synapse Analytics, o que restringe a capacidade de executar determinadas 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 que você faça 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 nosso curso de carreira de Desenvolvedor do SQL Server, que abrange tudo, desde transações e tratamento de erros até análise de séries temporais. Em nosso curso de Consultas hierárquicas e recursivas no SQL Server, você aprenderá a escrever consultas avançadas no SQL Server, incluindo métodos que envolvem CTEs.

Outros possíveis problemas

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

• Loops infinitos em CTEs recursivos: Se a condição de encerramento de um CTE recursivo não for atendida, isso poderá resultar em um loop 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, em que N é um limite especificado.

• Considerações sobre o desempenho: Os CTEs recursivos podem consumir muitos 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 a filtragem adequada para evitar níveis excessivos de recursão.

Quando usar CTEs em vez de CTEs. Outras técnicas

Embora os CTEs sejam apropriados para simplificar as consultas que envolvem tarefas repetidas, as tabelas derivadas, as exibições e as tabelas temporárias também têm finalidades semelhantes. A tabela a seguir destaca as vantagens e desvantagens de cada método e quando você deve usar cada um deles.

Conclusão

Dominar os CTEs requer prática, como qualquer outra coisa: Recomendo que você experimente o curso de carreira Associate Data Analyst in SQL da DataCamp para se tornar um analista de dados proficiente. O curso Reporting in SQL também ajudará você a se tornar proficiente na criação de relatórios e painéis complexos para a apresentação eficaz de dados. Por fim, você deve obter a Certificação SQL Associate para demonstrar seu domínio no uso do SQL para resolver problemas de negócios e se destacar entre outros profissionais.