Times between laps

Question

I have this SQL work fine except 1 point (at third value I need take previous time), this is the table (times) schema, output, and SQL:

id        number    time
-----------------------------------
1         9        00:00:10.000000
2         10       00:00:15.000000
3         9        00:00:22.000000
4         10       00:00:35.000000
1         9        00:00:55.000000

SELECT t.number, COUNT(1) laps, 
GROUP_CONCAT(SEC_TO_TIME(time) ORDER BY t.id) times
  FROM (
        SELECT t1.id, t1.number, 
        TIME_TO_SEC(t1.time) - COALESCE(SUM(TIME_TO_SEC(t2.time)), 0) time
        FROM times t1 
  LEFT JOIN times t2 ON t1.number = t2.number AND t2.id < t1.id
GROUP BY t1.id, t1.number, t1.time
) t
GROUP BY number

Result this output:

number    laps     times
-----------------------------------
9         3        00:00:10,00:00:12,00:00:23
10        2        00:00:15,00:00:20

I need this REsult expected:

number    laps     times
-----------------------------------
9         3        00:00:10,00:00:12,00:00:33
10        2        00:00:15,00:00:20

Where third time - previos calculated time, in this case (00:00:10,00:00:12,00:00:23 - 00:00:12) I Tried but SUM all times previous.

SQL Fiddle

The last id should be 5 according to your sql fiddle demo. – Jaugar Chang Oct 03 '14 at 09:06 — Jaugar Chang, Oct 03 '14 at 09:06

Jaugar Chang · Accepted Answer · 2014-10-03T09:04:47.347

Only need change SUM to MAX

SELECT t.number, COUNT(1) laps, 
  GROUP_CONCAT(SEC_TO_TIME(time) ORDER BY t.id) times
FROM (
  SELECT t1.id, t1.number, 
    TIME_TO_SEC(t1.time) - COALESCE(MAX(TIME_TO_SEC(t2.time)), 0) time
  FROM times t1 
  LEFT JOIN times t2 ON t1.number = t2.number AND t2.time < t1.time  -- Suggest you don't use id to compare
  GROUP BY t1.id, t1.number, t1.time
) t
GROUP BY number

See SQL FIDDLE DEMO

And here's another version without inner group by. They have the same effect, but different query plan. So you have choices to compare the performances.

SELECT t.number, COUNT(1) laps, 
  GROUP_CONCAT(SEC_TO_TIME(time) ORDER BY t.time) times
FROM (
  SELECT t1.id, t1.number, t1.time tm1,
    (SELECT  TIME_TO_SEC(t1.time) - COALESCE(MAX(TIME_TO_SEC(t2.time)),0) 
       FROM times t2 
      WHERE t1.number = t2.number AND t2.time < t1.time) AS time
  FROM times t1 
) t
GROUP BY number;

score 0 · Answer 2 · edited May 23 '17 at 10:33

This was a bit tricky, but here is a solution:

The main problem is the "smaller than" condition in your join. So first you have to add a running number (rank) to your Groups based on this answer. Then you can join by that rank. The resulting query is a bit large but gives you the desired result:

SELECT t.number, COUNT(1) laps,
GROUP_CONCAT(SEC_TO_TIME(time) ORDER BY t.id) times
FROM (
    SELECT t1.id, t1.number,
        TIME_TO_SEC(t1.time) - COALESCE(SUM(TIME_TO_SEC(t2.time)), 0) time
    FROM
    (SELECT
        number, id, time,
        (
            CASE number
            WHEN @curType
            THEN @curRow := @curRow + 1
            ELSE @curRow := 1 AND @curType := number END
        ) AS rank
    FROM times, (SELECT @curRow := 0, @curType := '') r
    ORDER BY  number, id
    ) as t1
    LEFT JOIN
    (SELECT
        number, id, time,
        (
            CASE number
            WHEN @curType
            THEN @curRow := @curRow + 1
            ELSE @curRow := 1 AND @curType := number END
        ) AS rank
    FROM times, (SELECT @curRow := 0, @curType := '') r
    ORDER BY  number, id
    ) t2
    ON t1.number = t2.number and t2.rank+1 = t1.rank
    GROUP BY t1.id, t1.number, t1.time
) t
GROUP BY number

gives:

| NUMBER | LAPS |                      TIMES |
|--------|------|----------------------------|
|      9 |    3 | 00:00:10,00:00:12,00:00:33 |
|     10 |    2 |          00:00:15,00:00:20 |

Times between laps

2 Answers2