Задача # 15. Разбор задачи по SQL: Выведите идентификатор пассажира Стив Мартин (Steve Martin) и дату и время его прилёта в Лондон (London)

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

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

Разбор задачи по SQL: Поиск данных о перелёте Стива Мартина в Лондон

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

Требуется найти идентификатор пассажира Стива Мартина (Steve Martin) и время его прилёта в Лондон (London). Результирующая таблица должна содержать:

• id — идентификатор пассажира
• time_in — дата и время прилёта

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

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

1. passenger — информация о пассажирах:
   id — идентификатор пассажира (PK)
   name — имя пассажира (фильтр по 'Steve Martin')
2. Pass_in_trip — связь между пассажирами и рейсами:
   passenger — ID пассажира (FK)
   trip — ID рейса (FK)
3. trip — информация о рейсах:
   id — идентификатор рейса (PK)
   town_to — город назначения (фильтр по 'London')
   time_in — время прилёта (нужно для вывода)

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

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

sql

SELECT
pt.passenger AS id,
time_in
FROM
trip tr
JOIN
Pass_in_trip pt ON tr.id = pt.trip
JOIN
passenger ps ON pt.passenger = ps.id
WHERE
ps.name = 'Steve Martin'
AND tr.town_to = 'London';

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

1. FROM trip tr — начинаем с таблицы рейсов
2. JOIN Pass_in_trip pt — соединяем с таблицей билетов по ID рейса
3. JOIN passenger ps — соединяем с таблицей пассажиров по ID пассажира
4. WHERE — условия фильтрации:
   ps.name = 'Steve Martin' — только для Стива Мартина
   tr.town_to = 'London' — только рейсы в Лондон
5. SELECT — выбираем:
   pt.passenger AS id — ID пассажира (с алиасом)
   time_in — время прилёта

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

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

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

2. Условия фильтрации

• Точное совпадение имени 'Steve Martin'
• Точное совпадение города назначения 'London'
• В реальной системе следует учитывать:
  Регистр (можно использовать LOWER/UPPER)
  Возможные опечатки в именах
  Разные форматы названия Лондона

3. Выбор полей результата

• pt.passenger переименован в id согласно требованиям задачи
• time_in выводится без изменений

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

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

SELECT
pt.passenger AS id,
tr.time_in
FROM
Pass_in_trip pt
JOIN
trip tr ON pt.trip = tr.id AND tr.town_to = 'London'
WHERE
pt.passenger = (SELECT id FROM passenger WHERE name = 'Steve Martin');

2. С явным указанием таблиц для всех полей

SELECT
pt.passenger AS id,
tr.time_in
FROM
trip tr
JOIN
Pass_in_trip pt ON tr.id = pt.trip
JOIN
passenger ps ON pt.passenger = ps.id
WHERE
ps.name = 'Steve Martin'
AND tr.town_to = 'London';

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

SELECT
pt.passenger AS id,
tr.time_in,
tr.town_from AS departure_city,
tr.time_out AS departure_time
FROM
trip tr
JOIN
Pass_in_trip pt ON tr.id = pt.trip
JOIN
passenger ps ON pt.passenger = ps.id
WHERE
ps.name = 'Steve Martin'
AND tr.town_to = 'London';

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

1. Индексы:
   На passenger(name) и passenger(id)
   На Pass_in_trip(passenger) и Pass_in_trip(trip)
   На trip(id), trip(town_to) и trip(time_in)
2. Для PostgreSQL:sql

EXPLAIN ANALYZE
SELECT pt.passenger AS id, time_in
FROM trip tr
JOIN Pass_in_trip pt ON tr.id = pt.trip
JOIN passenger ps ON pt.passenger = ps.id
WHERE ps.name = 'Steve Martin'
AND tr.town_to = 'London';

1. Для MySQL:sql

SELECT
pt.passenger AS id,
time_in
FROM
trip tr FORCE INDEX (town_to_index)
JOIN
Pass_in_trip pt FORCE INDEX (trip_index) ON tr.id = pt.trip
JOIN
passenger ps FORCE INDEX (name_index) ON pt.passenger = ps.id
WHERE
ps.name = 'Steve Martin'
AND tr.town_to = 'London';

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

1. Использование * вместо указания конкретных полей
2. Отсутствие алиаса AS id для поля passenger
3. Неправильный порядок JOIN (может повлиять на производительность)
4. Опечатки в именах таблиц или полей
5. Использование = вместо LIKE при возможных вариациях написания имени

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

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

SELECT
pt.passenger AS id,
time_in
FROM
trip tr
JOIN
Pass_in_trip pt ON tr.id = pt.trip
JOIN
passenger ps ON pt.passenger = ps.id
WHERE
LOWER(ps.name) = LOWER('Steve Martin')
AND LOWER(tr.town_to) = LOWER('London');

2. Поиск всех перелётов пассажира в Лондон

SELECT
pt.passenger AS id,
tr.time_in,
tr.time_out,
tr.town_from
FROM
trip tr
JOIN
Pass_in_trip pt ON tr.id = pt.trip
JOIN
passenger ps ON pt.passenger = ps.id
WHERE
ps.name = 'Steve Martin'
AND tr.town_to = 'London'
ORDER BY
tr.time_in;

3. Использование CTE (Common Table Expression)

WITH steve_flights AS (
SELECT pt.passenger, pt.trip
FROM passenger ps
JOIN Pass_in_trip pt ON ps.id = pt.passenger
WHERE ps.name = 'Steve Martin'
)
SELECT
sf.passenger AS id,
tr.time_in
FROM
trip tr
JOIN
steve_flights sf ON tr.id = sf.trip
WHERE
tr.town_to = 'London';

Заключение

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

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

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

SELECT
pt.passenger AS id,
time_in
FROM
trip tr
JOIN
Pass_in_trip pt ON tr.id = pt.trip
JOIN
passenger ps ON pt.passenger = ps.id
WHERE
ps.name = 'Steve Martin'
AND tr.town_to = 'London';

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

SELECT
pt.passenger AS id,
tr.time_in
FROM
Pass_in_trip pt
JOIN
(SELECT id, time_in FROM trip WHERE town_to = 'London') tr
ON tr.id = pt.trip
JOIN
(SELECT id FROM passenger WHERE name = 'Steve Martin') ps
ON ps.id = pt.passenger;