Qu'est-ce qu'une CTE ?

Après avoir maîtrisé des instructions telles que SELECT, DELETE, INSERT et GROUP BY, vous cherchez peut-être des moyens d'améliorer la maintenabilité, la reproductibilité et la lisibilité du code. À ce stade, vous commencerez probablement à vous familiariser avec les concepts SQL modernes qui ont été introduits au début des années 2000. L'une de ces techniques SQL est la CTE? (common table expression) -?un ensemble de résultats temporaire nommé. Dans cet article, vous apprendrez ce qu'est une CTE et comment l'utiliser pour améliorer la maintenance et la lisibilité de votre code.

CTE : définition et syntaxe de base

Une expression de table commune, ou CTE, est un ensemble de résultats temporaires nommés, créé à partir d'une simple instruction SQL, qui peut être utilisé dans les instructions suivantes SELECT, DELETE, INSERT ou UPDATE.

Commençons par un exemple. Considérons les deux tables suivantes :

• job_offersUn tableau qui contient les rôles de la science des données, avec les salaires par niveau et par lieu au Royaume-Uni.

  role	location	level	salary
  data scientist	London	entry	45000
  data scientist	outside London	entry	34000
  data scientist	London	mid	65000
  data scientist	outside London	mid	60000
  data scientist	London	tech lead	95000
  data scientist	outside London	tech lead	73000
  data scientist	London	director	140000
  data scientist	outside London	director	101000
  quantitative analyst	London	entry	55000
  quantitative analyst	outside London	entry	43000
  quantitative analyst	London	mid	83000
  quantitative analyst	outside London	mid	66000
  quantitative analyst	London	tech lead	100000
  quantitative analyst	outside London	tech lead	72000
  quantitative analyst	London	director	155000
  quantitative analyst	outside London	director	120000
  machine learning engineer	London	entry	44000
  machine learning engineer	outside London	entry	36000
  machine learning engineer	London	mid	67000
  machine learning engineer	outside London	mid	58000
  machine learning engineer	London	tech lead	95000
  machine learning engineer	outside London	tech lead	84000

• employee_occupationUn tableau qui contient 5 employés de la société X, avec leurs professions respectives et leurs lieux de travail.

  name	role	location
  Tim Smith	data scientist	London
  Joana Lo	quantitative analyst	outside London
  Ed Merith	machine learning engineer	London
  Maria Soldini	machine learning engineer	outside London
  Tina Morito	quantitative analyst	outside London

Imaginons maintenant que vous souhaitiez répertorier tous les employés avec le salaire moyen correspondant à leur rôle spécifique (colonne role). Voici ce que vous devez faire :

1. Calculez le salaire moyen pour chaque rôle. Pour cela, vous pouvez utiliser le job_offers tableau.
2. Fusionnez les valeurs agrégées avec le employee_occupation table (c'est-à-dire les joindre).

Voici le CTE permettant de calculer et d'afficher le résultat souhaité :

WITH average_salary AS (
  SELECT role, avg(salary) AS avg_salary 
  FROM job_offers 
  GROUP BY role
)
SELECT a.*, b.avg_salary  
FROM employee_occupation a 
LEFT JOIN average_salary b 
ON a.role = b.role;

Voici ce qui se passe dans le code :

1. La définition du CTE commence par un mot-clé spécial WITH.
2. Après le site WITH, le CTE reçoit un nom. Dans notre exemple, le nom du CTE est average_salary.
3. Le nom du CTE est suivi d'un mot-clé spécial AS.
4. L'instruction SELECT se trouve à l'intérieur des parenthèses, dont le jeu de résultats est stocké comme un CTE. Dans notre exemple, l'ensemble de résultats temporaire average_salary est créé avec l'instruction SELECT.
5. Maintenant que vous avez un CTE, vous pouvez l'utiliser dans une instruction SQL ultérieure en le référençant comme vous le feriez pour n'importe quelle autre table. Ici, nous utilisons notre jeu de résultats temporaire dans une instruction JOIN. La requête principale suit, dans laquelle le CTE average_salary est utilisé.
6. Le jeu de résultats temporaire average_salary ne vit que dans le cadre de l'instruction qui suit immédiatement la clause WITH. Dans notre exemple, cela signifie que average_salary disparaît automatiquement après l'exécution de la requête principale et ne peut être utilisé nulle part ailleurs dans le code. C'est comme si vous créiez une table, l'utilisiez dans une instruction SQL, puis la supprimiez ou la laissiez tomber de votre base de données.

En conséquence, SQL renvoie pour chaque employé le salaire moyen pour son rôle :

La syntaxe de base pour l'utilisation d'un CTE ressemble à ceci :

Comme vous pouvez le voir sur l'image, nous définissons un jeu de résultats temporaire (dans notre exemple, average_salary), après quoi nous l'utilisons dans la même instruction (dans notre exemple, l'instruction average_salary est utilisé dans l'instruction JOIN ). Il existe un excellent cours, Recursive queries, proposé par LearnSQL.fr Je vous suggère de commencer par ce cours si vous souhaitez en savoir plus sur la syntaxe des CTE.

Sous-requêtes et CTE

Vous avez peut-être remarqué que notre dernier exemple peut être écrit avec une sous-requête comme celle-ci :

SELECT a.*, b.avg_salary  
FROM employee_occupation a 
LEFT JOIN 
  (SELECT role, avg(salary) AS avg_salary 
   FROM job_offers 
   GROUP BY role) b
ON a.role = b.role;

Bien qu'écrit différemment, il renverra exactement la même sortie que notre code CTE de tout à l'heure. Voici ce que fait ce code avec une sous-requête :

• À l'intérieur de l'instruction JOIN, nous avons une sous-requête entre parenthèses : "select role, avg(salary) as avg_salary from job_offers group by role" est une sous-requête.
• Le moteur SQL exécute d'abord cette sous-requête, puis effectue la jointure avec le résultat renvoyé par la sous-requête.

Bien qu'une sous-requête renvoie le même résultat qu'une instruction qui utilise un CTE, je recommande toujours les CTE plutôt que les sous-requêtes dans un cas comme celui-ci. Pourquoi ? Eh bien, ils rendent le code plus lisible et plus compréhensible. Les requêtes longues et compliquées peuvent comporter de très nombreuses sous-requêtes qui deviennent rapidement difficiles à lire et à suivre.

Plusieurs CTE dans une déclaration

Jusqu'à présent, nous avons utilisé un seul jeu de résultats temporaire dans une clause WITH. Que faire si nous avons besoin de créer plusieurs CTE dans une seule déclaration ? Cela peut arriver si vous devez réécrire une instruction SQL qui comporte de nombreuses sous-requêtes. C'est possible - il est possible d'avoir plusieurs CTE dans une seule instruction SQL.

Prenons un exemple avec deux sous-requêtes. Imaginez que vous remarquez les différences de salaire par lieu en plus de la différence par rôle. Vous souhaitez afficher des informations supplémentaires dans votre sortie, à savoir le salaire moyen par rôle et par lieu, et l'afficher avec des informations au niveau de l'employé.

Dans ce cas, en plus de la sous-requête comme la suivante qui calcule le salaire moyen par rôle :

SELECT role, avg(salary) AS avg_salary 
FROM job_offers 
GROUP BY role

vous devez écrire une autre sous-requête comme celle-ci :

SELECT role, location, avg(salary) AS avg_salary 
FROM job_offers 
GROUP BY role, location

En utilisant ces sous-requêtes, votre SELECT final ressemblera à ceci :

SELECT a.*, b.avg_salary, c.avg_salary_additional 
FROM employee_occupation a
LEFT join 
  (SELECT role, avg(salary) as avg_salary 
   FROM job_offers 
   GROUP BY role) b 
ON a.role = b.role
LEFT JOIN 
  (SELECT role, location, avg(salary) AS avg_salary_additional 
   FROM job_offers 
   GROUP BY role, location) c
ON a.role = c.role AND a.location = c.location;

La requête finale semble maintenant beaucoup plus compliquée ; elle est plus difficile à suivre.

Voici à quoi ressemble la même requête en utilisant des CTE :

WITH average_salary AS (
  SELECT role, avg(salary) AS avg_salary 
  FROM job_offers 
  GROUP BY role
),
average_salary_additional AS (
  SELECT 
    role, location, avg(salary) AS avg_salary_additional 
  FROM job_offers 
  GROUP BY role, location
)
SELECT a.*, b.avg_salary, c.avg_salary_additional 
FROM employee_occupation a
LEFT JOIN average_salary b 
  ON a.role = b.role
LEFT JOIN average_salary_additional c 
  ON a.role = c.role and a.location = c.location;

C'est mieux, non ? Ici, nous avons défini deux CTE. Voici ce qui se passe dans ce code :

• Deux CTE sont définis et utilisés à l'intérieur d'une seule clause WITH: average_salary et average_salary_additional. Ils sont séparés par une virgule dans la même instruction WITH. Chaque sous-requête est nommée séparément, ce qui rend votre version finale SELECT plus lisible.
• La première requête SQL CTE (average_salary) stocke le salaire moyen par rôle. Le deuxième CTE (average_salaries_additional) enregistre le salaire moyen pour chaque combinaison de rôle et de lieu dans la table job_offers.

• Une fois définis, les deux CTE sont utilisés dans la requête principale en une seule instruction. Le résultat affiche toutes les moyennes - la moyenne par rôle et la moyenne pour chaque combinaison de rôle et de lieu - avec les détails de chaque employé :

  name	role	location	avg_salary	avg_salary_additional
  Tim Smith	data scientist	London	76625	86250
  Ed Merith	machine learning engineer	London	64000	68666.6667
  Maria Soldini	machine learning engineer	outside London	64000	59333.3333
  Joana Lo	quantitative analyst	outside London	86750	75250
  Tina Morito	quantitative analyst	outside London	86750	75250

• Une fois que cette seule instruction SQL est exécutée et que le résultat est affiché à l'écran, les deux CTE sont abandonnés et deviennent indisponibles pour une utilisation ultérieure dans le code.

Le même résultat peut être obtenu avec des fonctions SQL à fenêtre et un seul CTE, mais nous avons utilisé ici plusieurs expressions de table communes. C'est une bonne illustration de la façon dont deux CTE peuvent être utilisés dans une requête.

Vous avez peut-être remarqué que chaque CTE de notre exemple lit une table SQL nommée job_offers. Bien entendu, chaque CTE peut lire différentes tables de la base de données ou même lire un autre CTE défini dans la même instruction. En d'autres termes, l'imbrication est autorisée lorsqu'on travaille avec des CTE - un CTE peut lire ou référencer un autre CTE. La seule condition est qu'ils doivent être définis dans la même requête WITH.

Voici un exemple avec des CTEs imbriqués :

WITH avg_salary_per_role AS (
  SELECT role, avg(salary) AS avg_salary 
  FROM job_offers 
  GROUP BY role
),
min_avg_salary AS (
  SELECT min(avg_salary) AS min_avg_salary 
  FROM avg_salary_per_role
)
SELECT * 
FROM avg_salary_per_role a 
INNER JOIN min_avg_salary b 
ON a.avg_salary = b.min_avg_salary;

Dans ce code :

• Nous avons défini deux CTEs : avg_salary_per_role et min_avg_salary. Le premier CTE (avg_salary_per_role) calcule le salaire moyen par rôle. Le deuxième CTE (min_avg_salary) lit le premier CTE (avg_salary_per_role) et calcule le minimum des salaires moyens au niveau des rôles. Les salaires moyens ont été calculés par le premier CTE.

• La requête principale utilise les deux CTE dans une instruction JOIN et renvoie le rôle dont le salaire moyen est le plus bas parmi tous les rôles :

  role	avg_salary	min_avg_salary
  machine learning engineer	64000	64000

• Une fois que cette instruction SQL est exécutée et que le résultat est affiché à l'écran, les deux CTE sont abandonnés et ne sont plus disponibles pour une utilisation ultérieure dans le code.

Création de tables et CTE

Nous avons vu que l'utilisation des CTE est une excellente alternative aux sous-requêtes. C'est également une excellente alternative à la création d'une véritable table.

Les développeurs créent souvent des tables temporaires dans la base de données, les utilisent dans la requête suivante, puis les abandonnent ensuite. Cette approche peut être remplacée par une approche qui utilise les CTE.

Pourquoi est-ce que je mentionne ceci ? Avant de commencer à travailler avec les CTE, je faisais exactement ce que je viens de décrire.

Considérons une requête de notre dernier exemple qui ressemble à ceci :

WITH avg_salary_per_role AS (
  SELECT role, avg(salary) AS avg_salary 
  FROM job_offers 
  GROUP BY role
),
min_avg_salary AS (
  SELECT min(avg_salary) AS min_avg_salary 
  FROM avg_salary_per_role
)
SELECT * 
FROM avg_salary_per_role a 
INNER JOIN min_avg_salary b 
ON a.avg_salary = b.min_avg_salary;

Je l'aurais écrite comme ceci :

CREATE TABLE avg_salary_per_role AS
SELECT role, avg(salary) AS avg_salary 
FROM job_offers 
GROUP BY role;

CREATE TABLE min_avg_salary AS
SELECT min(avg_salary) AS min_avg_salary 
FROM avg_salary_per_role;

SELECT * 
FROM avg_salary_per_role a 
INNER JOIN min_avg_salary b 
ON a.avg_salary = b.min_avg_salary;

DROP TABLE avg_salary_per_role;
DROP TABLE min_avg_salary;

Bien que les deux solutions vous donneront le même résultat, il est préférable d'utiliser les CTE afin de ne pas avoir à se soucier des métadonnées ? Vous n'avez pas besoin de vous souvenir des suppressions de tables, car le moteur SQL s'en occupe pour vous avec les CTE. Pas mal, non ?

CTE non récursifs et récursifs

Jusqu'à présent, nous avons parlé des CTE dits "non récursifs". Cependant, nous divisons généralement les CTE en deux types principaux :

• les CTE non récursifs
• Les CTE récursifs

Un CTE récursif est une forme particulière de CTE imbriqué. Un CTE récursif est un CTE qui se référence lui-même dans la même expression de table commune.

Voici à quoi ressemble un CTE récursif :

WITH recursive_cte (column_list)
AS
(
   -----(in this query we are referencing recursive_cte)----
)

SELECT *
FROM recursive_cte

Les CTE récursifs fonctionnent sur des données hiérarchiques, les données doivent donc être correctement définies. Le code est exécuté jusqu'à ce que tous les niveaux de la hiérarchie aient été épuisés.

Je me souviens de l'époque où j'ai écrit mon premier CTE récursif. J'avais besoin d'extraire des informations de base sur la structure organisationnelle d'une banque. Un CTE récursif est approprié ici, car la structure organisationnelle d'une banque est hiérarchique (c'est-à-dire qu'elle a une structure arborescente) :

• Il existe une unité principale à laquelle toutes les unités appartiennent (ce qu'on appelle le "point d'ancrage"). Dans une banque, il s'agit du conseil de surveillance.
• Il existe des unités organisationnelles pour lesquelles une hiérarchie claire est définie. Par exemple, le groupe d'analyse des données relève du département informatique avec d'autres unités, le département informatique fait partie de l'unité de soutien commercial et l'unité de soutien commercial relève du conseil de surveillance, qui est l'unité principale (l'ancrage).

Comme j'avais besoin du nombre d'employés pour chaque unité (ancre, parent, enfant), le CTE récursif était la solution. Je me souviens de la joie que j'ai ressentie lorsque mon script a fonctionné - il ne comportait qu'une seule instruction SQL ! J'aurais pu écrire une requête distincte pour chaque service pour lequel j'avais besoin d'informations. Mais comme j'ai utilisé un CTE récursif, j'ai obtenu les résultats sans avoir à écrire autant de requêtes.

Nous ne nous attarderons pas ici sur les CTE récursifs ; pour plus de détails, consultez notre cours interactif en ligne sur LearnSQL.fr qui traite de ce sujet. Si vous voulez voir les CTE dans un véritable exemple commercial, je vous suggère Création de rapports basiques en SQL-L'agrégation multi-niveaux, souvent requise dans les rapports d'entreprise, y est démontrée à l'aide d'un CTE. En outre, il existe plusieurs excellents articles sur divers sujets relatifs aux CTE, accompagnés d'exemples ; vous pouvez les trouver ici et ici.

Lisibilité du code et CTE

En résumé, voici quelques raisons pour lesquelles les CTE sont importants, et pourquoi et quand vous devriez les utiliser :

• Pour éviter les sous-requêtes dans les instructions SQL. Cela concerne spécifiquement les situations dans lesquelles nous joignons plusieurs tables dans une requête principale et où une ou plusieurs de ces tables sont des sous-requêtes. Dans ce cas, un CTE est une excellente alternative qui améliorera la lisibilité de votre code.
• Pour éviter de créer inutilement des tables et des vues dans votre base de données. Les CTE permettent d'éviter les métadonnées inutiles. Il est courant de créer des tables à utiliser dans les jointures afin de ne pas avoir à écrire de sous-requêtes. Cependant, plutôt que de générer une "vraie" table dont les métadonnées sont stockées dans une base de données, vous pouvez utiliser un CTE comme alternative. Comme il s'agit d'un ensemble de résultats temporaires nommés, il n'est pas stocké dans une base de données ni utilisé ultérieurement dans votre code, et votre code sera tout aussi lisible que le code qui crée de véritables tables.
• Pour rendre votre code plus facile à comprendre et à maintenir. Les CTE sont particulièrement utiles dans les requêtes longues. Ils constituent un moyen très efficace de maintenir des requêtes plus complexes. Chaque CTE a un nom, et les noms intuitifs peuvent aider énormément à la lisibilité et à la maintenance du code. Il est beaucoup plus facile de comprendre ce qui se passe dans le code avec des noms intuitifs pour les morceaux de code, puisque vous pouvez comprendre rapidement ce dont il s'agit grâce à son nom.

Nous avons parcouru la syntaxe de base et plusieurs exemples de comment et quand vous pouvez définir et utiliser les CTE. Pour mieux comprendre et pour vous entraîner, je vous recommande un cours de LearnSQL.fr nommé Requêtes récursives. Dans ce cours, nous vous apprenons de manière interactive à utiliser les CTE (récursifs et non récursifs) dans votre travail quotidien. Vous pouvez également trouver d'autres CTE en pratique dans un cours interactif intitulé Création de rapports basiques en SQL, dans lequel vous pouvez apprendre à utiliser les CTE dans les agrégations multi-niveaux (comme la création de rapports d'entreprise). Une fois que vous aurez maîtrisé les CTE, vous serez ravi de voir à quel point vos scripts SQL sont plus efficaces !