Organizar consultas com expressões de tabela comuns

Concluído

Ao trabalhar com consultas complexas, muitas vezes você precisa dividir a lógica em partes gerenciáveis ou referenciar a mesma subconsulta várias vezes. As CTEs (Expressões comuns de tabela) fornecem uma maneira de definir conjuntos de resultados nomeados temporários que existem somente durante uma única consulta, tornando seu código mais legível e mantenedível.

Entender a sintaxe de CTE

Uma Expressão de Tabela Comum é definida usando a WITH cláusula, seguida pelo nome CTE, uma lista de colunas opcional e uma consulta que define o conjunto de resultados. Em seguida, a CTE pode ser referenciada na instrução subsequente SELECT, INSERT, UPDATE ou DELETE.

WITH CTE_Name (Column1, Column2)
AS
(
    -- CTE query definition
    SELECT Column1, Column2
    FROM SomeTable
    WHERE SomeCondition = 'Value'
)
SELECT * FROM CTE_Name;

Os CTEs oferecem várias vantagens em relação a tabelas e subconsultas derivadas:

• Legibilidade aprimorada: consultas complexas tornam-se mais fáceis de entender quando divididas em seções lógicas nomeadas
• Capacidade de auto-referência: CTEs recursivos podem referenciar a si mesmos, habilitando o percurso de dados hierárquicos
• Várias referências: referenciar o mesmo CTE várias vezes na consulta externa sem redefini-la
• Design modular: criar consultas complexas de forma incremental definindo várias CTEs

Observação

Ctes são conjuntos de resultados temporários que existem somente durante a execução da consulta. Ao contrário das tabelas tradicionais, elas não persistem além do comando que as usa e não exigem limpeza explícita.

Criar CTEs não recursivos

CTEs não recursivos definem um conjunto de resultados com base em uma consulta simples que não faz referência a si mesma. Esse padrão é útil para simplificar junções complexas, dividir cálculos de várias etapas ou melhorar a organização de código.

O exemplo a seguir usa um CTE para calcular as métricas de vendas antes de ingressar com as informações do produto:

WITH SalesSummary AS
(
    SELECT 
        ProductID,
        SUM(OrderQty) AS TotalQuantity,
        SUM(LineTotal) AS TotalRevenue,
        COUNT(DISTINCT SalesOrderID) AS OrderCount
    FROM SalesLT.SalesOrderDetail
    GROUP BY ProductID
)
SELECT 
    p.Name AS ProductName,
    p.ProductNumber,
    p.ListPrice,
    ss.TotalQuantity,
    ss.TotalRevenue,
    ss.OrderCount,
    ss.TotalRevenue / NULLIF(ss.TotalQuantity, 0) AS AverageUnitPrice
FROM SalesLT.Product AS p
INNER JOIN SalesSummary AS ss
    ON p.ProductID = ss.ProductID
ORDER BY ss.TotalRevenue DESC;

Essa consulta primeiro cria um CTE chamado SalesSummary que agrega detalhes do pedido por produto, calculando a quantidade total vendida, a receita total e a contagem de pedidos. Em seguida, a consulta principal une esse CTE à tabela Product para exibir nomes de produtos junto com suas métricas de vendas. A NULLIF função impede a divisão por zero ao calcular o preço unitário médio.

Você pode definir vários CTEs em uma única cláusula WITH, separando-os com vírgulas. CTEs posteriores podem referenciar os anteriores, permitindo a progressiva transformação de dados:

WITH CategorySales AS
(
    SELECT 
        p.ProductCategoryID,
        SUM(sod.LineTotal) AS CategoryRevenue
    FROM SalesLT.Product AS p
    INNER JOIN SalesLT.SalesOrderDetail AS sod
        ON p.ProductID = sod.ProductID
    GROUP BY p.ProductCategoryID
),
RankedCategories AS
(
    SELECT 
        ProductCategoryID,
        CategoryRevenue,
        RANK() OVER (ORDER BY CategoryRevenue DESC) AS RevenueRank
    FROM CategorySales
)
SELECT 
    pc.Name AS CategoryName,
    rc.CategoryRevenue,
    rc.RevenueRank
FROM RankedCategories AS rc
INNER JOIN SalesLT.ProductCategory AS pc
    ON rc.ProductCategoryID = pc.ProductCategoryID
WHERE rc.RevenueRank <= 5;

Dica

Ao criar consultas com vários CTEs, comece com as transformações de dados mais granulares e agrege ou filtre progressivamente em CTEs subsequentes. Essa abordagem facilita a depuração, pois você pode testar cada CTE de forma independente.

Criar CTEs recursivos

CTEs recursivos fazem referência a estruturas de dados hierárquicas ou semelhantes a gráfico. Um CTE recursivo consiste em duas partes: um membro de âncora que fornece o conjunto de resultados inicial e um membro recursivo que se refere ao CTE para se basear nos resultados anteriores.

A sintaxe geral de um CTE recursivo é:

WITH RecursiveCTE AS
(
    -- Anchor member: starting point
    SELECT columns
    FROM table
    WHERE starting_condition
    
    UNION ALL
    
    -- Recursive member: references the CTE
    SELECT columns
    FROM table
    INNER JOIN RecursiveCTE
        ON join_condition
)
SELECT * FROM RecursiveCTE;

Um caso de uso comum é percorrer uma hierarquia organizacional. Considere uma tabela de funcionários em que cada funcionário tenha um gerente:

WITH EmployeeHierarchy AS
(
    -- Anchor: Start with top-level managers (no manager)
    SELECT 
        EmployeeID,
        FirstName,
        LastName,
        ManagerID,
        0 AS Level,
        CAST(FirstName + ' ' + LastName AS NVARCHAR(500)) AS HierarchyPath
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    -- Recursive: Find employees who report to previously found employees
    SELECT 
        e.EmployeeID,
        e.FirstName,
        e.LastName,
        e.ManagerID,
        eh.Level + 1,
        CAST(eh.HierarchyPath + ' > ' + e.FirstName + ' ' + e.LastName AS NVARCHAR(500))
    FROM HumanResources.Employee AS e
    INNER JOIN EmployeeHierarchy AS eh
        ON e.ManagerID = eh.EmployeeID
)
SELECT 
    EmployeeID,
    FirstName,
    LastName,
    Level,
    HierarchyPath
FROM EmployeeHierarchy
ORDER BY HierarchyPath;

Importante

CTEs recursivos podem causar loops infinitos se a condição de término nunca for atendida. O SQL Server limita a recursão a 100 níveis por padrão. Use OPTION (MAXRECURSION n) para alterar esse limite, onde n está a profundidade máxima de recursão (0 para ilimitado).

Gerar sequências com CTEs recursivos

Os CTEs recursivos se destacam na geração de sequências de números ou datas sem a necessidade de uma tabela de números físicos. Essa técnica é útil para criar intervalos de datas, preencher lacunas em dados ou gerar dados de teste:

-- Generate a sequence of dates for the current month
WITH DateSequence AS
(
    -- Anchor: Get the first day of the current month
    SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS DATE) AS DateValue
    
    UNION ALL
    
    -- Recursive: Add one day until we reach the end of the month
    SELECT DATEADD(DAY, 1, DateValue)
    FROM DateSequence
    WHERE DateValue < EOMONTH(GETDATE())
)
-- Output each date with its day name
SELECT DateValue, DATENAME(WEEKDAY, DateValue) AS DayName
FROM DateSequence
OPTION (MAXRECURSION 31);  -- Allow up to 31 iterations (max days in a month)

Você pode combinar sequências geradas com dados reais para identificar lacunas ou criar relatórios de resumo:

-- Generate a numbers table from 1 to 1000
WITH Numbers AS
(
    -- Anchor: Start with 1
    SELECT 1 AS n
    UNION ALL
    -- Recursive: Increment until we reach 1000
    SELECT n + 1 FROM Numbers WHERE n < 1000
),
-- Convert numbers to dates for the entire year
DateRange AS
(
    SELECT DATEADD(DAY, n - 1, '2024-01-01') AS OrderDate
    FROM Numbers
    WHERE DATEADD(DAY, n - 1, '2024-01-01') <= '2024-12-31'
)
-- Count orders for each date, showing 0 for dates with no orders
SELECT 
    dr.OrderDate,
    COALESCE(COUNT(soh.SalesOrderID), 0) AS OrderCount
FROM DateRange AS dr
LEFT JOIN SalesLT.SalesOrderHeader AS soh
    ON CAST(soh.OrderDate AS DATE) = dr.OrderDate
GROUP BY dr.OrderDate
ORDER BY dr.OrderDate
OPTION (MAXRECURSION 366);  -- Allow up to 366 iterations (leap year)

Usar CTEs com instruções de modificação de dados

CTEs podem ser usados com instruções INSERT, UPDATE e DELETE, e não apenas com SELECT. Essa funcionalidade é útil quando a lógica de modificação requer filtragem ou cálculos complexos:

-- Update using a CTE to identify target rows
WITH DiscontinuedProducts AS
(
    SELECT ProductID
    FROM SalesLT.Product
    WHERE SellEndDate < DATEADD(YEAR, -2, GETDATE())
        AND ProductID NOT IN (
            SELECT DISTINCT ProductID 
            FROM SalesLT.SalesOrderDetail
            WHERE ModifiedDate > DATEADD(YEAR, -1, GETDATE())
        )
)
UPDATE SalesLT.Product
SET DiscontinuedDate = GETDATE()
WHERE ProductID IN (SELECT ProductID FROM DiscontinuedProducts);

Essa consulta usa um CTE para identificar produtos que devem ser marcados como descontinuados. O CTE encontra produtos cuja data de término de venda é superior a dois anos atrás e que não apareceram em nenhum dos detalhes de pedido modificados no último ano. Em seguida, a instrução UPDATE define o DiscontinuedDate para esses produtos. Ao separar a lógica de seleção em um CTE, a consulta fica mais fácil de ler e testar de forma independente.

Para obter mais informações sobre expressões comuns de tabela, consulte WITH common_table_expression (Transact-SQL) e consultas recursivas usando expressões de tabela comuns.