Finding contiguous ranges in grouped data

I have a table that records the data of a car race with the following structure:

``````ID int, (id of car)
Duration timestamp, (time taken to drive)
Pilot int,(drivers)
``````

where Duration is in time and tour=no of laps which consists of values from 0 to 3000 and pilot has two values 2,3 (2 drivers with id 2 and 3) The table shows the no of laps done within given time for each pilot. The pilots drive the car within the time.Assume pilot 2 has drove the car first next pilot 3 and so on..

Now,I want total tour(difference) and total duree(difference) done by pilot having an id value 2 ?

SAMPLE DATA:

``````ID|DURATION|TOUR| PILOT
1 |15:00:01|216 |  2 \
2 |15:00:02|216 |  2 /   1 sec.
3 |15:00:03|217 |  7
4 |15:00:04|218 |  7
5 |15:00:05|219 |  7
6 |15:00:06|220 |  2 \
7 |15:00:07|221 |  2 /   1 sec.
8 |15:00:08|222 |  7
9 |15:00:09|222 |  7
10|15:00:10|223 |  2 \
11|15:00:11|224 |  2  |
12|15:00:12|225 |  2 /   2 sec.
``````

Now I need the desired result:

For pilot id = 2, I need the tour difference along with duree(total)

Ans: 219 i.e 216 + (221-220) + (225-223)

``````First tour ID: 216 + (Dif of laps of each group) = 216 + 1 + 2 = 219
``````

And duree(total) = Sum(Dif time of each group) = 00:00:04

I've set up the next sample on rextester:

``````DROP TABLE IF EXISTS race;
CREATE TABLE IF NOT EXISTS race (ID int, DURATION time, TOUR int, PILOT int);
INSERT INTO race VALUES (1 , '15:00:01', 216, 2);
INSERT INTO race VALUES (2 , '15:00:02', 216, 2);
INSERT INTO race VALUES (3 , '15:00:03', 217, 7);
INSERT INTO race VALUES (4 , '15:00:04', 218, 7);
INSERT INTO race VALUES (5 , '15:00:05', 219, 7);
INSERT INTO race VALUES (6 , '15:00:06', 220, 2);
INSERT INTO race VALUES (7 , '15:00:07', 221, 2);
INSERT INTO race VALUES (8 , '15:00:08', 222, 7);
INSERT INTO race VALUES (9 , '15:00:09', 222, 7);
INSERT INTO race VALUES (10, '15:00:10', 223, 2);
INSERT INTO race VALUES (11, '15:00:11', 224, 2);
INSERT INTO race VALUES (12, '15:00:12', 225, 2);
``````

First it flags involved groups:

``````| ID | DURATION | TOUR | PILOT | GRP |
|----|----------|------|-------|-----|
| 1  | 15:00:01 | 216  | 2     | 1   |
| 2  | 15:00:02 | 216  | 2     | 1   |
| 6  | 15:00:06 | 220  | 2     | 2   |
| 7  | 15:00:07 | 221  | 2     | 2   |
| 10 | 15:00:10 | 223  | 2     | 3   |
| 11 | 15:00:11 | 224  | 2     | 3   |
| 12 | 15:00:12 | 225  | 2     | 3   |
``````

Then calculates time difference of each group:

``````| DUREE | DIF_TOUR | MIN_TOUR |
|-------|----------|----------|
| 1     | 0        | 216      |
| 1     | 1        | 220      |
| 2     | 2        | 223      |
``````

And finally returns aggregated values:

``````SELECT SUM(DUREE) DUREE, MIN(MIN_TOUR) + SUM(DIF_TOUR) TOURS
FROM
(SELECT MAX(DURATION) - MIN(DURATION) AS DUREE,
MAX(TOUR) - MIN(TOUR) AS DIF_TOUR,
MIN(TOUR) AS MIN_TOUR
FROM (SELECT ID, DURATION, TOUR, PILOT,
CASE WHEN TOUR <> @LTOUR AND TOUR <> @LTOUR + 1
THEN @GRP := @GRP + 1 ELSE @GRP END AS GRP,
@LTOUR := TOUR
FROM   (SELECT @GRP := 0, @LTOUR := 0) X,
(SELECT ID, DURATION, TOUR, PILOT
FROM race
WHERE PILOT = 2
ORDER BY PILOT, TOUR) Y) Z
GROUP BY GRP) W;

| DUREE | TOURS |
|-------|-------|
| 4     | 219   |
``````

Rextester here

`Duration` have to be called `timestamp`.

``````SELECT MAX(duration) - MIN(duration) AS timediff
, MAX(tour) - MIN(tour) AS tourdiff
FROM table
WHERE pilot = 2
GROUP BY pilot
;
``````
• This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review – Michael Green Jun 26 '17 at 9:54
• But it does. I'm sure it is some kind of slot-car racing where table is filled by checkpoint data `stopwatch/counter`. And query perform exactly what has been asked. – Kondybas Jun 26 '17 at 11:07
• Thanks for your reply. But ,I'm not getting the desired result as mentioned above. I'm getting the result as duree = 00:00:11 and tour =9 (I dont want this answer) – Uday Bora Jun 26 '17 at 13:50