Как найти SQL-запросом самые быстрые поездки с пересадкой с автобуса на поезд?

Условие и допущения

Даны таблицы bus(id_bus, start_dttm, duration_trip_min) и train(id_train, start_dttm, duration_trip_min). Идентификаторы уникальны, start_dttm — отправление, продолжительность указана в минутах. Нужно вывести все пары автобус + поезд, позволяющие доехать до Москвы за минимальное время. Между прибытием автобуса и посадкой на поезд нужно пройти по площади; переход занимает не более 15 минут.

Для решения считаем, что записи относятся к совместимым маршрутам, время задано согласованно, обязательные поля заполнены, длительности неотрицательны. Минимизируем время от отправления автобуса до прибытия поезда, включая ожидание пересадки. Время ожидания первого автобуса не учитываем: исходный момент начала ожидания в задаче не указан.

Поскольку точная длительность перехода неизвестна, закладываем все 15 минут. Это консервативный вариант, гарантирующий возможность пересадки; при известном меньшем времени параметр можно заменить.

Решение на PostgreSQL

WITH routes AS (
    SELECT
        b.id_bus,
        t.id_train,
        (t.start_dttm
            + t.duration_trip_min * INTERVAL '1 minute')
            - b.start_dttm AS total_time
    FROM bus AS b
    JOIN train AS t
      ON t.start_dttm >= b.start_dttm
           + (b.duration_trip_min + 15) * INTERVAL '1 minute'
), ranked AS (
    SELECT
        id_bus, id_train, total_time,
        DENSE_RANK() OVER (ORDER BY total_time) AS position
    FROM routes
)
SELECT id_bus, id_train
FROM ranked
WHERE position = 1
ORDER BY id_bus, id_train;

Если допустимых пересадок нет, результат пуст. При одинаковой минимальной длительности возвращаются все пары: LIMIT 1 потерял бы остальные лучшие варианты.

Почему условия именно такие

Пусть автобус отправляется в 09:00 и едет 60 минут. Поезд в 10:10 не подходит при заложенном переходе в 15 минут; поезд в 10:15 подходит. Если он едет 120 минут, поездка занимает 3 часа 15 минут: от 09:00 до 12:15.

Пятнадцать минут — запас на переход, а не максимальное допустимое ожидание поезда. Верхнюю границу отправления в JOIN добавлять не нужно. Нельзя и просто выбрать ближайший поезд: более поздний поезд может ехать быстрее и прибыть раньше.

Минимальная длительность поездки отличается от самого раннего прибытия по часам. Поездка с поздним отправлением может быть короче, но закончиться позже. Если требуется именно раннее прибытие после заданного момента, нужно изменить критерий сортировки и добавить ограничение на отправление первого транспорта. Для нескольких станций понадобятся их идентификаторы и условие совместимости маршрутов; текущая схема таких полей не содержит.