Как я могу включить в расписание сегодняшние вылеты после полуночи с использованием GTFS?

Question

Я начал с GTFS и случайно натолкнулся на большую проблему с моим SQL-запросом:

SELECT *, ( some columns AS shortcuts )
FROM stop_times 
LEFT JOIN trips ON stop_times.trip_id = trips.trip_id
WHERE trips.max_sequence != stop_times.stop_sequence
AND stop_id IN( $incodes )
AND trips.service_id IN ( $service_ids )
AND ( departure_time >= $time )
AND ( trips.end_time >= $time )
AND ( trips.start_time <= $time_plus_3hrs )
GROUP BY t,l,sm
ORDER BY t ASC, l DESC
LIMIT 14

Это должно показать вылеты с некоторой остановки в течение следующих 3 часов. Это работает, но с приближением полуночи (например, 23:50) он ловит только "сегодняшний отъезд". После полуночи он улавливает только "отправления на новый день", а вылеты из предыдущего дня отсутствуют, потому что у них время отправления, например "24:05" (= не больше, чем $time 00:05). Можно ли использовать что-то более легкое, чем UNION, тот же запрос на следующий день? Если используется UNION, как я могу ЗАКАЗАТЬ вылеты для обрезки по LIMIT?

Trips.start_time и end_time - мои вспомогательные переменные для ускорения выполнения SQL-запросов, это означает sequence1-прибытие_time и MAXsequence-ъезд_время любой поездки.

Answer 1

С помощью UNION Лучше всего связать запрос на каждый день, если, возможно, вы не захотите выполнить два совершенно отдельных запроса, а затем объединить результаты в своем приложении. Конторсионизм требуется, чтобы сделать все это с одним SELECT утверждение (при условии, что это даже возможно) не стоило бы усилий.

Отчасти сложность здесь заключается в том, что набор активных идентификаторов услуг может варьироваться между последовательными днями, поэтому для каждого из них должен использоваться отдельный набор. (Чтобы узнать, как построить этот набор в SQL с помощью подзапроса и объединения таблиц, см. Мой ответ на вопрос "Как использовать исключения из календаря для создания точных расписаний с использованием GTFS?".)

Сложность возникает из-за того, что результаты для каждого дня должны обрабатываться по-разному: для правильного упорядочения набора результатов нам необходимо вычесть двадцать четыре часа из всех (и только) вчерашних времен.

Попробуйте выполнить такой запрос, следуя "псевдо-SQL" в своем вопросе и предполагая, что вы используете MySQL/MariaDB:

SELECT *, SUBTIME(departure_time, '24:00:00') AS t, ...
  FROM stop_times
  LEFT JOIN trips ON stop_times.trip_id = trips.trip_id
  WHERE trips.max_sequence != stop_times.stop_sequence
    AND stop_id IN ( $incodes )
    AND trips.service_id IN ( $yesterdays_service_ids )
    AND ( departure_time >= ADDTIME($time, '24:00:00') )
    AND ( trips.end_time >= ADDTIME($time, '24:00:00') )
    AND ( trips.start_time <= ADDTIME($time_plus_3hrs, '24:00:00') )
  UNION
    SELECT *, departure_time AS t, ...
      FROM stop_times 
      LEFT JOIN trips ON stop_times.trip_id = trips.trip_id
      WHERE trips.max_sequence != stop_times.stop_sequence
        AND stop_id IN ( $incodes )
        AND trips.service_id IN ( $todays_service_ids )
        AND ( departure_time >= $time )
        AND ( trips.end_time >= $time )
        AND ( trips.start_time <= $time_plus_3hrs )
  GROUP BY t, l, sm
  ORDER BY t ASC, l DESC
  LIMIT 14