Кратчайшее время и поездка между двумя остановками при множестве вариантов поездки (GTFS)

Я использую реляционную базу данных GTFS, чтобы найти самое быстрое время поездки между двумя заданными точками. У меня есть поля trip_id, arrival_time и departure_time (в минутах от полуночи) и stop_id

Часть данных может быть:

trip_id | arrival_time | departure_time | stop_id
1       | 5            | 5              | 90001
1       | 8            | 8              | 90002
1       | 10           | 10             | 90003
1       | 15           | 15             | 90004
2       | 25           | 25             | 90001
2       | 28           | 28             | 90002
2       | 32           | 33             | 90003
2       | 38           | 38             | 90004
3       | 35           | 35             | 90001
3       | 38           | 38             | 90002
3       | 48           | 48             | 90004
4       | 8            | 8              | 90003
4       | 10           | 10             | 90004
4       | 15           | 15             | 90005

Я ищу кратчайшее время (и связанную с ним поездку) между отправлением с остановки 90001 и прибытием на остановку 90003. Ответ: 5 минут для поездки «1», что превосходит 8 минут для поездки 2 и тот факт, что поездка 3 не не останавливайтесь на 90003. Кроме того, разница во времени должна быть в одной и той же поездке, вывод не может составлять 3 минуты от комбинации поездки 1 и 4.

Как мне написать оператор SQL для этого? До сих пор я потерпел неудачу с треском:

SELECT trip_id, arrival_time, departure_time, stop_id, MIN(departure_time-arrival_time)
FROM stop_times
WHERE stop_id IN (90001, 90003)
GROUP BY trip_id

(Я уже создал подмножество данных, поэтому все поездки выполняются в нужном мне направлении, поэтому мне не придется переключать время прибытия и отправления на лету. Это всегда будет время между departure_time первого stop_id и arrival_time второго. .)


sql sqldf
person gren    schedule 26.11.2014    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Я думаю, что это должно работать, используя max и case с limit:

select trip_id, 
  max(case when stop_id = 90003 then arrival_time end)
  - max(case when stop_id = 90001 then departure_time end)
from stop_times
where stop_id in (90001,90003)
group by trip_id
order by 1
limit 1

Замечу, что для trip_id 2 это возвращает 7 вместо 8 (32-25). Но, возможно, следует использовать departure_time для обоих операторов case.

person sgeddes    schedule 26.11.2014
comment
Это работало на моем тестовом примере, но не на моих больших данных. При запуске он вернул trip_id для поездки, которая не находится на той же линии, что и моя вторая остановка, и NA для минут. Тем не менее, если я уберу ограничение 1, я получу массив NA и значений (как ни странно, в виде строк). Если я преобразую их обратно в числа и найду минимум, это значение окажется правильным минимальным временем поездки (а все остальные значения - правдоподобные времена поездки). Итак, ваш код великолепен, за исключением одной проблемы. Я собираюсь поиграть с ним и посмотреть, смогу ли я понять, почему он не работает. Если у вас есть какие-либо идеи, пожалуйста, дайте мне знать. Спасибо! - person gren; 26.11.2014