Trabalhando com dados hierárquicos – Parte I

enterprise
Dados hierárquicos: a fronteira final. Essas são as viagens de um DBA na nave MSSQL Enterprise. Sua missão: explorar novas hierarquias, procurar novas formas de consultar e materializar dados hierárquicos, ir onde um número razoável de DBAs já foi.

Hoje trouxe para vocês um assunto que normalmente causa arrepios quando se trata de consultas: dados hierárquicos. Na primeira parte, vou começar descrevendo o que são dados hierárquicos num banco de dados e como identificá-los. Nas partes seguintes, veremos as formas mais comuns de consultas a hierarquias – através de iteração e recursividade – e então iremos materializar uma hierarquia para melhorar o desempenho de nossas consultas. Finalmente, iremos aprender a converter dados hierárquicos numa estrutura materializada com o hierarchyid e conhecer alguns métodos deste “novo” tipo de dados.

O QUE SÃO DADOS HIERÁRQUICOS?

Dados hierárquicos são, em sua maioria, registros de uma mesma entidade que possuem uma ligação que estabelece uma relação de parentesco ou subordinação. Uma das representações mais comuns de uma hierarquia é a relação gerente/funcionário. No entanto, existem diversas estruturas hierárquicas fáceis de identificar, como as relações entre pai/filho ou país/estado/cidade, etc. Essas estruturas são representadas por árvores invertidas onde a raiz fica no topo. Cada nó da árvore – representados com círculos – ou é um nó interno ou um nó folha. Um nó interno é chamado de pai de seus nós filhos e pode ter um ou mais filhos. Filhos de um mesmo nó interno são chamados de irmãos como mostra a figura abaixo.

Adaptada do livro "Inside Microsoft SQL Server 2008: T-SQL Querying"

Imagem adaptada do livro “Inside Microsoft SQL Server 2008: T-SQL Querying”

Para entendermos melhor essa estrutura hierárquica, vamos criar duas tabelas e inserir alguns dados.

SET NOCOUNT ON;
USE [tempdb];
GO
-- Vamos garantir que as tabelas não existam
IF(OBJECT_ID('dbo.Patentes') IS NOT NULL) DROP TABLE dbo.Patentes;
GO
IF(OBJECT_ID('dbo.Tripulantes') IS NOT NULL) DROP TABLE dbo.Tripulantes;
GO
-- Criando a tabela de Patentes
CREATE TABLE dbo.Patentes (
	patente_id INT PRIMARY KEY,
	patente_superior_id INT NULL REFERENCES dbo.Patentes,
	patente_descricao VARCHAR(20) NOT NULL,
	CHECK(patente_id <> patente_superior_id)
);
-- Criando a tabela de Tripulantes
CREATE TABLE dbo.Tripulantes (
	tripulante_id INT PRIMARY KEY,
	superior_id	INT NULL REFERENCES dbo.Tripulantes,
	tripulante_nome VARCHAR(20) NOT NULL,
	patente_id INT NOT NULL,
	cargo VARCHAR(20) NOT NULL,
	CHECK(tripulante_id <> superior_id)
);
-- Inserindo Patentes
INSERT INTO dbo.Patentes(patente_id,patente_superior_id,patente_descricao) VALUES
(1,   NULL,  'Capitão'),
(2,   1,     'Comandante'),
(3,   2,     'Tenente-Comandante'),
(4,   3,     'Tenente'),
(5,   4,     'Alferes');
-- Inserindo a tripulação
INSERT INTO dbo.Tripulantes (tripulante_id,superior_id,tripulante_nome,patente_id,cargo) VALUES
(1,     NULL,   'Kirk',     1,	'Capitão Encarregado'),
(2,     1,      'Spock',    2,	'Primeiro Oficial'),
(3,     1,      'Scott',    2,	'Chefe de Engenharia'),
(4,     1,      'Uhura',    3,	'Chefe de Comunicações'),
(5,     1,      'McCoy',    3,	'Médico'),
(6,     1,      'Kelso',    3,	'Chefe de Navegação'),
(7,     1,      'Sulu',     3,	'Chefe dos Timoneiros'),
(8,     4,      'Farrell',  4,	'Oficial de Comunicações'),
(9,     4,      'Palmer',   4,	'Oficial de Comunicações'),
(10,    6,      'Stiles',   4,	'Navegador'),
(11,    6,      'Osborne',  4,	'Navegador'),
(12,    7,      'Leslie',   4,	'Timoneiro'),
(13,    7,      'Hansen',   4,	'Timoneiro'),
(14,    7,      'DePaul',   4,	'Timoneiro'),
(15,    11,     'Chekov',   5,	'Navegador Aprendiz'),
(16,    11,     'Haines',   5,	'Navegador Aprendiz'),
(17,    14,     'Dawson',   5,	'Timoneiro Aprendiz');

Nesse script, criamos uma tabela de patentes que possui uma hierarquia militar e uma tabela de tripulantes que possui outra hierarquia – a de comando. A hierarquia militar define níveis entre as patentes, onde o Capitão é o superior do Comandante, o Comandante é o superior do Tenente-Comandante e assim por diante. No entanto, na estrutura de comando, é definida uma relação direta de tripulante e superior. Em outras palavras, apesar da patente de Alferes ser subordinada à patente de Tenente, o Navegador Aprendiz Chekov é subordinado diretamente apenas ao Navegador Osborne e não ao Timoneiro Hansen mesmo que este também seja tenente. Caso desenhássemos nossa hierarquia de comando da Enterprise, teríamos algo como a figura abaixo.

E pensar que desisti de ser desenhista... tsc tsc...

E pensar que desisti de ser desenhista…

Agora que temos nossa hierarquia bem clara, vamos às solicitações mais comuns desse tipo de estrutura.

SUBORDINADOS

Provavelmente a solicitação mais comum de uma estrutura hierárquica é retornar os subordinados – ou filhos – de um determinado indivíduo – ou nó. Nesta pesquisa não buscamos apenas os subordinados diretos, mas todos direta e indiretamente subordinados ao nó. Em outras palavras, queremos toda a sub-árvore originada de um determinado nó.

Ex.:
Para retornarmos os subordinados do Tenente-Comandante Sulu, precisamos descer um nível hierárquico e analisar quais tenentes possuem o Sulu como seu superior. Neste caso, temos os tenentes Leslie, Hansen e DePaul. Então, desceremos mais um nível hierárquico para analisar se algum dos tenentes retornados na iteração anterior possui algum subordinado. Para este caso, observamos que o Tenente DePaul possui o Alferes Dawson como seu subordinado. Desta forma, repetimos este processo até que não retornem mais registros.

SUPERIORES

Outra solicitação comum numa hierarquia é retornar todos os superiores – ou pais – de um determinado nó. Da mesma forma que vimos para os subordinados, não buscamos apenas os superiores diretos, mas toda estrutura superior. Para nossa hierarquia, isto significa um único caminho até o topo (raiz) da árvore.

Ex.:
Para obtermos os superiores do Tenente Palmer, precisamos subir um nível hierárquico e retornar apenas o nó que possua o Palmer como subordinado (filho). Desta forma, repetiremos o mesmo processo para a Tenente-Comandante Uhura até chegar ao Capitão Kirk.

CAMINHO HIERÁRQUICO
Para esta solicitação, não queremos apenas os superiores ou subordinados, mas o caminho trilhado na hierarquia do nó de partida até o nó de destino. Para isso utilizaremos algum separador como “.” ou “\” para delimitar os nós.

Ex.:
O caminho hierárquico do Tenente-Comandante Sulu até o Alferes Dawson seria definido por “.Sulu.DePaul.Dawson.” ou “\Sulu\DePaul\Dawson\”.

APRESENTAÇÃO GRÁFICA

Nesta solicitação, o usuário deseja um resultado gráfico que demonstre a hierarquia desejada. Para representar graficamente, utilizaremos o nível e o caminho hierárquicos.

Ex.:
Para retornarmos graficamente os subordinados do Tenente-Comandante Sulu, podemos ter algo como a figura abaixo.

RepresentacaoGrafica_Hierarquia

Nos próximos capítulos dessa série, utilizaremos alguns métodos diferentes para atender às solicitações apresentadas. Ao final do artigo, você poderá decidir qual lhe servirá melhor. Até lá!

Anúncios

3 pensamentos sobre “Trabalhando com dados hierárquicos – Parte I

  1. Pingback: Trabalhando com dados hierárquicos – Parte II | Comunidade SQL Server

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s