Qu'est-ce qu'une CTE en SQL Server ?

Qu'est-ce qu'une CTE, et comment écrire une CTE dans SQL Server ? Rejoignez-nous dans un voyage où nous verrons toutes les utilisations typiques d'une CTE dans SQL Server.

Les CTE (ou Common Table Expressions) sont une fonctionnalité SQL utilisée pour définir un résultat temporaire nommé. Vous pouvez l'imaginer comme une table temporaire dont le résultat n'est disponible que lorsque la requête principale est exécutée. C'est pratique car le résultat d'une CTE n'est stocké nulle part mais peut toujours être référencé dans la requête comme n'importe quelle autre table. Les CTE sont le plus souvent utilisés dans l'instruction SELECT, mais ils peuvent également être utilisés dans les instructions INSERT, UPDATE, et DELETE.

Les CTE sont une fonctionnalité SQL relativement récente. Ils ont été introduits dans la norme SQL : 1999 (SQL 3). En 2005, elles ont été mises à disposition dans SQL Server 2005.

Vous pouvez acquérir une expérience pratique des CTE dans SQL Server dans notre cours interactif Requêtes récursives in MS SQL Server. Vous apprendrez la syntaxe des CTE dans SQL Server, comment utiliser plus d'un CTE, comment les imbriquer et comment les faire fonctionner dans SELECT, INSERT, UPDATE et DELETE. Il y a aussi une section qui explique la récursion et comment écrire un CTE récursif.

Nous couvrirons tous ces sujets dans cet article. Cependant, nous ne pouvons pas rivaliser ici avec les 112 exercices interactifs proposés par le cours. En dehors du cours et de cet article, il existe également d'autres moyens d'apprendre les CTE.

Syntaxe des CTE de SQL Server

La syntaxe de base des CTE de SQL Server est la suivante :

WITH cte AS (  
  SELECT
    ...
)

SELECT
  ...
FROM cte;

Un CTE dans SQL Server est initié en utilisant le mot-clé WITH. Il est suivi du nom du CTE (ici, cte), du mot-clé AS et des parenthèses. Les parenthèses contiennent la définition du CTE. En termes simples, il s'agit d'une instruction ordinaire SELECT, mais sous la forme d'un CTE.

La partie suivante vient après les parenthèses et s'appelle la requête principale. Sans elle, l'ETC ne fonctionne pas. Cette requête principale est, dans l'exemple générique, une instruction SELECT qui fait référence au CTE dans la clause FROM. Comme nous l'avons déjà mentionné, la requête principale pourrait être une instruction INSERT, UPDATE ou DELETE au lieu de SELECT.

Une autre façon d'écrire un CTE dans SQL Server est de spécifier explicitement les colonnes, ce qui ressemble à ceci :

WITH cte (cte_columns) AS (
  SELECT
    ...	
)

SELECT
  ...
FROM cte;

La seule différence est que vous définissez explicitement les colonnes CTE avant le mot-clé AS. Ceci est utile lorsque les colonnes CTE nécessitent des alias (par exemple, lorsqu'elles contiennent des fonctions) ; la requête est plus lisible avec les alias attribués de la manière décrite ci-dessus.

Comme vous le verrez dans les exemples de cet article, le principal argument en faveur de l'utilisation des CTE dans SQL Server est l'amélioration de la lisibilité du code. Vous pouvez également jeter un coup d'œil à certains de ses autres avantages.

Maintenant que vous connaissez la syntaxe de base des CTE, utilisons chaque approche dans un exemple. Au fur et à mesure que nous avancerons, nous montrerons de légers changements dans la syntaxe en fonction de l'utilisation de la requête.

Exemples de CTE dans SQL Server

Avant d'écrire le moindre code, nous allons nous familiariser avec le jeu de données. La table est flight_databasequi contient les données historiques des vols. Elle possède les colonnes suivantes :

• id - L'ID de l'enregistrement et la clé primaire (PK) de la table.
• flight_id - Le numéro de vol selon les normes IATA.
• airline - Le nom de la compagnie aérienne.
• flight_date - La date du vol.
• departure_airport - L'aéroport d'où le vol a décollé.
• arrival_airport - L'aéroport où le vol a atterri.
• planned_departure - L'heure à laquelle le vol devait partir.
• actual_departure - L'heure du départ effectif du vol.
• planned_arrival - L'heure d'arrivée prévue du vol.
• actual_arrival - L'heure d'arrivée effective du vol.
• airport_distance - La distance entre les aéroports de départ et d'arrivée, en kilomètres.

Il s'agit de données fictives pour l'aéroport d'Amsterdam Schiphol. Toutes les heures sont exprimées en GMT+1, ce qui nous permet de comparer plus facilement les heures de départ et d'arrivée.

Voici quelques lignes de ce tableau :

La colonne id est unique, car c'est la clé primaire de la table. La colonne flight_id n'est pas unique, car il existe des données pour les mêmes vols à des dates différentes.

Vous pouvez créer cet ensemble de données en utilisant le code dans le lien. Si vous devez installer SQL Server, voici les instructions pour le faire.

Exemple 1 : CTE standard dans SQL Server

La tâche ici est d'écrire un CTE et de trouver les plus longs délais de départ et d'arrivée par numéro de vol IATA.

Voici la requête :

WITH delay_times AS (
  SELECT 
    flight_id,
    flight_date,
    DATEDIFF(minute, planned_departure, actual_departure) AS departure_delay,
    DATEDIFF(minute, planned_arrival, actual_arrival) AS arrival_delay
  FROM flight_database
)

SELECT 
  flight_id,
  MAX(departure_delay) AS max_departure_delay,
  MAX(arrival_delay) AS max_arrival_delay
FROM delay_times
GROUP BY flight_id;

Le CTE commence par écrire le mot-clé WITH. Le nom du CTE est delay_times. Après le mot-clé AS et la parenthèse ouvrante, il y a une définition du CTE sous la forme d'une instruction SELECT. Elle calcule la différence entre le départ prévu et le départ réel à l'aide de la fonction DATEDIFF(). La même approche est appliquée pour le calcul de la différence entre l'arrivée prévue et l'arrivée réelle. Les deux résultats sont exprimés en minutes. Comme ces colonnes utilisent des fonctions, elles ont chacune un alias.

Après avoir fermé les parenthèses, il est temps d'écrire la requête principale. Il s'agit d'une instruction SELECT qui fait référence au CTE delay_times et utilise deux fois les fonctions d'agrégation MAX() de SQL Server pour calculer le plus grand retard au départ et à l'arrivée par vol.

La sortie se lit de la manière suivante. Le retard maximal du vol DL 47 au départ était de 5 minutes. Son retard maximal à l'arrivée était de 0 ; il est toujours arrivé à l'heure.

Exemple 2 : CTE avec des colonnes explicitement définies

L'exemple suivant est très similaire. La seule différence est que nous voulons trouver les plus petits retards au départ et à l'arrivée par vol. De plus, nous utiliserons des colonnes explicitement définies. Procédez comme suit :

WITH delay_times (flight_id, flight_date, departure_delay, arrival_delay) AS (
  SELECT 
    flight_id,
    flight_date,
    DATEDIFF(minute, planned_departure, actual_departure),
    DATEDIFF(minute, planned_arrival, actual_arrival)
  FROM flight_database
)

SELECT 
  flight_id,
  MIN(departure_delay) AS min_departure_delay,
  MIN(arrival_delay) AS min_arrival_delay
FROM delay_times
GROUP BY flight_id;

Ce CTE est à nouveau nommé delay_times. Pour définir explicitement les colonnes du CTE, écrivez-les entre parenthèses avant le mot-clé AS.

Le CTE lui-même n'est pas très différent du précédent : il utilise à nouveau la fonction DATEDIFF() pour calculer les différences de délai. Le seul changement est que les alias pour ces deux (et les deux autres) colonnes sont définis plus tôt, avec le nom du CTE.

La requête principale est presque la même que précédemment. La différence est qu'elle utilise maintenant la fonction MIN() puisque le but est de calculer les plus petits délais.

Le résultat montre que le retard le plus court du vol DL 47 était de zéro. En d'autres termes, il a été à l'heure au moins une fois. Le vol LH 2301 n'a jamais été à l'heure. Il a été retardé d'au moins 20 minutes au départ et à l'arrivée.

Exemple 3 : Un CTE imbriqué dans SQL Server

Dans SQL Server, un CTE imbriqué se produit lorsqu'il y a au moins deux CTE et que le deuxième CTE fait référence au premier. Nous en avons besoin dans l'exemple suivant. La tâche consiste à calculer la durée moyenne du vol en minutes et la vitesse moyenne du vol en km/h.

Voici le code :

WITH flight_duration AS (
  SELECT 
    flight_id,
    DATEDIFF(MINUTE, actual_departure, actual_arrival) AS minutes_of_flight,
    airport_distance
  FROM flight_database
),

average_flight_duration AS (
  SELECT 
    flight_id,
    AVG(minutes_of_flight) AS average_flight_duration,
    airport_distance
  FROM flight_duration
  GROUP BY flight_id, airport_distance
)

SELECT 
  flight_id,
  average_flight_duration,
  airport_distance/(CAST(average_flight_duration AS DECIMAL(10,2))/60) AS average_flight_speed
FROM average_flight_duration;

Le premier CTE est écrit comme d'habitude dans SQL Server : WITH Le nom du CTE est AS et l'instruction SELECT. Cette requête calcule la durée du vol en minutes. Après avoir fermé les parenthèses, vous écrivez le deuxième CTE. Mais attention, il doit y avoir une virgule entre les deux CTE. De plus, lorsque vous commencez à écrire le deuxième CTE, il n'y a pas de mot-clé WITH: vous commencez directement par le nom du CTE. Tout le reste est comme d'habitude. Ce deuxième CTE fait référence au premier CTE dans la clause FROM pour calculer la durée moyenne des vols par vol pour toutes les dates.

Il n'y a aucune différence dans la syntaxe de la requête principale. Cette requête fait référence au deuxième CTE. Il calcule la vitesse moyenne des vols en divisant la distance entre les aéroports par la durée moyenne des vols. Le résultat est converti en un nombre décimal. Il est également divisé par 60, de sorte que la vitesse moyenne sera indiquée en kilomètres par heure.

Le résultat montre que, par exemple, la durée moyenne du vol LH 987 pour atteindre la destination est de 54 minutes, avec une vitesse moyenne de 407,14 km/h.

Si vous souhaitez vous entraîner davantage, voici un autre exemple de CTE imbriqué.

Exemple 4 : Un CTE dans une instruction UPDATE

Dans cet exemple, nous allons vous montrer comment les CTE fonctionnent dans l'instruction UPDATE. De la manière dont il est présenté ci-dessous, vous pourriez également utiliser l'instruction INSERT.

La tâche consiste à mettre à jour le fichier flight_database. Pour être plus précis, nous allons mettre à jour sa colonne airport_distance. Elle contient actuellement des données en kilomètres, mais elle doit être modifiée en miles.

Voici comment procéder :

WITH distance_in_miles AS (
  SELECT 
    flight_id,
    airport_distance * 0.621371 AS airport_distance_miles
  FROM flight_database
)

UPDATE flight_database
SET airport_distance = airport_distance_miles
FROM distance_in_miles dim 
JOIN flight_database fd ON dim.flight_id = fd.flight_id;

Comme toujours, commencez par le mot-clé WITH. Le CTE distance_in_miles est utilisé pour convertir les kilomètres en miles. C'est simple, il suffit de multiplier les valeurs par 0,621371.

La requête principale est maintenant UPDATE au lieu de SELECT. Rien de difficile, il suffit de suivre la syntaxe de l'instruction. Mettez à jour la colonne airport_distance avec les valeurs de la colonne airport_distance_miles qui apparaît dans le CTE. Joignez la table et le CTE, et voilà, la table est mise à jour.

Voici plusieurs valeurs avant la mise à jour :

Et voici les mêmes lignes avec les valeurs de distance mises à jour :

Exemple 5 : Un CTE récursif dans SQL Server

Notre dernier exemple consiste à écrire un CTE récursif dans SQL Server. Il s'agit d'un CTE qui se référence lui-même. Il est le plus souvent utilisé pour interroger des données hiérarchiques (comme l'organisation d'une entreprise) ou des graphiques dont certaines ou toutes les parties sont liées (pensez à une carte routière avec les distances entre les villes). L'exemple que nous allons vous montrer est un peu plus simple que cela. L'essentiel est que vous compreniez la récursion et que vous sachiez comment la traduire en un CTE.

Supposons que l'aéroport dispose d'un certain nombre de créneaux de vol. Il y a aussi un prix par créneau. Chaque année, l'aéroport augmente le nombre de créneaux de 150 ; le prix du créneau reste le même. Nous voulons afficher le nombre de créneaux, le prix par créneau et le revenu total des créneaux pour l'année en cours et les quatre années suivantes.

Voici le CTE récursif pour le faire :

WITH airport_slots AS (
  SELECT 
    1 AS id,
    400000 AS number_of_slots,
    20574421.00 AS price_per_slot,
    CAST(20574421.00 * 400000 AS DECIMAL) AS slot_revenue
		   
  UNION ALL

  SELECT 
    id + 1,
    number_of_slots + 150,
    price_per_slot,
    CAST(price_per_slot * (number_of_slots + 150) AS DECIMAL)
  FROM airport_slots
  WHERE id <= 4
)

SELECT *
FROM airport_slots;

Une fois encore, la requête récursive commence par le mot-clé WITH. Le premier SELECT dans le CTE est appelé le membre d'ancrage. Nous définissons les valeurs de départ de l'ID, du nombre de créneaux horaires et du prix par créneau. Nous multiplions également ces deux valeurs pour obtenir le revenu des créneaux.

Vient ensuite UNION ALL, qui relie le membre d'ancrage au second SELECT (appelé membre récursif). UNION ALL stipule que le nombre de colonnes et leurs types de données doivent être identiques dans les deux instructions SELECT du CTE.

Le membre récursif fait référence au CTE lui-même. À chaque récursion, l'identifiant est incrémenté de un et le nombre d'emplacements de 150. Le prix par créneau reste le même. Le revenu des créneaux est le nombre accru de créneaux multiplié par le prix par créneau.

Nous avons également utilisé la clause WHERE pour arrêter la récursion lorsque l'ID est égal à quatre. La récursion le transformera en cinq, et une projection sur cinq ans est ce que nous voulons (actuelle + quatre prochaines années).

Après cela, c'est la même chose qu'avec n'importe quel CTE. Il y a SELECT comme requête principale.

Et voici la sortie :

Nous avons plus d'exemples de CTE récursifs sur notre blog. Nous nous en sommes tenus à la syntaxe SQL Server dans cet article, mais vous pouvez également apprendre à faire de la récursivité dans PostgreSQL et Oracle.

Les CTE de SQL Server vous rendent meilleur !

Littéralement, oui. La plupart des fonctions des CTE dans SQL Server peuvent être réalisées avec une sous-requête. Mais imaginez à quoi ressembleraient les codes ci-dessus - pas très joli ! L'une des utilisations typiques des CTE dans SQL Server est de vous aider à organiser les longues requêtes. Les CTEs rendent les requêtes plus lisibles en nommant les parties de la requête. Ainsi, vous pouvez facilement décomposer chaque partie d'un calcul complexe et rendre le calcul logique.

En sachant cela, vous et votre code T-SQL serez meilleurs. Il existe également une fonctionnalité SQL qui n'est pas possible sans les CTE : les requêtes récursives. Elles sont indispensables pour interroger des données hiérarchiques et graphiques. Vous pouvez en apprendre tous les détails dans notre cours Requêtes récursives in MS SQL Server.

Et si vous passez un entretien pour un poste SQL, n'oubliez pas de parcourir nos cinq exemples de CTE avant l'entretien !