Qu'est-ce qu'un CTE récursif en SQL ?

L'article qui vous montrera des exemples pratiques d'utilisation des CTE récursifs en SQL.

Si vous avez entendu parler des CTE récursifs de SQL mais ne les avez jamais utilisés, cet article est pour vous. Il est aussi pour vous si vous ne vous lassez jamais des exemples de CTE récursifs.

Avant d'aborder la récursivité, je vais vous rappeler ce que sont les CTE et quelle est leur syntaxe. Je ferai ensuite de même pour les CTE récursifs. Enfin, je vous montrerai comment fonctionnent les CTE récursifs dans trois exemples.

Que sont les CTE ?

La CTE (common table expression), également connue sous le nom de clause WITH, est une fonction SQL qui renvoie un ensemble de données temporaire pouvant être utilisé par une autre requête. Comme il s'agit d'un résultat temporaire, il n'est stocké nulle part, mais il peut être référencé comme n'importe quelle autre table.

Il existe deux types de CTE : non récursif et récursif.

Voici un bon article qui vous montrera ce que sont les CTE et comment ils fonctionnent.

Syntaxe des CTE non récursifs

La syntaxe générale d'un CTE non récursif ressemble à ceci :

WITH cte_name AS (cte_query_definition)

SELECT *
FROM   cte_name;

La première partie de la syntaxe est le CTE. Elle commence par le mot-clé WITH. Ensuite, vous donnez un nom à votre CTE. Après avoir fait suivre ce nom par le mot-clé AS, vous pouvez définir le CTE dans les parenthèses.

La deuxième partie de la syntaxe est une simple instruction SELECT. Elle est écrite immédiatement après le CTE récursif, sans virgule, point-virgule ou autre marque similaire. Comme je l'ai dit précédemment, le CTE est utilisé dans une autre requête comme n'importe quelle autre table. C'est exactement ce que fait l'instruction SELECT.

Voici l'article qui peut vous aider à comprendre la syntaxe du CTE et ses règles. Et si vous avez besoin d'autres exemples de CTE, cet article est fait pour vous.

Syntaxe des CTE récursifs

Un CTE récursif se référence lui-même. Il renvoie le sous-ensemble de résultats, puis se réfère à plusieurs reprises (de manière récursive) et s'arrête lorsqu'il renvoie tous les résultats.

La syntaxe d'un CTE récursif n'est pas très différente de celle d'un CTE non récursif :
.

WITH RECURSIVE cte_name AS (
    cte_query_definition (the anchor member)

    UNION ALL

    cte_query_definition (the recursive member)
)


SELECT *
FROM   cte_name;

Une fois encore, la clause WITH se trouve au début de votre CTE. Toutefois, si vous voulez que votre CTE soit récursif, vous écrivez le mot-clé RECURSIVE après WITH. Ensuite, c'est comme d'habitude : AS est suivi par les parenthèses avec la définition de la requête CTE. Cette première définition de requête est appelée membre d'ancrage.

Pour relier le membre d'ancrage au membre récursif, vous devez utiliser la commande UNION ou UNION ALL. Le membre récursif est, bien évidemment, la partie récursive du CTE qui fera référence au CTE lui-même. Vous verrez très bientôt comment cela fonctionne dans un exemple.

Les CTE récursifs sont principalement utilisés lorsque vous souhaitez interroger des données ou des graphiques hiérarchiques. Il peut s'agir de la structure organisationnelle d'une entreprise, d'un arbre généalogique, d'un menu de restaurant ou de divers itinéraires entre des villes. Consultez ces articles pour comprendre comment les CTE fonctionnent avec les structures hiérarchiques et comment interroger des données graphiques.

Maintenant que nous avons compris le fonctionnement des CTE récursifs, voyons quelques exemples.

Exemple 1 - Trouver les patrons et le niveau hiérarchique de tous les employés

Pour ce problème, je vais utiliser les données de la table employeesqui comporte les colonnes suivantes :

• id: L'ID de l'employé.
• first_name: Le prénom de l'employé.
• last_name: Le nom de famille de l'employé.
• boss_id: L'ID du patron de l'employé.

Voici à quoi ressemblent les données :

Ce n'est pas trop compliqué. Par exemple, le patron de Domenic Leaver est l'employé avec l'ID de 5 ; c'est Hermie Comsty. Le même principe fonctionne pour tous les autres employés, sauf Roxanna Fairlie. Elle n'a pas de patron ; il y a une valeur NULL dans la colonne boss_id. Nous pouvons en conclure que Roxanna est la présidente ou la propriétaire de l'entreprise.

Écrivons maintenant le CTE récursif pour lister tous les employés et leurs patrons directs.

WITH RECURSIVE company_hierarchy AS (
  SELECT	id,
    		first_name,
    		last_name,
    		boss_id,
		0 AS hierarchy_level
  FROM employees
  WHERE boss_id IS NULL

  UNION ALL 
  
  SELECT	e.id,
    		e.first_name,
    		e.last_name,
    		e.boss_id, 
		hierarchy_level + 1
  FROM employees e, company_hierarchy ch
  WHERE e.boss_id = ch.id
)

SELECT   ch.first_name AS employee_first_name,
	   ch.last_name AS employee_last_name,
	   e.first_name AS boss_first_name,
	   e.last_name AS boss_last_name,
	   hierarchy_level
FROM company_hierarchy ch
LEFT JOIN employees e
ON ch.boss_id = e.id
ORDER BY ch.hierarchy_level, ch.boss_id;

Que fait cette requête ? Il s'agit d'une requête récursive, qui commence donc par WITH RECURSIVE. Le nom du CTE est company_hierarchy. Après AS, la définition du CTE se trouve entre parenthèses.

La première instruction SELECT sélectionne toutes les employee La première instruction boss_id sélectionne toutes les colonnes du tableau où la colonne est NULL. En bref, elle sélectionnera Roxanna Fairlie, car elle seule a une valeur NULL dans cette colonne. Encore plus court : je commence la récursion depuis le sommet de l'organigramme. Il y a aussi une colonne hierarchy_level avec la valeur 0. Cela signifie que le niveau du propriétaire/président est 0 - il est au sommet de la hiérarchie.

J'ai utilisé UNION ALL pour relier cette instruction SELECT à la seconde, c'est-à-dire au membre récursif. Dans le membre récursif, je sélectionne toutes les colonnes du tableau employees et le CTE company_hierarchy où la colonne boss_id est égale à la colonne id. Remarquez la partie hierarchy_level + 1. Cela signifie qu'à chaque récursion, le CTE ajoutera 1 au niveau hiérarchique précédent, et ce jusqu'à ce qu'il atteigne la fin de la hiérarchie. Notez également que je traite ce CTE comme n'importe quelle autre table. Pour terminer la définition du CTE, il suffit de fermer les parenthèses.

Enfin, il y a une troisième instruction SELECT, en dehors de l'ETC. Elle sélectionne les colonnes qui afficheront les employés, les noms de leurs patrons et le niveau hiérarchique. Les données sont extraites du CTE et de la table employees. J'ai joint ces deux tableaux avec un LEFT JOIN, car je veux toutes les données du CTE - y compris Roxanna Fairlie, qui a la valeur NULL dans la colonne boss_id. Le résultat sera affiché dans l'ordre croissant : d'abord par le niveau hiérarchique, puis par l'ID du patron. Voici à quoi cela ressemble :

Roxanna Fairlie est la patronne suprême ; vous le saviez déjà. Il y a deux employés au niveau 1. Cela signifie que Bobbe Blakeway et Hermie Comsty sont les subordonnés directs de Roxanna Fairlie. Au niveau 2, il y a des employés dont les patrons directs sont Bobbe Blakeway et Hermie Comsty. Il y a aussi un troisième niveau dans la hiérarchie. Ce sont les employés dont le patron direct est Domenic Leaver.

Exemple 2 - Trouver le montant de l'investissement par investisseur

Dans cet exemple, je vais utiliser la table investment:

• id: L'identifiant de l'investissement .
• investment_amount: Le montant de l'investissement.

Les données du tableau ressemblent à ceci :

Ce sont les montants des trois options d'investissement possibles. Ils seront pris en compte par les trois investisseurs, qui diviseront le montant total de l'investissement en parts égales. Votre tâche consiste à calculer le montant par investisseur en fonction de leur nombre, c'est-à-dire si un, deux, trois ou aucun investisseur investit dans chaque investissement.

La requête qui résout ce problème est la suivante :

WITH RECURSIVE per_investor_amount AS (
	SELECT	0 AS investors_number,
			0.00 AS investment_amount,
			0.00 AS individual_amount
	UNION 

	SELECT	investors_number + 1,
			i.investment_amount,
			i.investment_amount / (investors_number + 1)
	FROM investment i, per_investor_amount pia
	WHERE investors_number << 3
)

SELECT *
FROM per_investor_amount
ORDER BY  investment_amount, investors_number;

Une fois encore, le CTE commence par WITH RECURSIVE, suivi de son nom et de la définition de la requête. Cette fois, je vais utiliser le membre d'ancrage de la requête récursive pour créer des données. Les colonnes sont investors_number, investment_amount, et individual_amount. C'est le point de départ de la récursion (comme dans l'exemple précédent, avec hierarchy_level = 0).

Puis vient le UNION et le membre récursif. Cette partie de la requête va augmenter la colonne investors_number d'une unité à chaque récursion. Elle le fera pour chaque investment_amount. La troisième colonne calculera le montant de cet investissement par investisseur, en fonction du nombre d'investisseurs participants. La récursion sera effectuée pour un maximum de trois investisseurs (c'est-à-dire jusqu'à ce qu'elle atteigne la condition WHERE investors_number < 3).

Vient ensuite la simple instruction SELECT qui renverra toutes les colonnes de l'ETC. Et voici le résultat :

Ce n'est pas difficile à analyser. S'il n'y a pas d'investisseurs, le montant de l'investissement est égal à zéro, de même que le montant individuel. Si l'investissement est de 5 322 146,00 et qu'il n'y a qu'un seul investisseur, alors le montant par investisseur sera de 5 322 146,00. S'il y a deux investisseurs pour le même montant, alors chacun d'entre eux devra payer 2.661.073,00. Si les trois investisseurs décident d'investir, chacun devra payer 1 774 048,67. Les deux autres montants d'investissement suivent le même schéma, comme vous pouvez le voir dans le tableau.

Exemple 3 - Trouver des itinéraires entre les villes

Dans le troisième exemple, je vais utiliser le tableau . cities_routequi contient des données sur les villes néerlandaises :

• city_from: La ville de départ .
• city_to: La ville de destination.
• distance: La distance entre les deux villes, en kilomètres.

Utilisez ce tableau pour trouver tous les itinéraires possibles de Groningue à Haarlem, en indiquant les villes sur l'itinéraire et la distance totale.

Voici la requête pour résoudre ce problème :

WITH RECURSIVE possible_route AS (
	SELECT	cr.city_to,
       		cr.city_from || '->' ||cr.city_to AS route,
       		cr.distance
      FROM cities_route cr
      WHERE cr.city_from = 'Groningen'

UNION ALL

SELECT 	cr.city_to,
       		pr.route || '->' || cr.city_to AS route,
        		CAST((pr.distance + cr.distance) AS DECIMAL(10, 2))
      FROM possible_route pr
INNER JOIN cities_route cr
      		ON cr.city_from = pr.city_to
)

SELECT 	pr.route,
		pr.distance
FROM possible_route pr
WHERE pr.city_to = 'Haarlem'
ORDER BY pr.distance;

Voyons ce que fait cette requête. La première instruction SELECT de la définition du CTE sélectionnera les colonnes de la table cities_route où la ville de départ est Groningen. Remarquez qu'il y a aussi une nouvelle colonne appelée route, que j'utiliserai pour concaténer les villes sur la route.

L'UNION ALL connecte ceci avec le membre récursif. Cette instruction SELECT va sélectionner la ville d'arrivée, concaténer les villes de l'itinéraire et enfin ajouter les distances entre ces villes au total de l'itinéraire entre Groningue et Haarlem. Pour réaliser tout cela, j'ai joint le CTE avec la table cities_route.

Vient ensuite l'instruction SELECT qui extrait les données du CTE. Elle sélectionne l'itinéraire et la distance lorsque la ville d'arrivée est Haarlem, les données étant classées par distance dans l'ordre croissant.

Le résultat de la requête ressemble à ceci :

Il n'est pas difficile de comprendre ce tableau. Il y a deux itinéraires de Groningue à Haarlem. Ils comprennent différentes villes entre elles et font respectivement 222,6 km et 246,5 km de long.

Si vous voulez continuer à apprendre, voyez comment vous pouvez utiliser un CTE récursif au lieu d'une longue requête SQL. Et après avoir abordé ce sujet, amusez-vous un peu en dessinant quelque chose à l'aide d'un CTE récursif.

Continuer à pratiquer les CTE récursifs

Ces trois exemples ont démontré les possibilités des CTE récursifs en SQL. Il est maintenant temps de mettre à profit ce que vous avez appris ici.

La meilleure option est probablement de suivre notre cours Requêtes récursives . Il vous offre de nombreux exemples, explications et possibilités de pratique. Ce cours fait partie du parcours de formation SQL avancé , où vous pouvez découvrir d'autres sujets SQL avancés tels que les fonctions de fenêtre, les extensions GROUP BY et les requêtes récursives.

Amusez-vous bien !