Datos jerárquicos y cómo consultarlos en SQL

¿Cómo se reconocen los datos jerárquicos y cómo se consultan? Dos preguntas comunes de SQL están a punto de ser respondidas.

La consulta de datos jerárquicos es una situación bastante común si se trabaja con SQL y bases de datos relacionales. No es que los datos jerárquicos sean algo místico y raro; al contrario, están en todas partes. Entonces, ¿por qué los datos jerárquicos son tan difíciles cuando se trata de bases de datos relacionales? Principalmente, las dificultades se producen al trasladar los datos jerárquicos a los principios de las bases de datos relacionales.

Cuando se trabaja con datos jerárquicos, el primer paso es reconocerlos. Empezaré por definir qué son los datos jerárquicos y daré varios ejemplos cotidianos de ellos. Luego pasaré a explicar cómo se suelen almacenar los datos jerárquicos en las bases de datos. Por último, aprenderás a consultar dichos datos mediante SQL.

Lo que aprendas en este artículo podrás practicarlo en nuestro curso Consultas recursivas y expresiones de tabla comunes .

¿Qué son los datos jerárquicos?

Los datos jerárquicos son un tipo específico de datos, caracterizados por una relación jerárquica entre los conjuntos de datos. ¿En qué piensa usted cuando piensa en jerarquía? Probablemente en diferentes niveles: algo está por encima, por debajo o al mismo nivel que otra cosa. En las bases de datos relacionales, una relación jerárquica también se llama relación padre-hijo. Esto significa que los datos hijos tienen sólo un padre, mientras que los datos padres tienen uno o más "hijos".

Es habitual decir que los datos jerárquicos se reconocen por su estructura en forma de árbol. Veremos por qué en un momento, cuando veamos ejemplos comunes de datos jerárquicos.

Ejemplos de datos jerárquicos

Jerarquías de empleados

Uno de los ejemplos típicos utilizados para explicar los datos jerárquicos son las jerarquías de empleados. Están representadas por organigramas como éste:

Como puede ver, esta estructura es estrecha en la parte superior y se ensancha más abajo, como un pino. El presidente está en la cima. Sus subordinados son dos miembros del consejo de administración. Esos dos miembros del consejo también tienen sus subordinados. En el caso de Jacqueline Managerovicz, se trata de Diane Drinkalot, directora de recursos humanos, y Rashawn Mangarello, director de contabilidad. Pero también son subordinados del presidente, aunque sean subordinados indirectos.

Los otros subordinados directos de los miembros de la junta directiva son Tony Workaholio, director de ventas, y Cassandra Ninetofiver, directora de informática. Paul Bossenheim, el presidente de la empresa, es también su superior indirecto.

Árbol genealógico

Un árbol genealógico es otro ejemplo común de datos jerárquicos. Su estructura nos permite encontrar a los antepasados y a sus descendientes. Un árbol genealógico podría tener el siguiente aspecto:

En este ejemplo, Mike Strongbow se casó con Victoria Stromboli. Tuvieron dos hijos, Florence y Claudio. Florence tuvo un hijo (Valerie), y Claudio tuvo dos hijos (Art y Michelle). Los tres son hijos de sus padres, pero también son nietos de Mike y Victoria. Mike y Victoria también son bisabuelos; su nieta tuvo dos hijos, Judy y James.

Menú de bebidas

Si vas a un bar, es probable que te encuentres con una estructura jerárquica. Me refiero a la que sueles mirar nada más sentarte: la carta de bebidas, o tarjeta, o como se llame. Por ejemplo, si visitas el bar Panthelya, verás que es un bar muy primitivo que sólo ofrece cervezas y vinos.

El bar ofrece dos tipos de cervezas: la ale y la de trigo. Hay muchas cervezas que se pueden pedir de cada categoría. La categoría de vinos se divide en tintos y blancos, con cuatro vinos en cada categoría.

Taxonomía de los seres vivos

Incluso usted, la persona que está leyendo este artículo, es un registro en datos jerárquicos. Como ser humano, tienes una determinada posición en la jerarquía de la taxonomía de los seres vivos.

El diagrama anterior muestra la jerarquía para los humanos, o homo sapiens. Sería demasiado complicado mostrar toda la taxonomía de los seres vivos. Sin embargo, el principio es el mismo que en todos los diagramas anteriores. Todos los humanos pertenecen a la especie Sapiens, que forma parte del género Homo. Este género forma parte de la familia Hominidae, una de las familias del orden de los Primates. Los primates pertenecen a la clase Mammalia, que está subordinada al filo, al reino y, finalmente, al dominio.

Carpetas en su ordenador

Si estás leyendo este artículo, es muy probable que lo hagas en tu ordenador. Teniendo en cuenta esto, es muy posible que las carpetas de tu ordenador se parezcan a esto:

Todas las carpetas de este ejemplo (Aprendizaje y Freelancing) están en tu disco D:. La carpeta Learning tiene dos subcarpetas: SQL, donde pones todos los artículos interesantes relacionados con SQL como éste, y Python. La carpeta Freelancing contiene tres subcarpetas: Trabajos, Facturas y Otros documentos.

Ahora que sabes cómo reconocer los datos jerárquicos, vamos a ver cómo se almacenan en la base de datos y cómo consultarlos.

Almacenamiento de datos jerárquicos en una base de datos

El problema de los datos jerárquicos suele surgir cuando se intenta guardar dichos datos en una base de datos. Para ello, hay que meter todos esos datos de varios niveles en un formato relativamente plano: una tabla. ¿Cómo se convierten los datos jerárquicos en simples filas de datos?

Para almacenar los datos jerárquicos en una base de datos, suele haber una columna que hace referencia a la misma tabla. ¿Qué significa eso? Probablemente sea mejor que te muestre un ejemplo. La jerarquía de empleados parece muy adecuada para ello.

Consulta de datos jerárquicos mediante un autoenlace

Te mostraré cómo consultar una jerarquía de empleados. Supongamos que tenemos una tabla llamada employee con los siguientes datos:

• employee_id - El ID del empleado y la clave primaria de la tabla (PK).
• first_name - El nombre del empleado.
• last_name - El apellido del empleado.
• reports_to - El ID del supervisor o gerente inmediato de este empleado.

La reports_to no es otra cosa que la columna employee_id que sirve como herramienta para mostrar qué empleado depende de qué empleado. Si el ID del empleado aparece en la columna reports_to, este empleado es el jefe de (al menos algunos) otros empleados. Déjeme mostrarle cómo funciona:

Vemos que Sharon Simon depende del empleado que tiene employee_id = 6, Martina Novak. En su caso, el valor de reports_to es NULL. Esto significa que Martina Novak no depende de nadie. De ahí podemos concluir que está en la cima de la jerarquía de empleados.

Esta es la parte en la que te auto-unirás a la tabla. ¿No sabes lo que es un self-join? Puedes aprender fácilmente leyendo este artículo con siete ejemplos de auto-unión. Recuerda que ya he mencionado que los datos jerárquicos en una base de datos suelen tener una columna que hace referencia a la misma tabla. Este es un ejemplo de ello. Para obtener los subordinados directos de la tabla employeetendrás que escribir esta consulta:

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 une la tabla employee con ella misma. Permíteme explicar cómo funciona. Cuando se une una tabla consigo misma, se deben utilizar alias claros para que SQL sepa qué datos provienen de qué tabla - y que usted sepa qué datos son de qué tabla. En la consulta anterior, un alias de tabla es sub. Esto significa que es la tabla con los datos de las subordinadas. El otro alias es supque es la tabla con los datos de los superiores. Aunque se trata de la misma tabla, la tratamos como si fueran dos tablas diferentes.

Así que la consulta anterior selecciona primero las columnas employee_id, first_name, y la last_name de la tabla sub tabla. Luego toma los mismos datos de la tabla sup. Así, la tabla employee se une a sí misma utilizando los dos alias. La autounión se realiza cuando la columna reports_to de la tabla sub es igual a la columna employee_id de la tabla sup. Los datos se ordenan finalmente por la columna superior_id.

Este es el resultado:

La tabla muestra que Kim Magnus, Sharon Simon y Vincent Trafalgar son subordinados directos de Martina Novak. Victor Fonseca depende de Patricia Rooney. A su vez, Patricia Rooney es subordinada directa de Sharon Simon, al igual que James Palin.

Uso de Consultas recursivas y expresiones de tabla comunes en datos jerárquicos profundos

En el ejemplo anterior, te he mostrado cómo encontrar superiores/subordinados directos. Esto significa que ha aprendido a buscar sólo un nivel por encima o por debajo. Aunque esto es muy útil, las jerarquías pueden ser muy profundas y tener un inmenso número de niveles. Antes de consultar esos datos, tendrás que aprender a utilizar las consultas recursivas. Hablemos primero de las consultas recursivas; luego te mostraré cómo funcionan con uno o dos ejemplos.

¿Qué es Consultas recursivas y expresiones de tabla comunes?

Si quieres conocer las consultas recursivas, primero tendrás que aprender sobre las Expresiones de Tabla Comunes, o CTEs.

Una CTE es un conjunto de datos temporal devuelto por una consulta, que luego es utilizado por otra consulta. Es temporal porque el resultado no se almacena en ningún sitio; sólo existe cuando se ejecuta la consulta. Los CTEs pueden ser no recursivos y recursivos. Ya he escrito sobre las CTEs (no recursivas) y cuándo usarlas; siéntase libre de revisar eso más adelante para obtener más información.
Una consulta recursiva es una consulta que hace referencia a sí misma. Al hacerlo, devuelven el subresultado y repiten el proceso hasta devolver el resultado final. Siguiendo esta lógica, un CTE recursivo es un CTE que se referencia a sí mismo.

La sintaxis de la ETC recursiva

La sintaxis general de las CTE recursivas tiene este aspecto y puede dividirse en tres partes:

WITH RECURSIVE cte_name AS (
   cte_query_definition

   UNION ALL

   cte_query_definition
   )


SELECT *
FROM cte_name;

Las CTEs también se llaman "consultas WITH". Si echa un vistazo a la sintaxis anterior, verá por qué; la CTE siempre comienza con la cláusula WITH. Si quieres que tu CTE sea recursivo, tienes que seguirlo con la palabra RECURSIVE. Después de eso, define el nombre del CTE.

A continuación, tienes que escribir la definición de la consulta CTE. Esta parte de la consulta se llama miembro ancla. Está "conectada" al otro CTE utilizando el UNION ALL. Esta segunda definición de consulta del CTE se llama miembro recursivo, y hace referencia al propio CTE.

Al final viene la sentencia SELECT, que obtiene los datos del CTE. Esta parte de la consulta se denomina invocación.

La sintaxis siempre se aprende mejor cuando se ve en un ejemplo. Así que aquí está su primer ejemplo de CTE recursivo.

Consulta de la jerarquía de empleados

Quiero continuar con el ejemplo en el que aprendiste a autounir la employee tabla. Ahora voy a usar la misma tabla, pero esta vez usaremos una consulta recursiva. La tarea es encontrar el jefe directo e indirecto de cada empleado. Esta relación entre los empleados se mostrará como un camino que lleva desde el jefe en la parte superior (el propietario) a cada empleado en la tabla.

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 ya ha aprendido, usted comienza a escribir una CTE recursiva usando WITH RECURSIVE. Luego se nombra el CTE. En este caso es employee_hierarchy.

El miembro ancla del CTE es la primera sentencia SELECT. Al hacer esto, se selecciona la raíz de la jerarquía; es la base sobre la que la consulta recursiva hará su magia y encontrará todos los demás niveles de la jerarquía. Esta sentencia selecciona todas las columnas de la tabla employee. También añade la nueva columna pathcuyo valor es 'Owner'. La cláusula WHERE significa que esto se hará sólo para las filas donde el valor de la columna reports_to es NULL. ¿Por qué? Si hay un valor NULL en la columna reports_to, el empleado no reporta a nadie. Significa que se trata del propietario de la empresa.

El siguiente paso es "conectar" el miembro ancla con el miembro recursivo del CTE con UNION ALL. Lo importante de usar UNION ALL es que las sentencias SELECT que estás "conectando" tienen que tener el mismo número de columnas. De lo contrario, UNION ALL no funcionará.

El miembro recursivo es la segunda sentencia SELECT. Esta sentencia vuelve a seleccionar todas las columnas de la tabla employee. También toma el valor (que es 'Owner') de la ruta de la columna del CTE employee_hierarchy. Le añade '->', seguido del valor de la columna last_name de la tabla employee. (El || es un operador de concatenación; combina dos o más valores en uno solo). Esto será un camino que lleva desde el propietario a cada empleado.

La tabla employee y el CTE employee_hierarchy se unen como cualquier otra tabla. Esto se hace cuando la columna reports_to es igual a la columna employee_id. El miembro recursivo sirve como una extensión del miembro ancla. Esto significa que extiende un resultado que ya ha sido encontrado (por el miembro ancla) con nuevos resultados. Así, el miembro recursivo realizará todo lo descrito hasta llegar al último empleado.

Finalmente, la parte de la invocación simple selecciona todos los datos del CTE employee_hierarchy. Y, ¡voilá! El resultado es:

Si miras, por ejemplo, a Víctor Fonseca, puedes ver que el camino desde el propietario hasta él pasa por Sharon Simon y Patricia Rooney.

Practiquemos las consultas recursivas con otro ejemplo.

Consulta de la jerarquía de carpetas

Las empresas suelen tener unidades de red donde los empleados guardan todo su trabajo. Esto suele dar lugar a una estructura de árbol de carpetas muy ramificada. Los datos sobre las carpetas se almacenan en la tabla folder. Sus columnas son:

• id - El ID de la carpeta y la clave primaria (PK) de la tabla.
• name - El nombre de la carpeta.
• subfolder_of - El nombre de la carpeta de un nivel superior.

Para encontrar la ruta de todas las carpetas, necesitará la siguiente 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;

El principio es el mismo que en el ejemplo anterior. De nuevo, se empieza con WITH RECURSIVE y el nombre: folder_hierarchy. La primera sentencia SELECT selecciona las tres columnas de la tabla folder. La cuarta columna es "path", que contiene los datos del nombre de la columna convertidos en valores de texto. Los datos son moldeados para que coincidan con el tipo de datos del miembro recursivo de la CTE. Por último, la cláusula WHERE limita los datos sólo a los que tienen los valores de NULL en la columna subfolder_of. Donde hay NULL, está la carpeta raíz (es decir, la que no tiene carpetas por encima).

El UNION ALL se utiliza de nuevo para "conectar" los miembros ancla y recursivos del CTE. Una cosa adicional a recordar: los tipos de datos en ambas sentencias SELECT tienen que ser los mismos para que UNION ALL funcione. De lo contrario, la consulta devolverá un error.

La segunda sentencia SELECT vuelve a seleccionar todas las columnas de la tabla folder. Los valores de las columnas path y name se ponen juntos, con '\' separando los datos.

Finalmente, se seleccionan todos los datos del CTE, que devuelve una bonita tabla:

Ahora cada carpeta tiene su ruta y se puede acceder a ella fácilmente. Ya no hay que navegar por varios niveles de carpetas para conseguir lo que se necesita.

Creo que estos son ejemplos convincentes del poder de las consultas recursivas. Si no es así, siempre hay una posibilidad de que necesites un artículo más para ver el verdadero potencial de las consultas recursivas. Y si eres un usuario de Oracle, aquí tienes cómo puedes utilizar las consultas jerárquicas en ese DBMS en particular.

Consultar datos jerárquicos es más fácil que nunca

Ahora que sabe qué son los datos jerárquicos, empezará a reconocerlos por todas partes. Te he dado varios ejemplos cotidianos, pero estoy seguro de que encontrarás aún más. Intenta implementar los auto-joins, los CTEs recursivos y todo lo que has aprendido aquí. Es la única manera de mantener frescos tus conocimientos.

Si te faltan ejercicios de práctica, puedes encontrar muchos en nuestro curso Consultas recursivas y expresiones de tabla comunes . ¿Qué puedes aprender en este curso? ¿Cómo pueden ayudarte las consultas recursivas? No hace falta que te lo preguntes; nuestro jefe de contenidos te lo explica todo con detalle en este artículo.