Задача # 8. Разбор задачи по SQL: Определение городов назначения и времени полёта из Парижа

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

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

Задача 8. Определение городов назначения и времени полёта из Парижа

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

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

• town_to — город назначения
• flight_time — продолжительность полёта в формате HH:MM:SS (использовать конструкцию as flight_time)

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

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

• Trip — содержит информацию о рейсах, включая:
  town_from — город отправления (фильтр по 'Paris')
  town_to — город назначения (нужен для вывода)
  time_out — время вылета
  time_in — время прилёта

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

SQL-запрос (для MySQL)

SELECT

town_to,

TIME_FORMAT(SEC_TO_TIME(TIMESTAMPDIFF(SECOND, time_out, time_in)), '%H:%i:%s') AS flight_time

FROM

Trip

WHERE

town_from = 'Paris';

Альтернативный вариант

SELECT

town_to,

TIMEDIFF(time_in, time_out) as flight_time

FROM trip

WHERE town_from = 'Paris'

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

1. Фильтрация рейсов:
   WHERE town_from = 'Paris' — выбираем только рейсы, отправляющиеся из Парижа
2. Расчёт продолжительности полёта:
   Вычисляем разницу между временем прилёта и вылета в секундах
   Преобразуем полученный интервал в формат HH:MM:SS
3. Форматирование времени:
   Используем функции конвертации для приведения к требуемому формату
   Применяем псевдоним AS flight_time как требуется в условии

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

1. Вычисление длительности полёта

Основные методы расчёта:

• TIMEDIFF(time_out, time_in) вычисляет разницу в секундах
• Для MySQL: TIMESTAMPDIFF(SECOND, time_out, time_in)
• Важно учитывать возможные пересечения полуночи

2. Форматирование результата

Требуемый формат HH:MM:SS достигается:

• В SQL Server: CONVERT(VARCHAR, ..., 108)
• В MySQL: TIME_FORMAT(SEC_TO_TIME(...), '%H:%i:%s')
• В PostgreSQL: TO_CHAR(interval, 'HH24:MI:SS')

3. Особенности разных СУБД

Варианты реализации для различных систем:

• SQL Server: использование DATEADD и CONVERT
• MySQL: функции TIMESTAMPDIFF и TIME_FORMAT
• PostgreSQL: EXTRACT и TO_CHAR
• Oracle: NUMTODSINTERVAL и TO_CHAR

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

1. Сортировка по времени полёта

SELECT

town_to,

TIMEDIFF(time_in, time_out) as flight_time

FROM trip

WHERE town_from = 'Paris'

ORDER BY

TIMEDIFF( time_out, time_in);

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

SELECT

town_to,

TIMEDIFF(time_in, time_out) as flight_time,

plane

FROM trip

WHERE town_from = 'Paris'

ORDER BY

TIMEDIFF( time_out, time_in);

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

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

1. Создать индекс на поле town_from
2. Для больших таблиц рассмотреть материализованное представление
3. Добавить фильтр по дате, если нужны только актуальные рейсы

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

1. Неправильный расчёт времени (учёт только часов и минут без секунд)
2. Использование неправильных функций форматирования для конкретной СУБД
3. Забывают указать псевдоним AS flight_time
4. Не учитывают перелёты через полночь

Заключение

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

1. Фильтрацию данных по условию
2. Работу с временными интервалами
3. Форматирование вывода результатов
4. Особенности реализации в разных СУБД

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

SELECT
town_to,
TIME_FORMAT(SEC_TO_TIME(TIMESTAMPDIFF(SECOND, time_out, time_in)), '%H:%i:%s') AS flight_time
FROM
Trip
WHERE
town_from = 'Paris';