<em id="hanht"></em>

    <dd id="hanht"></dd>

    <em id="hanht"><acronym id="hanht"></acronym></em>
    
    <button id="hanht"></button>
    <rp id="hanht"><object id="hanht"><blockquote id="hanht"></blockquote></object></rp><em id="hanht"></em>

    首頁 > 學院 > 綜合知識 > 正文

    Session重疊問題教學

    2022-07-30 23:12:07
    字體:
    來源:轉載
    供稿:網友
            周五晚上終于把這個算法初步實現了.
           連續加班忙碌了一個星期,終于有點曙光了.
           從這個問題的緣起,到目前應該已經優化了快100倍了
           但是周末的時候,想想還是不對.
           小花貍Session合并算法(對,以后這個算法就叫這個名稱了)實現的合并速度應該是非??斓?代價僅僅是掃描一遍記錄.
           這1.6秒到底用在哪里了?
     
    后來經過反復調試.發現還有兩塊可以優化改進的地方.
    改進后的過程如下:
     
    drop procedure p;  
    DELIMITER $$    
        
    CREATE DEFINER=`root`@`localhost` PROCEDURE `p`()    
    BEGIN      
        declare done int default 0;          
        declare v_roomid bigint;      
        declare v_time timestamp(6);      
        declare v_cur_type smallint;    
        
        declare v_before_roomid bigint default -1;    
        declare v_before_type smallint default -1;    
        declare v_before_time timestamp(6) ;    
        
        declare v_num bigint default 0;    
        
        
        declare cur_test CURSOR for select roomid,type,timepoint from tmp_time_point order by roomid,timepoint,type ;    
        DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET done = 1;          
        
              
        drop table if exists t1;      
        drop table if exists t2;    
        drop table if exists tmp_time_point;      
        drop table if exists tmp_result;    
        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(6),      
          `e` timestamp(6),  
           primary key(roomid,userid,s,e)  
        ) ENGINE=memory;      
        
       CREATE temporary TABLE `t2` (      
          `roomid` int(11) NOT NULL DEFAULT '0',      
          `s` timestamp(6),      
          `e` timestamp(6)  
        ) ENGINE=memory;      
        
        CREATE temporary TABLE `tmp_min_range` (      
          `roomid` int(11) NOT NULL DEFAULT '0',      
          `s` timestamp(6),      
          `e` timestamp(6),      
          primary key(roomid,s,e),  
          key(roomid,e)  
        ) ENGINE=memory;      
        
        create temporary table tmp_time_point(      
                roomid bigint,      
                timepoint timestamp(6),      
                type smallint,    
                key(roomid,timepoint)      
        ) engine=memory;      
            
        create temporary table tmp_result(      
                roomid bigint,      
                timepoint timestamp(6),    
                c int    
        ) engine=memory;      
          
        create temporary table tmp_s(  
            roomid bigint,  
            userid bigint,  
            s timestamp,  
            e timestamp,  
            i int  
        ) engine=memory;  
          
    SET @A=0;      
    SET @B=0;      
      
    insert into tmp_s  
        SELECT x.roomid,x.userid,s,e,datediff(e,s)+1 i   
        FROM     
        (    
            (    
                SELECT @B:=@B+1 AS id,roomid,userid,s      
                FROM (      
                    SELECT DISTINCT roomid, userid, roomstart AS s          
                    FROM u_room_log a          
                    WHERE NOT EXISTS (SELECT *          
                        FROM u_room_log b          
                        WHERE a.roomid = b.roomid          
                            AND a.userid = b.userid          
                            AND a.roomstart > b.roomstart          
                            AND a.roomstart <= b.roomend)    
                ) AS p    
            ) AS x,      
            (    
                SELECT @A:=@A+1 AS id,roomid,userid,e      
                FROM     
                (      
                    SELECT DISTINCT roomid, userid, roomend AS e          
                    FROM u_room_log a          
                    WHERE NOT EXISTS (SELECT *          
                        FROM u_room_log b          
                        WHERE a.roomid = b.roomid          
                            AND a.userid = b.userid          
                            AND a.roomend >= b.roomstart          
                            AND a.roomend < b.roomend)      
                ) AS o    
            ) AS y      
        )     
        WHERE x.id = y.id AND x.roomid = y.roomid AND x.userid = y.userid   ;     
      
    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  
         
    ;        
        
    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 ;    
         
        insert into tmp_time_point(roomid,timepoint,type) select roomid,s,1 from t2;    
        insert into tmp_time_point(roomid,timepoint,type) select roomid,e,0 from t2;    
           
        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);    
        
        open cur_test;          
        repeat          
            fetch cur_test into v_roomid,v_cur_type,v_time;          
            if done !=1 then        
                -- 第一行或者每個房間的第一行    
                if v_before_roomid=-1 or v_roomid!=v_before_roomid  then    
                    set v_before_roomid:=v_roomid;    
                    set v_before_type:=1;    
                    set v_before_time:='0000-00-00 00:00:00';    
                    set v_num:=0;    
                end if;    
                    
                    
                if v_before_type=1  then    
                 
                    set v_num:=v_num+1;    
              
                    insert into tmp_result(roomid,timepoint,c) values(v_roomid,v_time,v_num);    
                end if;    
                    
                if v_before_type=0 then    
                       
                    set v_num:=v_num-1;    
        
                    insert into tmp_result(roomid,timepoint,c) values(v_roomid,v_time,v_num);    
                end if;    
        
                set v_before_roomid:=v_roomid;    
                set v_before_type:=v_cur_type;    
                set v_before_time:=v_time;    
            end if;        
        until done end repeat;          
        close cur_test;       
          
        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(c)-1 c from (         
            select a.roomid,a.s,a.e,r.c,r.timepoint from tmp_result r     
            inner join     
            tmp_min_range a on( r.timepoint=a.e and r.roomid=a.roomid)    
            where     c>2    
        ) a group by roomid,date(s);      
        
    END    
     
    第一處改進
        原來同一房間同一用戶重疊時間合并,然后再拆分跨天數據,用的是一條SQL
        現在改進如下
    create temporary table tmp_s(  
            roomid bigint,  
            userid bigint,  
            s timestamp,  
            e timestamp,  
            i int  
        ) engine=memory;  
          
    SET @A=0;      
    SET @B=0;      
      
    insert into tmp_s  
        SELECT x.roomid,x.userid,s,e,datediff(e,s)+1 i   
        FROM     
        (    
            (    
                SELECT @B:=@B+1 AS id,roomid,userid,s      
                FROM (      
                    SELECT DISTINCT roomid, userid, roomstart AS s          
                    FROM u_room_log a          
                    WHERE NOT EXISTS (SELECT *          
                        FROM u_room_log b          
                        WHERE a.roomid = b.roomid          
                            AND a.userid = b.userid          
                            AND a.roomstart > b.roomstart          
                            AND a.roomstart <= b.roomend)    
                ) AS p    
            ) AS x,      
            (    
                SELECT @A:=@A+1 AS id,roomid,userid,e      
                FROM     
                (      
                    SELECT DISTINCT roomid, userid, roomend AS e          
                    FROM u_room_log a          
                    WHERE NOT EXISTS (SELECT *          
                        FROM u_room_log b          
                        WHERE a.roomid = b.roomid          
                            AND a.userid = b.userid          
                            AND a.roomend >= b.roomstart          
                            AND a.roomend < b.roomend)      
                ) AS o    
            ) AS y      
        )     
        WHERE x.id = y.id AND x.roomid = y.roomid AND x.userid = y.userid   ;     
      
    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  
         
    ;        
     
    先把同一房間同一用戶的重疊部分合并,然后暫存臨時表
    記錄最大的間隔時間,然后再拆分數據
     
    拆分數據的時候 使用STRAIGHT_JOIN 強制連接順序.
    這樣避免因為數字輔助表過大,而導致性能陡然變差.
     
     
    第二處改進
        原來使用distinct的查詢, 都改為在臨時表上增加主鍵.
        然后使用insert ignore into 代替 insert into
        這樣大概優化了300毫秒
     
    經過反復優化之后,執行時間大致穩定在1250毫秒 至 1300 毫秒
     
    各個部分耗時分析如下
    填充tmp_s,合并同一房間同一用戶的重疊部分,耗時655毫秒
    填充t1,拆分跨天的用戶數據,耗時62毫秒
    填充t2,用戶時間段首尾相交或者首尾全部重合的數據拆分,耗時140毫秒
    填充tmp_min_range,計算最小間隔范圍,耗時156毫秒
    小花貍Session合并算法,耗時219毫秒
    結果統計展示,耗時47毫秒

    (編輯:錯新網)

    發表評論 共有條評論
    用戶名: 密碼:
    驗證碼: 匿名發表
    一级特黄大片欧美久久久久_一本一道久久综合狠狠老_JLZZ日本人年轻护士_欧美男男作爱VIDEOS可播放
      <em id="hanht"></em>

      <dd id="hanht"></dd>

      <em id="hanht"><acronym id="hanht"></acronym></em>
      
      <button id="hanht"></button>
      <rp id="hanht"><object id="hanht"><blockquote id="hanht"></blockquote></object></rp><em id="hanht"></em>