Utilisation de CTEs (expressions de table communes)

Voir aussi :

CONNECT BY , WITH

Dans ce chapitre :

Qu’est-ce qu’une CTE ?

Une CTE (expression de table commune) est une sous-requête nommée définie dans une clause WITH. Vous pouvez considérer la CTE comme une vue temporaire à utiliser dans l’instruction qui définit la CTE. La CTE définit le nom de la vue temporaire, une liste facultative de noms de colonnes et une expression de requête (c’est-à-dire une instruction SELECT). Le résultat de l’expression de requête est effectivement une table. Chaque colonne de cette table correspond à une colonne de la liste (facultative) de noms de colonnes.

Le code suivant est un exemple de requête utilisant une CTE :

WITH
    my_cte (cte_col_1, cte_col_2) AS (
        SELECT col_1, col_2
            FROM ...
    )
SELECT ... FROM my_cte;
Copy

Dans l’exemple ci-dessus, le CTE commence sur la ligne contenant my_cte (cte_col_1, cte_col_2) AS ( et se termine sur la ligne contenant ).

Évitez de choisir des noms CTE qui correspondent aux éléments suivants :

  • Noms de fonctions SQL

  • Tables, vues ou vues matérialisées. Si une requête définit une CTE avec un nom particulier, la CTE est prioritaire sur les tables, etc.

Une CTE peut être récursive ou non récursive. Une CTE récursive est une CTE qui fait référence à elle-même. Une CTE récursive peut joindre une table à elle-même autant de fois que nécessaire pour traiter des données hiérarchiques dans la table.

Les CTEs augmentent la modularité et simplifient la maintenance.

CTEs récursives et données hiérarchiques

Les CTEs récursives vous permettent de traiter des données hiérarchiques, telles qu’un éclatement de nomenclature (composant, sous-composants) ou une hiérarchie administrative (responsable, employés). Pour plus d’informations sur les données hiérarchiques et sur les autres moyens d’interroger des données hiérarchiques, voir Interrogation des données hiérarchiques.

Une CTE récursive vous permet de rejoindre tous les niveaux d’une hiérarchie sans savoir à l’avance combien de niveaux il y a.

Présentation de la syntaxe d’une CTE récursive

Cette section fournit un aperçu de la syntaxe et de ses liens avec le fonctionnement de la récursivité :

WITH [ RECURSIVE ] <cte_name> AS
(
  <anchor_clause> UNION ALL <recursive_clause>
)
SELECT ... FROM ...;
Copy
Où :
anchor_clause

sélectionne une ligne initiale ou un ensemble de lignes représentant le haut de la hiérarchie. Par exemple, si vous essayez d’afficher tous les employés d’une entreprise, la clause d’ancrage sélectionnerait le président de l’entreprise.

La clause d’ancrage est une instruction SELECT et peut contenir toute construction SQL prise en charge. La clause d’ancrage ne peut pas faire référence au cte_name.

recursive_clause

sélectionne la couche suivante de la hiérarchie en fonction de la couche précédente. Dans la première itération, la couche précédente est l’ensemble de résultats de la clause d’ancrage. Dans les itérations suivantes, la couche précédente est l’itération terminée la plus récente.

La recursive_clause est une instruction SELECT ; cependant, elle est limitée aux projections, aux jointures et aux filtres. De plus, les éléments suivants ne sont pas autorisés dans l’instruction :

  • Fonctions d’agrégation ou de fenêtre.

  • GROUP BY, ORDER BY, LIMIT, ou DISTINCT.

La clause récursive peut faire référence au cte_name comme une table ou une vue normale.

Pour une description plus détaillée de la syntaxe, voir WITH.

Logiquement, les CTE récursives sont évaluées comme suit :

  1. La anchor_clause est évaluée et son résultat est écrit à la fois dans le jeu de résultats final et dans une table de travail. Le cte_name est en réalité un alias de la table de travail ; en d’autres termes, c’est une requête référençant que le cte_name lit les données à partir de la table de travail.

  2. Bien que la table de travail ne soit pas vide :

    1. La recursive_clause est évaluée en utilisant le contenu actuel de la table de travail partout où cte_name est référencé.

    2. Le résultat de recursive_clause est écrit à la fois dans le jeu de résultats final et dans une table temporaire.

    3. La table de travail est écrasée par le contenu de la table temporaire.

En effet, la sortie de l’itération précédente est stockée dans une table de travail appelée cte_name, et cette table est alors l’une des entrées de l’itération suivante. La table de travail contient uniquement le résultat de l’itération la plus récente. Les résultats accumulés de toutes les itérations jusqu’à présent sont stockés ailleurs.

Après l’itération finale, les résultats cumulés sont disponibles pour l’instruction principale SELECT en faisant référence à cte_name.

Considérations sur les CTE récursives

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.

Exemples

Cette section comprend des exemples de CTEs non récursives et récursives pour comparer les deux types.

CTE non récursive, à deux niveaux, auto-jointive

Cet exemple utilise une table d’employés et de responsables :

CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);
Copy
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);
Copy

Une auto-jointure à deux niveaux de cette table d’employés se présente comme suit :

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

La requête ci-dessus montre tous les employés. Les employés de chaque responsable apparaissent près de leur responsable dans le rapport. Toutefois, le rapport n’indique pas la hiérarchie sous forme visuelle. Sans examiner attentivement les données, vous ne savez pas combien de niveaux il y a dans l’organisation et vous devez lire chaque ligne pour voir quels employés sont associés à un responsable spécifique.

Une CTE récursive peut afficher ces données hiérarchiques sous forme d’arborescence latérale, comme indiqué dans la section suivante.

CTE récursive avec sortie mise en retrait

Vous trouverez ci-dessous deux exemples d’utilisation d’une CTE récursive :

  • La première utilise l’indentation pour montrer les différents niveaux de la hiérarchie. Pour simplifier cet exemple, le code ne produit pas les lignes dans un ordre particulier.

  • Le deuxième exemple utilise l’indentation et montre les employés de chaque responsable immédiatement en dessous de ce dernier.

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

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

Éclatement de nomenclature

Les hiérarchies responsables/employés ne sont pas le seul type de hiérarchies à profondeur variable que vous pouvez stocker dans une seule table et traiter avec une CTE récursive. Un autre exemple courant de données hiérarchiques est un « éclatement de nomenclature », dans lequel chaque composant peut être répertorié avec ses sous-composants, chacun pouvant être répertorié avec ses sous-sous-composants.

Par exemple, supposons que votre table contienne des données hiérarchiques, telles que les composants d’une voiture. Votre voiture contient probablement des composants tels qu’un moteur, des roues, etc. Nombre de ces composants contiennent des sous-composants (par exemple, un moteur peut contenir une pompe à carburant). La pompe à carburant peut contenir un moteur, une tubulure, etc. Vous pouvez lister tous les composants et leurs sous-composants en utilisant une CTE récursive.

Pour un exemple de requête produisant un éclatement de nomenclature, voir WITH.

Dépannage d’une CTE récursive

Une requête CTE récursive s’exécute jusqu’à ce qu’elle réussisse ou expire

Ce problème peut être provoqué par deux scénarios différents :

  • Votre hiérarchie de données peut avoir un cycle.

  • Vous avez peut-être créé une boucle infinie.

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.