Consulta SQL larga vs. Consulta SQL recursiva

La recursión es una de las ideas centrales de la informática. Podemos definirla como un método para resolver problemas en el que la solución del problema depende de la resolución de una instancia más pequeña de un problema. Si esto suena complicado no te preocupes, en este artículo aprenderemos sobre la recursión en SQL que podrás practicar y profundizar en la Academia Vertabelo.

La recursión es una forma de resolver problemas jerárquicos que encontramos en los datos con el SQL común. Este tipo de consultas también se denominan consultas jerárquicas. Podemos encontrar la capacidad de recursión en el SQL estándar desde SQL:1999 por medio de CTE's recursivas o expresiones comunes de tabla.

Algunos sistemas RDMBS tienen su propia forma de implementar la recursión, sobre todo las bases de datos de Oracle con la sentencia CONNECT BY. Dado que la CTE recursiva está en el estándar SQL y es compartida por todos los principales proveedores de RDBMS, exploraremos este tipo de recursión.

RECURSIÓN CON CTE

La recursión se domina mejor con la visualización de alguna estructura jerárquica. No hay mejor ejemplo de datos jerárquicos que la estructura organizativa de una gran empresa. Así que vamos a explorar una tabla típica employees que contiene datos sobre los empleados y sus superiores directos.

La tabla tiene el identificador del empleado actual y su superior directo como referencia a la misma tabla. Además de los identificadores también tenemos en la tabla el nombre y los apellidos del empleado.

Construiremos una consulta que busque en todas las filas de la tabla, empezando por la primera fila que suele llamarse fila ancla. En nuestra tabla la fila ancla es el máximo responsable, no tiene ningún responsable en la jerarquía por encima de él por lo que su atributo manager_id es nulo.

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

Digamos que queremos ver a quién gestiona Juan, ¿cómo sería la consulta? Algo así:

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

Y para los gerentes de esos 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 ves hay un patrón que surge ahí por cada nuevo nivel de gestión construimos una nueva subconsulta. Este enfoque es malo ya que tiene en cuenta un número fijo de niveles.

La recursión es una forma de tomar estas subconsultas y transformarlas para que sean generales de forma que representen el resultado anterior de la consulta.

En nuestro ejemplo de gestión, la parte general se construye de forma que unimos el conjunto de resultados anterior con el actual basándonos en el identificador de la gestión.

  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 datos anterior se define como un CTE.

Así que la función recursiva completa tiene el siguiente aspecto:

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;

La recursión comienza con el gestor principal y se une a cada nuevo nivel de la jerarquía de gestión. El SELECT final devuelve el conjunto de datos completo.

Podemos ampliar esta consulta para hacerla más útil, digamos que queremos ver los niveles de la jerarquía. Lo hacemos construyendo un nuevo parámetro que incrementamos, llamémoslo depth_level. Por cada nivel de la jerarquía el número se incrementa en 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;

Así que obtenemos los niveles de la jerarquía.

Podemos utilizar este nivel de profundidad para representar los datos de forma más gráfica con la 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;

y el conjunto de resultados:

La recursión no es la parte más intuitiva de la informática, pero es integral. La mejor manera de aprender la recursión es mediante la práctica diligente y persistente. No hay mejor lugar para practicar SQL que en LearnSQL.es. Así que abre tu navegador y repasa los ejemplos del Consultas recursivas y expresiones de tabla comunes curso y estarás en camino de dominar SQL.