Utilisation de CTEs (expressions de table communes)¶

Potentiel pour des boucles infinies¶

La construction incorrecte d’une CTE récursive peut provoquer une boucle infinie. Dans ce cas, la requête continue de s’exécuter jusqu’à ce qu’elle réussisse, qu’elle expire (c’est-à-dire qu’elle dépasse le nombre de secondes spécifié par le paramètre STATEMENT_TIMEOUT_IN_SECONDS) ou que vous annuliez la requête.

Pour plus d’informations sur la façon dont les boucles infinies peuvent se produire et pour des instructions sur la façon d’éviter ce problème, voir Dépannage d’une CTE récursive.

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 des CTEs récursives. Dans tous les exemples de ce chapitre, les hiérarchies sont contiguës.

Pour plus d’informations sur les hiérarchies non contiguës, voir Interrogation des données hiérarchiques.

Sortie non ordonnée¶

Voici le premier exemple.

 1)    WITH RECURSIVE managers
 2)          (indent, employee_ID, manager_ID, employee_title)
 3)        AS
 4)          (
 5)
 6)            SELECT '' AS indent, employee_ID, manager_ID, title AS employee_title
 7)              FROM employees
 8)              WHERE title = 'President'
 9)
10)            UNION ALL
11)
12)           SELECT indent || '--- ',
13)               employees.employee_ID, employees.manager_ID, employees.title
14)             FROM employees JOIN managers
15)               ON employees.manager_ID = managers.employee_ID
16)         )
17)
18)     SELECT indent || employee_title AS Title, employee_ID, manager_ID
19)       FROM managers
20)     ;

La requête comprend les sections suivantes :

• La ligne 2 contient les noms des colonnes pour la « vue » (CTE).

• Les lignes 4 - 16 contiennent le CTE.

• Les lignes 6 - 8 contiennent la clause d’ancrage du CTE.

• Les lignes 12 - 15 contiennent la clause récursive du CTE.

• Les lignes 18 - 19 contiennent l’instruction SELECT principale qui utilise la CTE comme vue. Cette instruction SELECT fait référence :

  • Au nom de la CTE (managers), défini dans la ligne 1.

  • Aux colonnes de la CTE(indent, employee_id, etc.) définies à la ligne 2.

La CTE contient deux instructions SELECT :

• L’instruction SELECT dans la clause d’ancrage est exécutée une fois et fournit l’ensemble des lignes du premier (haut) niveau de la hiérarchie.

• L’instruction SELECT de la clause récursive peut faire référence à la CTE. Vous pouvez considérer la requête comme une itération, chaque itération reposant sur les résultats de la requête des itérations précédentes.

Dans l’exemple responsable/employé, la clause d’ancrage émet la première ligne, c’est-à-dire la ligne décrivant le président de la société.

Lors de la prochaine itération de la clause récursive, la clause récursive recherche toutes les lignes dont le responsable est le président de la société (c’est-à-dire tous les vice-présidents). La 3e itération recherche tous les employés dont le responsable est l’un des vice-présidents. L’itération se poursuit jusqu’à ce qu’il y ait une itération dans laquelle toutes les lignes extraites sont des lignes d’employés au niveau feuille qui ne gèrent personne. L’instruction effectue une itération supplémentaire en recherchant (sans trouver) les employés dont les responsables sont des employés au niveau feuille. Cette itération produit 0 ligne et l’itération s’arrête.

Tout au long de ces itérations, la clause UNION ALL accumule les résultats. Les résultats de chaque itération sont ajoutés aux résultats des itérations précédentes. Une fois la dernière itération terminée, les lignes accumulées (comme toutes les lignes produites dans une clause WITH) sont mises à la disposition de la clause principale SELECT de la requête. Cet élément principal SELECT peut alors interroger ces lignes.

Cet exemple de requête utilise l’indentation pour montrer la nature hiérarchique des données. Si vous regardez la sortie, vous voyez que plus le niveau de l’employé est bas, plus les données de cet employé sont en retrait.

L’indentation est contrôlée par la colonne nommée indent. L’indentation commence à 0 caractère (une chaîne vide dans la clause d’ancrage) et augmente de 4 caractères (---) pour chaque itération (c’est-à-dire pour chaque niveau de la hiérarchie).

Sans surprise, il est très important de construire correctement la ou les jointures et de sélectionner les colonnes correctes dans la clause récursive. Les colonnes de SELECT de la clause récursive doivent correspondre correctement aux colonnes de la clause d’ancrage. Rappelez-vous que la requête commence par le président, puis sélectionne les vice-présidents, puis sélectionne les personnes relevant directement des vice-présidents, etc. Chaque itération recherche les employés dont le champ manager_id correspond à l’une des valeurs managers.employee_id générées lors de l’itération précédente.

En d’autres termes, l’ID d’employé de la « vue » des responsables est l’ID de responsable du niveau suivant d’employés. Les IDs d’employé doivent progresser dans la hiérarchie (président, vice-président, directeur principal, directeur junior, etc.) au cours de chaque itération. Si les IDs d’employé ne progressent pas, la requête peut effectuer une boucle à l’infini (si le même manager_ID continue d’apparaître dans la colonne managers.employee_ID à différentes itérations), ou ignorer un niveau, ou échouer d’une autre manière.

Sortie ordonnée¶

L’exemple précédent ne comportait pas de clause ORDER BY. Ainsi, même si le dossier de chaque employé est correctement mis en retrait, chaque employé ne figurait pas nécessairement directement sous son responsable. L’exemple ci-dessous génère une sortie avec l’indentation correcte et avec les employés de chaque responsable directement sous ce dernier.

La clause ORDER BY de la requête utilise une colonne supplémentaire, nommée sort_key. La clé de tri s’accumule à mesure que la clause récursive se répète ; vous pouvez considérer la clé de tri comme une chaîne contenant toute la chaîne de commandes située au-dessus de vous (votre responsable, le responsable de votre responsable, etc.). La personne la plus ancienne de cette chaîne de commandement (le président) se trouve au début de la chaîne de clé de tri. Bien que la clé de tri ne soit normalement pas affichée, la requête ci-dessous inclut la clé de tri dans le résultat afin de faciliter la compréhension du résultat.

Chaque itération doit augmenter la longueur de la clé de tri de la même quantité (même nombre de caractères). La requête utilise donc une UDF (fonction définie par l’utilisateur) nommée skey, avec la définition suivante, pour générer des segments de longueur cohérente de la clé de tri :

CREATE OR REPLACE FUNCTION skey(ID VARCHAR)
  RETURNS VARCHAR
  AS
  $$
    SUBSTRING('0000' || ID::VARCHAR, -4) || ' '
  $$
  ;

Copy

Voici un exemple de sortie de la fonction SKEY :

SELECT skey(12);
+----------+
| SKEY(12) |
|----------|
| 0012     |
+----------+

Copy

Voici la version finale de la requête. Cela met les employés de chaque responsable immédiatement sous celui-ci et indente en fonction du « niveau » de l’employé :

WITH RECURSIVE managers 
      -- Column list of the "view"
      (indent, employee_ID, manager_ID, employee_title, sort_key) 
    AS 
      -- Common Table Expression
      (
        -- Anchor Clause
        SELECT '' AS indent, 
            employee_ID, manager_ID, title AS employee_title, skey(employee_ID)
          FROM employees
          WHERE title = 'President'

        UNION ALL

        -- Recursive Clause
        SELECT indent || '--- ',
            employees.employee_ID, employees.manager_ID, employees.title, 
            sort_key || skey(employees.employee_ID)
          FROM employees JOIN managers 
            ON employees.manager_ID = managers.employee_ID
      )

  -- This is the "main select".
  SELECT 
         indent || employee_title AS Title, employee_ID, 
         manager_ID, 
         sort_key
    FROM managers
    ORDER BY sort_key
  ;
+----------------------------------+-------------+------------+-----------------+
| TITLE                            | EMPLOYEE_ID | MANAGER_ID | SORT_KEY        |
|----------------------------------+-------------+------------+-----------------|
| President                        |           1 |       NULL | 0001            |
| --- Vice President Engineering   |          10 |          1 | 0001 0010       |
| --- --- Programmer               |         100 |         10 | 0001 0010 0100  |
| --- --- QA Engineer              |         101 |         10 | 0001 0010 0101  |
| --- Vice President HR            |          20 |          1 | 0001 0020       |
| --- --- Health Insurance Analyst |         200 |         20 | 0001 0020 0200  |
+----------------------------------+-------------+------------+-----------------+

Copy

La requête suivante montre comment référencer un champ du niveau précédent (le plus élevé) dans la hiérarchie ; prêtez une attention particulière à la colonne mgr_title :

WITH RECURSIVE managers 
      -- Column names for the "view"/CTE
      (employee_ID, manager_ID, employee_title, mgr_title) 
    AS
      -- Common Table Expression
      (

        -- Anchor Clause
        SELECT employee_ID, manager_ID, title AS employee_title, NULL AS mgr_title
          FROM employees
          WHERE title = 'President'

        UNION ALL

        -- Recursive Clause
        SELECT 
            employees.employee_ID, employees.manager_ID, employees.title, managers.employee_title AS mgr_title
          FROM employees JOIN managers 
            ON employees.manager_ID = managers.employee_ID
      )

  -- This is the "main select".
  SELECT employee_title AS Title, employee_ID, manager_ID, mgr_title
    FROM managers
    ORDER BY manager_id NULLS FIRST, employee_ID
  ;
+----------------------------+-------------+------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MGR_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

Cause 1 : hiérarchie cyclique des données¶

Si votre hiérarchie de données contient un cycle (c’est-à-dire qu’il ne s’agit pas d’une véritable arborescence), plusieurs solutions sont possibles :

Solution 1.1:

Si les données ne sont pas censées contenir un cycle, corrigez-les.

Solution 1.2:

Limitez la requête d’une certaine manière (par exemple, limitez le nombre de lignes de sortie). Par exemple :

WITH RECURSIVE t(n) AS
    (
    SELECT 1
    UNION ALL
    SELECT N + 1 FROM t
   )
 SELECT n FROM t LIMIT 10;

Copy

Solution 1.3:

N’utilisez pas de requête contenant une CTE récursive, qui attend des données hiérarchiques.

Cause 2 : boucle infinie¶

Une boucle infinie peut se produire si la clause de projection de recursive_clause renvoie une valeur du « parent » (l’itération précédente) à la place de « l’enfant » (l’itération en cours), puis la prochaine itération utilise cette valeur dans une jointure lorsqu’elle devrait utiliser la valeur de l’itération en cours dans la jointure.

Le pseudo-code suivant en donne un exemple approximatif :

CREATE TABLE employees (employee_ID INT, manager_ID INT, ...);
INSERT INTO employees (employee_ID, manager_ID) VALUES
        (1, NULL),
        (2, 1);

WITH cte_name (employee_ID, manager_ID, ...) AS
  (
     -- Anchor Clause
     SELECT employee_ID, manager_ID FROM table1
     UNION ALL
     SELECT manager_ID, employee_ID   -- <<< WRONG
         FROM table1 JOIN cte_name
           ON table1.manager_ID = cte_name.employee_ID
  )
SELECT ...

Copy

Dans cet exemple, la clause récursive transmet sa valeur parente (manager_id) dans la colonne qui doit avoir la valeur actuelle/enfant (employee_id). Le parent apparaîtra en tant que valeur « actuelle » lors de la prochaine itération et sera de nouveau transmise en tant que valeur « actuelle » à la génération suivante, de sorte que la requête ne progresse jamais vers le bas des niveaux ; il garde le même niveau de traitement à chaque fois.

Étape 1:

Supposons que la clause d’ancrage sélectionne les valeurs employee_id = 1 et manager_id = NULL.

CTE:

employee_ID  manager_ID
-----------  ---------
      1         NULL

Copy

Étape 2:

Lors de la première itération de la clause récursive, employee_id = 2 et manager_id = 1 dans table1.

CTE:

employee_ID  manager_ID
-----------  ----------
       1         NULL

Copy

table1:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...

Copy

Résultat de la jointure dans la clause récursive :

table1.employee_ID  table1.manager_ID  cte.employee_ID  cte.manager_ID
-----------------   -----------------  ---------------  --------------
 ...
       2                   1                 1                NULL
 ...

Copy

Projection :

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...

Copy

Cependant, comme les colonnes employee_id et manager_id sont inversées dans la projection, la sortie réelle de la requête (et donc le contenu de la CTE au début de la prochaine itération) est la suivante :

employee_ID  manager_ID
-----------  ----------
 ...
       1         2        -- Because manager and employee IDs reversed
 ...

Copy

Étape 3:

Lors de la deuxième itération de la clause récursive :

CTE:

employee_ID  manager_ID
-----------  ----------
       1         2

Copy

table1:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...

Copy

Résultat de la jointure dans une clause récursive :

table1.employee_ID  table1.manager_ID  cte.employee_ID  cte.manager_ID
-----------------   -----------------  ---------------  --------------
 ...
       2                   1                 1                2
 ...

Copy

Projection :

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...

Copy

Résultat de la requête (contenu de la CTE au début de la prochaine itération) :

employee_ID  manager_ID
-----------  ----------
 ...
       1         2        -- Because manager and employee IDs reversed
 ...

Copy

Comme vous pouvez le constater, à la fin de la deuxième itération, la ligne dans la CTE est identique à celle du début de l’itération :

• employee_id est 1.

• manager_id est 2.

Ainsi, le résultat de la jointure lors de la prochaine itération sera identique à celui de la jointure lors de l’itération en cours, ainsi que la requête s’exécutant en boucle à l’infini.

Si vous avez créé une boucle infinie :

Solution 2:

Assurez-vous que la clause récursive transmet les variables correctes dans le bon ordre.

Assurez-vous également que la condition JOIN de la clause récursive est correcte. Dans un cas typique, le parent de la ligne « courante » doit être joint à la valeur enfant/actuelle de la ligne mère.