如何获得每条经过一个车站的总线的下一个2站时间(GTFS数据)？

Question

我的目标很简单。在我的应用程序中，用户会按下一个停止，它会打开一个屏幕，显示经过该站的每条路线(总线)，并显示每条路线在该站下的2个停止时间。当用户按下停止id为1571的停止时，我想显示什么：

乘搭569站的巴士

2号干线

23分钟(上午7时23分)

43分钟(上午7时43分)

37号公路

15分钟(上午7时15分)

45分钟(上午7时45分)

返回的示例数据(我想要的)：

st.departure_time，rte.route_long_name，tr.trip_headsign

7:23:00，2号公路，市中心

7:43:00，2号公路，市中心

07:15:00，37号公路，市中心

7:45:00，37号公路，市中心

应该注意的是，停止代码为569，停止id为1571。根据GTFS文档，用户通常会搜索停止代码(569)，但内部搜索的是停止id (1571)。这些文件来自GTFS包中的stops.txt文件。

示例显示2条路线(2条和37条)，但可能有更多的路线，可能只有1条路线，或者在不久的将来根本就没有路线经过这一站(我想忽略24小时以上的时间)。

免责声明:我不太擅长SQL。我创建了一个似乎检索相关数据的查询，但是，它返回的数据比每条路径接下来的2个停止时间还多。我在我的应用程序中解析它，以获得想要的结果，但我觉得只要改进我的查询就会容易得多。

SELECT stop_times.departure_time, routes.route_long_name, trips.trip_headsign, calendar.monday, calendar.tuesday, calendar.wednesday, calendar.thursday, calendar.friday, calendar.saturday, calendar.sunday
FROM stop_times, trips, routes, calendar
WHERE stop_times.stop_id = '1571' AND stop_times.trip_id = trips.trip_id AND trips.route_id = routes.route_id AND trips.service_id = calendar.service_id AND 20180801 >= calendar.start_date AND 20180801 <= calendar.end_date
ORDER BY routes.route_short_name, trips.service_id, stop_times.departure_time

这是相关的表格。我按照GTFS文档中指定的方式将数据存储在数据库中。

stops.txt示例行：

​

routes.txt示例行：

​

trips.txt示例行：

​

times.txt示例行：

​

calendar.txt示例行：

​

非常感谢您的帮助。

Answer 1

您已经可以做的事情之一是使用标准的联接表示法。

它的可读性更强，并且不太容易导致偶然的笛卡儿连接。

使用简短的别名可以使SQL更加简洁。

SET @StopId = '1571';
SET @CalDate = cast('2018-08-01' as date);

SELECT 
 st.departure_time, 
 rte.route_long_name, 
 tr.trip_headsign,
 cal.monday, cal.tuesday, cal.wednesday, cal.thursday, cal.friday, 
 cal.saturday, cal.sunday
FROM stop_times AS st
JOIN trips AS tr ON tr.trip_id = st.trip_id
JOIN routes AS rte ON rte.route_id = tr.route_id
JOIN calendar AS cal ON cal.service_id = tr.service_id
WHERE st.stop_id = @StopId
AND @CalDate between cal.start_date and cal.end_date
ORDER BY rte.route_short_name, tr.service_id, st.departure_time

至于每条路线下两个站的要求是什么？

这里有一个SQL示例。

包括工作日的支票。不确定是否需要，所以这只是为了证明。

它的SQL可以找到这里

SET @StopId = 1571;
SET @CalDate = cast('2018-08-01' as date);
SET @StartTime = cast('08:00:00' as time);

SELECT departure_time, route_long_name, trip_headsign
FROM 
(
    SELECT 
     st.departure_time,
     tr.service_id,
     rte.route_short_name,
     rte.route_long_name,
     tr.trip_headsign,
     @num := if(@prev_routeid = tr.route_id, @num + 1, 1) as RN,
     @prev_routeid := tr.route_id as route_id
    FROM stop_times AS st
    JOIN trips AS tr ON tr.trip_id = st.trip_id
    JOIN routes AS rte ON rte.route_id = tr.route_id
    JOIN calendar AS cal ON cal.service_id = tr.service_id
    -- LEFT JOIN stops AS s on s.stop_id = st.stop_id
    CROSS JOIN (SELECT @num := 0, @type := '') AS vars
    WHERE st.stop_id = @StopId
    AND @CalDate between cal.start_date and cal.end_date
    AND (CASE WEEKDAY(@CalDate)
        WHEN 0 THEN monday 
        WHEN 1 THEN tuesday 
        WHEN 2 THEN wednesday
        WHEN 3 THEN thursday
        WHEN 4 THEN friday
        WHEN 5 THEN saturday
        WHEN 6 THEN sunday
        END) = 1
    AND st.departure_time >= @StartTime
    ORDER BY tr.route_id, st.departure_time
) Q
WHERE RN <= 2
ORDER BY route_short_name, service_id, departure_time;

侧记：

RN是使用变量以旧的MySql 7样式计算的。

在MySql 8中，可以使用窗口函数ROW_NUMBER。