增加生成路由的'stop-time'

Question

我正在学习一些基本的SQL技能，我正在玩弄一些数据，看看我能做些什么，但是，我有点卡在如何继续处理我当前的问题。

我有一个表，看起来像这样:

<表类> RouteID SegmentID 为 BeginTime EndTime CarID tbody><<tr>145110:00:3010:01:001146210:01:0010:01:301147310:01:3010:02:001250110:05:0010:05:301249210:06:0010:06:3013900120:01:0020:01:302

您可以比较已排序的行来标记汽车的新路线起点。然后在一辆车里得到一个虚拟的路线号码。例如

select routeID, SegmentID,  [Order], BeginTime, EndTime, CarID,
-- virtual route nbr within a car
sum(newRouteFlag)  over(partition by CarID order by BeginTime) rtNmbr
from (
select routeID, SegmentID, [Order], BeginTime, EndTime, CarID,
case when lag(routeID) over(partition by CarID order by BeginTime) = routeID 
or dateadd(minute, 5, lag(BeginTime) over(partition by CarID order by BeginTime)) >= BeginTime then 0 else 1 end newRouteFlag
from tbl
) t