Trabalhando com dados hierárquicos – Parte III

Saudações! No capítulo anterior desta série, falei de solicitações comuns relacionadas a dados hierárquicos utilizando o método iterativo. Hoje tratarei das mesmas solicitações através de CTEs recursivas.

CTEs recursivas têm uma estrutura um pouco diferente das outras CTEs. De acordo com o BOL [1], uma CTE recursiva é constituída por três elementos:

1. Chamada da rotina
A primeira chamada da rotina é quando o membro âncora é executado. Por membro âncora, entenda qualquer consulta que não faça referência à própria CTE.

2. Chamada recursiva da rotina
A chamada recursiva inclui uma ou mais consultas unidas pela cláusula UNION ALL que fazem referências à própria CTE. Estas consultas são conhecidas como membros recursivos.

3. Verificação de encerramento
Um processo implícito que ocorre quando a chamada recursiva não retorna mais registros.

Inicialmente esta ideia de recursividade pode parecer complexa, então partirei direto para as solicitações e, através dos exemplos, este conceito ficará mais claro.

SUBORDINADOS – MÉTODO RECURSIVO

Uma grande vantagem do método recursivo sobre o método iterativo é não existir a necessidade de materializar os dados previamente. Desta forma, seguiremos os seguintes passos para elaborar o script:

  1. Criar o membro âncora da CTE para que traga o nó raiz da sub-árvore;
  2. Criar o membro recursivo da CTE para que traga os subordinados da recursão anterior;
  3. Retornar a hierarquia;

Utilizando o exemplo inicial do método iterativo, para trazer as patentes subordinadas à patente de Comandante, adaptado para o método recursivo seria algo como:

USE tempdb;
GO
-- Iniciando CTE
;WITH Subordinados AS (
-- Membro Âncora
SELECT patente_id, 0 AS nivel
FROM dbo.Patentes
WHERE patente_id = 2 -- Comandante

UNION ALL
-- Membro Recursivo
SELECT Filho.patente_id, Pai.nivel + 1 AS nivel
FROM Subordinados AS Pai
JOIN dbo.Patentes AS Filho
ON Pai.patente_id = Filho.patente_superior_id
)
-- Fazendo o JOIN para trazer as patentes
SELECT P.patente_id, P.patente_descricao, S.nivel
FROM Subordinados AS S
JOIN dbo.Patentes AS P
ON S.patente_id = P.patente_id;

A primeira consulta no corpo da CTE – que define a raíz – é o membro âncora, após o UNION ALL vem a consulta recursiva. No membro recursivo, é feito um JOIN da própria CTE com a tabela base em busca dos subordinados. Cada recursão irá para o próximo nível de subordinados até que não existam mais subordinados.

fascinating_spock

De fato, Spock. A recursividade é uma ferramenta muito poderosa e, para mim, é uma das funcionalidades mais intrigantes (ou deveria dizer “fascinantes”?) das CTEs. O resultado do script anterior é exatamente o mesmo que o encontrado através do método iterativo.

recursivo_subordinado_resultado

Note que poderíamos utilizar diretamente a descrição da patente nos membros da CTE para que não fosse necessário realizar o último JOIN. No entanto, estou optando por esta forma para facilitar o entendimento no momento de encapsular o código. Então, aproveitando este código para encapsular a lógica de subordinados numa função com valor de tabela, terei algo como o script a seguir para retornar os tripulantes subordinados ao Sulu.

USE tempdb;
GO
IF OBJECT_ID('dbo.Subordinados') IS NOT NULL
DROP FUNCTION dbo.Subordinados;
GO
CREATE FUNCTION dbo.Subordinados
(@raiz AS INT) RETURNS TABLE
AS
RETURN
-- Iniciando CTE
WITH Subordinados AS (
-- Membro Âncora
SELECT tripulante_id, 0 AS nivel
FROM dbo.Tripulantes 
WHERE tripulante_id = @raiz

UNION ALL
-- Membro Recursivo
SELECT Filho.tripulante_id, Pai.nivel + 1 AS nivel
FROM Subordinados AS Pai
JOIN dbo.Tripulantes AS Filho
ON Pai.tripulante_id = Filho.superior_id
)
SELECT tripulante_id,nivel
FROM Subordinados;

Agora é simples obter os subordinados de um tripulante. Basta fazer o JOIN com a tabela base e voilà. A figura abaixo mostra o resultado para o Tenente-Comandante Sulu.

consulta_recursiva_resultado

SUPERIORES – MÉTODO RECURSIVO

Para encontrar os nós pais de um determinado nó até a raiz da árvore, irei apenas seguir o caminho contrário do método para encontrar os filhos (subordinados). Logo, ao invés de descer nos níveis hierárquicos, vou subir até a raiz. É importante notar que, diferentemente do método iterativo utilizado no capítulo anterior, neste método é possível ter mais de um nó pai. Isso não ocorrerá para esta árvore, mas vale o aviso.

Adaptando o código anterior para trazer os superiores em comando de um determinado tripulante, temos:

USE tempdb;
GO
IF OBJECT_ID('dbo.Superiores') IS NOT NULL
DROP FUNCTION dbo.Superiores;
GO
CREATE FUNCTION dbo.Superiores
(@tripulante_id AS INT) RETURNS TABLE
AS
RETURN
-- Iniciando CTE
WITH Superiores AS (
-- Membro Âncora
SELECT	tripulante_id, superior_id, 0 AS nivel
FROM dbo.Tripulantes
WHERE tripulante_id = @tripulante_id

UNION ALL
-- Membro Recursivo
SELECT Pai.tripulante_id, Pai.superior_id, Filho.nivel + 1
FROM Superiores AS Filho
JOIN dbo.Tripulantes AS Pai
ON Filho.superior_id = Pai.tripulante_id
)
SELECT tripulante_id, superior_id, nivel
FROM Superiores;

Neste código é necessário manter o código do tripulante superior (superior_id) para que cada recursão da CTE consiga subir um nível, a parte este detalhe, o código é similar ao código para subordinados.

Verificando a cadeia de comando para o Navegador Aprendiz Chekov, temos:

recursivo_funcao_superior_resultado

CAMINHO HIERÁRQUICO – MÉTODO RECURSIVO

Como mostrei no capítulo anterior, o caminho hierárquico registra os nós necessários para encontrar um determinado nó partindo da raiz. Geralmente é utilizado um separador da sua escolha para delimitar cada nó, então para o nó raiz o caminho seria “\” + raiz + “\” e para os próximos nós será “caminho do nó pai” + nó + “\”. Reutilizando a função de subordinados para trazer também o caminho hierárquico, temos:

USE tempdb;
GO
IF OBJECT_ID('dbo.SubordinadosCH') IS NOT NULL
DROP FUNCTION dbo.SubordinadosCH;
GO
CREATE FUNCTION dbo.SubordinadosCH
(@raiz AS INT) RETURNS TABLE
AS
RETURN
-- Iniciando CTE
WITH SubordinadosCH AS (
-- Membro Âncora
SELECT	tripulante_id, 0 AS nivel,
      CAST('\' + CAST(tripulante_id AS VARCHAR) + '\' AS VARCHAR(MAX)) AS caminho
FROM dbo.Tripulantes 
WHERE tripulante_id = @raiz

UNION ALL
-- Membro Recursivo
SELECT	Filho.tripulante_id, Pai.nivel + 1 AS nivel,
   CAST(Pai.caminho + CAST(Filho.tripulante_id AS VARCHAR) + '\' AS VARCHAR(MAX))
FROM SubordinadosCH AS Pai
JOIN dbo.Tripulantes AS Filho
ON Pai.tripulante_id = Filho.superior_id
)
SELECT tripulante_id,nivel,caminho
FROM SubordinadosCH;

Algo que vale lembrar nesta função é a utilização do CAST para garantir que o atributo “caminho” de todas as recursões tenha o mesmo tamanho e tipo de dados. Caso isso não aconteça, não é possível realizar o UNION ALL e o código quebra.

Olhando o caminho hierárquico para todos os subordinados do Capitão Kirk, incluindo ele, temos:

recursivo_funcao_caminho_resultado

Agora que já existe o caminho hierárquico e os níveis, utilizarei esta função para a última solicitação.

APRESENTAÇÃO GRÁFICA – MÉTODO RECURSIVO

Para a apresentação gráfica, utilizarei a mesma técnica apresentada no capítulo anterior. Usarei o REPLICATE para identificar os níveis na hierarquia e a ordenação pelo atributo “caminho”, criado no passo anterior. Desta forma, temos:

recursivo_funcao_caminho_apresentacao_resultado

Por questões gráficas, retornei os resultados como texto. O essencial desta técnica é replicar uma string pelo número de níveis de cada nó. Desta forma, a estrutura fica montada e ordenada como desejado.

Aqui encerro o post de hoje! Na próxima parte, irei materializar o atributo “caminho” para mostrar formas diretas e eficientes de consultar uma hierarquia. Espero que tenha gostado! Até mais!

REFERÊNCIAS

[1] Recursive Queries Using Common Table Expressions

Anúncios