MySQL : the average time of the runners of a race

Question

Maybe i don't use the right words but i don't find a solution with the search box. i have a race with a lot of runner. Each runner have done 7/12 laps. I have one record by laps by runner. For example :

+------+-----------+---------------------+
| id.  | id.runner | lapse time          |
+------+-----------+---------------------+
| 1    | 2         | 2019-12-16 09:24:10 |
| 2    | 7         | 2019-12-16 09:24:20 |
| 3    | 4         | 2019-12-16 09:24:30 |
| 4    | 2         | 2019-12-16 09:25:10 |
| 5    | 7         | 2019-12-16 09:25:30 |
| 6    | 4         | 2019-12-16 09:25:50 |
| 7    | 2         | 2019-12-16 09:26:10 |
| 8    | 7         | 2019-12-16 09:26:40 |
| 9    | 4         | 2019-12-16 09:27:10 |
| 10   | 2         | 2019-12-16 09:27:10 |
+------+-----------+---------------------+

I need if possible one query for getting the average time by runner, here : 2 : 60seconds 7 : 70seconds 4 : 80seconds

And another one for getting the average of the average time, here : (60+70+80)/3 = 70

I tried a lot of queries but the result is always wrong.

Answer 1

You could use a combination of LEAD and TIMESTAMPDIFF to find each pair of lap times, and then take their difference:

SELECT
    id_runner,
    AVG(diff) AS avg_lap_time
FROM
(
    SELECT
        id_runner,
        TIMESTAMPDIFF(SECOND,
                      lapse,
                      LEAD(lapse) OVER (PARTITION BY id_runner ORDER BY lapse)) AS diff
    FROM yourTable
) t
GROUP BY
    id_runner;

Demo

Here for the last lap time, the LEAD would be NULL, so the entire diff calculation would also be NULL. But, this is acceptable, because then it would just drop out of the average calculation (i.e. it would be ignored).

Note that if you are using an earlier version of MySQL which does not support LEAD, we could instead use analytic functions to find the next sequential lap time.

SELECT
    id_runner,
    AVG(diff) AS avg_lap_time
FROM
(
    SELECT
        id_runner,
        TIMESTAMPDIFF(SECOND,
                      lapse,
                      (SELECT t2.lapse FROM yourTable t2
                       WHERE t2.id_runner = t1.id_runner AND t2.lapse > t1.lapse
                       ORDER BY t2.lapse LIMIT 1)) AS diff
    FROM yourTable t1
) t
GROUP BY
    id_runner;