Dados Hierárquicos e Como Consultá-los em SQL

Como você reconhece os dados hierárquicos e como consultá-los? Duas perguntas SQL comuns estão prestes a serem respondidas.

A consulta de dados hierárquicos é uma situação bastante comum se você estiver trabalhando com SQL e bancos de dados relacionais. Não que os dados hierárquicos sejam algo místico e raro; pelo contrário, estão em toda parte. Então por que os dados hierárquicos são tão desafiadores quando se trata de bancos de dados relacionais? Principalmente, as dificuldades ocorrem na tradução de dados hierárquicos para os princípios de bancos de dados relacionais.

Quando se trabalha com dados hierárquicos, o primeiro passo é reconhecê-los. Começarei definindo o que são dados hierárquicos e dando vários exemplos diários dos mesmos. Depois passarei a explicar como os dados hierárquicos são normalmente armazenados nos bancos de dados. Finalmente, você aprenderá como consultar tais dados usando SQL.

O que você aprenderá neste artigo pode ser praticado em nosso curso Consultas Recursivas .

O que são dados hierárquicos?

Dados hierárquicos é um tipo específico de dados, caracterizado por uma relação hierárquica entre os conjuntos de dados. O que você pensa quando pensa em hierarquia? Provavelmente de níveis diferentes: algo está acima, abaixo, ou no mesmo nível que algo mais. Em bancos de dados relacionais, uma relação hierárquica também é chamada de relação pai-filho. Isto significa que os dados dos filhos têm apenas um pai, enquanto os dados dos pais têm um ou mais "filhos".

É comum dizer que os dados hierárquicos são reconhecidos por sua estrutura em forma de árvore. Você verá por que, em um momento, enquanto olhamos para exemplos comuns de dados hierárquicos.

Exemplos de dados hierárquicos

Hierarquias de funcionários

Um dos exemplos típicos usados para explicar os dados hierárquicos são as hierarquias de funcionários. Elas são representadas por organogramas como este:

Como você pode ver, esta estrutura é estreita na parte superior e se torna mais larga mais abaixo - muito parecida com um pinheiro. O presidente está no topo. Seus subordinados são dois membros da diretoria. Esses dois membros da diretoria também têm seus subordinados. No caso de Jacqueline Managerovicz, esses são Diane Drinkalot, a gerente de RH, e Rashawn Mangarello, o gerente de contabilidade. Mas eles também são subordinados do presidente, apesar de serem subordinados indiretos.

Os outros subordinados diretos do conselho são Tony Workaholio, o gerente de vendas, e Cassandra Ninetofiver, a gerente de TI. Paul Bossenheim, o presidente da empresa, é também seu superior indireto.

Árvore genealógica

Uma árvore genealógica é outro exemplo comum de dados hierárquicos. Sua estrutura nos permite encontrar os antepassados e seus descendentes. Uma árvore genealógica poderia ser algo parecido com isto:

Neste exemplo, Mike Strongbow casou-se com Victoria Stromboli. Eles tiveram dois filhos, Florence e Claudio. Florence teve um filho (Valerie), e Claudio teve dois filhos (Art e Michelle). Todos os três são filhos de seus pais, mas também são netos de Mike e Victoria. Mike e Victoria também são bisavós; sua neta teve dois filhos, Judy e James.

Menu de Bebidas

Se você for a um bar, é provável que tenha se deparado com uma estrutura hierárquica. Estou falando daquele que você costuma olhar imediatamente após sentar-se: o menu de bebidas, ou cartão, ou o que quer que você lhe chame. Por exemplo, se você visitar o Bar Panthelya, verá que é um bar muito primitivo que só oferece cervejas e vinhos.

O bar oferece dois tipos de cervejas: cerveja de cerveja e cerveja de trigo. Há muitas cervejas que você pode pedir de cada categoria. A categoria de vinhos é dividida em tintos e brancos, com quatro vinhos em cada categoria.

Taxonomia das Coisas Vivas

Mesmo você, a pessoa que lê este artigo, é um registro em dados hierárquicos. Como ser humano, você tem uma certa posição na hierarquia da taxonomia dos seres vivos.

O diagrama acima mostra a hierarquia para humanos, ou homo sapiens. Seria muito complicado mostrar toda a taxonomia dos seres vivos. Entretanto, o princípio é o mesmo que em todos os diagramas acima. Todos os seres humanos pertencem à espécie Sapiens, que é parte do gênero Homo. Este gênero faz parte da família Hominidae, uma das famílias sob a ordem dos primatas. Os primatas pertencem à classe dos Mammalia, que é subordinada ao filo, ao reino e, finalmente, ao domínio.

Pastas em seu computador

Se você estiver lendo este artigo, há uma boa chance de estar fazendo isso em seu computador. Levando isto em consideração, é inteiramente possível que as pastas de seu computador sejam algo parecido com isto:

Todas as suas pastas neste exemplo (Learning and Freelancing) estão em seu disco D:. A pasta Learning tem duas subpastas: SQL, onde você coloca todos os artigos interessantes relacionados a SQL como esta, e Python. Sua pasta freelancing contém três subpastas: Jobs, Invoices, e Outros documentos.

Agora que você sabe como reconhecer os dados hierárquicos, vamos ver como eles são armazenados no banco de dados e como consultá-los.

Armazenamento de dados hierárquicos em uma base de dados

Um problema com dados hierárquicos geralmente surge quando se tenta salvar tais dados em um banco de dados. Para fazer isso, é preciso reunir todos esses dados multiníveis em um formato relativamente plano: uma tabela. Como você converte dados hierárquicos em simples linhas de dados?

Para armazenar os dados hierárquicos em um banco de dados, geralmente há uma coluna que se refere à mesma tabela. O que isso significa? É provavelmente melhor que eu lhe mostre um exemplo. A hierarquia de funcionários parece muito adequada para isso!

Consulta de dados hierárquicos usando um auto-ajuste

Eu lhe mostrarei como consultar uma hierarquia de funcionários. Suponha que tenhamos uma tabela chamada employee com os seguintes dados:

• employee_id - A identificação do funcionário e a chave primária da mesa (PK).
• first_name - O primeiro nome do funcionário.
• last_name - O sobrenome do funcionário.
• reports_to - A identificação do supervisor ou gerente imediato deste funcionário.

O reports_to é nada mais que a coluna employee_id servindo como uma ferramenta para mostrar qual funcionário se reporta a qual funcionário. Se a identificação do funcionário aparece na coluna reports_to, este funcionário é o chefe de (pelo menos alguns) outros funcionários. Deixe-me mostrar-lhe como funciona:

Vemos que Sharon Simon se reporta ao funcionário que tem employee_id = 6, Martina Novak. Em seu caso, o valor reports_to é NULL. Isto significa que Martina Novak não se reporta a ninguém. A partir daí, podemos concluir que ela está no topo da hierarquia de funcionários.

Esta é a parte em que você estará se auto-ajustando à mesa. Não sabe o que é uma auto-adesão? Você pode aprender facilmente lendo este artigo com sete exemplos de auto-inserção. Lembre-se, já mencionei que os dados hierárquicos em um banco de dados geralmente têm uma coluna que se refere à mesma tabela. Este é um exemplo. Para obter os subordinados diretos da tabela employeevocê precisará escrever esta pergunta:

SELECT	
sub.employee_id AS subordinate_id,
sub.first_name AS subordinate_first_name,
	sub.last_name AS subordinate_last_name,
	sup.employee_id AS superior_id,
	sup.first_name AS superior_first_name,
	sup.last_name AS superior_last_name
FROM employee sub 
JOIN employee sup 
ON sub.reports_to = sup.employee_id
ORDER BY superior_id;

Esta consulta junta-se ao employee mesa consigo mesma. Deixe-me explicar como ela funciona. Ao unir uma tabela consigo mesma, você deve usar pseudônimos claros para que o SQL saiba quais dados provêm de qual tabela - e que você saiba quais dados provêm de qual tabela. Na consulta acima, um pseudônimo de tabela é sub. Isto significa que é a tabela com os dados dos subordinados. O outro pseudônimo é sup, ou seja, a tabela com os dados dos superiores. Embora esta seja a mesma tabela, estamos tratando-a como se fossem duas tabelas diferentes.

Assim, a consulta acima seleciona primeiro as colunas employee_id, first_name, e a last_name do sub mesa. Em seguida, retira os mesmos dados da tabela sup. Assim, a tabela employee é então unida a si mesma usando os dois pseudônimos. A auto-união é feita onde a coluna reports_to da tabela sub é igual à coluna employee_id da tabela sup. Os dados são finalmente ordenados pela coluna superior_id.

Aqui está o resultado:

A tabela mostra que Kim Magnus, Sharon Simon e Vincent Trafalgar são subordinados diretos de Martina Novak. Victor Fonseca se reporta a Patricia Rooney. Por sua vez, Patricia Rooney é uma subordinada direta de Sharon Simon, assim como James Palin.

Usando Consultas Recursivas em Dados Hierárquicos Profundos

No exemplo acima, eu lhe mostrei como encontrar superiores/subordinados diretos. Isto significa que você aprendeu a procurar apenas um nível acima ou abaixo. Embora isto seja muito útil, as hierarquias podem ser muito profundas e ter um número imenso de níveis. Antes de consultar tais dados, você precisará aprender como contornar as consultas recursivas. Vamos falar primeiro sobre as consultas recursivas; depois eu lhe mostrarei como elas funcionam com um exemplo ou dois.

O que são Consultas Recursivas?

Se você quiser saber as consultas recursivas, primeiro você terá que aprender sobre Expressões de Tabela Comum, ou CTEs.

Um CTE é um conjunto de dados temporário retornado por uma consulta, que depois é usado por outra consulta. É temporário porque o resultado não é armazenado em nenhum lugar; ele existe apenas quando a consulta é executada. Os CTEs podem ser não-recursivos e recursivos. Já escrevi sobre os CTEs (não-recorrentes) e quando usá-los; sinta-se à vontade para verificar isso mais tarde para mais informações.
Uma consulta recursiva é uma consulta que se refere a si mesma. Ao fazer isso, eles retornam o subresultado e repetem o processo até retornarem o resultado final. Seguindo esta lógica, um CTE recursivo é um CTE que se refere a si mesmo.

A Sintaxe Recursiva do CTE

A sintaxe geral recursiva do CTE se parece com esta e pode ser dividida em três partes:

WITH RECURSIVE cte_name AS (
   cte_query_definition

   UNION ALL

   cte_query_definition
   )


SELECT *
FROM cte_name;

Os CTEs também são chamados de "COM PERGUNTAS". Se você der uma olhada na sintaxe acima, você verá por quê; o CTE sempre começa com a cláusula WITH. Se você quiser que seu CTE seja recursivo, você tem que segui-lo pela palavra RECURSIVE. Depois disso, você define o nome do CTE.

Em seguida, você tem que escrever a definição da consulta do CTE. Esta parte da consulta é chamada de membro âncora. Ela é "conectada" ao outro CTE usando o UNION ALL. Esta segunda definição de consulta do CTE é chamada de membro recursivo, e faz referência ao próprio CTE.

No final vem a declaração SELECT, que busca os dados do CTE. Esta parte da consulta é chamada de invocação.

A sintaxe é sempre melhor aprendida quando você a vê em um exemplo. Portanto, aqui está seu primeiro exemplo recursivo do CTE!

Consultando a Hierarquia de Funcionários

Quero me basear no exemplo onde você aprendeu como se auto-inscrever no employee mesa. Agora vou usar a mesma tabela, mas desta vez vamos usar uma consulta recursiva. A tarefa é encontrar o chefe direto e indireto de cada funcionário. Esta relação entre os funcionários será mostrada como um caminho que leva do chefe no topo (o proprietário) a cada funcionário da mesa.

WITH RECURSIVE employee_hierarchy AS (
  SELECT	employee_id,
    		first_name,
    		last_name,
    		reports_to,
    		'Owner' AS path
  FROM employee
  WHERE reports_to IS NULL

  UNION ALL 
  
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.reports_to,
    employee_hierarchy.path || '->' || e.last_name
  FROM employee e, employee_hierarchy
  WHERE e.reports_to = employee_hierarchy.employee_id
)
SELECT *
FROM employee_hierarchy;

Como você já aprendeu, você começa a escrever um CTE recursivo usando WITH RECURSIVE. Em seguida, você nomeia o CTE. É employee_hierarchy, neste caso.

O membro âncora do CTE é a primeira declaração SELECT. Ao fazer isso, você seleciona a raiz da hierarquia; é a base sobre a qual a consulta recursiva trabalhará sua magia e encontrará todos os outros níveis da hierarquia. Esta declaração seleciona todas as colunas da tabela employee. Também acrescenta a nova coluna pathcom o valor em 'Owner'. A cláusula WHERE significa que isso será feito somente para as linhas onde o valor na coluna reports_to é NULL. Por que isso acontece? Se houver um valor NULL na coluna reports_to, o funcionário não se reporta a ninguém. Isso significa que este é o proprietário da empresa.

O próximo passo é "conectar" o membro âncora com o membro recursivo do CTE com UNION ALL. O importante sobre o uso do UNION ALL é que as declarações SELECT que você está "conectando" têm que ter o mesmo número de colunas. Caso contrário, UNION ALL não vai funcionar.

O membro recursivo é a segunda declaração SELECT. Esta declaração seleciona novamente todas as colunas da tabela employee. Também retira o valor (que é 'Owner') do caminho da coluna do employee_hierarchy CTE. Adiciona '->' a ele, seguido do valor da coluna last_name da tabela employee. (O ||| é um operador de concatenação; combina dois ou mais valores em um só valor). Este será um caminho que leva do proprietário a cada funcionário.

A tabela employee e o CTE employee_hierarchy estão unidos como quaisquer outras duas mesas. Isto é feito onde a coluna reports_to é igual à coluna employee_id. O membro recursivo serve como uma extensão do membro âncora. Isto significa que ele estende um resultado que já foi encontrado (pelo membro âncora) com novos resultados. Assim, o membro recursivo executará tudo descrito até chegar ao último funcionário.

Finalmente, a parte da simples invocação seleciona todos os dados do CTE com employee_hierarchy. E, voilá! O resultado é:

Se você olhar, digamos, Victor Fonseca, você pode ver que o caminho do proprietário até ele leva através de Sharon Simon e Patricia Rooney.

Pratiquemos consultas recursivas em outro exemplo!

Consultando a Hierarquia da Pasta

As empresas normalmente têm unidades de rede onde os funcionários economizam todo o seu trabalho. Isto geralmente leva a uma estrutura em árvore muito ramificada de pastas. Os dados sobre as pastas são armazenados na tabela folder. Suas colunas são:

• id - A identificação da pasta e a chave primária da mesa (PK).
• name - O nome da pasta.
• subfolder_of - O nome da pasta um nível acima.

Para encontrar o caminho para todas as pastas, você precisará da seguinte consulta:

WITH RECURSIVE folder_hierarchy AS (
  SELECT	id,
   	 	name,
    		subfolder_of,
    		CAST (name AS text) AS path
  FROM folder
  WHERE subfolder_of IS NULL
	
  UNION ALL 
	
  SELECT	folder.id,
    		folder.name,
    		folder.subfolder_of,
    		folder_hierarchy.path || '\' || folder.name
  FROM folder, folder_hierarchy
  WHERE folder.subfolder_of = folder_hierarchy.id
)
SELECT *
FROM folder_hierarchy;

O princípio é o mesmo que no exemplo anterior. Novamente, você começa com WITH RECURSIVE e o nome: folder_hierarchy. A primeira declaração SELECT seleciona as três colunas da tabela folder. A quarta coluna é o caminho, que contém dados do nome da coluna lançados como valores de texto. Os dados são lançados para corresponder ao tipo de dados do membro recursivo do CTE. Finalmente, a cláusula WHERE limita os dados somente àqueles com os valores NULL na coluna subfolder_of. Onde há NULL, há a pasta raiz (ou seja, a que não tem pastas acima dela).

O UNION ALL é novamente usado para "conectar" os membros ancorados e recursivos do CTE. Uma coisa adicional a ser lembrada: os tipos de dados em ambas as declarações SELECT têm que ser os mesmos para que o UNION ALL funcione. Caso contrário, a consulta retornará um erro.

A segunda declaração SELECT seleciona novamente todas as colunas da tabela folder. O valor do caminho das colunas e o nome são colocados juntos, com '\' separando os dados.

Finalmente, todos os dados do CTE são selecionados, o que retorna uma bela tabela:

Agora cada pasta tem seu caminho e pode ser facilmente acessada. Chega de navegar por vários níveis de pastas para obter o que você precisa!

Acredito que estes são exemplos convincentes do poder das consultas recursivas. Se não, sempre há uma chance de você precisar de mais um artigo para ver o verdadeiro potencial das consultas recursivas. E se você for um usuário Oracle, veja como você pode usar consultas hierárquicas nesse SGBD em particular.

A consulta de dados hierárquicos é mais fácil do que nunca!

Agora que você sabe o que são dados hierárquicos, você começará a reconhecê-los em todos os lugares. Eu já dei vários exemplos diários, mas tenho certeza de que você encontrará ainda mais. Tente implementar os CTEs de auto-adesão, os CTEs recursivos e tudo mais que você aprendeu aqui. Essa é a única maneira de manter seu conhecimento atualizado!

Se você não tiver exercícios práticos, você pode encontrar muitos deles em nosso Consultas Recursivas Curso. O que você pode aprender neste curso? Como as consultas recorrentes podem lhe ajudar? Não é preciso se perguntar; nosso Diretor de Conteúdo explica tudo em detalhes neste artigo.