· 7 years ago · Sep 03, 2018, 02:40 PM
1How to calculate the number of times a route was travelled using SQL?
234 Warehouse 2011-03-26 18:17:50.000
334 Warehouse 2011-03-26 18:18:30.000
434 Warehouse 2011-03-26 18:19:05.000
534 School 2011-03-26 18:21:34.000
634 School 2011-03-26 18:21:59.000
734 School 2011-03-26 18:22:42.000
834 School 2011-03-26 18:23:55.000
934 Stadium 2011-03-26 18:24:20.000
1034 Stadium 2011-03-26 18:24:47.000
1134 Park 2011-03-26 18:25:30.000
1234 Park 2011-03-26 18:26:50.000
1334 Warehouse 2011-03-26 18:28:50.000
14
15SELECT
16 v.VehicleID,
17 ml.LocationName
18 gps.Time,
19FROM dbo.Routes r
20INNER JOIN dbo.RoutePoints rp
21 ON r.RouteId = rp.RouteId
22INNER JOIN dbo.MapLocations ml
23 ON rp.LocationId = ml.LocationId
24INNER JOIN dbo.GPSData gps
25 ON ml.LowerRightLatitude < gps.Latitude AND ml.UpperLeftLatitude > gps.Latitude
26 AND ml.UpperLeftLongitude < gps.Longitude AND ml.LowerRightLongitude > gps.Longitude
27INNER JOIN dbo.Vehicles v
28 ON gps.VehicleID = v.VehicleID
29WHERE r.Desc = @routename
30AND gps.Time BETWEEN @startTime AND @endTime
31ORDER BY v.VehicleId, gps.Time
32
33DECLARE @route
34 TABLE
35 (
36 route INT NOT NULL,
37 step INT NOT NULL,
38 destination INT NOT NULL,
39 PRIMARY KEY (route, step)
40 )
41
42INSERT
43INTO @route
44VALUES
45 (1, 1, 1),
46 (1, 2, 2),
47 (1, 3, 3),
48 (1, 4, 4),
49 (2, 1, 3),
50 (2, 2, 4)
51
52DECLARE @gps
53 TABLE
54 (
55 vehicle INT NOT NULL,
56 destination INT NOT NULL,
57 ts DATETIME NOT NULL
58 )
59
60INSERT
61INTO @gps
62VALUES
63 (1, 1, '2011-03-30 00:00:00'),
64 (1, 2, '2011-03-30 00:00:01'),
65 (1, 1, '2011-03-30 00:00:02'),
66 (1, 3, '2011-03-30 00:00:03'),
67 (1, 3, '2011-03-30 00:00:04'),
68 (1, 3, '2011-03-30 00:00:05'),
69 (1, 4, '2011-03-30 00:00:06'),
70 (1, 1, '2011-03-30 00:00:07'),
71 (1, 3, '2011-03-30 00:00:08'),
72 (1, 4, '2011-03-30 00:00:09'),
73 (1, 1, '2011-03-30 00:00:10'),
74 (1, 2, '2011-03-30 00:00:11'),
75 (1, 2, '2011-03-30 00:00:12'),
76 (1, 3, '2011-03-30 00:00:13'),
77 (1, 3, '2011-03-30 00:00:14'),
78 (1, 4, '2011-03-30 00:00:15'),
79 (1, 3, '2011-03-30 00:00:16'),
80 (1, 4, '2011-03-30 00:00:17')
81;
82
83WITH iteration (vehicle, destination, ts, route, edge, step, cnt) AS
84 (
85 SELECT vehicle, destination, ts, route, 1, step, cnt
86 FROM (
87 SELECT g.vehicle, r.destination, ts, route, step, cnt,
88 ROW_NUMBER() OVER (PARTITION BY route, vehicle ORDER BY ts) rn
89 FROM (
90 SELECT *, COUNT(*) OVER (PARTITION BY route) cnt
91 FROM @route
92 ) r
93 JOIN @gps g
94 ON g.destination = r.destination
95 WHERE r.step = 1
96 ) q
97 WHERE rn = 1
98 UNION ALL
99 SELECT vehicle, destination, ts, route, edge, step, cnt
100 FROM (
101 SELECT i.vehicle, r.destination, g.ts, i.route, edge + 1 AS edge, r.step, cnt,
102 ROW_NUMBER() OVER (PARTITION BY i.route, g.vehicle ORDER BY g.ts) rn
103 FROM iteration i
104 JOIN @route r
105 ON r.route = i.route
106 AND r.step = (i.step % cnt) + 1
107 JOIN @gps g
108 ON g.vehicle = i.vehicle
109 AND g.destination = r.destination
110 AND g.ts > i.ts
111 ) q
112 WHERE rn = 1
113 )
114SELECT route, vehicle, MAX(edge / cnt)
115FROM iteration
116GROUP BY
117 route, vehicle
118
119Foo: Locations 1, 2, 3, 4
120Bar: Locations 4, 3, 2, 1
121Quicky: Locations 2, 3
122
123CREATE TABLE #Routes
124(
125 RouteID INT,
126 RouteName VARCHAR(50),
127)
128
129CREATE TABLE #RoutePoints
130(
131 RouteID INT,
132 LocationID INT,
133 SequenceNumber INT
134)
135
136CREATE TABLE #MapLocations
137(
138 LocationID INT,
139 LocationName VARCHAR(50)
140)
141
142CREATE TABLE #GPSData
143(
144 VehicleID INT,
145 LocationID INT,
146 Time DATETIME
147)
148
149INSERT INTO #Routes (RouteID, RouteName) VALUES (1, 'Foo')
150INSERT INTO #Routes (RouteID, RouteName) VALUES (2, 'Bar')
151INSERT INTO #Routes (RouteID, RouteName) VALUES (3, 'Quicky')
152
153INSERT INTO #MapLocations (LocationID, LocationName) VALUES (1, 'Warehouse')
154INSERT INTO #MapLocations (LocationID, LocationName) VALUES (2, 'School')
155INSERT INTO #MapLocations (LocationID, LocationName) VALUES (3, 'Stadium')
156INSERT INTO #MapLocations (LocationID, LocationName) VALUES (4, 'Park')
157
158INSERT INTO #RoutePoints (RouteID, LocationID, SequenceNumber) VALUES (1, 1, 1)
159INSERT INTO #RoutePoints (RouteID, LocationID, SequenceNumber) VALUES (1, 2, 2)
160INSERT INTO #RoutePoints (RouteID, LocationID, SequenceNumber) VALUES (1, 3, 3)
161INSERT INTO #RoutePoints (RouteID, LocationID, SequenceNumber) VALUES (1, 4, 4)
162
163INSERT INTO #RoutePoints (RouteID, LocationID, SequenceNumber) VALUES (2, 4, 1)
164INSERT INTO #RoutePoints (RouteID, LocationID, SequenceNumber) VALUES (2, 3, 2)
165INSERT INTO #RoutePoints (RouteID, LocationID, SequenceNumber) VALUES (2, 2, 3)
166INSERT INTO #RoutePoints (RouteID, LocationID, SequenceNumber) VALUES (2, 1, 4)
167
168INSERT INTO #RoutePoints (RouteID, LocationID, SequenceNumber) VALUES (3, 2, 1)
169INSERT INTO #RoutePoints (RouteID, LocationID, SequenceNumber) VALUES (3, 3, 2)
170
171INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 12:17:50.000')
172INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 12:18:50.000')
173INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 12:19:50.000')
174INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 12:20:50.000')
175INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 12:21:50.000')
176INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 12:22:50.000')
177INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 12:23:50.000')
178INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 3, '2011-03-26 12:24:50.000')
179INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 3, '2011-03-26 12:25:50.000')
180
181INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 18:17:50.000')
182INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 18:18:50.000')
183INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 18:19:50.000')
184INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 18:20:50.000')
185INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 18:21:50.000')
186INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 18:22:50.000')
187INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 18:23:50.000')
188INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 3, '2011-03-26 18:24:50.000')
189INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 3, '2011-03-26 18:25:50.000')
190INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 4, '2011-03-26 18:26:50.000')
191INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 4, '2011-03-26 18:27:50.000')
192INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 18:28:50.000')
193
194INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 19:17:50.000')
195INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 19:18:50.000')
196INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 19:19:50.000')
197INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 19:20:50.000')
198INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 19:21:50.000')
199INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 19:22:50.000')
200INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 19:23:50.000')
201INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 3, '2011-03-26 19:24:50.000')
202INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 3, '2011-03-26 19:25:50.000')
203INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 4, '2011-03-26 19:26:50.000')
204INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 4, '2011-03-26 19:27:50.000')
205INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 19:28:50.000')
206
207CREATE TABLE #GPSRoute
208(
209 RouteID INT,
210 VehicleID INT,
211 LocationID INT,
212 SequenceNumber INT,
213 Time DATETIME,
214 StepsInRoute INT
215)
216
217INSERT INTO #GPSRoute
218(
219 RouteID,
220 VehicleID,
221 LocationID,
222 SequenceNumber,
223 Time,
224 StepsInRoute
225)
226SELECT
227 r.RouteID,
228 gps.VehicleID,
229 rp.LocationID,
230 rp.SequenceNumber,
231 gps.Time,
232 (
233 SELECT COUNT(*)
234 FROM #RoutePoints
235 WHERE RouteID = r.RouteID
236 )
237FROM
238 #Routes r JOIN
239 #RoutePoints rp ON r.RouteID = rp.RouteID JOIN
240 #GPSData gps ON gps.LocationID = rp.LocationID
241
242SELECT
243 r.RouteID,
244 r.RouteName,
245 previousRouteStep.VehicleID,
246 COUNT(*) / NULLIF(previousRouteStep.StepsInRoute - 1, 0) AS TimesRouteCompleted
247FROM
248 #Routes r JOIN
249 #GPSRoute previousRouteStep ON r.RouteID = previousRouteStep.RouteID JOIN
250 #GPSRoute nextRouteStep ON
251 previousRouteStep.RouteID = nextRouteStep.RouteID AND
252 previousRouteStep.VehicleID = nextRouteStep.VehicleID AND
253 previousRouteStep.SequenceNumber + 1 = nextRouteStep.SequenceNumber AND
254 previousRouteStep.Time < nextRouteStep.Time AND
255
256 -- Only include the step if it is followed by the subsequent step.
257 nextRouteStep.Time = (
258 SELECT MIN(Time)
259 FROM #GPSRoute
260 WHERE
261 RouteID = nextRouteStep.RouteID AND
262 VehicleID = nextRouteStep.VehicleID AND
263 sequenceNumber = nextRouteStep.SequenceNumber AND
264 Time > previousRouteStep.Time
265 ) AND
266
267 -- Only include the step if it is the latest step in the sequence that meets the criteria.
268 previousRouteStep.Time = (
269 SELECT MAX(Time)
270 FROM #GPSRoute
271 WHERE
272 RouteID = previousRouteStep.RouteID AND
273 VehicleID = previousRouteStep.VehicleID AND
274 sequenceNumber = previousRouteStep.sequenceNumber AND
275 Time < nextRouteStep.Time
276 )
277WHERE
278 -- This step is only valid if every preceding step has been done.
279 NOT EXISTS (
280 SELECT 1
281 FROM
282 #RoutePoints rp LEFT JOIN
283 #GPSRoute gpsr ON
284 gpsr.RouteID = rp.RouteID AND
285 gpsr.LocationID = rp.LocationID AND
286 gpsr.SequenceNumber = rp.SequenceNumber AND
287 gpsr.Time < previousRouteStep.Time AND
288 gpsr.VehicleID = previousRouteStep.VehicleID
289 WHERE
290 rp.SequenceNumber < previousRouteStep.SequenceNumber AND
291 gpsr.RouteID IS NULL AND
292 rp.RouteID = previousRouteStep.RouteID
293 )
294GROUP BY
295 r.RouteID,
296 r.RouteName,
297 previousRouteStep.VehicleID,
298 previousRouteStep.StepsInRoute