How to Summarize Consecutive Rows in a MySQL Table

Question

So I have a table that tracks the movements of multiple users inside a building. The columns show the user's ID, what room they are in, and the timestamp in which they were found in that room. The table looks like the following:

user_id  location  time                
-------  --------  -------------------   
1        room1     2018-05-18 03:20:00     
1        room1     2018-05-18 03:21:15
1        room2     2018-05-18 03:22:07
2        room1     2018-05-18 03:24:20     
2        room1     2018-05-18 03:27:55
2        room1     2018-05-18 03:29:09      
1        room2     2018-05-18 03:32:37    
1        room1     2018-05-18 03:34:41
1        room1     2018-05-18 03:39:28

What I'm trying to do is to summarize the information of how long was each user in each room, like so:

user_id  location  duration(s)
-------  --------  -----------
1        room1     75
2        room1     289
1        room2     630
1        room1     287

Is there any way to do this with a single query?

Answer 1

You can handle this using variables or a correlated subquery. Variables are usually more efficient:

select user_id, location, min(time), max(time),
       timestampdiff(second, min(time), max(time)) as duration
from (select t.*,
             (@grp := if(@ul = concat_ws(':', user_id, location), @grp,
                         if(@ul := concat_ws(':', user_id, location), @grp + 1, @grp + 1)
                        )
             ) as grp
      from (select t.*
            from t
            order by user_id, time
           ) t cross join
           (select @ul := '', @grp := 0) params
     ) t
group by user_id, location, grp;

Here is a SQL Fiddle with the working code.