使用GTFS提要检测停止类型

Question

我有一个GTFS提要，我正在尝试使用SQL检测停止类型(在我的例子中是bus、tram或multiple )。要做到这一点，我必须获取每个站点上的所有路线，并比较route.route_type字段，其中包含有关路由类型(0 -电车、3 - bus)的信息。

以下是我的查询：

SELECT stop.stop_id, route.route_type from stop
INNER JOIN stop_time on stop_time.stop_id = stop.stop_id
INNER JOIN trip on trip.trip_id = stop_time.trip_id
INNER JOIN route on route.route_id = trip.route_id
GROUP BY stop.stop_id, route.route_type 
ORDER BY stop.stop_id, route.route_type 

此查询返回：

stop_id,route_type
1,0
1,3
2,0
3,3

这意味着stop_id =1具有：

• 巴士路线，
• 电车路线。

stop_id =2只有电车路线。

stop_id =3只有巴士路线。

我想返回：

stop_id,stop_type
1,multiple
2,tram
3,bus

也许更容易的办法是只修改这些有公共汽车和电车路线的车站。所以应该是这样的：

stop_id,stop_type
1,-1
2,0
3,3

如何实现这一结果？我不确定，但我认为我应该计算每个stop_id的行数，然后以某种方式修改结果，但我不知道如何实现。

Answer 1

您可以检查每个COUNT的不同route_type，如果它超过1然后输出multiple，否则使用MAX (或MIN )，因为只有一个不同的值，但我们必须使用聚合函数以避免违反route_type的组，从而将stop_type设置为bus或tram

SELECT stop.stop_id, 
       CASE WHEN COUNT(DISTINCT route.route_type) > 1 THEN 'multiple'
            WHEN MAX(route.route_type) = 0 THEN 'tram'
            ELSE 'bus'
       END AS stop_type
FROM stop
INNER JOIN stop_time on stop_time.stop_id = stop.stop_id
INNER JOIN trip on trip.trip_id = stop_time.trip_id
INNER JOIN route on route.route_id = trip.route_id
GROUP BY stop.stop_id
ORDER BY stop.stop_id