Задача # 16. Разбор задачи по SQL: Вывести отсортированный по количеству перелетов (по убыванию) и имени (по возрастанию) список пассажиров

Вывести отсортированный по количеству перелетов (по убыванию) и имени (по возрастанию) список пассажиров, совершивших хотя бы 1 полет.

SQL — это универсальный язык для взаимодействия с базами данных, который должен знать каждый аналитик, разработчик и дата-инженер. На собеседованиях кандидатам часто предлагают решить практические задачи: написать эффективные запросы, оптимизировать их и предложить решение для конкретных бизнес-кейсов. Давай разберём распространённые типы SQL-задач и эффективные подходы к их решению.

Предыдущее задание:

Разбор задачи по SQL: Рейтинг пассажиров по количеству перелётов

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

Требуется вывести список пассажиров, совершивших хотя бы один перелёт, отсортированный:

1. По количеству перелётов (по убыванию)
2. По имени пассажира (по возрастанию, для одинакового количества перелётов)

Результирующая таблица должна содержать:

• name — имя пассажира
• count — количество совершённых перелётов

Анализ структуры базы данных

Для решения задачи потребуются три таблицы:

1. passenger — содержит информацию о пассажирах:
   id — идентификатор пассажира
   name — имя пассажира (нужно для вывода)
2. Pass_in_trip — связующая таблица между пассажирами и рейсами:
   passenger — ID пассажира (внешний ключ)
   trip — ID рейса (внешний ключ)
3. trip — содержит информацию о рейсах:
   id — идентификатор рейса (используется для соединения)

Детальный разбор решения

Оптимальное решение для MySQL/PostgreSQL

SELECT
ps.name,
COUNT(*) AS count
FROM
passenger ps
JOIN
Pass_in_trip pt ON ps.id = pt.passenger
JOIN
trip tr ON pt.trip = tr.id
GROUP BY
ps.name
HAVING
COUNT(*) >= 1
ORDER BY
count DESC,
ps.name ASC;

Пошаговое объяснение:

1. FROM passenger ps — начинаем с таблицы пассажиров
2. JOIN Pass_in_trip pt — соединяем с таблицей билетов по ID пассажира
3. JOIN trip tr — соединяем с таблицей рейсов по ID рейса
4. GROUP BY ps.name — группируем результаты по имени пассажира
5. HAVING COUNT(*) >= 1 — фильтруем только пассажиров с хотя бы одним перелётом
6. SELECT — выбираем:
   ps.name — имя пассажира
   COUNT(*) AS count — количество перелётов (с алиасом)
7. ORDER BY — сортируем:
   count DESC — по количеству перелётов (убывание)
   ps.name ASC — по имени (возрастание)

Ключевые аспекты решения

1. Многотабличное соединение

• Используется цепочка из двух JOIN для связи трёх таблиц
• Важно правильно указать условия соединения:
  ps.id = pt.passenger — пассажир и его билеты
  pt.trip = tr.id — билеты и соответствующие рейсы

2. Группировка и агрегация

• GROUP BY ps.name — группируем по имени пассажира
• COUNT(*) — подсчитываем количество перелётов для каждого пассажира
• HAVING COUNT(*) >= 1 — условие избыточно, так как JOIN уже исключает пассажиров без перелётов, но явно показывает требование задачи

3. Сортировка результатов

• Многоуровневая сортировка с приоритетами:
  Основной критерий — количество перелётов (DESC)
  Вторичный критерий — имя пассажира (ASC)

Альтернативные варианты решения

1. Без явного HAVING (эквивалентно исходному решению)

SELECT
ps.name,
COUNT(*) AS count
FROM
passenger ps
JOIN
Pass_in_trip pt ON ps.id = pt.passenger
GROUP BY
ps.name
ORDER BY
count DESC,
ps.name ASC;

2. С использованием подзапроса

SELECT
ps.name,
(SELECT COUNT(*) FROM Pass_in_trip WHERE passenger = ps.id) AS count
FROM
passenger ps
WHERE
EXISTS (SELECT 1 FROM Pass_in_trip WHERE passenger = ps.id)
ORDER BY
count DESC,
ps.name ASC;

3. С оконными функциями (PostgreSQL)

SELECT DISTINCT
ps.name,
COUNT(*) OVER (PARTITION BY ps.name) AS count
FROM
passenger ps
JOIN
Pass_in_trip pt ON ps.id = pt.passenger
ORDER BY
count DESC,
ps.name ASC;

Оптимизация запроса

1. Индексы:
   На Pass_in_trip(passenger) — для быстрого поиска билетов пассажира
   На passenger(name) — для сортировки по имени
   На trip(id) — для соединения с таблицей рейсов
2. Для больших таблиц:
   Ограничить выборку с помощью LIMIT
   Использовать материализованные представления
   Рассмотреть денормализацию данных для отчётов

Частые ошибки

1. Использование WHERE вместо HAVING для фильтрации по COUNT
2. Забыть указать DESC/ASC в ORDER BY
3. Неправильный порядок сортировки (сначала имя, потом количество)
4. Включение неагрегированных столбцов без GROUP BY

Дополнительные возможности

1. Добавление информации о пассажире

SELECT
ps.name,
ps.id,
COUNT(*) AS flight_count,
MIN(tr.time_out) AS first_flight,
MAX(tr.time_out) AS last_flight
FROM
passenger ps
JOIN
Pass_in_trip pt ON ps.id = pt.passenger
JOIN
trip tr ON pt.trip = tr.id
GROUP BY
ps.name, ps.id
ORDER BY
flight_count DESC,
ps.name ASC;

2. Ограничение выборки (топ-10)

SELECT
ps.name,
COUNT(*) AS count
FROM
passenger ps
JOIN
Pass_in_trip pt ON ps.id = pt.passenger
GROUP BY
ps.name
ORDER BY
count DESC,
ps.name ASC
LIMIT 10;

3. Фильтрация по периоду

SELECT
ps.name,
COUNT(*) AS count
FROM
passenger ps
JOIN
Pass_in_trip pt ON ps.id = pt.passenger
JOIN
trip tr ON pt.trip = tr.id
WHERE
tr.time_out BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY
ps.name
ORDER BY
count DESC,
ps.name ASC;

Заключение

Данная задача демонстрирует:

1. Технику многотабличных соединений
2. Группировку данных и агрегатные функции
3. Многоуровневую сортировку результатов
4. Лучшие практики построения SQL-запросов для аналитических отчётов

🔑 Итоговое решение:

SELECT
ps.name,
COUNT(*) AS count
FROM
passenger ps
JOIN
Pass_in_trip pt ON ps.id = pt.passenger
JOIN
trip tr ON pt.trip = tr.id
GROUP BY
ps.name
ORDER BY
count DESC,
ps.name ASC;

Оптимизированное решение для больших баз:

SELECT
ps.name,
COUNT(pt.trip) AS count
FROM
passenger ps
JOIN
(SELECT passenger, trip FROM Pass_in_trip) pt ON ps.id = pt.passenger
GROUP BY
ps.name
HAVING
COUNT(pt.trip) > 0
ORDER BY
count DESC,
ps.name ASC;