Comment utiliser 2 CTE dans une seule requête SQL

Vous vous êtes déjà demandé comment utiliser plusieurs CTE dans une seule requête SQL ? Lisez cet article et découvrez les CTE récursifs.

Après avoir appris les expressions de table communes ou CTE, une question naturelle est "Puis-je utiliser plusieurs CTE dans une requête ?" Oui, vous le pouvez ! Et vous pouvez le faire assez facilement, surtout si vous avez déjà quelques connaissances de base sur les CTE. Que vous connaissiez un peu les CTE ou que vous soyez entièrement nouveau dans le monde des CTE, lire ce qu'est un CTE est toujours un bon début.

Ce que vous apprendrez dans cet article peut être mis en pratique dans le cours LearnSQL.com Requêtes récursives , qui était notre cours du mois d'août.

Je vais commencer par vous expliquer comment utiliser deux CTE dans une requête. Ensuite, je vous apprendrai à utiliser des CTE où le deuxième CTE fait référence au premier. Pour pimenter cet article, je terminerai en vous montrant deux requêtes avec des CTEs, dont l'une est récursive.

2 CTEs, 1 requête SQL, 0 problème

Imaginons qu'il y ait une table nommée logins qui stocke des login données. Elle contient les colonnes :

• id - L'ID du login.
• username - L'utilisateur qui s'est connecté.
• login_date - La date de ce login.
• login_start - Le moment où l'utilisateur s'est connecté.
• login_end - Le moment où l'utilisateur s'est déconnecté.

Si vous regardez le tableau, vous remarquerez que chaque nom d'utilisateur apparaît au moins une fois.

Si votre tâche consiste à calculer le temps moyen (en minutes) que chaque utilisateur a passé à se connecter, comment le feriez-vous en utilisant des CTE ?

Vous aurez besoin de deux CTE, dont le code ressemblera à ceci :

WITH distinct_user AS (
	SELECT DISTINCT username
FROM logins),

minutes_logged AS (
	SELECT	username,
			DATEDIFF (minute, login_start, login_end) AS minutes
FROM logins)

SELECT	u.username,
		AVG (minutes) AS avg_time_logged
FROM distinct_user AS u 
JOIN minutes_logged AS m 
ON u.username = m.username
GROUP BY u.username;

Tout d'abord, je précise que vous devez avoir des noms d'utilisateurs distincts pour que cela fonctionne. Maintenant, commençons à écrire le premier CTE ! Rien d'inhabituel ici - comme pour chaque CTE, vous le définissez par WITH; on ne les appelle pas WITH queries pour rien !

Ce qui suit est le nom du CTE ; dans ce cas, le premier CTE s'appelle distinct_user. Vous le faites suivre du mot-clé AS, puis vous définissez simplement l'instruction régulière SELECT entre les parenthèses. Cette instruction SELECT sélectionne simplement les noms d'utilisateur uniques dans la table logins.

Une fois que vous avez des noms d'utilisateurs distincts, vous devez calculer combien de temps l'utilisateur a été connecté. C'est la partie critique - écrire le deuxième CTE. Mon deuxième CTE s'appelle minutes_logged.

Vous remarquerez qu'il n'y a pas de WITH avant le deuxième CTE. C'est extrêmement important ! Une fois que vous avez défini le premier CTE, il n'est séparé du second que par la virgule, c'est-à-dire que vous n'écrivez WITH qu'une seule fois. Ensuite, le nombre de CTE que vous définissez n'a pas d'importance ; il est seulement important de les séparer par une virgule et de commencer chaque CTE par son nom.

Analysons maintenant ce que fait le CTE minutes_logged. Il prend d'abord la colonne nom d'utilisateur dans la table logins. Ensuite, il calcule la différence entre login_start et login_end en minutes. Cette nouvelle valeur sera affichée dans la colonne minutes.

Vous devrez calculer le temps moyen (en minutes) que chaque utilisateur a passé à se connecter. Pour cette partie du calcul, j'ai utilisé la requête externe. Elle sélectionne le nom d'utilisateur dans le CTE distinct_user et calcule ensuite la moyenne des minutes de connexion en utilisant la colonne minutes du CTE minutes_logged.

Ces deux CTEs sont joints en utilisant la colonne username. Enfin, le résultat est groupé par la même colonne, puisque nous voulons le résultat au niveau de l'utilisateur.

Mais puis-je utiliser un CTE à l'intérieur d'un autre CTE ?

Une question simple mérite une réponse simple : oui, vous le pouvez. Maintenant que vous savez comment utiliser plusieurs CTE, écrire un CTE qui fait référence à un autre CTE n'est qu'une variation de ce que vous avez appris. Puisqu'il s'agit d'une variation, je pense qu'il est préférable de vous montrer comment le faire à l'aide d'un exemple que vous connaissez déjà.

Reprenons l'exemple précédent. Je vais modifier un peu le code pour vous montrer comment écrire un CTE qui fait référence au premier CTE. Voici la nouvelle version de notre code précédent :

WITH difference AS (
	SELECT 	username,
		 	DATEDIFF (minute, login_start, login_end) AS minutes
FROM logins),

average_logged AS (
SELECT	username,
			AVG (minutes) AS average
FROM difference
GROUP BY username)

SELECT DISTINCT 		username,
				average
FROM average_logged;

La logique est la même que dans le premier exemple, c'est juste que les étapes sont dans un ordre different. Le premier CTE, différence, calcule les minutes passées avec chaque connexion ; c'est le même que dans le premier exemple. Maintenant que j'ai les minutes, je dois calculer le temps moyen (en minutes) passé par chaque utilisateur. C'est pour cela que je vais utiliser la moyenne CTE. Comme vous vous en souvenez, nous mettons une virgule entre les deux CTE et nous commençons le second par son nom - pas besoin d'utiliser à nouveau WITH! Ce deuxième CTE calcule le temps moyen pour chaque connexion ; pour cela, il utilise la colonne minutes du premier CTE et stocke le résultat dans la colonne average.

Pour référencer le CTE difference dans le deuxième CTE, vous le traitez comme une table : FROM difference. Enfin, vous regroupez le résultat par le nom d'utilisateur, puisque vous ne voulez pas la moyenne de tous les utilisateurs.

La requête externe sélectionne alors simplement les colonnes username et average à partir du CTE average_logged. De cette façon, vous obtenez le même résultat que dans le premier exemple :

Si ces exemples ne sont pas suffisants pour vous, il existe un article qui vous donne plusieurs autres exemples à mettre en pratique. Vous pouvez également utiliser plus d'un CTE.

Utilisation de CTE récursifs avec des CTE non récursifs

Il y a une raison pour laquelle les CTE sont enseignés dans notre Requêtes récursives cours. C'est parce que les CTE peuvent aussi être récursifs. C'est la partie la plus compliquée de l'apprentissage des CTE. (Je suppose que vous savez déjà ce qu'est un CTE récursif et comment l'écrire. Si ce n'est pas le cas, consultez les sections Requête SQL longue et Requête SQL récursive pour une introduction et Do It in SQL : Recursive SQL Tree Traversal pour un exemple détaillé. ) Cependant, vous n'avez peut-être pas réalisé que vous pouvez utiliser plus d'un CTE, même si l'un d'entre eux est récursif. Voyons comment procéder.

Pour cet exemple, imaginons que vous voulez acheter une entreprise avec vos amis. Il existe quatre options d'investissement, chacune nécessitant une somme d'argent différente. Vous n'en êtes qu'au début des discussions. Vous n'êtes pas sûr du nombre d'amis qui participeront ; leur participation dépend du montant qu'ils ont à investir. Ce montant dépend du nombre total d'investisseurs impliqués et de l'option d'investissement choisie.

Pour les aider à se décider, vous avez décidé de calculer le montant requis par investisseur pour les quatre options d'investissement avec un nombre d'investisseurs allant de un à dix.

Dans cet exemple, nous avons d'abord un ETC non récursif, puis un ETC récursif :

WITH RECURSIVE investment AS (
	SELECT amount 5897645 AS investment_amount
	UNION 
SELECT 4536841 AS investment_amount
	UNION 
SELECT 3852457 AS investment_amount
	UNION 
SELECT 3452115 AS investment_amount
),

per_investor AS (
	SELECT	0 AS investors_number,
			0 AS amount,
			0 AS individual_amount
	UNION 
	SELECT	investors_number + 1,
			investment_amount,
			investment_amount/(investors_number + 1)
	FROM investment, per_investor
	WHERE investors_number < 10)

SELECT *
FROM per_investor
ORDER BY  amount, investors_number;

La principale distinction est évidente dès le départ ! Lorsque vous écrivez des requêtes récursives, vous devez commencer votre code en utilisant WITH RECURSIVE. Comme vous le remarquerez, il n'est pas nécessaire de commencer par le CTE récursif lui-même ; dans cet exemple, un CTE non récursif vient en premier, même si nous utilisons WITH RECURSIVE immédiatement avant de le définir. Vous pouvez écrire les CTE dans l'ordre que vous voulez ; le CTE récursif peut être le premier ou le dernier. Mais il est important de se rappeler que si vous avez au moins une requête récursive, il est obligatoire d'écrire WITH RECURSIVE.

Dans l'exemple actuel, mon premier CTE (non récursif) s'appelle investissement. Comme je ne dispose pas de la table contenant tous les montants des investissements, j'ai décidé d'utiliser le CTE comme une table temporaire. Comme vous pouvez le voir, les montants d'investissement possibles sont les suivants :

• 5,897,645
• 4,536,841
• 3,852,457
• 3,452,115

En utilisant UNION avec SELECT dans le premier CTE, je crée virtuellement une table qui contient ces quatre possibilités d'investissement. Elles seront affichées dans la colonne investment_amount. La table résultante pour cet ETC est :

Le deuxième CTE est récursif. Cependant, cela ne change rien par rapport à l'écriture de deux CTE non récursifs : la virgule sépare à nouveau les CTE.

Analysons un peu le deuxième CTE. La première instruction SELECT définit trois colonnes qui ont la valeur 0 : investors_number, amount, et individual_amount. Comme je l'ai dit, le CTE récursif se réfère à lui-même, c'est-à-dire que l'opérateur UNION est nécessaire.

L'opérateur UNION est suivi d'une ou plusieurs déclarations SELECT, qui effectueront l'opération souhaitée sur la première déclaration SELECT. Cela signifie qu'il ajoutera 1 à la colonne investors_number. Ensuite, il mettra investment_amount du premier CTE dans la colonne montant. Il divisera le montant de l'investissement par le nombre d'investisseurs concernés ; le résultat sera affiché dans la colonne individual_amount. Elle effectuera cette opération pour les quatre montants d'investissement jusqu'à ce qu'elle atteigne dix investisseurs.

La requête externe sélectionne simplement toutes les données du CTE per_investor, les données étant ordonnées par les colonnes montant et investors_number. Exécutez cette requête et appréciez toutes les possibilités que vous avez calculées :

Plutôt impressionnant, n'est-ce pas ?

La combinaison de deux CTE ou plus fonctionne réellement

Les expressions de table commune SQL sont un outil puissant. Elles sont utiles si vous voulez que votre code soit plus lisible, mais leur valeur n'est pas seulement cosmétique ; leurs véritables possibilités brillent si vous savez comment utiliser plusieurs CTE dans une requête ou même écrire un CTE récursif en SQL.

Comme vous l'avez vu, il n'est pas difficile de combiner deux ou plusieurs CTE. Une fois que vous savez cela, cela débloque vraiment les possibilités d'utilisation des CTE dans diverses situations. Maintenant, c'est à vous de trouver vous-même des cas où vous devrez utiliser des CTE. Et pratiquez, pratiquez, pratiquez ! Il est toujours conseillé d'utiliser notre coursRequêtes récursives pour cela. Il vous fournira des tableaux sur lesquels vous pourrez vous exercer, de sorte que vous n'aurez pas à vous soucier de créer vous-même des scénarios.

N'hésitez pas à partager votre expérience avec les CTE SQL dans notre section commentaires.