Comment écrire un CTE récursif dans SQL Server

Un guide pour comprendre et utiliser les CTE récursifs dans SQL Server

SQL Server offre de nombreux outils puissants pour travailler avec les données, y compris les expressions de table communes (CTE). Un CTE est un ensemble de résultats temporaire nommé auquel vous pouvez faire référence dans une instruction SELECT, INSERT, UPDATE ou DELETE. Les CTE permettent de décomposer les longues requêtes en éléments logiques plus petits et plus faciles à gérer, et de les rendre plus lisibles.

SQL Server propose des expressions de table commune récursives. Une expression de table commune récursive (CTE) dans SQL Server vous permet d'effectuer des requêtes récursives sur des structures de données hiérarchiques ou graphiques, telles que des organigrammes, des arbres généalogiques, des réseaux de transport, etc. Les requêtes récursives sont utilisées pour parcourir en boucle les relations entre les éléments de données.

Dans cet article, nous commencerons par la syntaxe CTE standard, puis nous étudierons comment écrire des CTE récursifs dans SQL Server.

Pour approfondir votre compréhension des CTE récursifs, vous pouvez suivre notre cours sur Requêtes récursives dans MS SQL Server. Vous y apprendrez à maîtriser le type de requête le plus difficile et à organiser efficacement les longues requêtes SQL.

Notions de base sur les ETC dans SQL Server

Tout d'abord, passons en revue les principes de base des CTE. Un CTE commence par la clause WITH, suivie du nom du CTE et de l'instruction SELECT qui le définit. La syntaxe d'un CTE se présente comme suit :

WITH cte_name AS (
   SELECT 
     column1,
     column2,
     …
   FROM table
   … 
) 
SELECT … 
FROM cte_name, …;

Commençons par le début. La première chose que nous faisons est de donner un nom à notre ETC, cte_name. Cet ETC sélectionne certaines colonnes (column1, column2, ...) à partir de table. Le reste de la requête (la requête externe) peut se référer à l'ETC et à ses colonnes comme si l'ETC était une table normale.

Vous pouvez considérer un ETC comme une vue temporaire à laquelle vous pouvez faire référence dans la requête externe, comme nous le faisons dans l'exemple ci-dessus.

Supposons que vous souhaitiez écrire une requête qui renvoie les achats gérés par les cinq employés ayant le salaire le plus élevé dans le service des ventes. Les tables que nous utiliserons dans cet exemple s'appellent employees (comprend les colonnes employee_id, employee_name, et salary) et purchase (contient les colonnes id, date, customer_id, et total_amount).

WITH top_5_sales_employees AS (
   SELECT TOP 5
     employee_id,
     employee_name 
   FROM employees
   WHERE department_id = ‘Sales’ 
   ORDER BY salary DESC
) 
SELECT 
  p.id AS purchase_id,
  p.date,
  p.total_amount,
  e.id AS employee_id
  e.employee_name 
FROM top_5_sales_employees e
JOIN purchase p
ON p.employee_id = e.id;

Cet ETC commence par le mot-clé AVEC et le nom de l'ETC, top_5_sales_employees. Entre les parenthèses, nous sélectionnons les colonnes que nous voulons inclure dans l'ETC et nous spécifions les conditions de la requête dans la clause WHERE. Enfin, nous utilisons l'instruction SELECT pour sélectionner les colonnes appropriées dans l'ETC comme s'il s'agissait d'une table normale.

Pour en savoir plus sur ce que sont les CTE et comment les écrire, consultez notre article Qu'est-ce qu'un CTE dans SQL Server ? Si vous cherchez d'autres exemples de CTE, vous les trouverez dans cet article.

Comment utiliser les CTE récursifs dans SQL Server

Qu'est-ce qu'un CTE récursif dans SQL Server ? Un CTE récursif est un type de CTE qui se référence lui-même dans l'instruction SELECT, créant ainsi une boucle. Les CTE récursifs sont utilisés pour parcourir des structures de données hiérarchiques, telles que des organigrammes ou des réseaux.

Supposons que nous ayons une table appelée employees avec des colonnes pour le nom de l'employé, le département et le responsable. C'est ce que montre le tableau ci-dessous, où chaque enregistrement comprend un employé et son supérieur hiérarchique au sein de l'organisation.

Au premier coup d'œil, il est assez facile de voir qui dépend de qui et à quoi ressemble cette hiérarchie organisationnelle. Toutefois, si nous avions des centaines d'employés, il serait beaucoup plus difficile de donner un sens à ces données.

Nous pouvons utiliser un CTE récursif pour générer un arbre hiérarchique des employés de la société. Pour ce faire, nous devons exécuter cette requête :

WITH employee_manager_cte AS (
  SELECT 
    id, 
    name,
    department,
    manager_id,
    manager_name,
    1 AS level 
  FROM employees 
  WHERE manager_id IS NULL 
  UNION ALL 
  SELECT 
    e.id, 
    e.name,
    e.department,
    e.manager_id,
    e.manager_name,
    level + 1 
  FROM employees e 
  INNER JOIN employee_manager_cte r 
	ON e.manager_id = r.id 
) 
SELECT * 
FROM employee_manager_cte;

Décomposons cette requête étape par étape.

Tout d'abord, nous définissons l'ETC récursif sous le nom employee_manager_cte. Nous sélectionnons les colonnes que nous voulons inclure dans la requête : id name , department, manager_id, manager_name et level. La colonne level est utilisée pour suivre la profondeur de l'arbre. Nous commencerons par le niveau 1 ; au fur et à mesure que nous progresserons dans la boucle, ce nombre augmentera.

La section qui précède UNION ALL est appelée membre d'ancrage. C'est dans le membre d'ancrage que nous commençons notre boucle. Dans notre exemple, nous sélectionnons tous les employés dont le responsable est NULL. Dans notre organigramme, il s'agit des employés situés tout en haut de l'échelle. Dans le cas présent, il n'y a qu'un seul employé à ce niveau : Sarah Smith, la PDG.

La partie située après le site UNION ALL est appelée membre récursif. Dans le membre récursif, nous ajoutons de nouvelles lignes aux lignes qui ont déjà été calculées. Dans notre exemple, nous joignons la table employees avec l'ETC employee_manager_cte sur la colonne manager_id. Cela crée une boucle qui parcourt l'arbre de haut en bas. Nous ajoutons 1 à la colonne level pour suivre la profondeur de chaque nœud.

Enfin, nous sélectionnons toutes les colonnes de l'ETC employee_manager_cte.

Lorsque vous exécutez cette requête, SQL Server traite d'abord le membre d'ancrage, qui sélectionne Sarah Smith comme racine de l'arbre. Il traite ensuite le membre récursif, qui joint Sarah Smith à son rapport direct (Jane Miller). Il joint ensuite Jane Miller à son supérieur hiérarchique (Jean Dupont) et Jean Dupont à son supérieur hiérarchique (aucun). Comme il n'y a plus de lignes à ajouter à l'ensemble de résultats, SQL Server arrête le traitement de l'ETC et renvoie le résultat final.

Voici à quoi ressemble l'ensemble de résultats :

Un autre excellent article explicatif que vous pouvez consulter est Qu'est-ce qu'un ETC récursif en SQL ? Vous y trouverez d'autres exemples pratiques d'ETC récursifs en SQL.

Conseils pour l'écriture d'un ETC récursif dans SQL Server

1. Commencez par le membre d'ancrage

Le membre d'ancrage est le point de départ de l'ETC récursif. C'est la partie de la requête qui définit le cas de base, ou le premier ensemble de lignes qui sera renvoyé. Dans notre exemple d'organigramme, il s'agit du niveau le plus élevé de la direction. Assurez-vous que le membre d'ancrage renvoie toutes les colonnes dont vous avez besoin dans l'ensemble de résultats final.

2. Veillez à ce que le nombre de colonnes corresponde

Le membre récursif et les membres ancrés doivent avoir le même nombre de colonnes et les mêmes types de données correspondant aux colonnes : Dans un ETC récursif, le membre récursif fait référence à l'ETC lui-même, il est donc important de s'assurer que le membre récursif et le membre ancré ont le même nombre de colonnes et les mêmes types de données dans les colonnes correspondantes.

3. Toujours UNION ALL

Dans SQL Server, vous ne pouvez utiliser UNION ALL que pour combiner un membre ancré et un membre récursif : Lorsque vous combinez un membre ancré et un membre récursif, vous devez utiliser UNION ALL et non UNION. L'UNION TOUS préserve toutes les lignes, y compris les doublons, alors que l'UNION supprime les doublons. L'UNION n'est pas autorisée dans les requêtes récursives de SQL Server.

4. Attention aux boucles infinies !

Assurez-vous d'écrire un contrôle de terminaison dans votre requête. Un contrôle de terminaison est une condition qui empêche l'ETC récursif de tourner en boucle indéfiniment. Sans contrôle de terminaison, la requête exécutera par défaut un maximum de 100 récursions, puis se terminera par une erreur.

Le contrôle de terminaison est généralement inclus dans la clause WHERE du membre récursif et spécifie le moment où la récursion doit s'arrêter.

En suivant ces conseils, vous pouvez écrire des CTE récursifs efficaces qui vous aideront à résoudre des problèmes hiérarchiques complexes dans SQL Server. N'oubliez pas de consulter cet article sur les meilleures pratiques en matière d 'ETC pour plus de détails.

Les ETC récursifs dans SQL Server - Autres exemples

Exemple 1 : ETC récursif pour un réseau de transport

Un autre exemple d'utilisation d'un CTE récursif est la représentation d'un réseau de transport. Supposons que nous ayons une table appelée routes qui contient des informations sur les itinéraires de transport entre les villes. Elle comprend la ville d'origine, la ville de destination et la distance entre les villes. Nous voulons écrire une requête qui renvoie toutes les villes accessibles à partir d'une ville de départ donnée, ainsi que la distance totale jusqu'à chaque ville.

Nos données se trouvent dans une table appelée routes:

Voici la requête :

WITH recursive_cte AS (
  SELECT 
    source_city, 
    destination_city, 
    distance,
    source_city AS visited_cities 
   FROM routes 
   WHERE source_city = ‘New York’
   
   UNION ALL 
   
   SELECT 
     r.source_city, 
     r.destination_city, 
     r.distance + rc.distance,
     rc.visited_cities + ‘,’ + r.destination_city
   FROM routes r 
   INNER JOIN recursive_cte rc 
	ON r.source_city = rc.destination_city 
   WHERE rc.distance < 2000
AND CHARINDEX(',' + r.destination_city + ',', ',' +                                 rc.visited_cities + ',') = 0
) 
SELECT 
  destination_city, 
  distance
FROM recursive_cte

Cet ETC récursif commence par le membre d'ancrage, qui sélectionne tous les itinéraires commençant à New York. Dans le membre récursif, nous joignons la table des itinéraires à l'ETC recursive_cte sur la colonne source_city pour trouver toutes les villes accessibles depuis New York.

Nous avons ajouté une nouvelle colonne appelée visited_cities qui stocke la liste des villes visitées sous la forme d'une chaîne de caractères séparée par des virgules. Nous initialisons cette colonne dans le membre d'ancrage de l'ETC en lui attribuant la ville source. Dans le membre récursif, nous concaténons la ville actuelle à la liste des villes visitées et vérifions si la ville de destination a déjà été visitée à l'aide de la fonction CHARINDEX. Si la ville de destination n'a pas été visitée, nous l'ajoutons à la liste des villes visitées et poursuivons la récursivité

Nous continuons à ajouter des lignes à l'ensemble de résultats jusqu'à ce qu'il n'y ait plus de villes à ajouter ou jusqu'à ce que nous atteignions notre vérification de terminaison. L'ensemble de résultats indique toutes les villes accessibles depuis New York et la distance totale jusqu'à chaque ville.

Exemple 2 : Utilisation de l'ETC récursif pour les dépendances de tâches dans un projet

Un autre scénario dans lequel nous pourrions utiliser un ETC récursif serait de comprendre les dépendances des tâches d'un projet.

Supposons que nous disposions d'une table appelée tasks qui stocke des informations sur les tâches d'un projet, notamment task_id, task_name, l'ID de la tâche dont elle dépend (depends_on_task_id) et time_required pour terminer la tâche. Nous voulons écrire une requête qui calcule le temps total nécessaire pour terminer une tâche donnée, y compris toutes les tâches qui en dépendent.

Les données sont présentées ci-dessous :

Écrivons une requête qui calcule le temps total requis pour la tâche de développement et ses dépendances.

WITH recursive_cte AS (
  SELECT 
    task_id, 
    task_name, 
    depends_on_task_id, 
    time_required
  FROM tasks 
  WHERE task_id = 2
  
  UNION ALL 

  SELECT 
    t.task_id, 
    t.task_name, 
    depends_on_task_id, 
    t.time_required + rc.time_required
  FROM tasks t 
  INNER JOIN recursive_cte rc 
	ON t.depends_on_task_id = rc.task_id
WHERE rc.total_time < 20) 
SELECT 
  task_name, 
  time_required as total_time
FROM recursive_cte
GROUP BY task_name;

Cet ETC récursif commence par le membre d'ancrage, qui sélectionne la ligne de la table tasks avec task_id = 2 (Développement). Dans le membre récursif, nous joignons la table tasks avec l'ETC recursive_cte sur les colonnes depends_on_task_id et task_id pour trouver toutes les tâches qui dépendent de Développement. Nous calculons le temps total requis pour chaque tâche en ajoutant le temps requis pour la tâche en cours au temps total requis pour les tâches précédentes.

Enfin, nous interrogeons les résultats par task_name et time_required pour chaque tâche. Voici les résultats :

La colonne task_name indique le nom de chaque tâche et la colonne total_time indique le temps total requis pour accomplir la tâche et toutes les tâches précédentes. Par exemple, la première ligne montre que le temps total nécessaire pour terminer le développement et toutes ses dépendances est de 15, ce qui correspond à la somme du temps nécessaire pour le développement (10) et les tests (5).

Les autres lignes illustrent le même concept, les colonnes task_name et total_time indiquant le nom de chaque tâche et le temps total requis.

En savoir plus sur les ETC récursives dans SQL Server

Les CTE récursives dans le serveur SQL peuvent être utiles dans de nombreux scénarios, tels que

• Hiérarchies d'employés: Un CTE récursif peut être utilisé pour parcourir un arbre d'employés et leurs responsables afin de calculer la rémunération totale de tous les employés d'une branche donnée.
• Catégories de produits: Un ETC récursif peut être utilisé pour parcourir un arbre de catégories de produits afin de trouver tous les produits d'une catégorie donnée et de ses sous-catégories.
• Réseaux sociaux: Un ETC récursif peut être utilisé pour parcourir un graphe de connexions de réseaux sociaux afin de trouver tous les amis d'un utilisateur donné et leurs connexions.
• Dépendances des tâches: Un ETC récursif peut être utilisé pour parcourir un arbre de dépendances de tâches afin de calculer le temps nécessaire à l'achèvement d'un projet.

En général, tout scénario dans lequel les données sont organisées de manière hiérarchique ou dans une structure graphique peut potentiellement bénéficier de l'utilisation d'un ETC récursif. En suivant les meilleures pratiques, telles que commencer par le membre d'ancrage, s'assurer que les membres récursifs et d'ancrage ont le même nombre de colonnes et de types de données, et écrire un contrôle de terminaison, vous pouvez écrire des requêtes efficaces et précises qui traversent des structures de données hiérarchiques et résolvent des problèmes complexes.

Renforcez votre compréhension des CTE récursifs en suivant notre cours Requêtes récursives in MS SQL Server. Ses 112 exercices interactifs vous aideront à maîtriser ce sujet avancé !