Utilisation de CTEs (expressions de table communes)

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.

Voici un exemple de requête utilisant une CTE :

with
    my_cte (cte_col_1, cte_col_2) AS (   -- begin CTE
        select col_1, col_2
            from ...
    )                                    -- end CTE
select ... from my_cte;

Évitez de choisir des noms CTE qui correspondent aux noms de 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 ...;
Où :
clause_ancrage

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 nom_cte.

clause_récursive

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 clause_récursive 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 nom_cte 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 clause_ancrage 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 nom_cte 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 nom_cte lit les données à partir de la table de travail.

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

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

    2. Le résultat de clause_récursive 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 nom_cte, 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 à nom_cte.

Considérations sur les CTE récursives

Potentiel pour des boucles infinies

En théorie, la construction incorrecte d’une CTE récursive peut provoquer une boucle infinie. En pratique, Snowflake empêche cela en limitant le nombre d’itérations que la clause récursive effectuera dans une requête unique. Le paramètre MAX_RECURSIONS limite le nombre d’itérations.

Pour modifier MAX_RECURSIONS pour votre compte, veuillez contacter le support Snowflake.

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

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

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 :

-- This is the WITH clause, of course.
WITH RECURSIVE managers 
      -- Column names for the "view"/CTE
      (indent, employee_ID, manager_ID, employee_title) 
    AS
      -- Common Table Expression
      (

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

        UNION ALL

        -- Recursive Clause
        SELECT indent || '--- ',
            employees.employee_ID, employees.manager_ID, employees.title
          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
    FROM managers
  ;
+----------------------------------+-------------+------------+
| TITLE                            | EMPLOYEE_ID | MANAGER_ID |
|----------------------------------+-------------+------------|
| President                        |           1 |       NULL |
| --- Vice President Engineering   |          10 |          1 |
| --- Vice President HR            |          20 |          1 |
| --- --- Programmer               |         100 |         10 |
| --- --- QA Engineer              |         101 |         10 |
| --- --- Health Insurance Analyst |         200 |         20 |
+----------------------------------+-------------+------------+

La CTE contient deux instructions SELECT :

  • La première de ces instructions SELECT est la clause d’ancrage. Elle est exécutée une fois et fournit l’ensemble des lignes du premier (haut) niveau de la hiérarchie.

  • Le second SELECT dans la CTE est la clause récursive. Cette clause 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 verrez 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) || ' '
  $$
  ;

Voici un exemple de sortie de la fonction SKEY :

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

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

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

É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

La requête CTE récursive s’exécute jusqu’à ce qu’elle expire ou atteigne la limite maximale d’itération

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;
Solution 1.2

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 clause_récursive 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 ...

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

table1:

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

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

Projection :

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

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
 ...
Étape 3

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

CTE:

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

table1:

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

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

Projection :

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

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

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.