Как правильно сформулировать поисковый вопрос к БД MySQL?

Question

[vincent_gun]

Доброго времени, уважаемые.

Заблудился на запросах к таблицам. Есть таблица сводных маршрутов поездов:

route_points
id route_id station_id date_arrival date_departure

route_id соответствует ссылается на другую таблицу, которая содержит названия маршрутов, station_id ссылается на станции (id, name), date_arrival/departure - дата прибытия и отправления соответственно.

Пример заполнения таблицы:



Мне необходимо реализовать поиск маршрута. Например, мне нужен рейс из станции [1] в станцию [3] на определённую дату, например, 9 сентября 2018 года. О номерах рейсов мне ничего не известно.

Подозреваю, что данную таблицу надо переделать, иначе каждый маршрут придётся ежедневно перебивать.

Вопросы:
1. Как реализовать график движения поездов по одному маршруту. Например, чтобы поезда по маршруту ходили ежедневно.
2. Как выполнить поиск по таблице определённого маршрута. Например, поезд едет по маршруту 1-2-3-4, мне необходимо проехать 1-2-3.

Благодарю за внимание.

Answer 1

[Rsa97]

Я бы сделал примерно так:
routes (route_id, station_id, arrival_timeshift, departure_timeshift)
routes_regular (route_id, weekday, deparure_time)
routes_irregular (route_id, deparure_date, departure_time)

В первой таблице сами маршруты и время прибытия/отправления от выхода на маршрут, в минутах.
Во второй таблице дни недели и время выходов на маршрут для регулярных маршрутов.
В третьей - дата и время выходов на маршрут для нерегулярных маршрутов.

Рейс ищем примерно так:

SELECT `s`.`route_id`, 
       ADDTIME(:date, TIME(ADDDATE(IFNULL(`rr`.`departure_time`, `ri`.`departure_time`), INTERVAL `s`.`departire_timeshift` MINUTE)) AS `departure_datetime`
  FROM `routes` AS `s`
  JOIN `routes` AS `e`
    ON `s`.`station_id` = :startStation
      AND `e`.`station_id` = :endStation
      AND `e`.`route_id` = `s`.`route_id`
      AND `e`.`arrival_time` > `s`.`departure_time`
  LEFT JOIN `routes_regular` AS `rr`
    ON `rr`.`route_id` = `s`.`route_id`
  LEFT JOIN `routes_irregular` AS `ri`
    ON `ri`.`route_id` = `s`.`route_id` 
      AND `ri`.`departure_date` <= :date
      AND `ri`.`departure_date` > SUBDATE(:date, INTERVAL 1 MONTH)
  WHERE DATE(ADDDATE(ADDTIME(`ri`.`departure_date`, `ri`.`departure_time`), 
                INTERVAL `s`.`departure_timeshift` MINUTE)) = :date
    OR (`rr`.`weekday` + FLOOR(HOUR(ADDDATE(`rr`.`departure_time`, 
            INTERVAL `s`.`departure_timeshift` MINUTE))/24)) % 7 = WEEKDAY(:date)

Comments

[vincent_gun]

Спасибо, интересная мысль! Не могли бы вы дать комментарии к SQL-запросу?

[Rsa97]

vincent_gun, Запрос, кстати, был неверный. Он искал не по дате прохождения маршрута через запрошенную станцию, а по дате выхода маршрута.
Переписал, но на коленке, без проверки.
Общая идея простая, первый JOIN ищет маршрут, проходящий через две заданные станции в заданном порядке, затем через LEFT JOIN прикрепляются данные о выходе найденных маршрутов и во WHERE идёт поиск маршрутов, проходящих через первую станцию в заданную дату.

Answer 2

[Dimonchik]

1. Как реализовать график движения поездов по одному маршруту. Например, чтобы поезда по маршруту ходили ежедневно.

это делается не в базе, или процедурами, что не принято

2. Как выполнить поиск по таблице определённого маршрута. Например, поезд едет по маршруту 1-2-3-4, мне необходимо проехать 1-2-3.

select * from * where station_id in (1, 2, 3)