sql - 如何使用 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() 轻松解决它)