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()）
602392714
清零编程群