Análisis de la tarea SQL: Encontrar vuelos con una escala

Esta tarea es un excelente ejemplo de cómo utilizar una de las herramientas más poderosas en SQL para analizar relaciones en los datos: unir una tabla consigo misma (SELF JOIN). Desglosaremos la lógica de la solución paso a paso.

Descripción de la tarea

Necesitamos encontrar todas las opciones de vuelo posibles desde el Aeropuerto de Pulkovo (LED) hasta Bryansk (BZK) que incluyan exactamente una escala.

Idea clave

Una ruta con una escala no es una, sino dos vuelos independientes conectados por un aeropuerto intermedio.

• Segmento 1: Salida de LED hacia un cierto aeropuerto de escala C.
• Segmento 2: Salida del mismo aeropuerto C y llegada a BZK.

Nuestro objetivo es encontrar todos esos pares de vuelos donde el aeropuerto de llegada del primer vuelo coincida con el aeropuerto de salida del segundo.

Enfoque técnico: SELF JOIN

Para implementar esta lógica, nos referimos a la tabla de vuelos como si fuera dos tablas diferentes. Mentalmente creamos dos alias para ella, por ejemplo, first_leg (primer segmento) y second_leg (segundo segmento).

Plan de solución paso a paso

1. Definir el primer segmento de la ruta. Usamos el alias first_leg para encontrar todos los vuelos que salen de nuestro punto de partida.

   • Condición: first_leg.departure_airport = 'LED'.

2. Definir el segundo segmento de la ruta. Usando el alias second_leg, buscamos todos los vuelos que llegan a nuestro destino.

   • Condición: second_leg.arrival_airport = 'BZK'.

3. Encontrar el punto de conexión (condición clave de JOIN). Ahora lo más importante: necesitamos conectar estos dos conjuntos de vuelos. Los combinamos bajo la condición de que el aeropuerto de llegada del primer segmento debe coincidir exactamente con el aeropuerto de salida del segundo.

   • Condición de conexión: first_leg.arrival_airport = second_leg.departure_airport.
   • Este aeropuerto común es nuestro deseado aeropuerto de escala (connection_airport).

4. Verificar el tiempo de escala: También necesitamos asegurarnos de que el segundo vuelo salga después de que el primer vuelo llegue. Esto se asegura con la condición:

   • second_leg.scheduled_departure > first_leg.scheduled_arrival - Esta condición asegura que solo consideremos conexiones donde hay suficiente tiempo para realizar la transferencia entre vuelos.

5. Formar el resultado. Después de que las tablas se unan, tenemos toda la información necesaria en una fila. Podemos seleccionar:

   • Aeropuerto de salida de first_leg (departure_airport).
   • Aeropuerto de escala (este es first_leg.arrival_airport o, de manera equivalente, second_leg.departure_airport).
   • Aeropuerto de destino de second_leg (arrival_airport).
6. Ordenamiento. Al final, solo necesitamos ordenar la lista resultante por el campo del aeropuerto de escala, como lo requiere la condición de la tarea.

Así, el SELF JOIN nos permite "desplegar" una tabla y emparejar filas de ella entre sí, encontrando rutas complejas y de múltiples etapas.

        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;