如何使用SQL计算路线经过的次数？

吃西瓜不吐籽儿

我需要确定在给定的日期范围内每辆汽车某条路线行驶了多少次，但是基于数据库的GPS管理软件没有此功能。
该数据库包含几个表，这些表存储GPS，路线和位置数据。一条路线由几个位置和一个序列号组成。位置是一组附加在名称上的上下界纬度/经度值。车辆每分钟几次将其GPS位置数据上载到服务器，服务器会更新GPS表。
例如。某个路线“ FOO”可以依次包括位置“仓库”，“学校”，“体育场”和“公园”。“
BAR”本质上是同一条路线，但方向相反（可能在几条不同的路线上找到位置）。
一个简单的选择即可从GPS数据表中返回此类信息（车辆ID，位置，日期时间）：
34  Warehouse  2011-03-26 18:17:50.000
34  Warehouse  2011-03-26 18:18:30.000
34  Warehouse  2011-03-26 18:19:05.000
34  School    2011-03-26 18:21:34.000
34  School    2011-03-26 18:21:59.000
34  School    2011-03-26 18:22:42.000
34  School    2011-03-26 18:23:55.000
34  Stadium 2011-03-26 18:24:20.000
34  Stadium 2011-03-26 18:24:47.000
34  Park    2011-03-26 18:25:30.000
34  Park    2011-03-26 18:26:50.000
34  Warehouse  2011-03-26 18:28:50.000
等等。
从手动检查中可以明显看出，车辆34当天至少走过“?? FOO”路线。如何使用SQL确定当天每辆车经过该路线的总次数？
我感觉到我将不得不使用某种控制中断结构，但我希望有一种更简单的方法。这是用于从GPS表检索信息的SQL。
SELECT
v.VehicleID,
ml.LocationName
gps.Time,
FROM dbo.Routes r
INNER JOIN dbo.RoutePoints rp
      ON r.RouteId = rp.RouteId
INNER JOIN dbo.MapLocations ml
      ON rp.LocationId = ml.LocationId
INNER JOIN dbo.GPSData gps
      ON ml.LowerRightLatitude gps.Latitude
      AND ml.UpperLeftLongitude  gps.Longitude
INNER JOIN dbo.Vehicles v
      ON gps.VehicleID = v.VehicleID
WHERE r.Desc = @routename
AND gps.Time BETWEEN @startTime AND @endTime
ORDER BY v.VehicleId, gps.Time
编辑：回溯不被视为同一路线的一部分。仓库，学校，体育场，公园仓库，学校，体育场，公园。FOO路线只有两次旅行。如果某个位置的偏离不属于
已知路线， 例如仓库，学校，体育场，酒吧，公园，则可以忽略该位置。（即，仍然将其视为路线“ FOO”）

解决方案:


            DECLARE @route
      TABLE
      (
      route INT NOT NULL,
      step INT NOT NULL,
      destination INT NOT NULL,
      PRIMARY KEY (route, step)
      )
INSERT
INTO @route
VALUES
      (1, 1, 1),
      (1, 2, 2),
      (1, 3, 3),
      (1, 4, 4),
      (2, 1, 3),
      (2, 2, 4)
DECLARE @gps
      TABLE
      (
      vehicle INT NOT NULL,
      destination INT NOT NULL,
      ts DATETIME NOT NULL
      )
INSERT
INTO @gps
VALUES
      (1, 1, '2011-03-30 00:00:00'),
      (1, 2, '2011-03-30 00:00:01'),
      (1, 1, '2011-03-30 00:00:02'),
      (1, 3, '2011-03-30 00:00:03'),
      (1, 3, '2011-03-30 00:00:04'),
      (1, 3, '2011-03-30 00:00:05'),
      (1, 4, '2011-03-30 00:00:06'),
      (1, 1, '2011-03-30 00:00:07'),
      (1, 3, '2011-03-30 00:00:08'),
      (1, 4, '2011-03-30 00:00:09'),
      (1, 1, '2011-03-30 00:00:10'),
      (1, 2, '2011-03-30 00:00:11'),
      (1, 2, '2011-03-30 00:00:12'),
      (1, 3, '2011-03-30 00:00:13'),
      (1, 3, '2011-03-30 00:00:14'),
      (1, 4, '2011-03-30 00:00:15'),
      (1, 3, '2011-03-30 00:00:16'),
      (1, 4, '2011-03-30 00:00:17')
;
WITH iteration (vehicle, destination, ts, route, edge, step, cnt) AS
      (
      SELECT  vehicle, destination, ts, route, 1, step, cnt
      FROM (
            SELECT  g.vehicle, r.destination, ts, route, step, cnt,
                     ROW_NUMBER() OVER (PARTITION BY route, vehicle ORDER BY ts) rn
            FROM (
                     SELECT  *, COUNT(*) OVER (PARTITION BY route) cnt
                     FROM @route
                     ) r
            JOIN @gps g
            ON    g.destination = r.destination
            WHERE r.step = 1
            ) q
      WHERE rn = 1
      UNION ALL
      SELECT  vehicle, destination, ts, route, edge, step, cnt
      FROM (
            SELECT  i.vehicle, r.destination, g.ts, i.route, edge + 1 AS edge, r.step, cnt,
                     ROW_NUMBER() OVER (PARTITION BY i.route, g.vehicle ORDER BY g.ts) rn
            FROM iteration i
            JOIN @route r
            ON    r.route = i.route
                     AND r.step = (i.step % cnt) + 1
            JOIN @gps g
            ON    g.vehicle = i.vehicle
                     AND g.destination = r.destination
                     AND g.ts > i.ts
            ) q
      WHERE rn = 1
      )
SELECT  route, vehicle, MAX(edge / cnt)
FROM iteration
GROUP BY
      route, vehicle
在这里，我们有两条路线：(1, 2, 3, 4)和(3, 4)
车辆在途中行驶了2次，在途中行驶了(1, 2, 3, 4)4次(3, 4)。
重要的是，每个路由具有编号从开始的步骤1和无间隙（虽然如果不是的话，你可以很容易地解决它使用附加CTE有ROW_NUMBER()）

如何使用SQL计算路线经过的次数？

吃西瓜不吐籽儿 LV1