Python/SQL:存储序列

我有下表( schedule )在我的数据库中(25万行):

驻留在其中的数据来自 GTFS static 在我的镇上吃饭。它是相当标准化的,包含每种车型、路线、车站等的描述。

当静态数据库中包含来自数据库的更新时,就会出现问题 GTFS realtime 喂养TripUpdates feed没有指定正在驾驶的车辆属于哪条线路,如果 这是为了应对突然增加的乘客数量,或者如果之前的车辆坏了 .

TripUpdates :

trip_update {
 trip {
   trip_id: "14010000574353508"      # Exists in the database
   start_date: "20220204"
   schedule_relationship: SCHEDULED  # Trip is scheduled, everything is OK
 }
 stop_time_update {
   stop_sequence: 72
   arrival {
     delay: 147
     time: 1643833304
     uncertainty: 0
   }
   departure {
     delay: 147
     time: 1643833304
     uncertainty: 0
   }
   stop_id: "9022001001281076"       # Exists in the database, "Central station"
 }
 vehicle {
   id: "9031001000158703"
 }
}
trip_update {
 trip {
   trip_id: "14010513667077909"      # Does not exist in the database
   start_time: "21:06:00"
   start_date: "20220204"
   schedule_relationship: ADDED      # New trip, needs to be added to the database 
 }
 stop_time_update {
   stop_sequence: 1
   arrival {
     delay: 0
     time: 1643833260
   }
   departure {
     delay: 0
     time: 1643833260
   }
   stop_id: "9022001001282313"       # Exists in the database, "Hospital"
 }
 stop_time_update {
   stop_sequence: 2
   arrival {
     delay: 0
     time: 1643833299
   }
   departure {
     delay: 0
     time: 1643833299
   }
   stop_id: "9022001001282314"       # Exists in the database, "Hogwarts", definitely Yellow line 
 }
 vehicle {
   id: "9031001002500015"
 }
 timestamp: 1643829559
}

我想做的是,根据车辆停下来的次数来确定车辆属于哪条线路,一旦我确定了,就将其添加到数据库中。为了更好地说明我的意思,我画了下面的地铁线路示意图。

以我的地铁地图为例:

• 如果旅行从 地下一层 然后去 B7 -将其作为蓝线添加到数据库中。
• 如果旅行从 X1 然后去 Y5 -将其作为黄线添加到数据库中。
• 如果旅行从 X1 然后去 X3 -将其作为“额外行”添加到数据库中。

我已经有一张桌子了 stop_id 属于每一条地铁线路的:

红线:

• 9022001001281079-斯普纳街。
• 9022001001281078-大酒店
• 9022001001281077-中央公园
• 9022001001281076 - 中央车站

蓝线:

• 9022001001221005-机场
• 9022001001221004-约翰·约翰森街。
• 9022001001221003-城市教堂
• 9022001001221002 - 中央车站

黄线:

• 9022001001282314-霍格沃茨
• 9022001001282313-医院
• 9022001001282312 - 中央车站

我的程序是用Python编写的,使用sqlite3数据库。我尝试过使用if语句,这导致了无法维护或读取的意大利面代码。我还尝试了不同的表连接,并将站名转换为二进制模式,不幸的是,这也导致了相同的意大利面代码。

我的问题是 如果有任何方法可以处理Python或SQL中的模式(想一想它更多的是关于SQL的),那么可以根据预期的行程停止点为行程分配一行吗?

基本上(伪代码):

IF anything BETWEEN 9022001001281079 AND 9022001001281071 -> "Red line"
IF anything BETWEEN 9022001001221005 AND 9022001001209996 -> "Blue line"
IF anything BETWEEN 9022001001282314 AND 9022001001282307 -> "Yellow line"
ELSE -> "Extra line"

记住 住手 s不是序列号。 本文中提供的ID只是示例,在真实的GTFS提要中,一个站点的ID可能为9022001001221005,下一个站点(在同一线路上)的ID可能为9022003201321094。

更新1 :

SQL CASE和BETWEEN在这里不起作用。

我尝试了评论中提供的解决方案(使用SQL),发现了一些问题。

在我从中提取最大值和最小值之后 住手 对于地铁线路,我得到以下方案:

红线 : 9022001001011003 - 9022001002851002

蓝线 : 9022001001051005 - 9022001003481002

黄线 : 9022001001011001 - 9022001001951002

因为第一部分 住手 总是一样的,我在进一步的描述中排除了它。

如果火车从 1011003 到 1011007 ,SQL CASE和BETWEEN将无法正确检测地铁线路,因为该范围适用于红线和黄线。