Задача # 12. Разбор задачи по SQL: Подсчёт пассажиров на рейсах (включая рейсы без пассажиров)

Выведите идентификаторы всех рейсов и количество пассажиров на них. Обратите внимание, что на каких-то рейсах пассажиров может не быть. В этом случае выведите число "0".

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

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

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

Требуется вывести идентификаторы всех рейсов и количество пассажиров на каждом из них. Особые условия:

• Необходимо учесть все рейсы, включая те, на которых нет пассажиров
• Для рейсов без пассажиров выводить 0
• Результирующая таблица должна содержать:
  id — идентификатор рейса
  count — количество пассажиров

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

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

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

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

Решение для MySQL/PostgreSQL

SELECT
t.id,
COUNT(pt.passenger) AS count
FROM
Trip t
LEFT JOIN
Pass_in_trip pt ON t.id = pt.trip
GROUP BY
t.id;

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

1. FROM Trip t — начинаем с таблицы рейсов (все рейсы должны быть в результате)
2. LEFT JOIN Pass_in_trip pt — присоединяем информацию о пассажирах:
   LEFT JOIN гарантирует, что все рейсы останутся в результате
   Для рейсов без пассажиров поля из Pass_in_trip будут NULL
3. ON t.id = pt.trip — условие соединения по ID рейса
4. COUNT(pt.passenger) — подсчёт пассажиров:
   COUNT игнорирует NULL значения, поэтому для рейсов без пассажиров вернёт 0
5. GROUP BY t.id — группировка по ID рейса

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

1. Использование LEFT JOIN

Критически важно использовать LEFT JOIN вместо INNER JOIN, так как:

• INNER JOIN исключит рейсы без пассажиров
• LEFT JOIN сохранит все рейсы из таблицы Trip
• Для рейсов без соответствия в Pass_in_trip поля будут NULL

2. Особенности COUNT

• COUNT(pt.passenger) подсчитывает только не-NULL значения
• COUNT(*) подсчитывает все строки, включая NULL (не подходит для этой задачи)
• Для рейсов без пассажиров результат будет 0

3. Группировка данных

• GROUP BY t.id объединяет все записи по ID рейса
• Без GROUP BY мы получим неагрегированные данные
• В SELECT можно включать только столбцы из GROUP BY или агрегатные функции

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

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

SELECT
t.id,
(SELECT COUNT(*) FROM Pass_in_trip pt WHERE pt.trip = t.id) AS count
FROM
Trip t;

2. С явной заменой NULL на 0 (избыточно, так как COUNT уже делает это)

SELECT
t.id,
COALESCE(COUNT(pt.passenger), 0) AS count
FROM
Trip t
LEFT JOIN
Pass_in_trip pt ON t.id = pt.trip
GROUP BY
t.id;

3. Для PostgreSQL с FILTER

SELECT
t.id,
COUNT(pt.passenger) FILTER (WHERE pt.passenger IS NOT NULL) AS count
FROM
Trip t
LEFT JOIN
Pass_in_trip pt ON t.id = pt.trip
GROUP BY
t.id;

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

Для больших таблиц:

1. Создать индекс на Pass_in_trip.trip
2. Для MySQL рассмотреть использование STRAIGHT_JOIN
3. Для PostgreSQL использовать ANALYZE для статистики

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

1. Использование INNER JOIN вместо LEFT JOIN
2. COUNT(*) вместо COUNT(pt.passenger)
3. Забыть GROUP BY
4. Включение лишних столбцов в SELECT без агрегации

Дополнительные соображения

1. В реальной системе могут быть дубликаты пассажиров на рейсе
2. Можно добавить сортировку по количеству пассажиров
3. Для анализа можно добавить другие поля рейса

Заключение

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

1. Правильное использование LEFT JOIN
2. Особенности агрегатных функций с NULL значениями
3. Основы группировки данных в SQL

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

SELECT
t.id,
COUNT(pt.passenger) AS count
FROM
Trip t
LEFT JOIN
Pass_in_trip pt ON t.id = pt.trip
GROUP BY
t.id;