Como escrever um CTE recursivo no SQL Server

Um guia para entender e usar CTEs recursivos no SQL Server

O SQL Server oferece muitas ferramentas poderosas para trabalhar com dados, incluindo CTEs (Common Table Expressions). Um CTE é um conjunto de resultados nomeado temporário que você pode referenciar em uma instrução SELECT, INSERT, UPDATE ou DELETE. Os CTEs podem dividir consultas longas em partes menores e mais gerenciáveis de lógica, tornando-as mais legíveis.

O SQL Server oferece expressões de tabela comum recursivas. Uma CTE (Common Table Expression, expressão de tabela comum) recursiva no SQL Server permite que você execute consultas recursivas em estruturas de dados hierárquicas ou baseadas em gráficos, como organogramas, árvores genealógicas, redes de transporte etc. As consultas recursivas são usadas para percorrer as relações entre os elementos de dados.

Neste artigo, começaremos com a sintaxe padrão de CTE e, em seguida, exploraremos como escrever CTEs recursivos no SQL Server.

Uma ótima maneira de aprofundar seu conhecimento sobre CTEs recursivos é fazer nosso curso sobre Consultas Recursivas no MS SQL Server. Ele o ensinará a dominar o tipo de consulta mais desafiador e a organizar com eficácia consultas SQL longas.

Noções básicas de CTEs no SQL Server

Primeiro, vamos revisar os conceitos básicos de CTEs. Um CTE começa com a cláusula WITH, seguida do nome do CTE e da instrução SELECT que o define. A sintaxe de um CTE é a seguinte:

WITH cte_name AS (
   SELECT 
     column1,
     column2,
     …
   FROM table
   … 
) 
SELECT … 
FROM cte_name, …;

Vamos começar pelo topo. A primeira coisa que estamos fazendo é dar ao nosso CTE um nome, cte_name. Esse CTE seleciona algumas colunas (column1, column2, ...) de table. O restante da consulta (a consulta externa) pode se referir ao CTE e suas colunas como se o CTE fosse uma tabela normal.

Você pode pensar em um CTE como uma visualização temporária que pode ser referenciada na consulta externa, como estamos fazendo no exemplo acima.

Digamos que você queira escrever uma consulta que retorne as compras gerenciadas pelos cinco funcionários com o salário mais alto no departamento de vendas. As tabelas que usaremos neste exemplo são chamadas employees (inclui as colunas employee_id, employee_name, e salary) e purchase (contém as colunas id, date, customer_id, e total_amount).

WITH top_5_sales_employees AS (
   SELECT TOP 5
     employee_id,
     employee_name 
   FROM employees
   WHERE department_id = ‘Sales’ 
   ORDER BY salary DESC
) 
SELECT 
  p.id AS purchase_id,
  p.date,
  p.total_amount,
  e.id AS employee_id
  e.employee_name 
FROM top_5_sales_employees e
JOIN purchase p
ON p.employee_id = e.id;

Esse CTE começa com a palavra-chave WITH e o nome do CTE, top_5_sales_employees. Entre parênteses, selecionamos as colunas que queremos incluir no CTE e especificamos as condições da consulta na cláusula WHERE. Por fim, usamos a instrução SELECT para selecionar as colunas apropriadas do CTE como se fosse uma tabela normal.

Para saber mais sobre o que são CTEs e como escrevê-los, consulte nosso artigo O que é um CTE no SQL Server? Se estiver procurando por mais exemplos de CTE, você os encontrará neste artigo.

Como usar CTEs recursivos no SQL Server

Então, o que é um CTE recursivo no SQL Server? Um CTE recursivo é um tipo de CTE que faz referência a si mesmo na instrução SELECT, criando um loop. Os CTEs recursivos são usados para percorrer estruturas de dados hierárquicas, como organogramas ou redes.

Digamos que tenhamos uma tabela chamada employees com colunas para o nome, o departamento e o gerente do funcionário. Isso é mostrado na tabela abaixo, onde cada registro inclui um funcionário e a quem ele se reporta dentro da organização.

Em uma olhada rápida, é muito fácil ver quem se reporta a quem e como é essa hierarquia organizacional. Entretanto, se tivéssemos centenas de funcionários, seria muito mais difícil entender os dados.

Podemos usar um CTE recursivo para gerar uma árvore hierárquica dos funcionários da empresa. Para fazer isso, executaríamos esta consulta:

WITH employee_manager_cte AS (
  SELECT 
    id, 
    name,
    department,
    manager_id,
    manager_name,
    1 AS level 
  FROM employees 
  WHERE manager_id IS NULL 
  UNION ALL 
  SELECT 
    e.id, 
    e.name,
    e.department,
    e.manager_id,
    e.manager_name,
    level + 1 
  FROM employees e 
  INNER JOIN employee_manager_cte r 
	ON e.manager_id = r.id 
) 
SELECT * 
FROM employee_manager_cte;

Vamos detalhar essa consulta passo a passo.

Primeiro, definimos o CTE recursivo com o nome employee_manager_cte. Selecionamos as colunas que queremos incluir na consulta: id, name, department, manager_id, manager_name, e level. A coluna level é usada para rastrear a profundidade da árvore. Começaremos com o nível 1; à medida que avançarmos no loop, esse número aumentará.

Essa seção antes de UNION ALL é chamada de membro âncora. No membro de ancoragem, iniciamos nosso loop. Em nosso exemplo, selecionamos todos os funcionários cujo gerente é NULL. Em nosso organograma, esses serão os funcionários que estão no topo. Nesse caso, há apenas um funcionário nesse nível: Sarah Smith, a CEO.

A parte após o UNION ALL é chamada de membro recursivo. No membro recursivo, adicionamos novas linhas às linhas que já foram computadas. Em nosso exemplo, juntamos a tabela employees com o CTE employee_manager_cte na coluna manager_id. Isso cria um loop que percorre a árvore de cima para baixo. Adicionamos 1 à coluna level para rastrear a profundidade de cada nó.

Por fim, selecionamos todas as colunas do CTE employee_manager_cte.

Quando você executa essa consulta, o SQL Server primeiro processa o membro âncora, que seleciona Sarah Smith como a raiz da árvore. Em seguida, ele processa o membro recursivo, que une Sarah Smith com seu relatório direto (Jane Miller). Em seguida, ele junta Jane Miller com seu subordinado direto (John Doe) e John Doe com seu subordinado direto (nenhum). Como não há mais linhas para adicionar ao conjunto de resultados, o SQL Server interrompe o processamento do CTE e retorna o resultado final.

Esta é a aparência do conjunto de resultados:

Outro ótimo artigo explicativo que você pode conferir é What Is a Recursive CTE in SQL? Lá você encontrará mais exemplos práticos de CTEs recursivos em SQL.

Dicas para escrever CTE recursivo no SQL Server

1. Comece com o membro âncora

O membro âncora é o ponto de partida do CTE recursivo. É a parte da consulta que define o caso base ou o primeiro conjunto de linhas que será retornado. Em nosso exemplo de organograma, esse é o nível mais alto de gerenciamento. Certifique-se de que o membro âncora retorne todas as colunas de que você precisa no conjunto de resultados final.

2. Certifique-se de que o número de colunas seja igual

O membro recursivo e os membros âncora devem ter o mesmo número de colunas e os mesmos tipos de dados correspondentes: Em um CTE recursivo, o membro recursivo faz referência ao próprio CTE, portanto, é importante garantir que o membro recursivo e o membro âncora tenham o mesmo número de colunas e os mesmos tipos de dados nas colunas correspondentes.

3. Sempre UNION ALL

No SQL Server, você só pode usar UNION ALL para combinar um membro âncora e um membro recursivo: Ao combinar o membro âncora e o membro recursivo, você deve usar UNION ALL, não UNION. O UNION ALL preserva todas as linhas, inclusive as duplicadas, enquanto o UNION remove as duplicadas. UNION não é permitido em consultas recursivas no SQL Server.

4. Cuidado com os loops infinitos!

Certifique-se de escrever uma verificação de término em sua consulta. Uma verificação de término é uma condição que impede que o CTE recursivo faça um loop indefinido. Sem uma verificação de término, a consulta executará, por padrão, um máximo de 100 recursões e, em seguida, apresentará erro.

A verificação de término é normalmente incluída na cláusula WHERE do membro recursivo e especifica quando a recursão deve parar.

Seguindo essas dicas, você poderá escrever CTEs recursivos eficientes e eficazes que o ajudarão a resolver problemas hierárquicos complexos no SQL Server. Não deixe de conferir este artigo sobre as práticas recomendadas de CTE para obter mais detalhes.

CTEs recursivos no SQL Server - Mais exemplos

Exemplo 1: CTE recursivo para uma rede de transporte

Outro exemplo de quando você pode usar um CTE recursivo é para mostrar uma rede de transporte. Suponha que tenhamos uma tabela chamada routes que armazena informações sobre rotas de transporte entre cidades. Ela inclui a cidade de origem, a cidade de destino e a distância entre as cidades. Queremos escrever uma consulta que retorne todas as cidades que podem ser alcançadas a partir de uma determinada cidade de origem, juntamente com a distância total até cada cidade.

Nossos dados estão em uma tabela chamada routes:

Aqui está a consulta:

WITH recursive_cte AS (
  SELECT 
    source_city, 
    destination_city, 
    distance,
    source_city AS visited_cities 
   FROM routes 
   WHERE source_city = ‘New York’
   
   UNION ALL 
   
   SELECT 
     r.source_city, 
     r.destination_city, 
     r.distance + rc.distance,
     rc.visited_cities + ‘,’ + r.destination_city
   FROM routes r 
   INNER JOIN recursive_cte rc 
	ON r.source_city = rc.destination_city 
   WHERE rc.distance < 2000
AND CHARINDEX(',' + r.destination_city + ',', ',' +                                 rc.visited_cities + ',') = 0
) 
SELECT 
  destination_city, 
  distance
FROM recursive_cte

Esse CTE recursivo começa com o membro âncora, que seleciona todas as rotas que começam em Nova York. No membro recursivo, juntamos a tabela de rotas com o CTE recursive_cte na coluna source_city para encontrar todas as cidades acessíveis a partir de Nova York.

Adicionamos uma nova coluna chamada visited_cities que armazena a lista de cidades visitadas como uma cadeia de caracteres separada por vírgulas. Inicializamos essa coluna no membro âncora do CTE, definindo-a como a cidade de origem. No membro recursivo, concatenamos a cidade atual com a lista de cidades visitadas e verificamos se a cidade de destino já foi visitada usando a função CHARINDEX. Se a cidade de destino não tiver sido visitada, nós a adicionamos à lista de cidades visitadas e continuamos a recursão

Continuamos adicionando linhas ao conjunto de resultados até que não haja mais cidades a serem adicionadas ou até que a verificação de término seja atingida. O conjunto de resultados mostra todas as cidades que podem ser alcançadas a partir de Nova York e a distância total até cada cidade.

Exemplo 2: uso de CTE recursivo para dependências de tarefas em um projeto

Outro cenário em que poderíamos usar um CTE recursivo seria para entender as dependências de tarefas de um projeto.

Suponhamos que tenhamos uma tabela chamada tasks que armazena informações sobre tarefas em um projeto, incluindo task_id, task_name, o ID da tarefa da qual ela depende (depends_on_task_id) e time_required para concluir a tarefa. Queremos escrever uma consulta que calcule o tempo total necessário para concluir uma determinada tarefa, incluindo todas as suas tarefas dependentes.

Os dados são mostrados abaixo:

Vamos escrever uma consulta que extraia o tempo total necessário para a tarefa de desenvolvimento e suas dependências.

WITH recursive_cte AS (
  SELECT 
    task_id, 
    task_name, 
    depends_on_task_id, 
    time_required
  FROM tasks 
  WHERE task_id = 2
  
  UNION ALL 

  SELECT 
    t.task_id, 
    t.task_name, 
    depends_on_task_id, 
    t.time_required + rc.time_required
  FROM tasks t 
  INNER JOIN recursive_cte rc 
	ON t.depends_on_task_id = rc.task_id
WHERE rc.total_time < 20) 
SELECT 
  task_name, 
  time_required as total_time
FROM recursive_cte
GROUP BY task_name;

Esse CTE recursivo começa com o membro âncora, que seleciona a linha na tabela tasks com task_id = 2 (Desenvolvimento). No membro recursivo, juntamos a tabela tasks com o CTE recursive_cte nas colunas depends_on_task_id e task_id para encontrar todas as tarefas que dependem de Development. Calculamos o tempo total necessário para cada tarefa adicionando o tempo necessário para a tarefa atual ao tempo total necessário para suas tarefas anteriores.

Por fim, consultamos os resultados por task_name e time_required para cada tarefa. Aqui estão os resultados:

A coluna task_name mostra o nome de cada tarefa e a coluna total_time mostra o tempo total necessário para concluir a tarefa e todas as suas tarefas anteriores. Por exemplo, a primeira linha mostra que o tempo total necessário para concluir o Desenvolvimento e todas as suas dependências é 15, que é a soma do tempo necessário para o Desenvolvimento (10) e o Teste (5).

As outras linhas mostram o mesmo conceito, com as colunas task_name e total_time indicando o nome de cada tarefa e seu tempo total necessário.

Saiba mais sobre CTEs recursivos no SQL Server

Os CTEs recursivos no SQL Server podem ser úteis em uma variedade de cenários, tais como

• Hierarquias de funcionários: Um CTE recursivo pode ser usado para percorrer uma árvore de funcionários e seus gerentes para calcular a remuneração total de todos os funcionários em um determinado ramo.
• Categorias de produtos: Um CTE recursivo pode ser usado para percorrer uma árvore de categorias de produtos para encontrar todos os produtos em uma determinada categoria e suas subcategorias.
• Redes sociais: Um CTE recursivo pode ser usado para percorrer um gráfico de conexões de redes sociais para encontrar todos os amigos de um determinado usuário e suas conexões.
• Dependências de tarefas: Um CTE recursivo pode ser usado para percorrer uma árvore de dependências de tarefas para calcular o tempo necessário para concluir um projeto.

Em geral, qualquer cenário em que os dados estejam organizados hierarquicamente ou em uma estrutura gráfica pode se beneficiar do uso de um CTE recursivo. Seguindo as práticas recomendadas, como começar com o membro âncora, garantir que os membros recursivos e âncora tenham o mesmo número de colunas e tipos de dados e escrever uma verificação de término, você pode escrever consultas eficientes e precisas que atravessam estruturas de dados hierárquicas e resolvem problemas complexos.

Reforce seu conhecimento sobre CTEs recursivos fazendo nosso curso Consultas Recursivas no MS SQL Server. Seus 112 exercícios interativos o ajudarão a se tornar um mestre nesse tópico avançado!