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

Question

我需要确定在给定的日期范围内每辆汽车某条路线行驶了多少次，但是基于数据库的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.UpperLeftLatitude  > gps.Latitude
        AND ml.UpperLeftLongitude < gps.Longitude AND ml.LowerRightLongitude > 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”）

Answer 1

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()）