Catégories :

Syntaxe de requête

CONNECT BY

Joint une table à elle-même pour traiter les données hiérarchiques de la table. La sous-clause CONNECT BY de la clause FROM se répète pour traiter les données.

Par exemple, vous pouvez créer une requête qui montre un « éclatement de nomenclature » pour répertorier de manière récursive un composant et les sous-composants de ce composant.

La syntaxe Snowflake pour CONNECT BY est principalement compatible avec la syntaxe Oracle.

Voir aussi :

WITH

Syntaxe

La forme générale d’une instruction avec CONNECT BY est similaire à celle-ci (certaines variations dans l’ordre sont autorisées mais ne sont pas affichées) :

SELECT <column_list> [ , <level_expression> ]
  FROM <data_source>
    START WITH <predicate>
    CONNECT BY [ PRIOR ] <col1_identifier> = [ PRIOR ] <col2_identifier>
           [ , [ PRIOR ] <col3_identifier> = [ PRIOR ] <col4_identifier> ]
           ...
  ...
liste_colonne

Cela suit généralement les règles de la clause de projection d’une instruction SELECT.

niveau_expression

Les requêtes CONNECT BY autorisent certaines pseudo-colonnes. Une de ces pseudo-colonnes est LEVEL, qui indique le niveau actuel de la hiérarchie (où le niveau 1 représente le sommet de la hiérarchie). La clause de projection de la requête peut utiliser LEVEL en tant que colonne.

source_données

La source de données est généralement une table, mais peut être une autre source de données semblable à une table, telle qu’une vue, UDTF, etc.

prédicat

Le prédicat est une expression qui sélectionne le premier « niveau » de la hiérarchie (par exemple, le président de la société ou le composant de niveau supérieur dans un éclatement de nomenclature). Le prédicat doit ressembler à une clause WHERE, mais sans le mot clé WHERE.

Consultez la section Exemples (dans cette rubrique) pour obtenir des exemples de prédicats.

colN_identificateur

La clause CONNECT BY doit contenir une ou plusieurs expressions similaires à celles utilisées dans les jointures. Plus précisément, une colonne du niveau « actuel » de la table doit faire référence à une colonne du niveau « antérieur » (supérieur) de la table.

Par exemple, dans une hiérarchie gestionnaire/employé, la clause peut ressembler à ceci :

... CONNECT BY manager_ID = PRIOR employee_ID ...

Le mot clé PRIOR indique que la valeur doit provenir du niveau antérieur (supérieur/parent).

Dans cet exemple, l’ID du responsable de l’employé actuel doit correspondre à l’ID de l’employé du niveau précédent.

La clause CONNECT BY peut contenir plusieurs expressions de ce type, par exemple :

... CONNECT BY y = PRIOR x AND b = PRIOR a ...

Chaque expression similaire à la suivante devrait avoir exactement une occurrence du mot clé PRIOR :

CONNECT BY <col_1_identifier> = <col_2_identifier>

Par exemple :

CONNECT BY <col_1_identifier> = PRIOR <col_2_identifier>

ou

CONNECT BY PRIOR <col_1_identifier> = <col_2_identifier>

Notes sur l’utilisation

  • Une clause CONNECT BY joint toujours une table à elle-même, pas à une autre table.

  • Par défaut, pour empêcher les requêtes d’effectuer une boucle infinie, le nombre d’itérations est limité.

  • Certaines variations dans la clause de projection sont valides. Bien que la syntaxe indique que expression_niveau se produit après la liste_colonnes, la ou les expressions de niveau peuvent apparaître dans n’importe quel ordre.

  • Le mot clé PRIOR doit apparaître exactement une fois dans chaque expression CONNECT BY. PRIOR peut apparaître du côté gauche ou du côté droit de l’expression, mais pas des deux.

  • L’implémentation Snowflake de CONNECT BY est principalement compatible avec l’implémentation Oracle. Cependant, Snowflake ne prend pas en charge :

    • NOCYCLE

    • CONNECT_BY_ISCYCLE

    • CONNECT_BY_ISLEAF

  • Snowflake prend en charge la fonction SYS_CONNECT_BY_PATH lorsqu’elle est utilisée avec la clause CONNECT BY. SYS_CONNECT_BY_PATH renvoie une chaîne contenant le chemin d’accès de la racine à l’élément actuel. Un exemple est inclus dans la section Exemples ci-dessous.

  • Snowflake prend en charge l’opérateur CONNECT_BY_ROOT lorsqu’il est utilisé avec la clause CONNECT BY. L’opérateur CONNECT_BY_ROOT permet au niveau actuel d’utiliser les informations du niveau racine de la hiérarchie, même si le niveau racine n’est pas le parent immédiat du niveau actuel. Un exemple est inclus dans la section Exemples ci-dessous.

  • Bien qu’en théorie, la clause CONNECT BY puisse se répéter autant de fois que nécessaire pour traiter les données, Snowflake limite actuellement le nombre d’itérations à 100 pour empêcher la requête de s’exécuter indéfiniment si la requête est construite de manière incorrecte.

Exemples

Cet exemple utilise un CONNECT BY pour afficher la hiérarchie de gestion dans une table d’informations sur les employés. La table et les données sont indiquées ci-dessous :

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);

La requête et la sortie sont indiquées ci-dessous :

SELECT employee_ID, manager_ID, title
  FROM employees
    START WITH title = 'President'
    CONNECT BY
      manager_ID = PRIOR employee_id
  ORDER BY employee_ID;
+-------------+------------+----------------------------+
| EMPLOYEE_ID | MANAGER_ID | TITLE                      |
|-------------+------------+----------------------------|
|           1 |       NULL | President                  |
|          10 |          1 | Vice President Engineering |
|          20 |          1 | Vice President HR          |
|         100 |         10 | Programmer                 |
|         101 |         10 | QA Engineer                |
|         200 |         20 | Health Insurance Analyst   |
+-------------+------------+----------------------------+

Cet exemple utilise la fonction SYS_CONNECT_BY_PATH pour afficher la hiérarchie du président au salarié actuel :

SELECT SYS_CONNECT_BY_PATH(title, ' -> '), employee_ID, manager_ID, title
  FROM employees
    START WITH title = 'President'
    CONNECT BY
      manager_ID = PRIOR employee_id
  ORDER BY employee_ID;
+----------------------------------------------------------------+-------------+------------+----------------------------+
| SYS_CONNECT_BY_PATH(TITLE, ' -> ')                             | EMPLOYEE_ID | MANAGER_ID | TITLE                      |
|----------------------------------------------------------------+-------------+------------+----------------------------|
|  -> President                                                  |           1 |       NULL | President                  |
|  -> President -> Vice President Engineering                    |          10 |          1 | Vice President Engineering |
|  -> President -> Vice President HR                             |          20 |          1 | Vice President HR          |
|  -> President -> Vice President Engineering -> Programmer      |         100 |         10 | Programmer                 |
|  -> President -> Vice President Engineering -> QA Engineer     |         101 |         10 | QA Engineer                |
|  -> President -> Vice President HR -> Health Insurance Analyst |         200 |         20 | Health Insurance Analyst   |
+----------------------------------------------------------------+-------------+------------+----------------------------+

Cet exemple utilise le mot clé CONNECT_BY_ROOT pour afficher les informations du haut de la hiérarchie dans chaque ligne de la sortie :

SELECT 
employee_ID, manager_ID, title,
CONNECT_BY_ROOT title AS root_title
  FROM employees
    START WITH title = 'President'
    CONNECT BY
      manager_ID = PRIOR employee_id
  ORDER BY employee_ID;
+-------------+------------+----------------------------+------------+
| EMPLOYEE_ID | MANAGER_ID | TITLE                      | ROOT_TITLE |
|-------------+------------+----------------------------+------------|
|           1 |       NULL | President                  | President  |
|          10 |          1 | Vice President Engineering | President  |
|          20 |          1 | Vice President HR          | President  |
|         100 |         10 | Programmer                 | President  |
|         101 |         10 | QA Engineer                | President  |
|         200 |         20 | Health Insurance Analyst   | President  |
+-------------+------------+----------------------------+------------+

Cet exemple utilise un CONNECT BY pour afficher un « éclatement de nomenclature » :

Voici les données :

-- The components of a car.
CREATE TABLE components (
    description VARCHAR,
    quantity INTEGER,
    component_ID INTEGER,
    parent_component_ID INTEGER
    );

INSERT INTO components (description, quantity, component_ID, parent_component_ID) VALUES
    ('car', 1, 1, 0),
       ('wheel', 4, 11, 1),
          ('tire', 1, 111, 11),
          ('#112 bolt', 5, 112, 11),
          ('brake', 1, 113, 11),
             ('brake pad', 1, 1131, 113),
       ('engine', 1, 12, 1),
          ('piston', 4, 121, 12),
          ('cylinder block', 1, 122, 12),
          ('#112 bolt', 16, 112, 12)   -- Can use same type of bolt in multiple places
    ;

Voici la requête et la sortie :

SELECT
  description,
  quantity,
  component_id, 
  parent_component_ID,
  SYS_CONNECT_BY_PATH(component_ID, ' -> ') AS path
  FROM components
    START WITH component_ID = 1
    CONNECT BY 
      parent_component_ID = PRIOR component_ID
  ORDER BY path
  ;
+----------------+----------+--------------+---------------------+----------------------------+
| DESCRIPTION    | QUANTITY | COMPONENT_ID | PARENT_COMPONENT_ID | PATH                       |
|----------------+----------+--------------+---------------------+----------------------------|
| car            |        1 |            1 |                   0 |  -> 1                      |
| wheel          |        4 |           11 |                   1 |  -> 1 -> 11                |
| tire           |        1 |          111 |                  11 |  -> 1 -> 11 -> 111         |
| #112 bolt      |        5 |          112 |                  11 |  -> 1 -> 11 -> 112         |
| brake          |        1 |          113 |                  11 |  -> 1 -> 11 -> 113         |
| brake pad      |        1 |         1131 |                 113 |  -> 1 -> 11 -> 113 -> 1131 |
| engine         |        1 |           12 |                   1 |  -> 1 -> 12                |
| #112 bolt      |       16 |          112 |                  12 |  -> 1 -> 12 -> 112         |
| piston         |        4 |          121 |                  12 |  -> 1 -> 12 -> 121         |
| cylinder block |        1 |          122 |                  12 |  -> 1 -> 12 -> 122         |
+----------------+----------+--------------+---------------------+----------------------------+