Session重叠问题学习(九)--该问题第七次优化.优化合并算法-优快云博客

接前文
Session重叠问题学习(二)，这是问题和需求的描述，执行时间90秒
http://blog.itpub.net/29254281/viewspace-2150229/

Session重叠问题学习(三)--优化，一次优化后,执行时间25秒
http://blog.itpub.net/29254281/viewspace-2150259/

Session重叠问题学习(四)--再优化，二次优化后,执行时间10秒
http://blog.itpub.net/29254281/viewspace-2150297/

Session重叠问题学习(五)--最优化，三次优化后,执行时间1.6秒
http://blog.itpub.net/29254281/viewspace-2150339/

Session重叠问题学习(六)--极致优化，四次优化后,执行时间1250-1300毫秒
http://blog.itpub.net/29254281/viewspace-2150364/

Session重叠问题学习(七)--小花狸合并算法和最后一次优化.第五次优化,980毫秒
http://blog.itpub.net/29254281/viewspace-2150403/

Session重叠问题学习(八)--该问题第六次优化和Oracle版本.第六次优化,880毫秒
http://blog.itpub.net/29254281/viewspace-2150464/

虽然经过反复优化,还是需要880毫秒.
而Oracle仅仅需要200毫秒左右.
这主要是因为MySQL没有提供开窗函数.在合并同一房间同一用户的重合时间段时,需要大量扫描和计算.

这块可以修改成游标方式.减少扫描和计算.

本次优化之后,MySQL版本耗时420毫秒.

CREATE DEFINER=`root`@`localhost` PROCEDURE `p`()
BEGIN
declare done int default 0;
declare v_roomid bigint;
declare v_userid bigint;
declare v_start timestamp;
declare v_end timestamp;
declare v_prev_roomid bigint default -1;
declare v_prev_userid bigint default -1;
declare v_max_end timestamp;
declare cur_test CURSOR for select roomid,userid,roomstart,roomend from u_room_log order by roomid,userid,roomstart,roomend ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
drop table if exists t;
drop table if exists t1;
drop table if exists tmp_time_point;
drop table if exists tmp_s;
create temporary table t(
roomid bigint,
userid bigint,
s timestamp,
e timestamp,
broken int
) engine=memory;
CREATE temporary TABLE `t1` (
`roomid` int(11) NOT NULL DEFAULT '0',
`userid` bigint(20) NOT NULL DEFAULT '0',
`s` timestamp,
`e` timestamp,
primary key(roomid,userid,s,e)
) ENGINE=memory;
create temporary table tmp_time_point(
roomid bigint,
timepoint timestamp,
type smallint,
key(roomid,timepoint)
) engine=memory;
create temporary table tmp_s(
roomid bigint,
userid bigint,
s timestamp,
e timestamp,
i int
) engine=memory;
open cur_test;
repeat
fetch cur_test into v_roomid,v_userid,v_start,v_end;
if done !=1 then
if(v_roomid=v_prev_roomid and v_userid=v_prev_userid) then
if(v_start<=v_max_end) then
insert into t values(v_roomid,v_userid,v_start,v_end,0);
else
insert into t values(v_roomid,v_userid,v_start,v_end,1);
end if;
if(v_end>=v_max_end) then
set v_max_end:=v_end;
end if;
set v_prev_roomid:=v_roomid;
set v_userid:=v_userid;
else
set v_max_end:=v_end;
set v_prev_roomid:=v_roomid;
set v_prev_userid:=v_userid;
insert into t values(v_roomid,v_userid,v_start,v_end,1);
end if;
end if;
until done end repeat;
close cur_test;
insert into tmp_s
select roomid,userid,min(s) s,max(e) e,datediff(max(e),min(s))+1 i from (
select roomid,userid,s,e,case when @flag=flag then @rn:=@rn+broken when @flag:=flag then @rn:=broken end ran from (
select roomid,userid,s,e,broken,concat(roomid,',',userid) flag from t,(select @flag:='',@rn:=0) vars
) a order by roomid,userid,s,e
) b
group by roomid,userid,ran;
select max(i) into @c from tmp_s;
insert ignore into t1(roomid,userid,s,e)
select
roomid, userid,
if(date(s)!=date(e) and id>1,date(s+interval id-1 day),s) s,
if(date(s+interval id-1 day)=date(e) ,e,date_format(s+interval id-1 day,'%Y-%m-%d 23:59:59')) e
from tmp_s t1 STRAIGHT_JOIN
nums on(nums.id<=t1.i)
where nums.id<=@c
;
-- 开始点+1,结束点-1
insert into tmp_time_point(roomid,timepoint,type) select roomid,s,1 from t1;
insert into tmp_time_point(roomid,timepoint,type) select roomid,e,-1 from t1;
select roomid,date(s) dt,round(sum(timestampdiff(second,date_format(s,'%Y-%m-%d %H:%i:%s'),date_format(e,'%Y-%m-%d %H:%i:%s')))/60) ts,max(rn) c from (
select
if(@roomid=roomid,@d,'') as s,@d:=str_to_date(timepoint,'%Y-%m-%d %H:%i:%s.%f'),@roomid:=roomid,p.roomid,str_to_date(timepoint,'%Y-%m-%d %H:%i:%s.%f') e ,rn
from
(
select round(case when @roomid=roomid then @rn:=@rn+prevType when @roomid:=roomid then @rn:=prevType end) rn,b.prevType,roomid,timepoint,type from (
select if(@roomid=roomid,@type,0) prevType ,case when @roomid=roomid then @type:=type when @roomid:=roomid then @type:=1 end,a.roomid,timepoint,type from (
select * from ( select roomid,timepoint,sum(type) type from tmp_time_point group by roomid,timepoint) tmp_time_point,(select @roomid:=-1,@rn:=0,@type:=0) vars order by roomid ,timepoint
) a
) b order by roomid ,timepoint
)p,(select @d:='',@roomid:=-1) vars
order by roomid,timepoint
) v4 where s!='' and date(s)=date(e) and rn>=2
group by roomid,date(s);
END