Заполняемость рейсов по классам обслуживания в SQL

Урок 12.2 · Время чтения: ~7 мин · Задачи для практики ↓

В этом уроке разобрано решение SQL-задачи Заполняемость рейсов по классам обслуживания на базе данных Bookings (PostgreSQL). Вы узнаете, как посчитать все места и занятые места в одном запросе, как разделить подсчёт по классам с помощью FILTER и почему для ответа нужны два уровня агрегации: сначала по рейсам, затем по аэропортам.

Задача: для каждого аэропорта вылета посчитать среднюю заполняемость бизнес- и эконом-класса на рейсах, фактически вылетевших в августе 2017 года. Заполняемость рейса - это процент занятых мест среди всех мест данного класса. В результате три столбца: departure_airport, average_business_occupancy и average_economy_occupancy, в процентах с округлением до двух знаков.

Сначала попробуйте решить задачу сами; полный запрос спрятан в конце урока.

Какие таблицы нужны

flights: рейс, его departure_airport, actual_departure и самолёт (aircraft_code).
seats: все места каждой модели самолёта с классом (fare_conditions).
boarding_passes: места, реально занятые на рейсе (flight_id, seat_no).

План такой: для каждого рейса перечислить все места его самолёта и отметить, на какие из них выдан посадочный талон.

Решение по шагам

1. Все места и занятые места каждого рейса

Соедините каждый рейс с местами его самолёта, затем присоедините посадочные талоны через LEFT JOIN, чтобы пустые места остались в результате:

SELECT
    flights.flight_id,
    flights.departure_airport,
    COUNT(boarding_passes.seat_no) FILTER (WHERE seats.fare_conditions = 'Business') AS business_occupied,
    COUNT(seats.seat_no) FILTER (WHERE seats.fare_conditions = 'Business') AS business_seats,
    COUNT(boarding_passes.seat_no) FILTER (WHERE seats.fare_conditions = 'Economy') AS economy_occupied,
    COUNT(seats.seat_no) FILTER (WHERE seats.fare_conditions = 'Economy') AS 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, flights.departure_airport
ORDER BY flights.flight_id
LIMIT 3;

Результат: например, на рейсе 2 из DME занято 14 из 28 мест бизнес-класса и 87 из 142 мест эконом-класса.

COUNT(seats.seat_no) считает все места; COUNT(boarding_passes.seat_no) - только места с посадочным талоном, потому что для пустых мест LEFT JOIN даёт NULL.
При внутреннем JOIN с boarding_passes пустые места исчезли бы, и каждый рейс выглядел бы заполненным на 100%.
Посадочный талон должен совпадать и по месту, и по рейсу: одинаковый номер места есть на каждом рейсе этого самолёта.
COUNT(...) FILTER (WHERE ...) считает только строки одного класса. В других СУБД то же самое записывают как COUNT(CASE WHEN ... THEN 1 END) (урок 4.4).

2. Заполняемость каждого рейса

Разделите занятые места на все места. Осторожно с типами: COUNT возвращает целое число, а в PostgreSQL целое, делённое на целое, - тоже целое:

SELECT 3 / 12 AS integer_division, 3::numeric / 12 AS numeric_division;

Результат: integer_division равно 0, numeric_division - 0.25.

Поэтому решение приводит количество мест к numeric. У некоторых самолётов бизнес-класса нет вовсе, например у Bombardier CRJ-200 и Cessna 208 Caravan. У них число мест бизнес-класса равно 0, и деление нужно пропустить: CASE WHEN business_seats > 0 THEN ... END возвращает NULL, который AVG игнорирует.

3. Среднее по аэропорту

Поместите расчёт по рейсам в CTE и снова агрегируйте его, теперь по departure_airport:

AVG(business_occupied / business_seats) * 100 - средний процент заполняемости;
::numeric(5, 2) округляет его до двух знаков.

Результат: 104 аэропорта, начиная с AAQ (82.83% в бизнес-классе, 81.54% в эконом-классе).

Среднее процентов и общий процент

Задача просит среднее из процентов заполняемости рейсов: у каждого рейса одинаковый вес, и почти пустой маленький самолёт весит столько же, сколько полный большой. Другая возможная метрика - общая заполняемость: все занятые места в аэропорту, делённые на все места, SUM(occupied) / SUM(seats), где большие самолёты весят больше. Обе метрики корректны, но дают разные числа, поэтому внимательно читайте условие. Здесь двухуровневая агрегация нужна именно потому, что среднее берётся по рейсам.

Спойлер: SQL-запрос с решением спрятан ниже. Нажмите, чтобы открыть.


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;

Ключевые выводы:

Считайте все места по seats, а занятые - по boarding_passes, присоединённой через LEFT JOIN; внутреннее соединение скрыло бы пустые места.
COUNT(column) пропускает NULL и поэтому считает только найденные строки; FILTER (WHERE ...) разделяет подсчёт по категориям.
Целочисленное деление в PostgreSQL отбрасывает дробную часть: приводите к numeric до деления.
Пропускайте классы, которых нет у самолёта, с помощью CASE WHEN seats > 0; AVG игнорирует NULL.
Среднее из процентов по рейсам требует двух уровней агрегации: CTE по рейсам, затем GROUP BY по аэропортам.

Часто задаваемые вопросы

Почему расчёт процента в SQL возвращает 0?

Потому что оба операнда целые, а PostgreSQL, SQL Server и SQLite выполняют целочисленное деление: 3 / 12 = 0. Приведите один из операндов к numeric или decimal либо сначала умножьте на 100.0. MySQL возвращает для / десятичный результат, и этой проблемы в нём нет.

Что делает COUNT(...) FILTER (WHERE ...)?

Считает только строки, удовлетворяющие условию, поэтому несколько подсчётов с разными условиями помещаются в один GROUP BY. FILTER поддерживают PostgreSQL и SQLite; в MySQL и SQL Server используйте COUNT(CASE WHEN condition THEN 1 END) или SUM(CASE WHEN condition THEN 1 ELSE 0 END).

Когда нужны два уровня агрегации?

Когда метрика считается для каждого объекта, а затем усредняется по группам: заполняемость по рейсу, затем среднее по аэропорту. Один GROUP BY не может сделать и то и другое, поэтому первый уровень выносят в CTE или подзапрос.