Exemplo de Self-Join em SQL: o Voo com Conexão Mais Rápido

Q: Como encontrar voos com conexão em SQL?

Junte a tabela de voos consigo mesma: a primeira cópia é o primeiro trecho, a segunda é o segundo trecho. Junte-as pela igualdade entre o aeroporto de chegada do primeiro trecho e o aeroporto de partida do segundo, e exija que a segunda partida seja posterior à primeira chegada.

Q: Como calcular o tempo entre dois timestamps no PostgreSQL?

Subtraia-os: scheduled_arrival - scheduled_departure devolve um interval. No MySQL, use TIMESTAMPDIFF(MINUTE, start_time, end_time).

Q: Como encontrar rotas com duas ou mais conexões?

Acrescente mais um self-join por cada trecho extra, ou use uma CTE recursiva que prolonga uma rota um voo de cada vez (Lição 6.6).

Lição 12.1 · Tempo de leitura: ~6 min · Tarefas para praticar ↓

Esta lição explica a solução da tarefa SQL O voo mais rápido no banco de dados Bookings (PostgreSQL). Você vai ver como um self-join transforma uma única tabela flights nos dois trechos de uma rota com conexão, como garantir que a conexão é possível e como escolher a opção mais rápida.

A tarefa: encontrar a forma mais rápida de voar do Aeroporto de Pulkovo, em São Petersburgo (LED), para Briansk (BZK) em 16 de agosto de 2017, com exatamente uma conexão. Para cada opção são necessários os números dos voos, o aeroporto de conexão, a duração de cada voo, o tempo de espera e o tempo total da viagem. Os tempos são calculados a partir de scheduled_departure e scheduled_arrival.

Tente resolver a tarefa sozinho primeiro; a consulta completa está escondida no fim da lição.

A Ideia Principal: uma Rota São Duas Linhas

Uma rota com uma conexão não é um voo, mas dois:

Trecho 1: de LED para um aeroporto X.
Trecho 2: desse mesmo aeroporto X para BZK.

Os dois trechos são linhas da mesma tabela flights. Para os colocar lado a lado numa linha do resultado, junte a tabela consigo mesma e dê a cada cópia o seu próprio alias, first_leg e second_leg. Isto é um self-join (ver Lição 5.7).

Solução Passo a Passo

1. Escolher os Voos Candidatos

O primeiro trecho parte de LED, o segundo chega a BZK, e ambos partem em 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: 33 voos partem de LED nesse dia; qualquer um pode ser o primeiro trecho.

2. Ligar os Trechos

Os trechos têm de se encontrar no mesmo aeroporto, e o segundo voo tem de partir depois de o primeiro aterrar:

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 opções através de 3 aeroportos de conexão; a mais rápida passa por Moscovo-Sheremetyevo (SVO) e demora 1 hora e 25 minutos.

first_leg.arrival_airport = second_leg.departure_airport é o ponto de conexão.
second_leg.scheduled_departure > first_leg.scheduled_arrival elimina as opções em que o segundo avião parte antes de o primeiro aterrar. Sem esta condição, a consulta devolveria rotas impossíveis.

3. Calcular as Durações

No PostgreSQL, subtrair um timestamp de outro dá um interval, como 01:25:00:

primeiro voo: first_leg.scheduled_arrival - first_leg.scheduled_departure;
tempo de espera: second_leg.scheduled_departure - first_leg.scheduled_arrival;
viagem total: second_leg.scheduled_arrival - first_leg.scheduled_departure, da primeira partida à chegada final.

4. Ficar com a Opção Mais Rápida

Ordene pelo tempo total da viagem e mantenha a primeira linha com LIMIT 1. Se várias opções puderem ter o mesmo tempo, LIMIT 1 devolve apenas uma delas; para mostrar todas, compare com o mínimo, por exemplo com RANK() (Lição 7.2).

Pontos de Atenção

A conexão é de apenas 5 minutos. Na opção mais rápida, o segundo voo sai de SVO cinco minutos depois de o primeiro aterrar. A tarefa só exige que o segundo voo parta mais tarde; um sistema de reservas real exigiria um tempo mínimo de conexão, por exemplo second_leg.scheduled_departure >= first_leg.scheduled_arrival + INTERVAL '1 hour'.
Filtre os dois trechos pela data. Sem a condição de data em second_leg, a consulta também ligaria voos de dias seguintes.
BETWEEN inclui os dois extremos. BETWEEN '2017-08-16' AND '2017-08-17' inclui também um voo exatamente à meia-noite de 17 de agosto. Para dias inteiros, >= '2017-08-16' AND < '2017-08-17' é mais preciso.

Spoiler: a consulta SQL com a solução está escondida abaixo. Clique para revelar.


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;

Principais conclusões:

Uma rota com uma conexão são duas linhas da mesma tabela, ligadas com um self-join e dois aliases.
A condição de conexão é first_leg.arrival_airport = second_leg.departure_airport.
O segundo voo tem de partir depois de o primeiro chegar; caso contrário, a rota é impossível.
No PostgreSQL, subtrair timestamps dá um interval, que pode ser comparado e ordenado.
ORDER BY ... LIMIT 1 devolve a melhor opção; use RANK() para manter os empates.

Perguntas Frequentes

Como encontrar voos com conexão em SQL?