Mysql – Finding contiguous ranges in grouped data

MySQL

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)
Tour int,(no of laps made)
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

Best Answer

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

Related Question