Qu'est-ce qu'une expression de table commune (CTE) en SQL ?

L'expression de table commune (CTE) est une construction puissante en SQL qui permet de simplifier une requête. Les CTE fonctionnent comme des tables virtuelles (avec des enregistrements et des colonnes), créées pendant l'exécution d'une requête, utilisées par la requête et éliminées après l'exécution de la requête. Les CTE servent souvent de pont pour transformer les données des tables sources au format attendu par la requête.

Une expression de table commune, ou CTE, est un ensemble de résultats temporaire nommé créé à partir d'une simple instruction SELECT et pouvant être utilisé dans une instruction SELECT ultérieure. Chaque CTE SQL est comme une requête nommée, dont le résultat est stocké dans une table virtuelle (un CTE) pour être référencé plus tard dans la requête principale.

La meilleure façon d'apprendre les expressions de table courantes est de s'exercer. Je recommande le cours interactif de LearnSQL.frRequêtes récursives de . Il contient plus de 100 exercices qui enseignent les CTE en commençant par les bases et en progressant vers des sujets avancés tels que les expressions de table commune récursives.

Les ETC simplifient les requêtes

Commençons par la syntaxe d'une expression de table commune.

WITH my_cte AS (
  SELECT a,b,c
  FROM T1
)
SELECT a,c
FROM my_cte
WHERE ....

Le nom de cette CTE est my_cteet la requête CTE est SELECT a,b,c FROM T1. L'ETC commence par le mot-clé WITH, après quoi vous indiquez le nom de votre ETC, puis le contenu de la requête entre parenthèses. La requête principale vient après la parenthèse fermante et fait référence à l'ETC. Ici, la requête principale (également appelée requête externe) est SELECT a,c FROM my_cte WHERE ….

Cet excellent article d'introduction aux ETC contient de nombreux exemples d'entrée de gamme. Parmi les autres articles d'introduction, citons "Improving Query Readability with Common Table Expressions" et "When Should I Use a Common Table Expression (CTE)", qui expliquent les expressions de table communes.

Apprendre les expressions de table commune SQL à l'aide d'exemples

Dans cette section, nous présentons quelques exemples de requêtes SQL utilisant des expressions de tables communes. Tous les exemples sont basés sur la base de données d'une chaîne de magasins de téléphones portables. La table salesillustrée ci-dessous, contient un enregistrement par produit vendu :

Dans le premier exemple, nous obtenons un rapport contenant les mêmes enregistrements que ceux de la table sales mais nous ajoutons une colonne supplémentaire avec le prix de l'article le plus cher vendu dans la même succursale ce jour-là. Pour obtenir le prix de l'article le plus cher, nous utilisons une expression de table commune comme celle-ci :

WITH highest AS (
  SELECT
    branch,
    date,
    MAX(unit_price) AS highest_price
  FROM sales
  GROUP BY branch, date
)
SELECT
  sales.*,
  h.highest_price
FROM sales
JOIN highest h
  ON sales.branch = h.branch
    AND sales.date = h.date

Cette requête définit un CTE SQL appelé highest dont le résultat crée une table virtuelle. La table virtuelle comporte les colonnes branche, date et highest_price, qui contiennent respectivement le nom de la branche, la date et le prix unitaire le plus élevé vendu ce jour-là dans cette branche.

Ensuite, la requête externe est exécutée et utilise la table virtuelle comme s'il s'agissait d'une requête ordinaire. highest comme s'il s'agissait d'une table normale. Enfin, nous joignons le résultat de l'ETC highest avec la table sales avec la table virtuelle.

Le résultat de l'ensemble de la requête est présenté ci-dessous :

Dans l'exemple suivant, nous générons un rapport indiquant les revenus quotidiens les plus élevés par branche.

WITH daily_revenue AS (
  SELECT
    branch,
    date,
    SUM(unit_price * quantity) AS daily_revenue
  FROM   sales
  WHERE EXTRACT(YEAR FROM date) = 2021
  GROUP BY 1,2
)
SELECT
  branch,
  MAX(daily_revenue) max_daily_revenue
FROM daily_revenue
GROUP BY 1
ORDER BY 2 DESC

Dans cette requête, un CTE appelé daily_revenue contient les colonnes branch, date et daily_revenue pour chaque date de 2021. Ensuite, dans la requête externe, nous obtenons le montant le plus élevé des recettes pour chaque branche en 2021. Le rapport est classé par max_daily_revenue dans l'ordre décroissant.

Vous trouverez ci-dessous les résultats de cette requête.

Utilisation des CTE dans les requêtes SQL avancé

Vous pouvez définir deux ou plusieurs CTE et les utiliser dans la requête principale. Dans l'exemple suivant, nous vous montrons comment diviser et organiser une longue requête à l'aide d'ETC SQL. En nommant les différentes parties de la requête, les CTE en facilitent la lecture.

Supposons que nous souhaitions obtenir un rapport indiquant le chiffre d'affaires mensuel total à Londres en 2021, mais que nous souhaitions également obtenir le chiffre d'affaires de chaque succursale à Londres dans le même rapport. Dans ce cas, nous créons deux CTE, puis nous les joignons dans la requête principale.

WITH london1_monthly_revenue AS (
  SELECT
    EXTRACT(MONTH FROM date) as month,
    SUM(unit_price * quantity) AS revenue
  FROM sales
  WHERE EXTRACT(YEAR FROM date) = 2021
    AND branch = 'London-1'
  GROUP BY 1
),
london2_monthly_revenue AS (
  SELECT
    EXTRACT(MONTH FROM date) as month,
    SUM(unit_price * quantity) AS revenue
  FROM sales
  WHERE EXTRACT(YEAR FROM date) = 2021
    AND branch = 'London-2'
  GROUP BY 1
)
SELECT
  l1.month,
  l1.revenue + l2.revenue AS london_revenue,
  l1.revenue AS london1_revenue,
  l2.revenue AS london2_revenue
FROM london1_monthly_revenue l1, london2_monthly_revenue l2
WHERE l1.month = l2.month

Dans la requête ci-dessus, nous définissons deux CTE, london1_monthly_revenue et london2_monthly_revenueafin d'obtenir les recettes mensuelles en 2021 pour chaque succursale à Londres. Enfin, nous joignons les deux CTE à l'aide de la colonne du mois et calculons le revenu total pour Londres en additionnant les revenus des deux succursales.

Le résultat de la requête est présenté ci-dessous :

Dans l'exemple suivant, nous obtenons un rapport informant chaque succursale de la date à laquelle le billet le plus important (c'est-à-dire le montant de la combinaison article-quantité) a été vendu et du montant de ce billet. Pour ce faire, nous devons créer un CTE qui classe les tickets (la colonne position est le classement) pour chaque succursale en fonction du montant du ticket.

WITH tickets AS (
  SELECT distinct
    branch,
    date,
    unit_price * quantity AS ticket_amount,
    ROW_NUMBER() OVER (
      PARTITION BY branch
      ORDER by unit_price * quantity DESC
    ) AS position
  FROM sales
  ORDER BY 3 DESC
)
SELECT
  branch,
  date,
  ticket_amount
FROM tickets
WHERE position =1

Dans la requête ci-dessus, nous créons un CTE avec les colonnes branch, date, ticket_amount, et position. Ensuite, dans la requête externe, nous filtrons uniquement les enregistrements contenant position = 1 pour obtenir ce que nous voulons, le billet le plus élevé par branche.

Le résultat de la requête est illustré ci-dessous :

Les ETC imbriqués dans les requêtes SQL

L'exemple suivant montre un ETC imbriqué. L'idée est de générer un rapport avec tous les articles dont le prix est supérieur à 90 $ et la quantité de ces articles vendus par la succursale London-2.

WITH over_90_items AS (
  SELECT DISTINCT
    item,
    unit_price
  FROM sales
  WHERE unit_price >=90
),
london2_over_90 AS (
  SELECT
    o90.item,
    o90.unit_price,
    coalesce(SUM(s.quantity), 0) as total_sold
  FROM over_90_items o90
  LEFT JOIN sales s
  ON o90.item = s.item AND s.branch = 'London-2'
  GROUP BY o90.item, o90.unit_price
)
SELECT item, unit_price, total_sold
FROM   london2_over_90;

Le premier CTE est over_90_itemsqui sélectionne tous les articles dont le prix est supérieur ou égal à 90 $. La deuxième ETC est london2_over_90qui sélectionne la quantité vendue par London-2 pour chaque article inclus dans l'ETC over_90_items. Cette requête comporte un ETC imbriqué - notez le FROM dans le deuxième ETC qui fait référence au premier. Nous utilisons LEFT JOIN sales parce que London-2 n'a peut-être pas vendu tous les articles contenus dans over_90_items.

Le résultat de la requête est le suivant :

Avant de passer à la section suivante, j'ai quelques articles à suggérer sur les expressions de table courantes. Les articles "SQL CTEs Explained with Examples" et "Where Can I Find Good SQL CTE Exercises" contiennent tous deux de nombreux exemples et exercices.

Requêtes récursives et les expressions de table courantes

Dans les bases de données relationnelles, il est courant d'avoir des tables représentant des hiérarchies de données telles que employé-gestionnaire, partie-sous-partie ou parent-enfant. Pour parcourir ces hiérarchies dans n'importe quelle direction (de haut en bas ou de bas en haut), les bases de données utilisent une construction appelée CTE récursive.

RECURSIVE est un mot réservé pour définir un ETC permettant de parcourir une structure de données récursive. La forme de la requête récursive est la suivante :

WITH RECURSIVE  cte_name AS (
     CTE_query_definition  -- non recursive query term
UNION ALL
     CTE_query_definition  -- recursive query term
)
SELECT * FROM cte_name;

Les requêtes récursives sortent du cadre de cet article d'introduction, mais j'en ai trois autres à suggérer à ceux qui veulent en savoir plus sur le sujet : "Comment organiser les requêtes SQL avec les ETC", "Doit in SQL : RecursiveSQL Tree Traversal" et "Get to Know the Power of SQL Requêtes récursives". Ils expliquent les requêtes récursives en détail avec de nombreux exemples.

Les CTE SQL sont une ressource puissante

Les expressions de table communes sont une ressource puissante du langage SQL. Elles nous permettent de créer des requêtes plus lisibles et de gérer les différences de format entre les données des tables et les données des rapports. Dans cet article, nous avons expliqué ce qu'est une CTE et comment l'utiliser dans différents types de requêtes. Nous avons également mentionné que les CTE peuvent être utilisés dans des requêtes récursives.

Je recommande le cours Requêtes récursives à l'adresse LearnSQL.froù vous apprendrez de manière interactive à travailler avec des expressions de tables communes en SQL. Vous apprenez également à traiter des structures de données récursives telles que des graphes et des arbres en SQL en utilisant des CTE récursifs.

Consultez également la piste SQL avancéoù vous allez au-delà des bases pour devenir un maître du langage SQL. Si vous devez vous préparer à un entretien SQL, un article intéressant intitulé "Top 5 SQL CTE Interview Questions" aborde les expressions de table courantes d'un autre point de vue.

Si vous avez appris à utiliser les CTE, vous avez fait un pas de plus vers le métier de développeur SQL. Continuez à progresser !