Session重叠难题学习

发布时间：2022-03-26 02:45:12 所属栏目：MySql教程来源：互联网

导读：这是对这个问题的算法总结和最后一次优化. 经过这次优化,在我的电脑上(SSD硬盘,机械硬盘还是没有这么快),运行时间是980毫秒左右.真正意义上的秒出.并且我确实觉得是优无可优了。之所以能从10秒的版本,跳跃优化到1.6s,1.3s的版本.是因为采用了小花狸Session

这是对这个问题的算法总结和最后一次优化.
经过这次优化,在我的电脑上(SSD硬盘,机械硬盘还是没有这么快),运行时间是980毫秒左右.真正意义上的秒出.并且我确实觉得是优无可优了。

之所以能从10秒的版本,跳跃优化到1.6s,1.3s的版本.是因为采用了小花狸Session合并算法。

但是该规律仅仅存在于用户首尾时间段不重合的情况.
比如A用户上线时间是 10点至11点整,而用户B上线时间是11点整到12点.
因为11点整这个时刻,用户A和B重合了,所以这个算法就不能生效了.
Session重叠难题学习

所以当时取巧,如果重合了,就增加或者减去一个很小的时间
s+interval startnum/1000000 second s,
e-interval endnum/1000000 second e

insert into t2 (roomid,s,e)
select roomid,
s+interval startnum/1000000 second s,
e-interval endnum/1000000 second e
from (
    select
    roomid,
    s,e,
    startnum,
    when @eflag=eflag then @rn:=@rn+1 when @eflag:=eflag then @rn else @rn end endnum
    from (
        select * from (
            select when @sflag=sflag then @rn:=@rn+1 when @sflag:=sflag then @rn else @rn end startnum,roomid,s,e,sflag,eflag from
            (
                select * from
                (
                    select t1.*,concat('[',roomid,'],',s) sflag,concat('[',roomid,'],',e) eflag from t1 order by roomid ,sflag
                )a,(select @sflag:='',@rn:=0,@eflag:='') vars
            ) b
        ) bb order by roomid,eflag
    ) c
) d ;

但是这样引入一个问题,就是有误差.误差只能缩小却不能消除.
这也是为什么1.3s和1.6s版本使用timestamp(6)的原因,就是为了缩小误差.

改进的过程如下:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `p`()
BEGIN
    drop table if exists t1;
    drop table if exists t2;
    drop table if exists tmp_time_point;
    drop table if exists tmp_min_range;
    drop table if exists tmp_s;
    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 `t2` (
      `roomid` int(11) NOT NULL DEFAULT '0',
      `timepoint` timestamp,
        c int,
        key(roomid,timepoint)
    ) ENGINE=memory;

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 date(s+interval id-1 date(e) ,e,date_format(s+interval id-1 '%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;

    -- 计算每个点的标号
    insert into t2(roomid,timepoint,c)
    select roomid,timepoint,from tmp_time_point group by roomid,timepoint;

    -- 计算最小范围
    insert ignore into tmp_min_range(roomid,s,e)
                select   roomid,starttime starttime, endtime endtime from (
                    select
                    if(@roomid=roomid,@d,'') as starttime,@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') endtime
                    from tmp_time_point p,(select @d:='',@roomid:=-1) vars
                    order by roomid,timepoint
                ) v4 where starttime!='' and date(starttime)=date(endtime);


    select roomid,date(s) dt,round(second,date_format(s,'%Y-%m-%d %H:%i:%s'),date_format(e,'%Y-%m-%d %H:%i:%s')))/60) ts,max(num) c from
    (
        select a.roomid,num,a.s,a.e from (
            select when @roomid=roomid and date(@timepoint)=date(timepoint) then @num:=@num+prevC when @roomid:=roomid then @num:=0 end num,@timepoint:=timepoint ,a.* from (
                select when @roomid=roomid then @prevC when @roomid:=roomid then @prevC:=0 end prevC,@prevC:=c,b.* from (
                    select * from t2 ,(select @roomid:=-1,@timepoint:='',@num:=0,@prevC:=-1) vars
                ) b order by roomid,timepoint
            ) a order by roomid,timepoint
        ) c
        inner join
                tmp_min_range a on( c.timepoint=a.e and c.roomid=a.roomid)
        where num>=2
    ) d group by roomid,date(s);   &nb

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

MySQL源码安置	MySQL自增列主从不一致
数据库设计的主要过程	mysql company