mysql - 如何使用 GTFS 列出从一站到另一站的所有行程？

Question

我正在使用下表:

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 秒。

解释添加:

进行此查询的最快/最佳方法是什么？

Answer 1

换句话说，您正在寻找一个查询，该查询将识别经过一对停靠点、起点(起点)和终点(终点)的所有行程。

试试这个查询:

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;

或者，为了更漂亮的 View (并匹配您问题中的第二个查询):

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 的查询会识别所有经过任一站点的行程，而不仅仅是先经过一个站点然后再经过另一个站点的行程。)