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.

Can you post the queries you have tried so far?? – Prabhjot Singh Kainth Dec 16 '19 at 08:45 — Prabhjot Singh Kainth, Dec 16 '19 at 08:45

Tim Biegeleisen · Accepted Answer · 2019-12-16T09:04:15.557

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;

No chance : "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OVER (PARTITION BY id_runner ORDER BY lapse)) AS diff FROM yourTable ) t ' at line 10". I run on MySQL 5.1.73, no choice — user3726755, Dec 16 '19 at 09:02
@user3726755 Try the second version of my answer, which should work on your version of MySQL. — Tim Biegeleisen, Dec 16 '19 at 09:05

MySQL : the average time of the runners of a race

1 Answers1

Demo