PostgreSQL CTE : Ce que c'est et comment l'utiliser

Les CTE, ou Common Table Expressions, sont un outil puissant de PostgreSQL qui est souvent ignoré. Cet article examine les différents CTE de PostgreSQL y compris les CTE imbriqués et récursifs - et ce que vous pouvez faire avec eux.

Si vous écrivez des requêtes complexes en SQL, vous constaterez rapidement que votre code devient encombré et difficile à lire. Les ETC - également connus sous le nom de clauses WITH - sont avant tout un moyen de simplifier les requêtes. Cependant, ils vous permettent également d'utiliser la récursivité. La récursivité, entre autres, vous permet de naviguer facilement dans les structures hiérarchiques.

Les CTE (ou Common Table Expressions) de PostgreSQL sont très similaires aux sous-requêtes ; la différence est que les CTE sont nommés et définis en haut de votre code. Cela vous permet de diviser une requête volumineuse en petites sections.

Dans cet article, je vais présenter plusieurs exemples d'ETC PostgreSQL. Je suppose que vous êtes déjà familiarisé avec l'écriture de requêtes dans PostgreSQL. Si ce n'est pas le cas, notreCheat Sheet PostgreSQL vaut la peine d'être téléchargée.

Si vous pensez que les CTE de PostgreSQL vous aideront dans votre travail, vous pouvez consulter notre cours interactif Common Table Expressions in PostgreSQL. Ce cours est conçu pour ceux qui sont déjà familiers avec le langage SQL de base. Vous pourrez vous entraîner à utiliser les CTE de PostgreSQL grâce à plus d'une centaine d'exercices interactifs.

Nous allons nous plonger dans les expressions de table courantes de PostgreSQL !

Expressions de table courantes dans PostgreSQL

Syntaxe des ETC dans PostgreSQL

Regardons maintenant de plus près la syntaxe des CTE. Dans sa forme la plus simple, elle ressemble à ceci :

WITH cte_name AS (query_1)
query_2;

• cte_name est le nom que vous attribuez à l'ETC. Vous pouvez faire référence à ce nom dans votre requête principale ou dans des sous-requêtes, comme vous le feriez pour une table.
• query_1 est n'importe quel SELECT valide
• query_2 est une instruction SQL valide. Il peut s'agir d'un SELECT, d'un UPDATE, d'un INSERT ou d'un DELETE.

Les résultats de query_1 seront disponibles comme s'il s'agissait d'un tableau. Le nom de la table sera le nom que vous avez spécifié à cte_name. Vous pouvez l'utiliser dans le reste de votre requête de la même manière que vous utilisez d'autres tables.

Exemple d'ETC dans PostgreSQL

Prenons un exemple. Alpha Sales est un détaillant en ligne. Il souhaite savoir si sa dernière stratégie marketing a été efficace et quel type de client y a le mieux répondu.

Voici un exemple de leur table order_summary qui contient la valeur de chaque commande passée en avril, mai et juin 2024.

Pour commencer à analyser le succès de leur campagne de marketing, les dirigeants de l'entreprise souhaitent comparer les ventes de juin par client aux ventes mensuelles moyennes par client pour les mois d'avril et de mai et calculer le pourcentage de variation.

Bien sûr, vous pourriez y parvenir en utilisant des sous-requêtes, mais le code serait assez complexe. Vous souhaitez que la moyenne du mois précédent apparaisse dans le rapport, mais aussi qu'elle soit utilisée dans le calcul de la variation en pourcentage.

En utilisant un CTE, la requête ressemblerait à ceci :

WITH april_may_sales AS
(SELECT 
   customer_id, 
   SUM(value) / 2 AS prev_avg
 FROM order_summary 
 WHERE EXTRACT (MONTH FROM order_date) in (4,5)
 GROUP BY customer_id;
)
SELECT 
  order_summary.customer_id, 
  prev_avg, 
  SUM(value) AS jun_total, 
  (SUM(value) - prev_avg) * 100 / prev_avg AS percent_change
FROM order_summary  
JOIN april_may_sales
ONapril_may_sales.customer_id = order_summary.customer_id
WHERE EXTRACT (MONTH FROM order_date) = 6
GROUP BY order_summary.customer_id, prev_avg
ORDER BY customer_id;

Cette requête utilise la clause WITH pour créer une table virtuelle nommée april_may_sales. Elle extrait le total des ventes par client pour les mois d'avril et de mai, divise le résultat par 2 pour obtenir une moyenne mensuelle et stocke ces informations dans une colonne nommée prev_avg.

Cette table est jointe à la table order_summary dans la requête principale afin que nous puissions examiner le total de juin avec la moyenne des mois d'avril et de mai.

La requête produit l'ensemble de résultats suivant :

Définition des noms de colonnes CTE

En option, vous pouvez définir spécifiquement les noms des colonnes du tableau CTE à l'aide de la syntaxe suivante :

WITH cte_name (column_name_list)
AS (query_1)
query_2;

Ici, column_name_list est une liste de noms de colonnes séparés par des virgules.

En modifiant l'exemple précédent pour utiliser cette syntaxe, nous obtenons la requête suivante :

WITH april_may_sales (customer_id, prev_avg)
AS (
  SELECT 
 	customer_id, 
      SUM(value) /2 
  FROM order_summary 
  WHERE EXTRACT (MONTH FROM order_date) in (4,5)
  GROUP BY customer_id
)
SELECT 
order_summary.customer_id,
prev_avg, 
SUM(value) AS jun_total, 
(SUM(value) - prev_avg) * 100/prev_avg AS percent_change
FROM order_summary  
JOIN april_may_sales
ON april_may_sales.customer_id = order_summary.customer_id
WHERE EXTRACT (MONTH FROM order_date) = 6
GROUP BY order_summary.customer_id, prev_avg
ORDER BY customer_id;

Cela ne change rien au résultat de la requête, qui reste identique à l'exemple de requête précédent. En revanche, cela facilite la compréhension de votre requête par un tiers.

Requêtes imbriquées à l'aide des CTE de PostgreSQL

Vous pouvez définir deux ou plusieurs CTE en utilisant un seul mot-clé WITH dans PostgreSQL. Vous commencez simplement par utiliser le mot-clé WITH et vous spécifiez ensuite chaque CTE séparé par des virgules. La syntaxe est la suivante :

WITH 
cte_name_1 AS (query_1),
cte_name_2 AS (query_2)
query_3;

Chaque CTE a son propre nom et sa propre instruction select. Chaque ETC peut se référer à n'importe quel ETC précédemment défini pour récupérer les données dont il a besoin. Notez que vous ne répétez pas le mot-clé WITH: vous ne faites qu'énumérer les ETC séparés par des virgules.

Voyons cela en action. Supposons qu'Alpha Sales souhaite maintenant pousser l'analyse plus loin. Il souhaite extraire les données démographiques des clients qui ont acheté plus en juin que la moyenne de leurs achats en avril et en mai.

Pour ce faire, ils doivent combiner les données extraites dans la requête précédente avec les données de leur table customer table. Voici un échantillon de ces données :

Pour ce faire, vous pouvez

• Déplacer la requête principale précédente vers l'avant sous la forme d'un ETC imbriqué. Cela permet de créer une table virtuelle contenant customer_id, la moyenne précédente, le total de juin et le pourcentage de variation.
• Écrire une nouvelle requête principale qui joint cette table à la table customer pour calculer l'âge du client et extraire son état.

La nouvelle requête ressemble à ceci :

WITH april_may_sales AS
  (SELECT 
     customer_id, 
     SUM(value) / 2 AS prev_avg
   FROM order_summary 
   WHERE EXTRACT (MONTH FROM order_date) in (4,5)
   GROUP BY customer_id
),
comparison AS
  (
    SELECT 
      order_summary.customer_id, 
      prev_avg, 
      SUM(value) AS jun_total, 
      (SUM(value) - prev_avg) * 100/prev_avg AS percent_change
    FROM order_summary  
    JOIN april_may_sales
    ON april_may_sales.customer_id = order_summary.customer_id
    WHERE EXTRACT (MONTH FROM order_date) = 6
    GROUP BY order_summary.customer_id, prev_avg
  )
SELECT 
  customer.customer_id,
  name,
  EXTRACT(YEAR from CURRENT_DATE) - 
		EXTRACT(YEAR from date_of_birth) AS age,
  state,
  prev_avg, 
  jun_total,
  percent_change
FROM customer
JOIN comparison 
    ON comparison.customer_id = customer.customer_id
WHERE percent_change > 0;

Comme précédemment, la requête définit l'ETC nommé april_may_sales comme une table virtuelle contenant les ventes moyennes d'avril et de mai.

Elle définit ensuite un nouvel ETC appelé comparaison, qui contient une comparaison des totaux de juin par client avec le contenu de april_may_sales.

Enfin, la requête principale combine les données de la table virtuelle comparison avec les données de la table customer La requête principale combine les données de la table virtuelle avec les données de la table

Le jeu de résultats ressemble à ceci :

L'ETC PostgreSQL dans le langage de manipulation de données

Examinons maintenant les instructions de manipulation de données telles que INSERT, UPDATE, et DELETE.

Une des limitations des CTE est que vous ne pouvez pas les utiliser directement à la place d'une valeur dans une instruction UPDATE de la même manière que vous pouvez le faire avec une sous-requête.

Supposons que vous souhaitiez mettre à jour le solde de la table customer en ajoutant la valeur de toutes les commandes du mois de juin. Avec des sous-requêtes ordinaires, vous pouvez faire quelque chose comme cela :

UPDATE customer 
SET balance = balance + 
(select SUM(value) FROM order_summary 
WHERE customer.customer_id = order_summary.customer_id
   AND EXTRACT (MONTH from order_date) = 6);

Vous ne pouvez pas faire cela avec un CTE. En revanche, vous pouvez utiliser la syntaxe suivante :

WITH cte_name AS (select_statement)
UPDATE tablename 
SET column_name_1 = column_name_2
FROM cte_name 
WHERE join_clause;

• cte_name est le nom que vous utiliserez pour faire référence à la "table" créée par l'ETC.
• select_statement est l'instruction que vous utiliserez pour remplir le CTE.
• column_name_1 est le nom de la colonne de la table principale que vous souhaitez mettre à jour.
• column_name_2 est le nom de la colonne de votre CTE que vous utiliserez pour définir la nouvelle valeur.
• join_clause spécifie la condition que vous utiliserez pour joindre les deux tables.

La requête suivante ajoute le total des commandes de juin de la table order_summary au solde de la table customer au solde de la table

WITH june_total AS
(SELECT 
   customer_id, 
   SUM(value) AS jun_tot
 FROM order_summary WHERE EXTRACT(MONTH FROM order_date) = 6
 GROUP BY customer_id
)
UPDATE customer
SET balance = balance + jun_tot
FROM june_total 
WHERE customer.customer_id = june_total.customer_id;

Tout d'abord, la clause WITH crée une pseudo-table nommée june_total. Elle contient les totaux par customer_id des commandes pour lesquelles le mois de order_date est 6.

Ensuite, la colonne jun_tot de cette table est utilisée pour augmenter le solde où customer_id correspond entre les deux tables.

Le tableau customer contient désormais les données suivantes :

Vous pouvez également utiliser des CTE pour insérer ou supprimer des lignes de la même manière.

Requêtes récursives

Les requêtes récursives sont une caractéristique des ETC. Ces requêtes vous permettent d'effectuer des boucles à partir d'une requête de base unique afin d'exécuter de manière répétitive une tâche spécifique. Elles sont particulièrement utiles pour interroger des données hiérarchiques telles que les structures organisationnelles et les nomenclatures.

Une présentation complète des requêtes récursives dépasse le cadre de cet article. Nous nous contenterons d'examiner la syntaxe et un exemple simple. Pour plus de détails, consultez la page Qu'est-ce qu'un ETC récursif en SQL, qui donne une explication complète et plusieurs exemples.

La syntaxe des requêtes récursives dans PostgreSQL est la suivante :

WITH RECURSIVE cte_name AS 
(query_1 UNION query_2)
query_3;

• Le mot-clé RECURSIVE indique qu'il s'agit d'une requête récursive.
• query_1 est la requête de base, ou point de départ. Par exemple, supposons que vous travailliez avec un organigramme. Dans ce cas, query_1 pourrait être une requête qui sélectionne le responsable de haut niveau à partir d'un fichier d'employés.
• query_2 est la requête récursive. Cette requête sera répétée jusqu'à ce que plus aucune ligne ne réponde aux critères spécifiés dans WHERE. Elle peut faire référence à la dernière ligne ajoutée à l'ensemble de résultats pour récupérer des données. Elle peut être utilisée pour trouver tous les employés qui dépendent d'un directeur.
• UNION combine les résultats. Si vous utilisez UNION ALL, les doublons seront conservés ; sinon, ils seront omis.
• query_3 est utilisé pour renvoyer l'ensemble des résultats finaux. Il peut faire référence à la table virtuelle créée par l'ETC.

Prenons l'exemple d'une table employee dans laquelle les enregistrements des employés comportent un champ identifiant le responsable auquel ils sont rattachés. Que se passe-t-il si vous utilisez une requête récursive pour naviguer dans cette hiérarchie ?

Les résultats de la requête de base sont ajoutés à la table virtuelle. La requête de base extrait l'enregistrement de l'employé du PDG. Le moteur de base de données utilise ensuite cette ligne pour trouver toutes les lignes qui correspondent aux critères de la partie récursive de la requête. Il s'agit de tous les employés qui dépendent directement du directeur général.

Pour chacun de ces enregistrements, le moteur trouvera tous les employés qui dépendent de cette personne. Cette opération est répétée jusqu'à ce qu'il n'y ait plus d'employés répondant à la condition.

Prenons un exemple simple. Une société de consultants en informatique a plusieurs projets en cours, et sa politique consiste à organiser des réunions hebdomadaires sur l'état d'avancement de chaque projet. Une table nommée projects contient les détails des nouveaux projets. Un exemple de cette table ressemble à ceci :

L'entreprise souhaite créer les détails des réunions programmées dans une table nommée meetingsCes informations seront utilisées pour envoyer des rappels et réserver un lieu de réunion chaque semaine. La colonne meet_day contient le jour de la semaine où les réunions seront programmées. Elle est stockée sous la forme d'un numéro de jour dans la semaine, où 0 représente le dimanche.

Vous pouvez obtenir ce résultat à l'aide de la requête récursive suivante :

WITH RECURSIVE date_list
   (proj_name, meet_date, end_date, meet_day, meet_time)
AS (
    SELECT proj_name, start_date, end_date, meet_day, meet_time
	FROM projects
    UNION ALL
    SELECT 
proj_name, 
meet_date + 1,
	end_date, 
meet_day, 
meet_time
    FROM date_list
    WHERE meet_date + 1 <= end_date
	
)
INSERT INTO meetings
SELECT proj_name, meet_date, meet_time
FROM date_list 
WHERE meet_day = EXTRACT (DOW from meet_date)
ORDER BY proj_name, meet_date;

Après l'exécution de la requête, la table meetings contient les données suivantes :

Décomposons la requête et examinons ce qu'elle fait réellement.

Tout d'abord, elle définit les colonnes qui seront incluses dans l'ETC date_list:

WITH RECURSIVE date_list
   (proj_name, meet_date, end_date, meet_day, meet_time)

Ensuite, elle établit les données de base pour la récursion, à savoir le contenu de la table des projets :

AS (
    SELECT proj_name, start_date, end_date, meet_day, meet_time
	from projects

Elle spécifie ensuite les données qui doivent être incluses dans chaque récursion, avec une condition qui garantit que la récursion se termine lorsqu'elle est terminée :

    UNION ALL
    SELECT proj_name, 
	meet_date + 1,
	end_date, meet_day, meet_time
	FROM date_list
    WHERE meet_date + 1 <= end_date

Enfin, la requête principale insère les résultats contenus dans la table virtuelle dans la table meetings.

Cela vous semble-t-il utile ? Vous pouvez en apprendre plus sur les requêtes récursives et pratiquer quelques exemples du monde réel en suivant notre cours en ligne CTE in PostgreSQL.

En savoir plus sur les ETC de PostgreSQL

Bien que les CTEs de PostgreSQL n'améliorent pas les performances de vos requêtes, ils rendent les requêtes complexes plus faciles à écrire et à comprendre. En divisant une longue requête en plusieurs parties, vous pouvez organiser vos idées et garder votre codage simple. Les CTE facilitent également le travail avec des structures hiérarchiques en utilisant la clause RECURSIVE.

Cet article utilise spécifiquement la syntaxe et les exemples de PostgreSQL, mais les CTE fonctionnent de la même manière pour d'autres dialectes SQL comme MS SQL Server.

Si vous souhaitez vous familiariser avec les CTE, le cours de LearnSQL sur les expressions de tables communes dans PostgreSQL contient plus de 100 exercices pratiques qui vous aideront à comprendre cet outil.

Si vous souhaitez vous entraîner davantage, essayez ces 11 exercices gratuits d'expression de table commune SQL. Chaque exercice présente le type de défi auquel vous serez confronté dans le monde réel, et les solutions et explications sont incluses. Et si vous vous préparez à un entretien, voici quelques exemples de questions et de réponses sur les CTE.

J'espère que cet article vous a donné une bonne idée de ce que le CTE de PostgreSQL peut faire pour vous. Si vous souhaitez apprendre d'autres concepts avancés de PostgreSQL, cet article est un bon point de départ.

Maintenant, c'est à vous de jouer ! N'oubliez pas que c'est en forgeant qu'on devient forgeron, alors consultez notre piste d'apprentissage SQL avancé Practice pour plus de pratique sur les fonctionnalités avancées de SQL !