Comment écrire des CTEs multiples en SQL

Exploitez tout le potentiel des CTE en en combinant deux ou plusieurs dans une seule requête SQL.

Les expressions de table communes, ou CTE, peuvent être un outil SQL puissant. Lorsque vous écrivez deux (ou même plus) CTE ensemble, cette puissance se multiplie. Dans cet article, je vais vous montrer trois façons d'écrire plusieurs CTE :

• L'utilisation de deux CTE indépendants dans une requête SQL.
• Utilisation de deux CTE où le deuxième CTE fait référence au premier.
• Utilisation de deux CTE dont l'un est récursif.

Si vous n'êtes pas familier avec les CTE, je vous recommande notre cours interactif Common Table Expressions, qui couvre tous les types de CTE, y compris les CTE récursifs, dans 114 exercices pratiques. Vous pouvez lire ce cours dans un épisode de notre série de cours du mois.

Si vous souhaitez rafraîchir rapidement vos connaissances, ces articles sur les CTE et les cas où vous devez les utiliser constituent un bon point de départ.

Présentation des données

Je vais vous montrer les exemples de CTE sur un jeu de données composé de deux tables.

La première table est cars. Elle contient des données sur les voitures fabriquées par Renault et Nissan. Ses colonnes sont :

• id - L'ID de la voiture et la clé primaire (PK) de la table.
• car_make - Le fabricant de la voiture.
• model - Le modèle de la voiture.
• motor_type - Les détails sur le type de moteur du modèle.
• year_of_production - L'année de fabrication de la voiture.

Voici un exemple de données de la table :

La deuxième table, ventes_voitures, a ces colonnes :

• id - L'ID de l'information sur la vente et la clé primaire (PK) de la table.
• report_period - La date de la déclaration de vente.
• sales - Le nombre de voitures vendues.
• cars_id - La clé étrangère (FK) qui fait référence à la table cars.

Quelques exemples de données de la table :

Deux CTEs : Indépendant

Dans ce premier exemple, je vais vous montrer comment afficher le total des ventes dans son ensemble ainsi que par marque avec deux CTE indépendants. Le code se trouve ici :

WITH sales_per_make AS (
	SELECT car_make,
		 SUM(sales) AS total_sales_per_make
	FROM cars c
JOIN car_sales cs
		ON c.id = cs.cars_id
GROUP BY car_make
),

sales_sum AS (
	SELECT SUM(sales) AS total_sales
	FROM car_sales
)
	
SELECT car_make,
	 total_sales_per_make,
	 total_sales
FROM sales_per_make, sales_sum ss;

Je commence à écrire le premier CTE comme si c'était le seul et unique CTE de ma requête. Le nom du CTE , sales_per_makesuit le mot clé WITH, puis vient le mot clé AS. Après cela, j'écris entre parenthèses ce que je veux que le CTE fasse.

Dans ce cas, j'utilise la fonction d'agrégation SUM() pour trouver les ventes par marque de voiture. Pour cela, je dois joindre les deux tables que j'ai à ma disposition.

Le deuxième CTE vient ensuite. L'essentiel ici est qu'une virgule doit séparer les deux CTE. Ensuite, le deuxième CTE ne commence pas par le mot clé WITH mais immédiatement par le nom du deuxième CTE. Je l'ai nommé sales_sum. L'instruction SELECT entre parenthèses calcule les ventes totales de toutes les marques de voitures.

Ces deux CTE sont indépendants car le second CTE ne fait pas référence au premier.

Pour utiliser ces requêtes, je dois écrire une déclaration SELECT (la requête principale) qui les référence. C'est la même chose que lorsque vous n'écrivez qu'un seul CTE. L'instruction SELECT joint ici les résultats des deux CTE pour obtenir cette sortie :

Le résultat signifie que les concessionnaires ont vendu 176 569 voitures Renault et 185 359 voitures Nissan, pour un total de 361 928 voitures.

Vous voyez maintenant que l'écriture de deux CTE n'est pas si difficile. Cependant, il existe certains pièges lors de l'utilisation de plusieurs CTE dans une requête. Voici les principaux éléments auxquels vous devez faire attention lorsque vous écrivez plusieurs CTE :

• N'utilisez qu'un seul WITH.
• Séparez les CTE par des virgules.
• N'utilisez pas de virgule avant la requête principale.
• Il n'y a qu'une seule requête principale.

Les CTE multiples ne fonctionnent que si vous écrivez le mot-clé WITH une seule fois. Mais ce n'est pas n'importe où que vous le voulez. Vous devez l'écrire avant le premier CTE. Le deuxième CTE et tout CTE suivant commencent par le nom des CTE respectifs, contrairement au premier CTE qui commence par le mot-clé WITH.

Le premier CTE est séparé du second par une virgule. Il en va de même si vous écrivez plus de deux CTE : tous les CTE sont séparés par une virgule. Cependant, quel que soit le nombre de CTE, il n'y a pas de virgule entre le dernier CTE et la requête principale.

Enfin, il n'y a qu'une seule requête principale. Quel que soit le calcul que vous voulez effectuer, il ne fonctionne que s'il y a une seule requête principale. Cela semble logique car vous pouvez référencer tous les CTE que vous voulez joindre comme n'importe quelle autre table. C'est l'un des avantages des CTE, alors profitez-en !

Deux CTE : L'un référençant l'autre

Dans ce calcul un peu plus complexe, j'utilise à nouveau deux CTE. Cette fois, le second fait référence au premier. C'est la seule option si vous voulez qu'un CTE en référence un autre. Vous ne pouvez référencer que les CTE qui précèdent le CTE actuel et non les CTE qui suivent.

Je vais les écrire pour calculer les ventes réelles en 2022 (à savoir, en janvier et février), budgétiser les ventes annuelles de 2022 en utilisant les ventes moyennes, et enfin trouver les ventes restant à réaliser en 2022.

Je vous guiderai pas à pas dans le code ci-dessous, afin que vous compreniez ce qui se passe ici :

WITH sales_per_car AS (
	SELECT c.id,
		 c.car_make,
	   	 c.model,
	       c.motor_type,
	       c.year_of_production,
	       AVG(cs.sales)::INT AS average_sales_2022
	FROM cars c JOIN car_sales cs ON c.id = cs.cars_id
	WHERE c.year_of_production = 2022
	GROUP BY c.id, c.car_make, c.model, c.motor_type, c.year_of_production
),

budget AS (
	SELECT *,
		 average_sales_2022 * 12 AS annual_planned_sales_2022
	FROM sales_per_car
)
	
SELECT b.car_make,
	 b.model,
	 b.motor_type,
	 b.year_of_production,
	 SUM(cs.sales) AS actual_ytd_sales_2022,
	 b.annual_planned_sales_2022,
	 b.annual_planned_sales_2022 - SUM(cs.sales) AS remaining_annual_sales_2022
FROM budget b
JOIN car_sales cs
ON b.id = cs.cars_id
GROUP BY b.car_make, b.model, b.motor_type, b.year_of_production, b.annual_planned_sales_2022;

La syntaxe est la même que dans l'exemple précédent. Le CTE sales_per_car renvoie certaines colonnes de la table cars. J'utilise également la fonction AVG() pour calculer les ventes moyennes en 2022. Ce nombre est converti en un nombre entier parce que je parle de voitures et que je veux donc voir la moyenne sous la forme d'un nombre entier de voitures.

Ce CTE me donne les ventes moyennes pour chaque modèle produit en 2022. Il permet également de comprendre pourquoi je sais que les ventes ne concernent que 2022 : les voitures produites en 2022 n'ont pas pu être vendues en 2021. C'est l'hypothèse retenue ici, du moins.

Une virgule sépare ce CTE du second, qui commence par son nom, budget. Ce CTE fait maintenant référence au premier CTE comme n'importe quelle autre table. Vous pouvez le voir dans la clause FROM. J'utilise la colonne average_sales_2022 car il s'agit de la vente mensuelle moyenne réelle pour 2022. Disons que la méthode de budgétisation consiste à multiplier les ventes mensuelles moyennes de cette année par 12 pour obtenir les ventes annuelles prévues. C'est exactement ce que fait ce deuxième CTE, et c'est la raison pour laquelle il doit faire référence au premier CTE.

La requête principale joint le CTE budget et la table car_sales. J'utilise cette requête pour trouver les ventes réelles par modèle en 2022. Ensuite, j'affiche la colonne annual_planned_sales_2022 à partir du deuxième CTE. Enfin, en calculant la différence entre ces deux colonnes, j'obtiens le nombre de ventes restant à réaliser pour le reste de l'année 2022.

Voici le rapport que j'obtiens en exécutant la requête :

Deux CTEs : L'un des CTE est récursif.

Je vais maintenant m'éloigner de ces données tout en restant dans le cadre de ces marques automobiles. Imaginons que Renault envisage trois investissements : acheter Jaguar pour 2 300 000 000, Volvo pour 1 548 470 000 ou Alfa Romeo pour 2 450 000 000. Elle envisage de le faire seule, avec Nissan, ou avec Nissan et Citroën.

Cet exemple est parfait pour écrire deux CTE, dont l'un est récursif :

WITH RECURSIVE company_purchase AS (
	SELECT 2300000000 AS amount
UNION
	SELECT 1548470000 AS amount
UNION
	SELECT 2450000000 AS amount
),

per_buyer AS (
	SELECT  0 AS number_of_buyers,
		  0::DECIMAL AS purchase_amount,
		  0::DECIMAL AS amount_per_buyer
UNION
	SELECT  number_of_buyers + 1,
		  amount,
		  amount/(number_of_buyers + 1)::DECIMAL
	FROM company_purchase, per_buyer
	WHERE number_of_buyers <= 3)

SELECT *
FROM per_buyer
ORDER BY purchase_amount, number_of_buyers;

Chaque fois que vous voulez un CTE récursif, vous devez commencer à écrire des CTE avec WITH RECURSIVE. Vous annoncez toujours votre intention d'écrire un CTE récursif, que cette requête récursive soit le premier ou le second CTE. Dans ce cas, mon premier CTE est non récursif.

J'utilise le company_purchase CTE pour créer différents investissements dans Jaguar, Volvo, ou Alfa Romeo.

Ensuite vient la requête récursive. Le principe est le même : séparer les CTE par une virgule et commencer le deuxième CTE sans le mot clé WITH. Dans ce deuxième CTE, mon point de départ est l'absence totale d'investissement et d'acheteurs. Les valeurs seront partout égales à zéro. J'utilise ensuite la récursion, et la requête calcule le montant par acheteur pour un, deux ou trois investisseurs pour le premier investissement. La récursion répète ensuite le même calcul pour les deuxième et troisième investissements. Bien sûr, pour ce faire, je dois joindre la requête récursive à la requête non récursive.

Enfin, la requête principale sélectionne toutes les données de l'ETC. per_buyer CTE, avec le résultat suivant :

Que me disent ces données ? Par exemple, si trois acheteurs (Renault, Nissan et Citroën) achètent Volvo pour 1 548 470 000, chaque entreprise doit investir 516 156 666,67.

Vous trouverez d'autres exemples dans l'article sur les 5 principales questions d'entretien sur les CTE SQL et dans un autre article sur l'utilisation de deux CTE.

Multipliez encore la puissance des CTEs

Ces trois exemples ne sont que des exemples de ce que les CTE peuvent faire, et surtout de ce qu'ils peuvent faire si vous combinez plusieurs CTE de différentes manières. Ce concept n'est pas facile et demande beaucoup de pratique.

Au lieu d'inventer vos propres données et scénarios, suivez notre cours interactif sur les expressions de tableaux communs! Il vous offre tout cela et élimine toutes les inquiétudes que vous avez sur la façon de pratiquer les CTE, avec plus de 100 exercices pratiques !