Задача # 9. Разбор задачи по SQL: Определение авиакомпаний, выполняющих рейсы из Владивостока

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

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

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

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

• name — название авиакомпании

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

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

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

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

SQL-запрос

SELECT DISTINCT c.name
FROM trip t
JOIN company c ON c.id = t.company
WHERE t.town_from = 'Vladivostok';

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

1. FROM trip t — начинаем с таблицы рейсов (алиас 't')
2. JOIN company c ON c.id = t.company — соединяем с таблицей компаний по ключу
3. WHERE t.town_from = 'Vladivostok' — фильтруем только рейсы из Владивостока
4. SELECT DISTINCT c.name — выбираем уникальные названия компаний

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

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

• Соединение таблиц необходимо, так как:
  Таблица trip содержит только ID компаний
  Названия компаний хранятся в таблице company
• Соединение выполняется по полю company в trip и id в company

2. Применение DISTINCT

Оператор DISTINCT критически важен, так как:

• Одна компания может выполнять множество рейсов из Владивостока
• Без DISTINCT мы получим дублирующиеся названия
• Обеспечивает вывод только уникальных значений

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

Особенности условия town_from = 'Vladivostok':

• Должно точно соответствовать формату хранения данных
• В реальных базах могут быть варианты написания
• Важно учитывать регистр (при необходимости использовать LOWER/UPPER)

Возможные модификации запроса

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

Если есть сомнения в регистре:

SELECT DISTINCT c.name
FROM trip t
JOIN company c ON c.id = t.company
WHERE LOWER(t.town_from) = 'vladivostok';

2. Добавление информации о рейсах

Если нужно больше деталей:

SELECT DISTINCT c.name, t.plane, COUNT(*) as flight_count
FROM trip t
JOIN company c ON c.id = t.company
WHERE t.town_from = 'Vladivostok'
GROUP BY c.name, t.plane;

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

Для упорядоченного вывода:

SELECT DISTINCT c.name
FROM trip t
JOIN company c ON c.id = t.company
WHERE t.town_from = 'Vladivostok'
ORDER BY c.name;

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

Для улучшения производительности:

1. Создать индекс на поле town_from в таблице trip
2. Создать индекс на поле company в таблице trip
3. Создать индекс на поле id в таблице company

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

1. Забывают указать DISTINCT, получая дублирующиеся названия
2. Неправильно указывают условие соединения таблиц
3. Используют town_to вместо town_from по ошибке
4. Не учитывают регистр при сравнении названия города

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

1. В реальной базе могут быть рейсы с пересадками
2. Некоторые рейсы могут быть сезонными
3. Результат может меняться со временем (изменение маршрутов)

Заключение

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

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

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

SELECT DISTINCT c.name
FROM trip t
JOIN company c ON c.id = t.company
WHERE t.town_from = 'Vladivostok';