O que é um CTE Recursivo em SQL?

O artigo que mostrará exemplos práticos do uso de CTEs recursivas em SQL.

Se você já ouviu falar sobre os CTEs recursivos de SQL mas nunca os utilizou, este artigo é para você. É também para você se você nunca se cansa de exemplos de CTE recursivos.

Antes de nos aprofundarmos na recursividade, vou lhe lembrar o que são CTEs e qual é sua sintaxe. Depois farei o mesmo para os CTEs recursivos. Depois disso, mostrarei a você como os CTEs recursivos funcionam em três exemplos.

O que são CTEs?

O CTE (expressão comum de tabela), também conhecido como cláusula WITH, é um recurso SQL que retorna um conjunto de dados temporário que pode ser usado por outra consulta. Como é um resultado temporário, não é armazenado em nenhum lugar, mas ainda assim pode ser referenciado como se você referisse qualquer outra tabela.

Existem dois tipos de CTEs, não-recorrentes e recursivas.

Aqui está um belo artigo que lhe mostrará o que são CTEs e como eles funcionam.

Sintaxe não-recursiva dos CTE

A sintaxe geral de um CTE não-recorrente se parece com esta:

WITH cte_name AS (cte_query_definition)

SELECT *
FROM   cte_name;

A primeira parte da sintaxe é o CTE. Ela começa com a palavra-chave WITH. Em seguida, você dá um nome ao seu CTE. Depois de seguir essa palavra-chave AS, você pode definir o CTE entre parênteses.

A segunda parte da sintaxe é uma simples declaração SELECT. Ela é escrita imediatamente após o CTE recursivo, sem vírgulas, ponto-e-vírgula ou marcas similares. Como eu disse anteriormente, o CTE é usado em outra consulta como em qualquer outra tabela. Isto é exatamente o que a declaração SELECT faz.

Aqui está o artigo que pode adicionalmente ajudá-lo com a sintaxe do CTE e suas regras. E se você precisar de mais alguns exemplos do CTE, este artigo é para você.

Sintaxe Recursiva do CTE

Um CTE recursivo se refere a si mesmo. Ele retorna o subconjunto de resultados, depois repetidamente (recursivamente) se refere a si mesmo, e pára quando retorna todos os resultados.

A sintaxe de um CTE recursivo não é muito diferente da de um CTE não recursivo:

WITH RECURSIVE cte_name AS (
    cte_query_definition (the anchor member)

    UNION ALL

    cte_query_definition (the recursive member)
)


SELECT *
FROM   cte_name;

Novamente, no início do seu CTE está a cláusula WITH. Entretanto, se você quiser que seu CTE seja recursivo, então após WITH você escreve a palavra-chave RECURSIVE. Depois é business as usual: AS é seguido pelos parênteses com a definição da consulta do CTE. Esta primeira definição de consulta é chamada de membro âncora.

Para conectar o membro âncora com o membro recursivo, você precisa usar o comando UNION ou UNION ALL. O membro recursivo é, obviamente, a parte recursiva do CTE que fará referência ao próprio CTE. Você verá como ele funciona em um exemplo muito em breve.

Os CTEs recursivos são usados principalmente quando você deseja consultar dados hierárquicos ou gráficos. Isto pode ser a estrutura organizacional de uma empresa, uma árvore genealógica, um cardápio de restaurante ou várias rotas entre cidades. Veja estes artigos para entender como os CTEs funcionam com estruturas hierárquicas e como consultar os dados dos gráficos.

Agora que entendemos como funcionam os CTEs recursivos, vejamos alguns exemplos.

Exemplo 1 - Encontrar patrões e nível hierárquico para todos os funcionários

Para este problema, vou usar os dados da tabela employeesque tem as seguintes colunas:

• id: A identificação do funcionário.
• first_name: O primeiro nome do funcionário.
• last_name: Sobrenome do funcionário.
• boss_id: Identificação do chefe do funcionário.

Aqui está como os dados são:

Não é muito complicado. Por exemplo, o chefe de Domenic Leaver é o funcionário com a identificação de 5; isto é Hermie Comsty. O mesmo princípio funciona para todos os outros funcionários, exceto Roxanna Fairlie. Ela não tem patrão; há um valor NULL na coluna boss_id. Podemos concluir que Roxanna é a presidente ou proprietária da empresa.

Vamos agora escrever o CTE recursivo para listar todos os funcionários e seus patrões diretos.

WITH RECURSIVE company_hierarchy AS (
  SELECT	id,
    		first_name,
    		last_name,
    		boss_id,
		0 AS hierarchy_level
  FROM employees
  WHERE boss_id IS NULL

  UNION ALL 
  
  SELECT	e.id,
    		e.first_name,
    		e.last_name,
    		e.boss_id, 
		hierarchy_level + 1
  FROM employees e, company_hierarchy ch
  WHERE e.boss_id = ch.id
)

SELECT   ch.first_name AS employee_first_name,
	   ch.last_name AS employee_last_name,
	   e.first_name AS boss_first_name,
	   e.last_name AS boss_last_name,
	   hierarchy_level
FROM company_hierarchy ch
LEFT JOIN employees e
ON ch.boss_id = e.id
ORDER BY ch.hierarchy_level, ch.boss_id;

O que esta consulta faz? É uma consulta recursiva, portanto, começa com WITH RECURSIVE. O nome do CTE é company_hierarchy. Depois de AS, a definição do CTE está entre parênteses.

A primeira declaração SELECT seleciona todos os employee colunas da tabela onde a coluna boss_id é NULL. Em resumo, selecionará Roxanna Fairlie, pois somente ela tem um valor NULL nessa coluna. Ainda mais curto: estou iniciando a recorrência a partir do topo da estrutura organizacional. Há também uma coluna hierarchy_level com o valor 0. Isso significa que o nível do proprietário/presidente é 0 - eles estão no topo da hierarquia.

Usei o UNION ALL para conectar esta declaração SELECT com a segunda, ou seja, com o membro recursivo. No membro recursivo, estou selecionando todas as colunas da tabela employees e o CTE company_hierarchy onde a coluna boss_id é igual à coluna id. Observe a parte hierarchy_level + 1. Isto significa que, a cada repetição, o CTE adicionará 1 ao nível hierárquico anterior, e o fará até chegar ao final da hierarquia. Observe também que estou tratando este CTE como qualquer outra tabela. Para terminar de definir o CTE, basta fechar os parênteses.

Finalmente, há uma terceira declaração SELECT, fora do CTE. Ela seleciona as colunas que mostrarão os funcionários, os nomes de seus chefes e o nível hierárquico. Os dados são retirados do CTE e da tabela employees. Juntei esses dois com um LEFT JOIN, pois quero todos os dados do CTE - incluindo Roxanna Fairlie, que tem o valor NULL na coluna boss_id. O resultado será mostrado em ordem ascendente: primeiro pelo nível hierárquico, depois pelo ID do chefe. Veja como fica:

Roxanna Fairlie é a última chefe; você já sabia disso. Há dois funcionários no nível 1. Isto significa que Bobbe Blakeway e Hermie Comsty são os subordinados diretos de Roxanna Fairlie. No nível 2, há funcionários cujos chefes diretos são Bobbe Blakeway e Hermie Comsty. Há também um terceiro nível na hierarquia. Esses são funcionários cujo chefe imediato é Domenic Leaver.

Exemplo 2 - Encontrar o Valor do Investimento pelo Investidor

Neste exemplo, vou usar a tabela investment:

• id: O ID do investimento.
• investment_amount: O valor do investimento.

Os dados na tabela são parecidos com estes:

Estes são os valores das três opções de investimento possíveis. Eles serão considerados pelos três investidores, que dividirão o valor total do investimento em partes iguais. Sua tarefa é calcular o montante por investidor dependendo de seu número, ou seja, se um, dois, três ou nenhum investidor investe em cada investimento.

A consulta que resolve este problema é:

WITH RECURSIVE per_investor_amount AS (
	SELECT	0 AS investors_number,
			0.00 AS investment_amount,
			0.00 AS individual_amount
	UNION 

	SELECT	investors_number + 1,
			i.investment_amount,
			i.investment_amount / (investors_number + 1)
	FROM investment i, per_investor_amount pia
	WHERE investors_number << 3
)

SELECT *
FROM per_investor_amount
ORDER BY  investment_amount, investors_number;

Mais uma vez, o CTE começa com WITH RECURSIVE, seguido por seu nome e a definição da consulta. Desta vez, vou usar o membro âncora da consulta recursiva para criar alguns dados. As colunas são investors_number, investment_amount, e individual_amount. Este é o ponto a partir do qual quero que a recursividade comece (da mesma forma que no exemplo anterior, com hierarchy_level = 0).

Depois vem o UNION e o membro recursivo. Esta parte da consulta aumentará a coluna investors_number em um a cada recursividade. Ela fará isso para cada investment_amount. A terceira coluna calculará o valor desse investimento por investidor, dependendo do número de investidores participantes. A recursividade será feita para até três investidores (ou seja, até atingir a condição WHERE investors_number < 3).

Depois disso, vem a simples declaração SELECT que retornará todas as colunas do CTE. E aqui está o resultado:

Não é difícil de analisar. Se não houver investidores, o valor do investimento é zero, assim como o valor individual. Se o investimento for 5.322.146,00 e houver apenas um investidor, então o valor por investidor será 5.322.146,00. Se houver dois investidores no mesmo valor, cada um deles terá que pagar 2.661.073,00. Se os três investidores decidirem investir, cada um pagará 1.774.048,67. Os outros dois valores de investimento seguem o mesmo padrão, como você pode ver na tabela.

Exemplo 3 - Encontrando Rotas entre as Cidades

No terceiro exemplo, vou usar a tabela cities_routeque contém dados sobre as cidades holandesas:

• city_from: A cidade de partida.
• city_to: A cidade de destino.
• distance: A distância entre duas cidades, em quilômetros.

Use esta tabela para encontrar todas as rotas possíveis de Groningen a Haarlem, mostrando as cidades na rota e a distância total.

Aqui está a consulta para resolver este problema:

WITH RECURSIVE possible_route AS (
	SELECT	cr.city_to,
       		cr.city_from || '->' ||cr.city_to AS route,
       		cr.distance
      FROM cities_route cr
      WHERE cr.city_from = 'Groningen'

UNION ALL

SELECT 	cr.city_to,
       		pr.route || '->' || cr.city_to AS route,
        		CAST((pr.distance + cr.distance) AS DECIMAL(10, 2))
      FROM possible_route pr
INNER JOIN cities_route cr
      		ON cr.city_from = pr.city_to
)

SELECT 	pr.route,
		pr.distance
FROM possible_route pr
WHERE pr.city_to = 'Haarlem'
ORDER BY pr.distance;

Vamos ver o que esta consulta faz. A primeira declaração SELECT na definição do CTE selecionará as colunas da tabela cities_route onde a cidade de partida é Groningen. Note também que há uma nova coluna chamada rota, que vou usar para concatenar as cidades na rota.

A UNION ALL conecta isto com o membro recursivo. Esta declaração SELECT selecionará a cidade de chegada, concatenará as cidades na rota, e finalmente adicionará as distâncias entre estas cidades ao total da rota entre Groningen e Haarlem. Para conseguir tudo isso, juntei-me ao CTE com a tabela cities_route.

Depois vem a declaração SELECT que extrai dados do CTE. Ele selecionará a rota e a distância onde a cidade de chegada é Haarlem, com os dados sendo ordenados por distância em ordem ascendente.

O resultado da consulta é o seguinte:

Não é difícil entender esta tabela. Há duas rotas de Groningen para Haarlem. Elas incluem diferentes cidades no meio e têm 222,6 km e 246,5 km de comprimento, respectivamente.

Se você quiser continuar aprendendo, verifique como você pode usar um CTE recursivo em vez de uma longa consulta SQL. E depois de abordar esse assunto, divirta-se um pouco desenhando algo usando um CTE recursivo.

Continuar praticando CTEs recursivos

Estes três exemplos têm demonstrado as possibilidades de CTEs recursivas em SQL. Agora é hora de construir a partir do que você aprendeu aqui.

Provavelmente, a melhor opção é fazer um curso em nosso Consultas Recursivas. Ele oferece a você muitos exemplos, explicações e oportunidades de prática. O curso é parte da trilha do cursoSQL Avançado , onde você pode aprender sobre outros tópicos avançados de SQL como funções de janela, extensões GROUP BY, e consultas recursivas.

Divirta-se!