如何使用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) );

我正在尝试获取不同的 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秒。

说明附加：

如何使这个查询更快/更好？

- Yonkee

---

为您的查询提供解释计划。 - M Khalid Junaid

@MKhalidJunaid 解释已添加。 - Yonkee

您提供了8列数据，但在“CREATE”中有10列。 - EternalHour

@EternalHour 没错。我之后会使用那些列。这会对查询有很大影响吗？ - Yonkee

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;

或者，为了更美观的外观（和与您问题中的第二个查询相匹配），您可以这样做：

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

- user473305

---

你是对的，Ethernals的查询没有正确地工作。我需要一条经过两个站点的旅程。你的方法很好，但速度比较慢。即使使用了索引。 - Yonkee

Answer 2

似乎这是您需要的查询。我删除了DISTINCT并将其替换为GROUP BY，还将WHERE替换为IN。您的查询表明stop_id应该是INT类型，而不是varchar类型，因为您没有添加引号，fiddle反映了这一点。 IN基本上执行OR而不是AND。使用AND不会返回任何内容，因为两个stop_id不存在于同一行中。

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

这里是一个SQLFiddle。

- EternalHour

---

您需要查询为每个stop_id返回单独的行吗？ - EternalHour