CODEWITHSUNDEEP
mysqlsqldatabase
romrom123
romrom123

Reputation: 15

mysql retrieving max timelapse between events

When I have a table I need to retrieve max difference between events in mysql query for example

time/event
1/a
2/a
5/a
7/a
1/b
4/b
15/b

in this case the answer should be (15-4) hence 11/b

the table is not sorted it was just for demonstrating my problem

Upvotes: 1

Views: 148

Answers (3)

symcbean
symcbean

Reputation: 48387

While Gordon's suggestion should work (assigning alias names which are the same as table names, @Gordon?) it will be very slow (O(N^2)).

CREATE FUNCTION maxgap()
RETURNS VARCHAR(30)
READS SQL DATA
BEGIN
   DECLARE prevtime INT;
   DECLARE prevevent VARCHAR(20) DEFAULT '';
   DECLARE curtime INT;
   DECLARE curevent VARCHAR(20);
   DECLARE maxtime INT;
   DECLARE maxevent VARCHAR(20);
   DECLARE finished INTEGER DEFAULT 0;

   DECLARE curs CURSOR FOR
      SELECT UNIX_TIMESTAMP(time), event FROM yourtable ORDER BY event,time;
   DECLARE CONTINUE HANDLER 
      FOR NOT FOUND SET finished = 1;

   get_records: LOOP
      FETCH curs INTO curtime, curevent;
      IF finished = 1 THEN 
           LEAVE get_records;
      END IF;
      IF (curevent != prevevent) THEN
         SET prevtime=curtime;
         SET prevevent=curevent;
      END IF 
      IF (maxtime<curtime-prevtime) THEN
         SET maxtime=curtime-prevtime;
         SET maxevent=curevent;
      END IF
    END LOOP get_records;
    CLOSE curs;

    RETURN (CONCAT(maxtime, '/', maxevent));

  END$$

(it should be possible to create the same behaviour using variables in a select satement - but just thinking about it makes my head hurt).

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272296

Assuming you do not have access to LEAD function you can join the table with itself to find the next row:

SELECT curr.time, curr.event, MIN(next.time) - curr.time AS diff
FROM testdata AS curr
INNER JOIN testdata AS next ON next.event = curr.event AND next.time > curr.time
GROUP BY curr.time, curr.event
ORDER BY diff DESC
LIMIT 1

Result:

+------+-------+------+
| time | event | diff |
+------+-------+------+
|    4 | b     |   11 |
+------+-------+------+

Here is what the results would look like without sorting and limit:

+------+-------+------+
| time | event | diff |
+------+-------+------+
|    1 | a     |    1 |
|    2 | a     |    3 |
|    5 | a     |    2 |
|    1 | b     |    3 |
|    4 | b     |   11 |
+------+-------+------+

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You need the previous or next time for the event. I would suggest a subquery:

select t.*
from (select t.*,
             (select t2.time
              from t t2
              where t2.event = t.event and t2.time < t.time
              order by t2.time desc
              limit 1
             ) as prev_time
      from t
     ) t
where prev_time is not null
order by (time - prev_time) desc
limit 1;

Upvotes: 0

Related Questions