SQLtest

📚 leçons & ✍️ pratique

Analyse de la tâche SQL : Trouver des vols avec une escale

Cette tâche illustre l'utilisation d'un des outils les plus puissants en SQL pour analyser les relations dans les données : joindre une table à elle-même (SELF JOIN). Décomposons la logique de la solution étape par étape.

Description de la tâche

Nous devons trouver toutes les options de vol possibles de l'aéroport de Pulkovo (LED) à Bryansk (BZK) avec exactement une escale.

Idée clé

Un trajet avec une escale consiste en deux vols indépendants reliés par un aéroport intermédiaire.

• Segment 1 : Départ de LED vers un aéroport d'escale C.
• Segment 2 : Départ du même aéroport C et arrivée à BZK.

Notre objectif est de trouver toutes les paires de vols où l'aéroport d'arrivée du premier vol correspond à l'aéroport de départ du second.

Approche technique : SELF JOIN

Pour implémenter cette logique, nous considérons la table des vols comme deux tables différentes, en utilisant des alias comme first_leg (premier segment) et second_leg (second segment).

Plan de solution étape par étape

1. Définir le premier segment du trajet. On utilise l'alias first_leg pour trouver tous les vols partant de LED.
   • Condition : first_leg.departure_airport = 'LED'.
2. Définir le second segment du trajet. Avec l'alias second_leg, on cherche tous les vols arrivant à BZK.
   • Condition : second_leg.arrival_airport = 'BZK'.
3. Trouver le point de connexion (condition de JOIN clé). On relie les deux ensembles de vols sur la condition que l'aéroport d'arrivée du premier segment doit correspondre à l'aéroport de départ du second.
   • Condition de connexion : first_leg.arrival_airport = second_leg.departure_airport.
   • Cet aéroport commun est notre escale (connection_airport).
4. Vérifier le temps d'escale : Il faut aussi s'assurer que le second vol décolle après l'arrivée du premier. Condition :
   • second_leg.scheduled_departure > first_leg.scheduled_arrival
5. Former le résultat. Après la jointure, toutes les informations nécessaires sont dans une ligne. On peut sélectionner :
   • Aéroport de départ (first_leg.departure_airport)
   • Aéroport d'escale (first_leg.arrival_airport ou second_leg.departure_airport)
   • Aéroport d'arrivée (second_leg.arrival_airport)
6. Tri. Enfin, il suffit de trier la liste par l'aéroport d'escale, comme demandé.

Ainsi, le SELF JOIN permet de "déplier" une table et de faire correspondre ses lignes entre elles pour trouver des itinéraires complexes.

        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 (second_leg.scheduled_arrival - first_leg.scheduled_departure)
        limit 1;