CODEWITHSUNDEEP
mysql
Meng Hai
Meng Hai

Reputation: 127

mysql how to group records with time in series

Say I have the following records in TableA:

UserID, Date, GroupNo, StartTime, EndTime
4, 2015-08-03, 1, 2015-08-03 08:00:00, 2015-08-03 09:00:00 
4, 2015-08-03, 1, 2015-08-03 09:00:00, 2015-08-03 10:00:00 
4, 2015-08-03, 1, 2015-08-03 13:00:00, 2015-08-03 14:00:00 
4, 2015-08-03, 1, 2015-08-03 14:00:00, 2015-08-03 15:00:00 
4, 2015-08-03, 1, 2015-08-03 15:00:00, 2015-08-03 16:00:00 
4, 2015-08-03, 2, 2015-08-03 17:00:00, 2015-08-03 18:00:00

Is there a way I can use a query to group my records into 3 rows whereby all the linked time(EndTime)=time(StartTime) will be group together?

The end result of the query will be:

UserID, Date, GroupNo, StartTime, EndTime
4, 2015-08-03, 1, 08:00:00, 10:00:00 
4, 2015-08-03, 1, 13:00:00, 16:00:00 
4, 2015-08-03, 2, 17:00:00, 18:00:00

I think the query should look something like this:

select UserID, Date, GroupNo, min(time(StartTime)) as StartTime,   
       max(time(EndTime)) as EndTime
from TableA
group by "??"

But I am not sure how to group it by...

Is it do-able?

Thank you very much.

In case the data is not already in sequence, or when there are other dates involved: I find this modified query to be working: (for others who may need to know in future)

select userid, min(date) as date, groupno, min(time(starttime)), max(time(endtime)) from (select a.*, (@grp := if(@ugt = concat_ws('-', userid, date, groupno, time(starttime)), if(@ugt := concat_ws('-', userid, date, groupno, time(endtime)), @grp, @grp), if(@ugt := concat_ws('-', userid, date, groupno, time(endtime)), @grp+1, @grp+1) ) ) grp from (select * from TableA where userid=4 order by date, Time(starttime)) a cross join (select @grp := 0, @ugt := NULL) params order by userid, date, groupno, time(starttime) ) a where userid=4 group by userid, groupno, grp order by date, time(starttime)

Upvotes: 1

Views: 98

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

This is a bit of a pain in MySQL, but possible. You need to keep track of the times to identify when a new group starts. Variables is probably the simplest way:

select userid, min(date) as date, groupno, min(starttime), max(endtime)
from (select a.*,
             (@grp := if(@ugt = concat_ws('-', userid, groupno, starttime),
                         if(@ugt := concat_ws('-', userid, groupno, endtime), @grp, @grp),
                         if(@ugt := concat_ws('-', userid, groupno, endtime), @grp+1, @grp+1)
                        )
             ) grp
      from tableA a cross join
           (select @grp := 0, @ugt := NULL) params
      order by userid, groupno, starttime
     ) a
group by userid, groupno, grp;

The idea is to sort the data by the userid, groupno, and starttime (perhaps date should be included as well, it is hard to tell from the question).

Then assign the variable @grp. This value does not change when the keys are the same. The value is incremented when the keys do change. There is a bit of trickery because the endtime on the previous row needs to be compared to the starttime on the current row.

Finally, @grp is used for the aggregation.

Upvotes: 1

Related Questions