Ejemplo de self-join en SQL: el vuelo con escala más rápido

Lección 12.1 · Tiempo de lectura: ~6 min · Tareas de práctica ↓

Esta lección explica la solución del ejercicio SQL El vuelo más rápido en la base de datos Bookings (PostgreSQL). Verás cómo un self-join convierte una sola tabla flights en los dos tramos de una ruta con escala, cómo asegurarte de que la conexión es posible y cómo elegir la opción más rápida.

El ejercicio: encontrar la forma más rápida de volar desde el aeropuerto de Púlkovo, en San Petersburgo (LED), a Briansk (BZK) el 16 de agosto de 2017, con exactamente una escala. Para cada opción se necesitan los números de vuelo, el aeropuerto de escala, la duración de cada vuelo, el tiempo de espera y el tiempo total del viaje. Los tiempos se calculan con scheduled_departure y scheduled_arrival.

Primero intenta resolver el ejercicio por tu cuenta; la consulta completa está oculta al final de la lección.

La idea clave: una ruta son dos filas

Una ruta con una escala no es un vuelo, sino dos:

• Tramo 1: de LED a algún aeropuerto X.
• Tramo 2: de ese mismo aeropuerto X a BZK.

Ambos tramos son filas de la misma tabla flights. Para ponerlos uno junto al otro en una fila del resultado, une la tabla consigo misma y dale a cada copia su propio alias, first_leg y second_leg. Esto es un self-join (ver lección 5.7).

Solución paso a paso

1. Elegir los vuelos candidatos

El primer tramo sale de LED, el segundo llega a BZK, y ambos salen el 16 de agosto:

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

Resultado: ese día salen 33 vuelos de LED; cualquiera de ellos puede ser el primer tramo.

2. Conectar los tramos

Los tramos deben coincidir en el mismo aeropuerto, y el segundo vuelo debe salir después de que aterrice el primero:

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;

Resultado: 18 opciones a través de 3 aeropuertos de escala; la más rápida pasa por Moscú-Sheremétievo (SVO) y dura 1 hora y 25 minutos.

• first_leg.arrival_airport = second_leg.departure_airport es el punto de conexión.
• second_leg.scheduled_departure > first_leg.scheduled_arrival descarta las opciones en las que el segundo avión sale antes de que aterrice el primero. Sin esta condición, la consulta devolvería rutas imposibles.

3. Calcular las duraciones

En PostgreSQL, restar un timestamp de otro da un interval, como 01:25:00:

• primer vuelo: first_leg.scheduled_arrival - first_leg.scheduled_departure;
• tiempo de espera: second_leg.scheduled_departure - first_leg.scheduled_arrival;
• viaje total: second_leg.scheduled_arrival - first_leg.scheduled_departure, desde la primera salida hasta la llegada final.

4. Quedarse con la opción más rápida

Ordena por el tiempo total del viaje y conserva la primera fila con LIMIT 1. Si varias opciones pueden tener el mismo tiempo, LIMIT 1 devuelve solo una; para mostrarlas todas, compara con el mínimo, por ejemplo con RANK() (lección 7.2).

Detalles a tener en cuenta

• La escala es de solo 5 minutos. En la opción más rápida, el segundo vuelo sale de SVO cinco minutos después de que aterriza el primero. El ejercicio solo exige que el segundo vuelo salga más tarde; un sistema de reservas real exigiría un tiempo mínimo de conexión, por ejemplo second_leg.scheduled_departure >= first_leg.scheduled_arrival + INTERVAL '1 hour'.
• Filtra ambos tramos por fecha. Sin la condición de fecha en second_leg, la consulta también conectaría vuelos de días posteriores.
• BETWEEN incluye ambos extremos. BETWEEN '2017-08-16' AND '2017-08-17' incluye también un vuelo exactamente a la medianoche del 17 de agosto. Para días completos, >= '2017-08-16' AND < '2017-08-17' es más preciso.

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;
    

Conclusiones clave:

• Una ruta con una escala son dos filas de la misma tabla, unidas con un self-join y dos alias.
• La condición de conexión es first_leg.arrival_airport = second_leg.departure_airport.
• El segundo vuelo debe salir después de que llegue el primero; si no, la ruta es imposible.
• En PostgreSQL, restar timestamps da un interval, que se puede comparar y ordenar.
• ORDER BY ... LIMIT 1 devuelve la mejor opción; usa RANK() para conservar los empates.

Preguntas frecuentes

¿Cómo encuentro vuelos con escala en SQL?

Une la tabla de vuelos consigo misma: la primera copia es el primer tramo y la segunda, el segundo tramo. Únelas por la igualdad entre el aeropuerto de llegada del primer tramo y el aeropuerto de salida del segundo, y exige que la segunda salida sea posterior a la primera llegada.

¿Cómo calculo el tiempo entre dos timestamps en PostgreSQL?

Réstalos: scheduled_arrival - scheduled_departure devuelve un interval. En MySQL, usa TIMESTAMPDIFF(MINUTE, start_time, end_time).

¿Cómo encuentro rutas con dos o más escalas?

Agrega un self-join más por cada tramo adicional, o usa una CTE recursiva que extienda la ruta vuelo a vuelo (lección 6.6).