Exemple d'auto-jointure SQL : le vol avec correspondance le plus rapide

Q: Comment trouver des vols avec correspondance en SQL ?

Joignez la table des vols à elle-même : la première copie est le premier segment, la seconde le second. Joignez-les sur l'égalité entre l'aéroport d'arrivée du premier segment et l'aéroport de départ du second, et exigez que le second départ soit postérieur à la première arrivée.

Q: Comment calculer le temps entre deux timestamps dans PostgreSQL ?

Soustrayez-les : scheduled_arrival - scheduled_departure renvoie un interval. Dans MySQL, utilisez TIMESTAMPDIFF(MINUTE, start_time, end_time).

Q: Comment trouver des trajets avec deux correspondances ou plus ?

Ajoutez une auto-jointure par segment supplémentaire, ou utilisez une CTE récursive qui prolonge un trajet d'un vol à la fois (leçon 6.6).

Leçon 12.1 · Temps de lecture : ~6 min · Exercices pratiques ↓

Cette leçon détaille la solution de l'exercice SQL Le vol le plus rapide sur la base de données Bookings (PostgreSQL). Vous verrez comment une auto-jointure transforme une seule table flights en deux segments d'un trajet avec correspondance, comment s'assurer que la correspondance est possible et comment choisir l'option la plus rapide.

L'exercice : trouver le moyen le plus rapide d'aller de l'aéroport de Poulkovo à Saint-Pétersbourg (LED) à Briansk (BZK) le 16 août 2017, avec exactement une correspondance. Pour chaque option, il faut les numéros de vol, l'aéroport de correspondance, la durée de chaque vol, le temps d'attente et la durée totale du trajet. Les temps sont calculés à partir de scheduled_departure et scheduled_arrival.

Essayez d'abord de résoudre l'exercice vous-même ; la requête complète est cachée à la fin de la leçon.

L'idée clé : un trajet, ce sont deux lignes

Un trajet avec une correspondance n'est pas un vol, mais deux :

Segment 1 : de LED vers un aéroport X.
Segment 2 : de ce même aéroport X vers BZK.

Les deux segments sont des lignes de la même table flights. Pour les mettre côte à côte dans une ligne de résultat, joignez la table à elle-même et donnez à chaque copie son propre alias, first_leg et second_leg. C'est une auto-jointure (self-join, voir leçon 5.7).

Solution pas à pas

1. Choisir les vols candidats

Le premier segment part de LED, le second arrive à BZK, et les deux partent le 16 août :

SELECT COUNT(*) AS flights_from_led
FROM flights
WHERE departure_airport = 'LED'
  AND scheduled_departure BETWEEN '2017-08-16' AND '2017-08-17';

Résultat : 33 vols partent de LED ce jour-là ; chacun peut être le premier segment.

2. Relier les segments

Les segments doivent se rejoindre dans le même aéroport, et le second vol doit partir après l'atterrissage du premier :

SELECT
    first_leg.flight_no,
    first_leg.arrival_airport AS connection_airport,
    second_leg.flight_no,
    second_leg.scheduled_departure - first_leg.scheduled_arrival AS connection_time,
    second_leg.scheduled_arrival - first_leg.scheduled_departure AS total_trip_time
FROM flights AS first_leg
JOIN flights AS second_leg
    ON first_leg.arrival_airport = second_leg.departure_airport
   AND second_leg.arrival_airport = 'BZK'
   AND second_leg.scheduled_departure > first_leg.scheduled_arrival
WHERE first_leg.departure_airport = 'LED'
  AND first_leg.scheduled_departure BETWEEN '2017-08-16' AND '2017-08-17'
  AND second_leg.scheduled_departure BETWEEN '2017-08-16' AND '2017-08-17'
ORDER BY total_trip_time;

Résultat : 18 options via 3 aéroports de correspondance ; la plus rapide passe par Moscou-Cheremetievo (SVO) et dure 1 heure 25 minutes.

first_leg.arrival_airport = second_leg.departure_airport est le point de correspondance.
second_leg.scheduled_departure > first_leg.scheduled_arrival écarte les options où le second avion part avant l'atterrissage du premier. Sans cette condition, la requête renverrait des trajets impossibles.

3. Calculer les durées

Dans PostgreSQL, soustraire un timestamp d'un autre donne un interval, comme 01:25:00 :

premier vol : first_leg.scheduled_arrival - first_leg.scheduled_departure ;
temps d'attente : second_leg.scheduled_departure - first_leg.scheduled_arrival ;
trajet total : second_leg.scheduled_arrival - first_leg.scheduled_departure, du premier départ à l'arrivée finale.

4. Garder l'option la plus rapide

Triez par durée totale du trajet et gardez la première ligne avec LIMIT 1. Si plusieurs options peuvent avoir la même durée, LIMIT 1 en renvoie une seule ; pour toutes les afficher, comparez plutôt au minimum, par exemple avec RANK() (leçon 7.2).

Points d'attention

La correspondance ne dure que 5 minutes. Dans l'option la plus rapide, le second vol quitte SVO cinq minutes après l'atterrissage du premier. L'exercice exige seulement que le second vol parte plus tard ; un vrai système de réservation imposerait un temps de correspondance minimal, par exemple second_leg.scheduled_departure >= first_leg.scheduled_arrival + INTERVAL '1 hour'.
Filtrez les deux segments par date. Sans la condition de date sur second_leg, la requête relierait aussi des vols des jours suivants.
BETWEEN inclut les deux bornes. BETWEEN '2017-08-16' AND '2017-08-17' inclut aussi un vol à minuit pile le 17 août. Pour des journées entières, >= '2017-08-16' AND < '2017-08-17' est plus précis.

Spoiler : la requête SQL avec la solution est cachée ci-dessous. Cliquez pour l'afficher.


select
    first_leg.flight_no flight1_no,
    first_leg.departure_airport,
    first_leg.arrival_airport connection_airport,
    first_leg.scheduled_arrival - first_leg.scheduled_departure flight1_time,
    second_leg.scheduled_departure - first_leg.scheduled_arrival connection_time,
    second_leg.flight_no flight2_no,
    second_leg.arrival_airport,
    second_leg.scheduled_arrival - second_leg.scheduled_departure flight2_time,
    second_leg.scheduled_arrival - first_leg.scheduled_departure total_trip_time
from flights first_leg
join flights second_leg on first_leg.arrival_airport = second_leg.departure_airport
    and second_leg.arrival_airport = 'BZK'
    and second_leg.scheduled_departure > first_leg.scheduled_arrival
where first_leg.departure_airport = 'LED'
    and first_leg.scheduled_departure between '2017-08-16' and '2017-08-17'
    and second_leg.scheduled_departure between '2017-08-16' and '2017-08-17'
order by total_trip_time
limit 1;

Points clés :

Un trajet avec une correspondance, ce sont deux lignes de la même table, reliées par une auto-jointure avec deux alias.
La condition de correspondance est first_leg.arrival_airport = second_leg.departure_airport.
Le second vol doit partir après l'arrivée du premier ; sinon le trajet est impossible.
Dans PostgreSQL, la soustraction de timestamps donne un interval, qu'on peut comparer et trier.
ORDER BY ... LIMIT 1 renvoie la meilleure option ; utilisez RANK() pour garder les ex æquo.

Questions fréquentes

Comment trouver des vols avec correspondance en SQL ?