SQLtest уроки SQL

Объяснение задачи Найти заполняемость рейсов по тарифам

Основная цель задачи

Задача состоит в том, чтобы рассчитать средний процент заполняемости самолетов для каждого класса обслуживания (Бизнес и Эконом) в разрезе аэропортов вылета. Иными словами, нужно выяснить, насколько в среднем были заполнены бизнес- и эконом-классы на рейсах, вылетающих из каждого аэропорта.

Логика решения (по шагам)

1. Шаг 1: Сбор данных по каждому рейсу.

   • Для начала нам нужно для каждого отдельного рейса определить две ключевые метрики по каждому классу обслуживания:
     1. Общее количество мест, доступных в самолете (total_seats).
     2. Количество фактически занятых мест (occupied_seats).
   • Для этого необходимо объединить несколько таблиц:
     • flights — основная информация о рейсах, включая flight_id и aircraft_code.
     • seats — информация о всех местах в каждом самолете (aircraft_code) и их классе обслуживания (fare_conditions).
     • boarding_passes — информация о выданных посадочных талонах, которая говорит нам, какие места на каком рейсе были заняты.
   • Ключевым моментом здесь является использование LEFT JOIN для таблицы boarding_passes. Это позволяет нам учесть все места в самолете, даже те, на которые не были выданы посадочные талоны (т.е. свободные места).

2. Шаг 2: Расчет заполняемости для каждого рейса.

   • На этом этапе, сгруппировав данные по flight_id, мы можем посчитать количество занятых и общее количество мест для каждого класса.
   • Используя условную агрегацию (например, COUNT(...) FILTER (WHERE ...)), мы подсчитываем места отдельно для 'Business' и 'Economy'.
   • Процент заполняемости для одного рейса и одного класса вычисляется по формуле: (занятые_места / всего_мест) * 100.
   • Важно предусмотреть случай, когда в самолете нет мест определенного класса (например, нет бизнес-класса), чтобы избежать деления на ноль.

3. Шаг 3: Агрегация по аэропортам вылета.

   • Результаты предыдущего шага (заполняемость каждого рейса) теперь нужно сгруппировать по аэропорту вылета (departure_airport).
   • Используя функцию AVG(), мы находим среднее значение процента заполняемости по всем рейсам, вылетевшим из данного аэропорта. Это и будет финальным результатом.

Таким образом, решение строится на двух уровнях агрегации: сначала по каждому рейсу, а затем по каждому аэропорту.

        with occupancy as (
            select 
                flights.flight_id, departure_airport,
                count(boarding_passes.seat_no) filter (where seats.fare_conditions = 'Business') business_occupancy,
                (count(seats.seat_no) filter (where seats.fare_conditions = 'Business'))::numeric business_seats, 
                count(boarding_passes.seat_no) filter (where seats.fare_conditions = 'Economy') economy_occupancy,
                (count(seats.seat_no) filter (where seats.fare_conditions = 'Economy'))::numeric economy_seats
            from flights
            join seats using (aircraft_code)
            left join boarding_passes on 
                boarding_passes.seat_no = seats.seat_no and 
                boarding_passes.flight_id = flights.flight_id
            where flights.actual_departure between '2017-08-01' and '2017-09-01'
            group by flights.flight_id, departure_airport
        ) select 
            departure_airport,
            (avg(case when business_seats > 0 then business_occupancy / business_seats end) * 100)::numeric(5, 2) average_business_occupancy,
            (avg(case when economy_seats  > 0 then economy_occupancy  / economy_seats  end) * 100)::numeric(5, 2) average_economy_occupancy
        from occupancy 
        group by departure_airport
        order by departure_airport;