Données hiérarchiques et comment les interroger en SQL

Comment reconnaître les données hiérarchiques et comment les interroger ? Deux questions SQL courantes sont sur le point de trouver une réponse.

L'interrogation de données hiérarchiques est une situation assez courante si vous travaillez avec SQL et des bases de données relationnelles. Non pas que les données hiérarchiques soient quelque chose de mystique et de rare ; au contraire, elles sont partout. Alors pourquoi les données hiérarchiques sont-elles si difficiles à traiter dans les bases de données relationnelles ? Les difficultés surviennent principalement dans la traduction des données hiérarchiques en principes de bases de données relationnelles.

Lorsque l'on travaille avec des données hiérarchiques, la première étape consiste à les reconnaître. Je commencerai par définir ce que sont les données hiérarchiques et je vous donnerai plusieurs exemples de la vie quotidienne. Je vous expliquerai ensuite comment les données hiérarchiques sont généralement stockées dans les bases de données. Enfin, vous apprendrez comment interroger ces données à l'aide de SQL.

Ce que vous apprenez dans cet article peut être mis en pratique dans notre cours Requêtes récursives .

Qu'est-ce que les données hiérarchiques ?

Les données hiérarchiques sont un type de données spécifique, caractérisé par une relation hiérarchique entre les ensembles de données. À quoi pensez-vous lorsque vous pensez à la hiérarchie ? Probablement à des niveaux différents : quelque chose est au-dessus, au-dessous ou au même niveau que quelque chose d'autre. Dans les bases de données relationnelles, une relation hiérarchique est également appelée relation parent-enfant. Cela signifie que les données enfant n'ont qu'un seul parent, tandis que les données parent ont un ou plusieurs "enfants".

Il est courant de dire que les données hiérarchiques se reconnaissent à leur structure arborescente. Vous verrez pourquoi dans un instant, lorsque nous examinerons des exemples courants de données hiérarchiques.

Exemples de données hiérarchiques

Hiérarchies d'employés

L'un des exemples typiques utilisés pour expliquer les données hiérarchiques est celui des hiérarchies d'employés. Elles sont représentées par des organigrammes comme celui-ci :

Comme vous pouvez le voir, cette structure est étroite au sommet et s'élargit en descendant - un peu comme un pin. Le président est au sommet. Ses subordonnés sont deux membres du conseil d'administration. Ces deux membres du conseil ont aussi leurs subordonnés. Dans le cas de Jacqueline Managerovicz, il s'agit de Diane Drinkalot, responsable des ressources humaines, et de Rashawn Mangarello, responsable de la comptabilité. Mais ils sont aussi les subordonnés de la présidente, même s'ils sont des subordonnés indirects.

Les autres subordonnés directs des membres du conseil d'administration sont Tony Workaholio, le directeur des ventes, et Cassandra Ninetofiver, le directeur informatique. Paul Bossenheim, le président de l'entreprise, est également leur supérieur indirect.

L'arbre généalogique

Un arbre généalogique est un autre exemple courant de données hiérarchiques. Sa structure nous permet de trouver les ancêtres et leurs descendants. Un arbre généalogique peut ressembler à ceci :

Dans cet exemple, Mike Strongbow a épousé Victoria Stromboli. Ils ont eu deux enfants, Florence et Claudio. Florence a eu un enfant (Valerie), et Claudio a eu deux enfants (Art et Michelle). Tous trois sont des enfants pour leurs parents, mais ils sont aussi des petits-enfants pour Mike et Victoria. Mike et Victoria sont également arrière-grands-parents ; leur petite-fille a eu deux enfants, Judy et James.

Menu des boissons

Si vous allez dans un bar, vous avez probablement rencontré une structure hiérarchique. Je parle de celle que vous regardez généralement immédiatement après avoir pris place : la carte des boissons, ou carte, ou peu importe comment vous l'appelez. Par exemple, si vous visitez le Panthelya Bar, vous constaterez qu'il s'agit d'un bar très primitif qui ne propose que des bières et des vins.

Le bar propose deux types de bières : l'ale et la bière de blé. Il y a beaucoup de bières que vous pouvez commander dans chaque catégorie. La catégorie des vins est divisée en rouges et blancs, avec quatre vins dans chaque catégorie.

Taxonomie des êtres vivants

Même vous, la personne qui lit cet article, êtes un dossier dans les données hiérarchiques. En tant qu'être humain, vous occupez une certaine position dans la hiérarchie de la taxonomie des êtres vivants.

Le schéma ci-dessus montre la hiérarchie pour les humains, ou homo sapiens. Il serait trop compliqué de montrer l'ensemble de la taxonomie des êtres vivants. Cependant, le principe est le même que dans tous les diagrammes ci-dessus. Tous les humains appartiennent à l'espèce Sapiens, qui fait partie du genre Homo. Ce genre fait partie de la famille des Hominidés, une des familles de l'ordre des Primates. Les primates appartiennent à la classe des Mammifères, qui est subordonnée à l'embranchement, au règne et, enfin, au domaine.

Les dossiers sur votre ordinateur

Si vous lisez cet article, il y a de fortes chances que vous le fassiez sur votre ordinateur. En tenant compte de cela, il est tout à fait possible que les dossiers de votre ordinateur ressemblent à quelque chose comme ceci :

Tous vos dossiers dans cet exemple (Learning et Freelancing) se trouvent sur votre disque D :. Le dossier Learning comporte deux sous-dossiers : SQL, où vous mettez tous les articles intéressants liés à SQL comme celui-ci, et Python. Votre dossier Freelancing contient trois sous-dossiers : Jobs, Invoices, et Other documents.

Maintenant que vous savez comment reconnaître les données hiérarchiques, voyons comment elles sont stockées dans la base de données et comment les interroger.

Stockage des données hiérarchiques dans une base de données

Le problème des données hiérarchiques se pose généralement lorsque vous essayez de les enregistrer dans une base de données. Pour ce faire, vous devez rassembler toutes ces données multi-niveaux dans un format relativement plat : une table. Comment convertir des données hiérarchiques en simples lignes de données ?

Pour stocker les données hiérarchiques dans une base de données, il y a généralement une colonne qui fait référence à la même table. Qu'est-ce que cela signifie ? Il est probablement préférable que je vous montre un exemple. La hiérarchie des employés semble tout à fait appropriée pour cela !

Interrogation de données hiérarchiques à l'aide d'une auto-jonction

Je vais vous montrer comment interroger une hiérarchie d'employés. Supposons que nous ayons une table nommée employee contenant les données suivantes :

• employee_id - L'ID de l'employé et la clé primaire (PK) de la table.
• first_name - Le prénom de l'employé.
• last_name - Le nom de famille de l'employé.
• reports_to - L'ID du superviseur ou du responsable immédiat de cet employé.

La colonne reports_to n'est rien d'autre que la colonne employee_id qui sert d'outil pour montrer quel employé dépend de quel employé. Si l'ID de l'employé apparaît dans la colonne reports_to, cet employé est le patron de (au moins certains) autres employés. Laissez-moi vous montrer comment cela fonctionne :

Nous voyons que Sharon Simon est sous les ordres de l'employée qui a employee_id = 6, Martina Novak. Dans son cas, la valeur reports_to est NULL. Cela signifie que Martina Novak ne rend compte à personne. Nous pouvons donc en conclure qu'elle se trouve au sommet de la hiérarchie des employés.

C'est la partie où vous allez auto-joindre la table. Vous ne savez pas ce qu'est une auto-jonction ? Vous pouvez facilement l'apprendre en lisant cet article qui contient sept exemples d'auto-jonction. Rappelez-vous, j'ai déjà mentionné que les données hiérarchiques dans une base de données ont généralement une colonne qui fait référence à la même table. Voici un tel exemple. Pour obtenir les subordonnés directs de la table employeevous devez écrire la requête suivante :

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;

Cette requête joint la table employee avec elle-même. Laissez-moi vous expliquer comment cela fonctionne. Lorsque vous joignez une table à elle-même, vous devez utiliser des alias clairs afin que SQL sache quelles données proviennent de quelle table - et que vous sachiez quelles données proviennent de quelle table. Dans la requête ci-dessus, un alias de table est sub. Cela signifie qu'il s'agit de la table contenant les données des subordonnés. L'autre alias est supce qui signifie qu'il s'agit de la table contenant les données des supérieurs. Même s'il s'agit de la même table, nous la traitons comme s'il s'agissait de deux tables différentes.

Ainsi, la requête ci-dessus sélectionne d'abord les colonnes employee_id, first_name et last_name dans la table sub table. Ensuite, elle prend les mêmes données dans la table sup. Ainsi, la table employee est ensuite jointe à elle-même en utilisant les deux alias. L'auto-jointure est réalisée lorsque la colonne reports_to de la table sub est égale à la colonne employee_id de la table sup. Les données sont enfin ordonnées par la colonne superior_id.

Voici le résultat :

Le tableau montre que Kim Magnus, Sharon Simon et Vincent Trafalgar sont des subordonnés directs de Martina Novak. Victor Fonseca rend compte à Patricia Rooney. A son tour, Patricia Rooney est un subordonné direct de Sharon Simon, tout comme James Palin.

Utilisation de Requêtes récursives sur des données hiérarchiques profondes

Dans l'exemple ci-dessus, je vous ai montré comment trouver des supérieurs/subordonnés directs. Cela signifie que vous avez appris à ne regarder qu'un niveau au-dessus ou au-dessous. Bien que cela soit très utile, les hiérarchies peuvent être très profondes et comporter un nombre immense de niveaux. Avant d'interroger de telles données, vous devez vous familiariser avec les requêtes récursives. Parlons d'abord des requêtes récursives, puis je vous montrerai comment elles fonctionnent à l'aide d'un ou deux exemples.

Qu'est-ce que Requêtes récursives?

Si vous voulez connaître les requêtes récursives, vous devez d'abord vous familiariser avec les Common Table Expressions, ou CTE.

Une CTE est un ensemble de données temporaire renvoyé par une requête, qui est ensuite utilisé par une autre requête. Il est temporaire car le résultat n'est stocké nulle part ; il n'existe que lorsque la requête est exécutée. Les CTE peuvent être non récursifs et récursifs. J'ai déjà écrit un article sur les CTE (non récursifs) et sur le moment où il faut les utiliser; n'hésitez pas à le consulter plus tard pour plus d'informations.
Une requête récursive est une requête qui se référence elle-même. Ce faisant, elle renvoie le sous-résultat et répète le processus jusqu'à ce qu'elle renvoie le résultat final. Suivant cette logique, un ETC récursif est un ETC qui se référence lui-même.

Syntaxe des CTE récursifs

La syntaxe générale des CTE récursifs ressemble à ceci et peut être divisée en trois parties :

WITH RECURSIVE cte_name AS (
   cte_query_definition

   UNION ALL

   cte_query_definition
   )


SELECT *
FROM cte_name;

Les CTE sont également appelés "requêtes AVEC". Si vous examinez la syntaxe ci-dessus, vous comprendrez pourquoi : l'ETC commence toujours par la clause WITH. Si vous voulez que votre CTE soit récursif, vous devez le faire suivre du mot RECURSIVE. Ensuite, vous définissez le nom de l'ETC.

Ensuite, vous devez écrire la définition de la requête CTE. Cette partie de la requête est appelée le membre d'ancrage. Elle est "connectée" à l'autre CTE à l'aide de UNION ALL. Cette deuxième définition de requête CTE est appelée membre récursif et fait référence au CTE lui-même.

À la fin vient l'instruction SELECT, qui récupère les données du CTE. Cette partie de la requête s'appelle l'invocation.

La syntaxe s'apprend toujours mieux lorsqu'on la voit dans un exemple. Voici donc votre premier exemple de CTE récursif !

Interrogation de la hiérarchie des employés

Je veux m'appuyer sur l'exemple où vous avez appris à faire l'auto-jonction de la employee tableau. Je vais maintenant utiliser la même table, mais cette fois-ci, nous allons utiliser une requête récursive. La tâche consiste à trouver le patron direct et indirect de chaque employé. Cette relation entre les employés sera représentée sous la forme d'un chemin qui mène du patron au sommet (le propriétaire) à chaque employé de la table.

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;

Comme vous l'avez déjà appris, vous commencez à écrire un CTE récursif en utilisant WITH RECURSIVE. Ensuite, vous nommez le CTE. Dans ce cas, il s'agit de employee_hierarchy.

Le membre d'ancrage du CTE est la première instruction SELECT. En faisant cela, vous sélectionnez la racine de la hiérarchie ; c'est la base sur laquelle la requête récursive va travailler sa magie et trouver tous les autres niveaux de la hiérarchie. Cette instruction sélectionne toutes les colonnes de la table employee. Elle ajoute également la nouvelle colonne pathdont la valeur est 'Owner'. La clause WHERE signifie que cette opération sera effectuée uniquement pour les lignes dont la valeur de la colonne reports_to est NULL. Pourquoi cela ? S'il y a une valeur NULL dans la colonne reports_to, l'employé ne rend compte à personne. Cela signifie que c'est le propriétaire de l'entreprise.

L'étape suivante consiste à "connecter" le membre d'ancrage au membre récursif de l'ETC avec UNION ALL. L'élément important de l'utilisation de UNION ALL est que les instructions SELECT que vous "connectez" doivent avoir le même nombre de colonnes. Sinon, UNION ALL ne fonctionnera pas.

Le membre récursif est la deuxième instruction SELECT. Cette instruction sélectionne à nouveau toutes les colonnes de la table employee. Elle prend également la valeur (qui est 'Owner') du chemin de colonne de employee_hierarchy CTE. Elle y ajoute '->', suivi de la valeur de la colonne last_name de la table. employee. (Le || est un opérateur de concaténation ; il combine deux ou plusieurs valeurs en une seule). Il s'agira d'un chemin menant du propriétaire à chaque employé.

La table employee et le CTE employee_hierarchy sont joints comme n'importe quelles autres tables. Cela se fait lorsque la colonne reports_to est égale à la colonne employee_id. Le membre récursif sert d'extension du membre d'ancrage. Cela signifie qu'il étend un résultat qui a déjà été trouvé (par le membre d'ancrage) avec de nouveaux résultats. Ainsi, le membre récursif effectuera tout ce qui est décrit jusqu'à ce qu'il atteigne le dernier employé.

Enfin, la partie invocation simple sélectionne toutes les données du CTE employee_hierarchy. Et voilà ! Le résultat est le suivant :

Si vous regardez, par exemple, Victor Fonseca, vous pouvez voir que le chemin du propriétaire à lui passe par Sharon Simon et Patricia Rooney.

Pratiquons les requêtes récursives sur un autre exemple !

Interrogation de la hiérarchie des dossiers

Les entreprises disposent généralement de lecteurs réseau sur lesquels les employés enregistrent tout leur travail. Cela conduit généralement à une structure arborescente très ramifiée de dossiers. Les données relatives aux dossiers sont stockées dans la table folder. Ses colonnes sont les suivantes :

• id - L'ID du dossier et la clé primaire (PK) de la table.
• name - Le nom du dossier.
• subfolder_of - Le nom du dossier d'un niveau supérieur.

Pour trouver le chemin d'accès de tous les dossiers, vous aurez besoin de la requête suivante :

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;

Le principe est le même que dans l'exemple précédent. De nouveau, on commence par WITH RECURSIVE et le nom : folder_hierarchy. La première instruction SELECT sélectionne les trois colonnes de la table folder. La quatrième colonne est path, qui contient les données du nom de la colonne exprimées en valeurs de texte. Les données sont converties pour correspondre au type de données du membre récursif du CTE. Enfin, la clause WHERE limite les données à celles qui ont les valeurs NULL dans la colonne subfolder_of. Lorsqu'il y a NULL, il y a le dossier racine (c'est-à-dire celui qui n'a pas de dossiers au-dessus de lui).

Le site UNION ALL est à nouveau utilisé pour "connecter" les membres d'ancrage et récursifs du CTE. Une autre chose à retenir : les types de données des deux instructions SELECT doivent être les mêmes pour que UNION ALL fonctionne. Sinon, la requête renverra une erreur.

La deuxième instruction SELECT sélectionne à nouveau toutes les colonnes de la table folder. Les valeurs des colonnes path et name sont assemblées, '\' séparant les données.

Enfin, toutes les données de l'ETC sont sélectionnées, ce qui donne un beau tableau :

Maintenant, chaque dossier a son chemin d'accès et peut être facilement consulté. Plus besoin de parcourir plusieurs niveaux de dossiers pour trouver ce dont vous avez besoin !

Je pense que ce sont des exemples convaincants de la puissance des requêtes récursives. Si ce n'est pas le cas, il y a toujours une chance que vous ayez besoin d'un article supplémentaire pour voir le véritable potentiel des requêtes récursives. Et si vous êtes un utilisateur d'Oracle, voici comment vous pouvez utiliser les requêtes hiérarchiques dans ce SGBD particulier.

L'interrogation de données hiérarchiques est plus facile que jamais !

Maintenant que vous savez ce que sont les données hiérarchiques, vous allez commencer à les reconnaître un peu partout. Je vous ai donné plusieurs exemples quotidiens, mais je suis sûr que vous en trouverez encore plus. Essayez de mettre en œuvre les auto-joints, les CTE récursifs et tout ce que vous avez appris ici. C'est la seule façon de garder vos connaissances à jour !

Si vous manquez d'exercices pratiques, vous en trouverez beaucoup dans notre cours Requêtes récursives . Que pouvez-vous apprendre dans ce cours ? En quoi les requêtes récursives peuvent-elles vous aider ? Inutile de se poser la question : notre responsable du contenu explique tout en détail dans cet article.