SQLtest уроки SQL

Разбор задачи: Поиск авиарейсов с одной пересадкой

Эта задача — отличный пример использования одного из самых мощных инструментов SQL для анализа связей в данных: соединения таблицы саму с собой (SELF JOIN). Давайте разберем логику решения по шагам.

Постановка задачи

Нам нужно найти все возможные варианты перелёта из аэропорта Пулково (LED) в Брянск (BZK), которые включают ровно одну пересадку.

Ключевая идея

Маршрут с одной пересадкой — это не один, а два независимых рейса, связанных между собой промежуточным аэропортом.

• Сегмент 1: Вылет из LED в некий аэропорт пересадки C.
• Сегмент 2: Вылет из этого же аэропорта C и прилёт в BZK.

Наша цель — найти все такие пары рейсов, где аэропорт прибытия первого рейса совпадает с аэропортом вылета второго.

Технический подход: SELF JOIN

Чтобы реализовать эту логику, мы обращаемся к таблице рейсов так, как будто это две разные таблицы. Мысленно создадим для неё два псевдонима, например, first_leg (первый сегмент) и second_leg (второй сегмент).

Пошаговый план решения

1. Определяем первый сегмент пути. Мы используем псевдоним first_leg для поиска всех рейсов, которые вылетают из нашего начального пункта.

   • Условие: first_leg.departure_airport = 'LED'.

2. Определяем второй сегмент пути. Используя псевдоним second_leg, мы ищем все рейсы, которые прилетают в наш конечный пункт.

   • Условие: second_leg.arrival_airport = 'BZK'.

3. Находим точку стыковки (ключевое условие JOIN). Теперь самое главное: нам нужно соединить эти два набора рейсов. Мы объединяем их по условию, что аэропорт прилёта первого сегмента должен в точности совпадать с аэропортом вылета второго.

   • Условие соединения: first_leg.arrival_airport = second_leg.departure_airport.
   • Этот общий аэропорт и есть наш искомый аэропорт пересадки (connection_airport).

4. Проверяем время пересадки: Нам также нужно убедиться, что второй рейс вылетает после того, как прилетит первый. Это обеспечивается условием:

   • second_leg.scheduled_departure > first_leg.scheduled_arrival - Это условие гарантирует, что мы рассматриваем только те пересадки, где достаточно времени для осуществления пересадки между рейсами.

5. Формируем результат. После того как таблицы соединены, у нас есть вся необходимая информация в одной строке. Мы можем выбрать:

   • Аэропорт вылета из first_leg (departure_airport).
   • Аэропорт пересадки (это first_leg.arrival_airport или, что то же самое, second_leg.departure_airport).
   • Аэропорт назначения из second_leg (arrival_airport).
6. Сортировка. В конце остаётся лишь отсортировать полученный список по полю аэропорта пересадки, как того требует условие задачи.

Таким образом, SELF JOIN позволяет нам "развернуть" одну таблицу и сопоставить строки из неё друг с другом, находя сложные, многоступенчатые маршруты.

        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;