Как составить список всех поездок с одной остановки на другую с помощью GTFS?

Я использую следующую таблицу:

1   0051ML66220600132482    06:00:00        06:00:00        1538    100 0   1
2   0051ML66220600132482    06:00:00        06:00:00        1540    200 0   0
3   0051ML66220600132482    06:00:00        06:00:00        1541    300 0   0
4   0051ML66220600132482    06:01:00        06:01:00        1542    400 0   0
5   0051ML66220600132482    06:01:00        06:01:00        1543    500 0   0
6   0051ML66220600132482    06:02:00        06:02:00        1544    600 0   0
7   0051ML66220600132482    06:03:00        06:03:00        1546    700 0   0

Структура таблицы, как показано ниже:

> ------------------------------------------------------------------
> --  TABLE stop_times
> ------------------------------------------------------------------
> 
> CREATE TABLE stop_times ( id int(12),
>                           trip_id varchar(100),
>                           arrival_time varchar(8),
>                           arrival_time_seconds int(100),
>                           departure_time varchar(8),
>                           departure_time_seconds int(100),
>                           stop_id varchar(100),
>                           stop_sequence varchar(100),
>                           pickup_type varchar(2),
>                           drop_off_type varchar(2) );

Я пытаюсь получить DISTINCT trip_id, соответствует ли он и пункту назначения, и прибытию stop_id

Я пробовал приведенный ниже SQL без везения:

select DISTINCT trip_id from stop_times where stop_id=1538 AND stop_id =1540;

Где должен производиться: 0051ML66220600132482

Я также пробовал INNER JOIN SQL, как показано ниже:

SELECT 
       t.trip_id,
       start_s.stop_name as departure_stop,
       end_s.stop_name as arrival_stop
FROM
trips t 
        INNER JOIN stop_times start_st ON t.trip_id = start_st.trip_id
        INNER JOIN stops start_s ON start_st.stop_id = start_s.stop_id
        INNER JOIN stop_times end_st ON t.trip_id = end_st.trip_id
        INNER JOIN stops end_s ON end_st.stop_id = end_s.stop_id
WHERE 
   start_s.stop_id = 1538 
  AND end_s.stop_id = 1540;

Но это ооочень медленно и занимает около 8-15 секунд для этого простого запроса.

Объяснение Добавлено:

введите здесь описание изображения

Каков самый быстрый/лучший способ сделать этот запрос?


mysql sql gtfs
person Yonkee    schedule 30.04.2015    source источник
comment
предоставить план объяснения для вашего запроса   -  person M Khalid Junaid    schedule 30.04.2015
comment
@MKhalidJunaid Добавлено объяснение.   -  person Yonkee    schedule 30.04.2015
comment
Вы предоставили 8 столбцов данных, но 10 столбцов в файле CREATE.   -  person EternalHour    schedule 30.04.2015
comment
@EternalHour верно. Я собирался использовать эти колонки позже. Сильно ли это влияет на запрос?   -  person Yonkee    schedule 30.04.2015

Ответы (2)


arrow_upward
3
arrow_downward

Другими словами, вы ищете запрос, который идентифицирует все поездки, которые проходят через пару остановок, пункт отправления (начальный пункт) и пункт назначения (конечный пункт).

Попробуйте этот запрос:

SELECT destination.trip_id
    FROM stop_times AS origin
    INNER JOIN stop_times AS destination
        ON destination.trip_id = origin.trip_id
        AND destination.stop_id = 1540
    WHERE origin.stop_id = 1538
        AND origin.stop_sequence < destination.stop_sequence;

Или, для более красивого вида (и для соответствия второму запросу в вашем вопросе):

SELECT destination.trip_id, origin_stop.name, destination_stop.name
    FROM stop_times AS origin
    INNER JOIN stop_times AS destination
        ON destination.trip_id = origin.trip_id
        AND destination.stop_id = 1540
    INNER JOIN stops AS origin_stop
        ON origin_stop.id = origin.stop_id
    INNER JOIN stops AS destination_stop
        ON destination_stop.id = destination.stop_id
    WHERE origin.stop_id = 1538
        AND origin.stop_sequence < destination.stop_sequence;

Для хорошей производительности сначала создайте индекс для stop_id и trip_id:

CREATE INDEX stop_times_stop_id_trip_id_index ON stop_times(stop_id, trip_id);

(Обратите внимание, что запрос EternalHour идентифицирует все поездки, проходящие через любую остановку, а не только поездки, проходящие сначала через одну, а затем через другую.)

person Community    schedule 30.04.2015
comment
Вы правы, запрос Ethernals работает некорректно. Мне нужна поездка, которая проходит через обе остановки. У тебя отлично работает, но медленно. Даже с индексом - person Yonkee; 02.05.2015

arrow_upward
1
arrow_downward

Похоже, это тот запрос, который вам нужен. Я удалил DISTINCT и заменил его на GROUP BY, а также заменил WHERE на IN. Ваш запрос указывает, что stop_id должен иметь тип INT, а не varchar, поскольку вы не добавили к нему кавычки, скрипка отражает это.

IN в основном делает OR вместо AND. Ничего не будет возвращено с AND, так как оба stop_id не существуют в одной строке.

SELECT trip_id 
FROM stop_times 
WHERE stop_id IN(1538,1540)
GROUP BY trip_id

Вот SQLFiddle.

person EternalHour    schedule 30.04.2015
comment
Вам нужно, чтобы запрос возвращал отдельную строку для каждого stop_id? - person EternalHour; 30.04.2015