Задача # 14. Разбор задачи по SQL: В какие города летал Bruce Willis

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

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

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

Требуется вывести список городов, в которые совершал перелёты пассажир Bruce Willis. Результирующая таблица должна содержать:

• town_to — город назначения (пункт прибытия)

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

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

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

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

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

SELECT DISTINCT town_to
FROM passenger ps
JOIN Pass_in_trip pt ON ps.id = pt.passenger
JOIN trip tr ON tr.id = pt.trip
WHERE name = 'Bruce Willis';

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

1. FROM passenger ps — начинаем с таблицы пассажиров
2. JOIN Pass_in_trip pt — соединяем с таблицей билетов по ID пассажира
3. JOIN trip tr — соединяем с таблицей рейсов по ID рейса
4. WHERE name = 'Bruce Willis' — фильтруем только перелёты Брюса Уиллиса
5. SELECT DISTINCT town_to — выбираем уникальные города назначения

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

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

• Используется два JOIN для связи трёх таблиц
• Порядок соединения важен для производительности
• Для больших таблиц следует проверять план выполнения запроса

2. Использование DISTINCT

• DISTINCT устраняет дубликаты городов
• Без DISTINCT город будет повторяться для каждого перелёта
• Альтернатива — GROUP BY town_to

3. Условие фильтрации

• Точное совпадение имени 'Bruce Willis'
• В реальной системе следует учитывать:
  Регистр (использовать LOWER/UPPER)
  Возможные опечатки
  Полное имя (если в базе хранится иначе)

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

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

sql

SELECT DISTINCT town_to
FROM trip
WHERE id IN (
SELECT trip
FROM Pass_in_trip
WHERE passenger IN (
SELECT id
FROM passenger
WHERE name = 'Bruce Willis'
)
);

2. С GROUP BY вместо DISTINCT

sql

SELECT town_to
FROM passenger ps
JOIN Pass_in_trip pt ON ps.id = pt.passenger
JOIN trip tr ON tr.id = pt.trip
WHERE name = 'Bruce Willis'
GROUP BY town_to;

3. С дополнительной информацией

sql

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

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

1. Индексы:
   На passenger(name) и passenger(id)
   На Pass_in_trip(passenger) и Pass_in_trip(trip)
   На trip(id) и trip(town_to)

Для PostgreSQL:
SET enable_hashjoin = on;
SET enable_mergejoin = off;

Для MySQL:

SELECT DISTINCT town_to
FROM passenger ps FORCE INDEX (name_index)
JOIN Pass_in_trip pt ON ps.id = pt.passenger
JOIN trip tr ON tr.id = pt.trip
WHERE name = 'Bruce Willis';

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

1. Забыть DISTINCT или GROUP BY (дубликаты городов)
2. Неправильный порядок JOIN (может повлиять на производительность)
3. Использование INNER JOIN вместо LEFT JOIN (если нужно учитывать возможные NULL)
4. Опечатка в имени ('Bruce Willice', 'Brus Willis' и т.д.)

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

1. Регистронезависимый поиск

sql

SELECT DISTINCT town_to
FROM passenger ps
JOIN Pass_in_trip pt ON ps.id = pt.passenger
JOIN trip tr ON tr.id = pt.trip
WHERE LOWER(name) = LOWER('Bruce Willis');

2. Поиск с учётом города отправления

sql

SELECT DISTINCT town_from, town_to
FROM passenger ps
JOIN Pass_in_trip pt ON ps.id = pt.passenger
JOIN trip tr ON tr.id = pt.trip
WHERE name = 'Bruce Willis'
ORDER BY town_from, town_to;

3. Использование EXISTS

sql

SELECT DISTINCT town_to
FROM trip tr
WHERE EXISTS (
SELECT 1
FROM Pass_in_trip pt
JOIN passenger ps ON ps.id = pt.passenger
WHERE pt.trip = tr.id
AND ps.name = 'Bruce Willis'
);

Заключение

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

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

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

SELECT DISTINCT town_to
FROM passenger ps
JOIN Pass_in_trip pt ON ps.id = pt.passenger
JOIN trip tr ON tr.id = pt.trip
WHERE name = 'Bruce Willis';

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

SELECT town_to
FROM trip tr
WHERE EXISTS (
SELECT 1
FROM Pass_in_trip pt
JOIN passenger ps ON ps.id = pt.passenger
WHERE pt.trip = tr.id
AND ps.name = 'Bruce Willis'
)
GROUP BY town_to;