SQLtest Aulas de SQL

Análise da Tarefa SQL: Encontrando Voos com Uma Escala

Esta tarefa é um excelente exemplo do uso de uma das ferramentas mais poderosas em SQL para analisar relações em dados: juntar uma tabela a ela mesma (SELF JOIN). Vamos detalhar a lógica da solução passo a passo.

Descrição da Tarefa

Precisamos encontrar todas as opções de voo possíveis do Aeroporto de Pulkovo (LED) para Bryansk (BZK) que incluem exatamente uma escala.

Ideia Chave

Uma rota com uma escala não é uma, mas dois voos independentes conectados por um aeroporto intermediário.

• Segmento 1: Partida de LED para um certo aeroporto de escala C.
• Segmento 2: Partida do mesmo aeroporto C e chegada em BZK.

Nosso objetivo é encontrar todos os pares de voos onde o aeroporto de chegada do primeiro voo corresponde ao aeroporto de partida do segundo.

Abordagem Técnica: SELF JOIN

Para implementar essa lógica, nos referimos à tabela de voos como se fossem duas tabelas diferentes. Mentalmente, criamos dois aliases para ela, por exemplo, first_leg (primeira etapa) e second_leg (segunda etapa).

Plano de Solução Passo a Passo

1. Defina o primeiro segmento da rota. Usamos o alias first_leg para encontrar todos os voos que partem do nosso ponto de partida.

   • Condição: first_leg.departure_airport = 'LED'.

2. Defina o segundo segmento da rota. Usando o alias second_leg, procuramos todos os voos que chegam ao nosso destino.

   • Condição: second_leg.arrival_airport = 'BZK'.

3. Encontre o ponto de conexão (condição chave do JOIN). Agora, a coisa mais importante: precisamos conectar esses dois conjuntos de voos. Combinamos eles sob a condição de que o aeroporto de chegada do primeiro segmento deve corresponder exatamente ao aeroporto de partida do segundo.

   • Condição de conexão: first_leg.arrival_airport = second_leg.departure_airport.
   • Este aeroporto comum é o nosso aeroporto de escala desejado (connection_airport).

4. Verifique o tempo de conexão: Também precisamos garantir que o segundo voo decole após a chegada do primeiro. Isso é garantido pela condição:

   • second_leg.scheduled_departure > first_leg.scheduled_arrival - Esta condição garante que estamos considerando apenas conexões onde há tempo suficiente para fazer a transferência entre os voos.

5. Forme o resultado. Depois que as tabelas são unidas, temos todas as informações necessárias em uma linha. Podemos selecionar:

   • Aeroporto de partida de first_leg (departure_airport).
   • Aeroporto de escala (este é first_leg.arrival_airport ou, equivalentemente, second_leg.departure_airport).
   • Aeroporto de destino de second_leg (arrival_airport).
6. Ordenação. No final, só precisamos ordenar a lista resultante pelo campo do aeroporto de escala, conforme exigido pela condição da tarefa.

Assim, o SELF JOIN nos permite "desdobrar" uma tabela e combinar linhas dela umas com as outras, encontrando rotas complexas de vários estágios.

        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;