Interrogation des données hiérarchiques¶

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.

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
    ORDER BY employees.title;
+----------------------------+-------------+------------+---------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MANAGER TITLE |
|----------------------------+-------------+------------+---------------|
| Vice President Engineering |          10 |          1 | President     |
| Vice President HR          |          20 |          1 | President     |
+----------------------------+-------------+------------+---------------+

Copy

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          |
+----------------------------+-------------+------------+----------------------------+

Copy

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 :

• WITH et Utilisation de CTEs (expressions de table communes).

• CONNECT BY.

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 voyez 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ë.