Consulta SQL Longa vs. Consulta SQL Recursiva

A recorrência é uma das idéias centrais na ciência da computação. Podemos defini-la como um método para resolver problemas onde a solução do problema depende da resolução de uma instância menor de um problema. Se isto parece complicado, não se preocupe, neste artigo aprenderemos sobre a recorrência em SQL que você pode praticar e aprofundar na Academia Vertabelo.

A recursividade é uma forma de resolver problemas hierárquicos que encontramos em dados com SQL comum. Estes tipos de consultas também são chamados de consultas hierárquicas. Podemos encontrar a capacidade de recorrência em SQL padrão desde SQL:1999 por meio de CTE's recursivas ou expressões comuns em tabelas.

Alguns sistemas RDMBS têm sua própria maneira de implementar a recursividade, mais notadamente os bancos de dados Oracle com a declaração CONNECT BY. Como os CTE recursivos estão no padrão SQL e são compartilhados com todos os principais fornecedores de RDBMS, vamos explorar este tipo de recursividade.

RECURSIVIDADE COM CTE

A recursividade é melhor dominada com a visualização de alguma estrutura hierárquica. Não há melhor exemplo de dados hierárquicos do que a estrutura organizacional de uma grande empresa. Assim, vamos explorar um típico employees tabela que contém dados sobre funcionários e seus superiores diretos.

A tabela tem o identificador do funcionário atual e seu superior direto como referência à mesma tabela. Além dos identificadores que temos também no nome e sobrenome do funcionário.

Construiremos uma consulta que busca em todas as linhas da tabela, a partir da primeira linha normalmente chamada de linha de âncora. Em nossa tabela, a linha de âncora é o gerente superior, ele não tem gerente de relatórios na hierarquia acima dele, portanto seu atributo manager_id é nulo.

  SELECT id,
         manager_id,
         first_name,
         last_name,
         0 depth_level
  FROM   employees
  WHERE  manager_id IS NULL

Digamos que queremos ver quem John gerencia, como seria a consulta? Algo parecido com isto:

SELECT id,
         manager_id,
         first_name,
         last_name
  FROM   employees cur
  WHERE  manager_id in (
    SELECT id
    FROM   employees
    WHERE  manager_id IS NULL
  )

E para os gerentes desses gerentes:

SELECT id,
  manager_id,
  first_name,
  last_name
FROM employees
WHERE manager_id IN
  (SELECT id
  FROM employees
  WHERE manager_id IN
    ( SELECT id FROM employees WHERE manager_id IS NULL
    )
  ) 

Como você vê, há um padrão emergindo lá para cada novo nível de gerência, construímos um novo subquey. Esta abordagem é ruim, pois leva em conta um número fixo de níveis.

A recorrência é uma forma de pegarmos essas subquetes e transformá-las de modo que elas sejam gerais, de modo que representem o resultado anterior da consulta.

Em nosso exemplo de gestão, a parte geral é construída de modo que unimos o resultado anterior ao atual, com base na id de gestão.

  SELECT cur.id,
         cur.manager_id,
         cur.first_name,
         cur.last_name
  FROM   employees cur, previous
  WHERE  cur.manager_id = previous.id

Este conjunto de dados anterior é definido como um CTE.

Assim, a função recursiva completa se parece com esta :

WITH previous(id, manager_id,first_name,last_name) AS (
  SELECT id,
         manager_id,
         first_name,
         last_name
  FROM   employees
  WHERE  manager_id IS NULL
  UNION ALL
  SELECT cur.id,
         cur.manager_id,
         cur.first_name,
         cur.last_name
  FROM   employees cur, previous
  WHERE  cur.manager_id = previous.id
)
SELECT *
FROM   previous;

A recursividade começa com o gestor de topo e é acompanhada por cada novo nível na hierarquia de gestão. O SELECT final retorna todo o conjunto de dados.

Podemos expandir esta consulta para torná-la mais útil, digamos que queremos ver os níveis na hierarquia. Fazemos isto construindo um novo parâmetro que incrementamos, vamos chamá-lo depth_level. Para cada nível da hierarquia, o número é aumentado em 1.

WITH previous(id, manager_id,first_name,last_name,depth_level) AS (
  SELECT id,
         manager_id,
         first_name,
         last_name,
         0 depth_level
  FROM   employees
  WHERE  manager_id IS NULL
  UNION ALL
  SELECT cur.id,
         cur.manager_id,
         cur.first_name,
         cur.last_name,
         previous.depth_level+1 depth_level
  FROM   employees cur, previous
  WHERE  cur.manager_id = previous.id
)
SELECT previous.*
FROM   previous;

Assim, obtemos os níveis da hierarquia.

Podemos usar este nível de profundidade_ para representar os dados de uma forma mais gráfica com a consulta

WITH previous(id, manager_id,first_name,last_name,depth_level) AS (
  SELECT id,
         manager_id,
         first_name,
         last_name,
         0 depth_level
  FROM   employees
  WHERE  manager_id IS NULL
  UNION ALL
  SELECT cur.id,
         cur.manager_id,
         cur.first_name,
         cur.last_name,
         previous.depth_level+1 depth_level
  FROM   employees cur, previous
  WHERE  cur.manager_id = previous.id
)
SELECT previous.*,
RPAD('.', (depth_level)*2, '.') || id AS tree
FROM   previous;

e o conjunto de resultados:

A recorrência não é a parte mais intuitiva da ciência da computação, mas é integral. A melhor maneira de aprender a recorrência é através de uma prática diligente e persistente. Não há melhor lugar para se praticar SQL do que em LearnSQL.com.br. Portanto, abra seu navegador através dos exemplos no Consultas Recursivas curso e você estará a caminho do domínio do SQL.