Interrogation des données hiérarchiques

Cette rubrique explique comment stocker et interroger des données hiérarchiques à l’aide de :

  • JOINs

  • CTEs récursives (expressions de table communes)

  • CONNECT BY

Voir aussi :

CONNECT BY , la partie CTE récursive de la commande WITH , Utilisation de CTEs (expressions de table communes) , Fonctions de table définies par l’utilisateur (UDTFs SQL)

Dans ce chapitre :

Stockage des données hiérarchiques

De nombreux types de données sont mieux représentés sous forme de hiérarchie, telle qu’une arborescence.

Par exemple, les employés sont généralement organisés dans une hiérarchie, avec un président d’entreprise au sommet de la hiérarchie.

Un autre exemple de hiérarchie est un « éclatement de nomenclature ». Par exemple, une voiture contient un moteur ; un moteur contient une pompe à carburant ; et une pompe à carburant contient un flexible.

Vous pouvez stocker des données hiérarchiques dans :

  • Une hiérarchie de tables.

  • Une seule table avec une (ou plusieurs) colonnes représentant la hiérarchie (indiquant par exemple le responsable direct de chaque employé).

Les deux techniques sont décrites ci-dessous.

Données hiérarchiques sur plusieurs tables

Les bases de données relationnelles stockent souvent des données hiérarchiques en utilisant différentes tables. Par exemple, une table peut contenir des données « mères » et une autre table peut contenir des données « enfants ». Lorsque toute la hiérarchie est connue à l’avance, une table peut être créée pour chaque couche de la hiérarchie.

Par exemple, considérons une base de données de ressources humaines qui stocke les informations relatives aux employés et aux responsables. Si la société est petite, il ne peut y avoir que deux niveaux, par exemple un responsable et deux employés.

CREATE OR REPLACE TABLE managers  (title VARCHAR, employee_ID INTEGER);
CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);
INSERT INTO managers (title, employee_ID) VALUES
    ('President', 1);
INSERT INTO employees (title, employee_ID, manager_ID) VALUES
    ('Vice President Engineering', 10, 1),
    ('Vice President HR', 20, 1);

Données hiérarchiques dans une seule table

Dans certaines situations, le nombre de niveaux dans la hiérarchie peut changer.

Par exemple, une entreprise qui a démarré avec une hiérarchie à deux niveaux (président et autres employés) peut augmenter le nombre de niveaux au fur et à mesure que l’entreprise grandit. L’entreprise pourrait se développer pour inclure un président, des vice-présidents et des employés réguliers.

Si le nombre de niveaux est inconnu, de sorte qu’il ne soit pas possible de créer une hiérarchie avec un nombre connu de tables, dans certains cas, les données hiérarchiques peuvent alors être stockées dans une seule table. Par exemple, une table unique peut contenir tous les employés et peut inclure une colonne qui stocke l’ID de responsable de chaque employé, qui pointe vers un autre employé de la même table. Par exemple :

CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);
INSERT INTO employees (title, employee_ID, manager_ID) VALUES
    ('President', 1, NULL),  -- The President has no manager.
        ('Vice President Engineering', 10, 1),
            ('Programmer', 100, 10),
            ('QA Engineer', 101, 10),
        ('Vice President HR', 20, 1),
            ('Health Insurance Analyst', 200, 20);

Le stockage de toute une hiérarchie de données dans une table donne de meilleurs résultats si tous les niveaux de la hiérarchie stockent les mêmes données. Dans notre exemple, l’ID d’employé, le titre, etc. Si les données à différents niveaux ne correspondent pas à la même structure d’enregistrement, le stockage de toutes les données dans une table peut ne pas être pratique.

Utilisation de jointures pour interroger des données hiérarchiques

Dans une hiérarchie à deux niveaux (par exemple, responsables et employés), les données peuvent être interrogées avec une jointure à double sens :

SELECT 
        employees.title, 
        employees.employee_ID, 
        managers.employee_ID AS MANAGER_ID, 
        managers.title AS "MANAGER TITLE"
    FROM employees, managers
    WHERE employees.manager_ID = managers.employee_ID;
+----------------------------+-------------+------------+---------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MANAGER TITLE |
|----------------------------+-------------+------------+---------------|
| Vice President Engineering |          10 |          1 | President     |
| Vice President HR          |          20 |          1 | President     |
+----------------------------+-------------+------------+---------------+

Dans une hiérarchie à trois niveaux, vous pouvez utiliser une jointure à trois sens :

SELECT
     emps.title,
     emps.employee_ID,
     mgrs.employee_ID AS MANAGER_ID, 
     mgrs.title AS "MANAGER TITLE"
  FROM employees AS emps LEFT OUTER JOIN employees AS mgrs
    ON emps.manager_ID = mgrs.employee_ID
  ORDER BY mgrs.employee_ID NULLS FIRST, emps.employee_ID;
+----------------------------+-------------+------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MANAGER TITLE              |
|----------------------------+-------------+------------+----------------------------|
| President                  |           1 |       NULL | NULL                       |
| Vice President Engineering |          10 |          1 | President                  |
| Vice President HR          |          20 |          1 | President                  |
| Programmer                 |         100 |         10 | Vice President Engineering |
| QA Engineer                |         101 |         10 | Vice President Engineering |
| Health Insurance Analyst   |         200 |         20 | Vice President HR          |
+----------------------------+-------------+------------+----------------------------+

Ce concept peut être étendu à autant de niveaux que nécessaire, pour autant que vous sachiez combien de niveaux sont nécessaires. Mais si le nombre de niveaux change, les requêtes doivent changer.

Utilisation de CONNECT BY ou de CTEs récursives pour interroger des données hiérarchiques

Snowflake propose deux méthodes pour interroger des données hiérarchiques dans lesquelles le nombre de niveaux n’est pas connu à l’avance :

  • CTEs récursives (expressions de table communes).

  • Clauses CONNECT BY.

Une CTE récursive vous permet de créer une clause WITH pouvant se référer à elle-même. Cela vous permet de parcourir chaque niveau de votre hiérarchie et d’accumuler les résultats.

Une clause CONNECT BY vous permet de créer un type d’opération JOIN qui traite la hiérarchie un niveau à la fois et permet à chaque niveau de faire référence aux données du niveau précédent.

Pour plus de détails, voir :

Différences entre auto-jointures, CTE récursives et clauses CONNECT BY

CONNECT BY autorise uniquement les auto-jointures. Les CTEs récursives sont plus souples et permettent de joindre une table à une ou plusieurs autres tables.

Une clause CONNECT BY a plus de pouvoir qu’une CTE récursive. Cependant, une CTE récursive peut faire certaines choses qu’un CONNECT BY ne peut pas.

Par exemple, si vous examinez les exemples de CTE récursives, vous verrez qu’une des requêtes indente la sortie et trie également la sortie de sorte que chaque « enfant » apparaisse sous le « parent » correspondant. Le tri est effectué en créant une clé de tri contenant la chaîne d’IDs du sommet jusqu’au plus bas niveau actuel. Dans l’exemple responsable/employé, la chaîne contient l’ID du président, suivi de l’ID du vice-président, etc. Cette clé de tri regroupe les lignes de manière similaire à une arborescence latérale. La syntaxe CONNECT BY ne prend pas cela en charge car la clause « START WITH » ne permet pas au code de spécifier des colonnes supplémentaires (autres que celles de la table), telles que la clé sort_key. Comparez les deux extraits de code ci-dessous :

SELECT indent(LEVEL) || employee_ID, manager_ID, title
  FROM employees
    -- This sub-clause specifies the record at the top of the hierarchy,
    -- but does not allow additional derived fields, such as the sort key.
    START WITH TITLE = 'President'
    CONNECT BY ...

WITH RECURSIVE current_layer
   (employee_ID, manager_ID, sort_key) AS (
     -- This allows us to add columns, such as sort_key, that are not part
     -- of the employees table.
     SELECT employee_ID, manager_ID, employee_ID AS sort_key
     ...
     )

Vous pouvez toutefois utiliser la fonction SYS_CONNECT_BY_PATH pour obtenir un effet similaire avec la clause CONNECT BY.

Bien que la version de la clause CONNECT BY soit limitée car la clause START WITH ne peut pas ajouter de colonnes à celles déjà présentes dans la ligne (même les colonnes dérivées basées sur des valeurs déjà présentes dans la ligne), elle présente également certains avantages :

  • Vous avez accès à toutes les colonnes de chaque ligne sans spécifier ces colonnes dans une liste de colonnes. Dans une CTE récursive, la clause récursive n’a pas accès aux colonnes qui ne sont pas explicitement spécifiées dans la CTE.

  • Dans une CTE récursive, vous devez spécifier les colonnes dans la CTE. Les listes de projection des sélections dans la clause d’ancrage et la clause récursive doivent correspondre aux colonnes contenues dans la CTE. Si l’ordre des colonnes dans les différentes clauses de projection ne correspond pas, vous pouvez créer des problèmes tels que des boucles infinies.

  • La syntaxe CONNECT BY prend en charge des pseudo-colonnes pratiques telles que LEVEL, CONNECT_BY_ROOT et CONNECT_BY_PATH

Une différence mineure entre CONNECT BY et une CTE récursive réside dans le fait que, dans CONNECT BY, vous utilisez le mot clé PRIOR pour indiquer quelles valeurs de colonne doivent être extraites de l’itération précédente, tandis que dans une CTE récursive, vous utilisez le nom de la table et le nom de la CTE pour indiquer quelles valeurs sont extraites de l’itération actuelle et lesquelles sont extraites de l’itération précédente. (Dans une CTE récursive, vous pouvez également faire la distinction entre les itérations actuelles et précédentes en utilisant des noms de colonnes différents dans la liste de colonnes CTE et dans la table source ou l’expression de table.)

Hiérarchies non contiguës

Cette rubrique a décrit les hiérarchies et a expliqué comment les relations parent-enfant peuvent être utilisées par les clauses récursives CTEs (expressions de table courantes) et CONNECT BY. Dans tous les exemples de cette rubrique, ainsi que dans la documentation CONNECT BY et dans la documentation sur les CTE récursives, les hiérarchies sont contiguës. Aucun des exemples n’a de parent et un petit-enfant sans avoir un enfant correspondant entre eux.

Par exemple, si vous faites un « éclatement de nomenclature » d’une voiture, vous ne disposerez pas d’un composant pour la voiture et d’un composant pour le pneu sans avoir un composant pour la roue qui contient le pneu (et celle contenue par la voiture).

Cependant, il peut y avoir des cas où les données sont incomplètes. Par exemple, dans une hiérarchie d’employés/responsables, supposons que le vice-président de l’ingénierie prenne sa retraite et que la société n’engage pas de remplaçant immédiatement. Si la fiche d’employé du VP est supprimée, les employés situés au-dessous du VP sont « coupés » du reste de la hiérarchie, de sorte que la table des employés ne contienne plus une seule hiérarchie contiguë.

Si vous utilisez des CTEs récursives ou une clause CONNECT BY pour traiter des données, vous devez déterminer si les données de votre table représentent une seule arborescence contigüe. Vous pouvez utiliser les CTEs récursives et CONNECT BY sur une seule table contenant plusieurs arborescences, mais vous ne pouvez interroger qu’une arborescence à la fois, et cette arborescence doit être contiguë.