5 exemples pratiques de la clause WITH de SQL

Ces cinq cas d'utilisation pratiques vous donneront envie d'utiliser quotidiennement la clause SQL WITH.

La clause WITH peut vous aider à écrire des requêtes SQL lisibles et à décomposer des calculs complexes en étapes logiques. Elle a été ajoutée à SQL pour simplifier les longues requêtes compliquées. Dans cet article, nous allons vous montrer 5 exemples pratiques de la clause WITH et vous expliquer comment son utilisation rend les requêtes SQL plus lisibles.

La clause WITH est également appelée expression de table commune (CTE). Apprendre à l'utiliser n'est pas une connaissance de base du langage SQL, c'est pourquoi nous vous recommandons de vous exercer AVEC un cours interactif structuré, tel que notre cours Requêtes récursives cours. En plus de 100 exercices, vous apprendrez la syntaxe de base des CTE ainsi que les concepts avancés des CTE imbriqués et récursifs.

Qu'est-ce que la clause WITH en SQL ?

L'autre nom de la clause WITH, Common Table Expression, donne un indice de ce qu'elle fait. Une clause WITH vous permet de créer une instruction SELECT qui renvoie un résultat temporaire ; vous pouvez nommer ce résultat et le référencer dans une autre requête. En fait, il s'agit d'une sous-requête nommée, mais elle peut être récursive. Voici comment une clause WITH se compare à une sous-requête.

Le CTE ne peut pas s'exécuter sans la requête principale, il doit donc être suivi d'une requête régulière. Cette requête est généralement aussi une instruction SELECT, mais elle peut être INSERT, UPDATE ou DELETE.

Considérez un CTE comme une table temporaire avec une requête qui doit toujours être exécutée pour que la table soit utilisée. Comme il s'agit d'une sorte de table, vous pouvez faire référence à l'élément CTE dans la clause FROM comme à une table ordinaire.

Syntaxe de base de la clause WITH

Ce que nous avons expliqué ci-dessus peut être représenté par un code SQL :

WITH cte AS (
  SELECT …
  FROM table
)

SELECT …
FROM cte;

Jetons un coup d'œil aux parties clés de la syntaxe. L'ETC est toujours, sans exception, initié par la clause WITH. Le nom de l'unité CTE suit, qui est cte dans l'exemple générique ci-dessus. Après le nom de l'ECC vient le mot clé AS. Ce qui suit entre parenthèses est la définition du CTE. Dans un exemple très simple, il s'agit simplement d'une déclaration SELECT. Enfin, il y a la requête principale (comme pour les sous-requêtes ordinaires) qui fait référence au CTE.

Attention, il s'agit de la syntaxe de base. Nous allons y revenir dans les exemples. En cours de route, vous verrez également comment cette syntaxe de base peut être étendue en écrivant des CTE multiples ou imbriqués.

Exemples 1 et 2 : Informations sur les salaires

Exemple de données

Nous utiliserons le tableau employees dans les deux premiers exemples. Il 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é.
• salary - Le salaire de l'employé.
• department - Le département de l'employé.

Voici les données :

Et voici un lien vers une requête que vous pouvez exécuter pour créer le tableau:

Exemple 1 : Montrer comment le salaire de chaque employé se compare à la moyenne de l'entreprise.

Pour résoudre ce problème, vous devez afficher toutes les données du tableau employees. Vous devez également afficher le salaire moyen de l'entreprise, puis la différence avec le salaire de chaque employé.

Voici la solution :

WITH avg_total_salary AS (
	SELECT AVG(salary) AS average_company_salary
	FROM employees
)
	
SELECT id,
	 first_name,
	 last_name,
	 salary,
	 department,
	 average_company_salary,
	 salary - average_company_salary  AS salary_difference
FROM employees, avg_total_salary;

Tout d'abord, lancez le CTE en utilisant la clause WITH. Le nom du CTE ('avg_total_salary') vient ensuite. Ouvrez les parenthèses après AS, et écrivez l'instruction normale SELECT. Elle calcule le salaire moyen de l'entreprise.

Pour utiliser la sortie du CTE, écrivez une autre instruction SELECT comme requête principale. Remarquez que le CTE et la requête principale ne sont séparés que par des parenthèses. La nouvelle ligne n'est là que pour faciliter la lecture. La requête principale sélectionne toutes les colonnes de la table et du CTE. Le CTE et la table employees sont jointes comme deux tables normales. De plus, il y a la colonne calculée salary_difference.

La requête renvoie ceci :

Nous pouvons voir les données de tous les employés et voir dans quelle mesure leur salaire est supérieur ou inférieur à la moyenne de l'entreprise. Par exemple, le salaire de Melissa Allman est supérieur de 293,48 à la moyenne. Le salaire de Nina Longhetti est inférieur de 993,20 à la moyenne.

Exemple 2 : Affichez les données des employés ainsi que le salaire moyen du département et de l'entreprise.

Allons un peu plus loin que dans l'exemple précédent. Cette fois, vous n'avez pas à calculer la différence entre les salaires. Mais vous devez afficher toutes les données analytiques des employés, puis le salaire moyen du département et de l'entreprise.

Vous pouvez le faire en écrivant deux CTE au lieu d'un :

WITH avg_total_salary AS (
	SELECT AVG(salary) AS average_company_salary
	FROM employees
),
	
avg_dpt_salary AS (
	SELECT department,
		 AVG(salary) AS average_department_salary
	FROM employees
	GROUP BY department
)
	
SELECT e.id,
	   e.first_name,
	   e.last_name,
	   e.salary,
	   average_department_salary,
	   average_company_salary
FROM employees e JOIN avg_dpt_salary ads ON e.department = ads.department, avg_total_salary;

Rappelez-vous que nous avons mentionné que la syntaxe de base pouvait être étendue. Voici un exemple de cette possibilité. Oui, vous pouvez écrire plusieurs CTE, l'un après l'autre, et ensuite les référencer dans la requête principale.

Voyons comment faire. Tout est habituel avec le premier CTE : la clause WITH, le nom du CTE, AS, et la définition du CTE entre parenthèses. Ce CTE calcule le salaire moyen de l'entreprise.

Le deuxième CTE calcule le salaire moyen par département. Mais il y a quelque chose de différent ici ! Remarquez deux choses essentielles : il n'y a pas d'autre clause WITH et les CTE sont séparés par une virgule.

C'est ainsi que vous écrivez plusieurs CTE : la clause WITH est écrite uniquement avant la première requête (elle ne doit pas apparaître avant un autre CTE !), et une virgule doit séparer les CTE.

Quel que soit le nombre de CTE, ils doivent toujours être suivis de la requête principale. Et il n'y a pas de virgule entre le dernier CTE et la requête principale !

La requête principale joint la table avec les deux CTE et sélectionne les données pertinentes. La jointure se fait de la même manière qu'avec les tables normales : spécifiez le type de jointure et la colonne sur laquelle les tables seront jointes.

Voici le résultat :

Vous avez maintenant toutes les données en un seul endroit : les salaires individuels, la moyenne du département et la moyenne de l'entreprise. A partir de là, vous pouvez aller plus loin dans votre analyse de données.

Exemples 3 et 4 : Revenu

Exemple de données

Dans les deux exemples suivants, nous utiliserons la table revenue. Nous l'avons créée avec la requête CREATE TABLE que vous trouverez ici. Elle possède les colonnes suivantes :

• id - L'ID de l'enregistrement du revenu.
• year - L'année du revenu.
• quarter - Le trimestre du revenu.
• revenue_amount - Le montant de la recette.

Familiarisez-vous avec les données présentées ci-dessous :

Exemple 3 : Afficher chaque année avec le revenu annuel et le revenu total correspondants

Utilisez les données ci-dessus pour afficher toutes les années disponibles. Pour chaque année, indiquez le revenu annuel et le revenu total de l'entreprise pour toutes les années.

Cette tâche est similaire à l'exemple 1, mais nous utiliserons une fonction d'agrégation différente :

WITH total_revenue AS (
	SELECT SUM(revenue_amount) AS total_company_revenue
	FROM revenue
)
	
SELECT year,
	 SUM (revenue_amount) AS annual_revenue,
	 total_company_revenue
FROM revenue, total_revenue
GROUP BY year, total_company_revenue
ORDER BY year;

Le CTE utilise la fonction d'agrégation SUM() pour calculer le revenu total de l'entreprise.

La requête principale joint le CTE avec la table revenue. Nous utilisons cette instruction SELECT pour afficher l'année, puis calculer le revenu annuel pour chaque année et afficher le revenu total.

Voici la sortie de la requête :

Le résultat montre que le revenu annuel en 2019 était de 10 489 632,27. En 2020, il était de 20 919 087,62, et ainsi de suite. Si l'on additionne les quatre années, le revenu total est de 80 812 015,89.

Exemple 4 : Indiquez chaque trimestre ainsi que le revenu trimestriel le plus élevé et le plus bas pour cette année et pour l'ensemble de l'année.

Vous devez répertorier toutes les années et tous les trimestres avec les recettes correspondantes. Jusqu'à présent, tout va bien. Ensuite, vous devez indiquer le revenu trimestriel le plus faible pour cette année et le revenu trimestriel le plus faible pour toutes les années. Ensuite, vous faites de même pour le revenu le plus élevé.

Voici la solution :

WITH yearly_min_max_quarter AS (
	SELECT year,
		   MIN(revenue_amount) AS minimum_quarterly_revenue_annual,
		   MAX(revenue_amount) AS maximum_quarterly_revenue_annual
FROM revenue
GROUP BY year),

min_max_overall AS (
	SELECT MIN(revenue_amount) AS overall_min_revenue,
	       MAX(revenue_amount) AS overall_max_revenue
FROM revenue)

SELECT r.year,
	   quarter,
	   revenue_amount,
	   minimum_quarterly_revenue_annual,
	   overall_min_revenue,
	   maximum_quarterly_revenue_annual, 
	   overall_max_revenue
FROM revenue r 
JOIN yearly_min_max_quarter ymmq 
ON r.year = ymmq.year, min_max_overall
ORDER BY year, quarter ASC;

Cette solution nécessite à nouveau deux CTE. Vous savez probablement déjà comment l'écrire, mais nous allons expliquer chaque étape.

Le premier CTE trouve le revenu trimestriel le plus bas et le plus élevé pour chaque année. Pour ce faire, utilisez les fonctions MIN() et MAX() et regroupez les données par année.

Ensuite, écrivez le deuxième CTE sans la clause WITH et séparez-le du premier par une virgule. Ce CTE renvoie le revenu trimestriel le plus bas et le plus élevé de toutes les années.

La requête principale joint la table revenue avec le premier, puis avec le deuxième CTE. Elle affichera les données de la table et du CTE comme une seule table.

Jetez un coup d'oeil au résultat :

En plus du chiffre d'affaires de chaque trimestre, vous disposez maintenant d'autres informations importantes. Vous savez que le revenu le plus faible en 2019 était de 984 157,15, et vous pouvez voir que cela s'est produit au troisième trimestre. Le revenu le plus faible en 2020 (2 497 441,68) a été enregistré au cours des trois premiers mois de l'année. Vous pouvez analyser toutes les autres années de la même manière en regardant la colonne minimum_quarterly_revenue_annual.

La valeur de la colonne overall_min_revenue représente le revenu le plus faible de tous les temps. Elle est la même dans toutes les lignes et correspond au troisième trimestre de 2019. Les deux colonnes suivantes sont similaires, mais elles indiquent le revenu le plus élevé au lieu du revenu le plus faible. En d'autres termes, le revenu le plus élevé en 2019 était de 5 417 884,15, ce qui correspond au T4. Le revenu le plus élevé de tous les temps est de 12 478 945,47, réalisé au 1er trimestre 2021.

Exemple 5 : Heures travaillées

Exemple de données

La table du dernier exemple est nommée employé_feuille de temps. Elle enregistre les heures de travail des employés. Ses colonnes sont explicites, nous allons donc nous contenter d'examiner les données :

Exemple 5 : Afficher les heures de travail moyennes les plus basses et les plus hautes

Cet exemple vous demande d'abord de trouver la moyenne des heures de travail par employé, puis d'afficher uniquement la moyenne la plus basse et la plus haute.

Voici le code pour résoudre ce problème :

WITH login_times AS (
SELECT 
id,
employee_id,
start_time,
end_time,
end_time - start_time AS working_hours
FROM employee_timesheet),

avg_login AS (
	SELECT 
employee_id,
AVG(working_hours) AS average_working_hours
FROM login_times
GROUP BY employee_id)

SELECT MIN(average_working_hours) AS min_average_working_hours,
	 MAX(average_working_hours) AS max_average_working_hours
FROM avg_login;

Au début, cela pourrait ressembler à n'importe quelle requête avec deux CTE. Regardez de plus près, et vous verrez que ce n'est pas le cas ! Oui, il y a deux CTEs. Mais la différence est que le deuxième CTE fait référence au premier, ce qui n'était pas le cas dans les exemples 2 et 4.

C'est ce qu'on appelle un CTE imbriqué. Le premier CTE est utilisé pour obtenir la différence entre le début et la fin de la session ; c'est ainsi que l'on obtient les heures de travail pour chaque session.

Un employé a plusieurs sessions, nous devons donc trouver la durée moyenne de la session, c'est-à-dire le nombre moyen d'heures travaillées. Le deuxième CTE est utilisé à cette fin. En termes de syntaxe, rien de nouveau, si ce n'est que le CTE fait référence au premier CTE dans la clause FROM.

Ensuite, dans la requête principale, nous faisons ce que l'on appelle une agrégation multi-niveaux. Nous prenons les heures de travail moyennes par employé (agrégation de premier niveau) et trouvons le minimum et le maximum de ces valeurs (agrégation de second niveau).

Voici le résultat :

Le résultat nous indique que la moyenne d'heures de travail par employé la plus basse est de 5 heures, 57 minutes et 4 secondes. La moyenne la plus élevée est de 13:24:29.

Avantages de la clause SQL WITH

Les cinq exemples que nous vous avons présentés ont été soigneusement sélectionnés pour illustrer l'utilisation typique et les avantages de la clause WITH.

Le premier avantage apparaît clairement lorsque vous avez plusieurs étapes de calcul, comme nous l'avons vu dans les exemples ci-dessus. En utilisant la clause WITH, vous pouvez organiser le code de manière élégante et le diviser en parties logiques.

Lorsque les calculs deviennent plus compliqués, la longueur et la complexité du code augmentent également. L'utilisation de la clause WITH permet de contrôler ce phénomène. Bien que les codes des exemples ci-dessus puissent sembler longs, ils seraient beaucoup plus longs (et moins lisibles) si nous utilisions des sous-requêtes au lieu de la clause WITH. Et comme vous l'avez vu dans le dernier exemple, l'utilisation de la clause WITH vous permet de calculer facilement des agrégations multi-niveaux.

Un autre avantage est que la clause WITH vous permet d'écrire des requêtes récursives en SQL, ce qui vous ouvre un tout nouveau monde de possibilités.

L'apprentissage de la clause WITH peut parfois être difficile, c'est pourquoi nous avons préparé un guide qui vous aidera à structurer votre approche. Bon apprentissage, et nous savons que la clause SQL WITH vous récompensera amplement de vos efforts !