Session重疊問題學習(八)--該問題第六次優化和Oracle版本

壹頁書發表於2018-01-23
Session優化Oracle
已經說好不玩了.還是不能罷手.

之前極致優化的方法,是先計算最小區間,再計算最小區間上的使用者數量.

其實可以一邊計算最小區間,同時計算最小區間上的使用者數量.

  1. DELIMITER $$  
  2.   
  3. CREATE DEFINER=`root`@`localhost` PROCEDURE `p`()  
  4. BEGIN          
  5.     drop table if exists t1;          
  6.     drop table if exists tmp_time_point;          
  7.     drop table if exists tmp_s;      
  8.     CREATE temporary TABLE `t1` (          
  9.       `roomid` int(11) NOT NULL DEFAULT '0',          
  10.       `userid` bigint(20) NOT NULL DEFAULT '0',          
  11.       `s` timestamp,          
  12.       `e` timestamp,      
  13.        primary key(roomid,userid,s,e)      
  14.     ) ENGINE=memory;          
  15.         
  16.   
  17.     create temporary table tmp_time_point(          
  18.             roomid bigint,          
  19.             timepoint timestamp,          
  20.             type smallint,        
  21.             key(roomid,timepoint)          
  22.     ) engine=memory;          
  23.           
  24.     create temporary table tmp_s(      
  25.         roomid bigint,      
  26.         userid bigint,      
  27.         s timestamp,      
  28.         e timestamp,      
  29.         i int      
  30.     ) engine=memory;      
  31.           
  32. SET @A=0;          
  33. SET @B=0;          
  34.       
  35. insert into tmp_s      
  36.     SELECT x.roomid,x.userid,s,e,datediff(e,s)+1 i       
  37.     FROM         
  38.     (        
  39.         (        
  40.             SELECT @B:=@B+1 AS id,roomid,userid,s          
  41.             FROM (          
  42.                 SELECT DISTINCT roomid, userid, roomstart AS s              
  43.                 FROM u_room_log a              
  44.                 WHERE NOT EXISTS (SELECT *              
  45.                     FROM u_room_log b              
  46.                     WHERE a.roomid = b.roomid              
  47.                         AND a.userid = b.userid              
  48.                         AND a.roomstart > b.roomstart              
  49.                         AND a.roomstart <= b.roomend)        
  50.             ) AS p        
  51.         ) AS x,          
  52.         (        
  53.             SELECT @A:=@A+1 AS id,roomid,userid,e          
  54.             FROM         
  55.             (          
  56.                 SELECT DISTINCT roomid, userid, roomend AS e              
  57.                 FROM u_room_log a              
  58.                 WHERE NOT EXISTS (SELECT *              
  59.                     FROM u_room_log b              
  60.                     WHERE a.roomid = b.roomid              
  61.                         AND a.userid = b.userid              
  62.                         AND a.roomend >= b.roomstart              
  63.                         AND a.roomend < b.roomend)          
  64.             ) AS o        
  65.         ) AS y          
  66.     )         
  67.     WHERE x.id = y.id AND x.roomid = y.roomid AND x.userid = y.userid   ;         
  68.       
  69. select max(i) into @c from tmp_s;      
  70.           
  71. insert ignore into t1(roomid,userid,s,e)        
  72. select             
  73. roomid,  userid,            
  74. if(date(s)!=date(e) and id>1,date(s+interval id-1 day),s) s,            
  75. if(date(s+interval id-1 day)=date(e) ,e,date_format(s+interval id-1 day,'%Y-%m-%d 23:59:59')) e            
  76. from tmp_s t1 STRAIGHT_JOIN          
  77. nums on(nums.id<=t1.i)      
  78. where nums.id<=@c      
  79.          
  80. ;            
  81.         
  82.     -- 開始點+1,結束點-1    
  83.     insert into tmp_time_point(roomid,timepoint,type) select roomid,s,1 from t1;        
  84.     insert into tmp_time_point(roomid,timepoint,type) select roomid,e,-1 from t1;     
  85.     
  86.         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 (          
  87.                 select           
  88.                 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        
  89.                 from   
  90.                 (  
  91.                     select round(case when @roomid=roomid then @rn:=@rn+prevType when @roomid:=roomid then @rn:=prevType end) rn,b.prevType,roomid,timepoint,type  from (  
  92.                         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 (  
  93.                             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  
  94.                         ) a  
  95.                     ) b order by roomid ,timepoint   
  96.                 )p,(select @d:='',@roomid:=-1) vars          
  97.                 order by roomid,timepoint          
  98.         ) v4 where s!='' and date(s)=date(e) and rn>=2      
  99.         group by roomid,date(s);         
  100.         
  101. END  


到了這個MySQL的版本,確實已經非常的晦澀難懂了.

由於MySQL沒有提供開窗函式,這種複雜計算非常的吃虧.

這個最後的版本,執行時間在 889毫秒到921毫秒之間. 
MySQL 版本 最後還能優化小100毫秒,非常的不容易.

這個版本過於晦澀,我用同樣的思路,寫了一個Oracle版本的.
效能好,可讀性還強.

  1. with c1 as  -- 合併同一房間同一使用者的重疊時間段，用於統計峰值人數  
  2. (  
  3.    select distinct roomid,userid,min(s) s,max(e) e   
  4.      from (select roomid,userid,s,e,  
  5.                   sum(broken) over (partition by roomid, userid order by s,e) flag  
  6.              from (select t.*,  
  7.                           (case when s <= max(e) over (partition by roomid, userid order by s,e rows between unbounded preceding and 1 preceding) then 0  
  8.                            else 1  
  9.                             end) as broken  
  10.                      from (select roomid,userid,roomstart s,roomend e from u_room_log ) t  
  11.                    ) t  
  12.            ) t    
  13.     group by roomid,userid,flag  
  14. ),  
  15. c2 as  -- 拆分跨天的時間段  
  16. (  
  17.    select *   
  18.      from (select roomid,userid,s,e   
  19.              from c1  
  20.             where trunc(s) = trunc(e)  -- 不跨天  
  21.             union all  
  22.            select roomid,userid,  
  23.                   case when id = 1 then s else trunc(s)+id-1 end s,  
  24.                   case when id = m2 then e else (trunc(s)+id) -(1/24/60/60)  end e       
  25.              from (select roomid,userid,s,e,id,  
  26.                           max(id) over (partition by roomid,userid,s) m2  
  27.                      from c1,nums  
  28.                     where trunc(s) <> trunc(e) -- 跨天  
  29.                       and id <= trunc(e)-trunc(s)+1) t1) t1  
  30. ),  
  31. c3 as -- 在計算最小範圍的同時,計算區間使用者數  
  32. (  
  33.   
  34.       select roomid,ts endtime,sum(prevType) over(partition by roomid order by ts) rn,   
  35.       lag(ts) over (partition by roomid order by ts) starttime  
  36.       from (  
  37.         select a.*,nvl(lag(type) over (partition by roomid order by ts),0) prevType  
  38.         from (  
  39.           select   
  40.           roomid,ts,sum(type) type  
  41.           from (  
  42.               select roomid,e ts, -1 type  
  43.               from c2  
  44.               union all  
  45.               select roomid,s ts, 1 type  
  46.               from c2  
  47.           ) group by roomid,ts  
  48.         ) a  
  49.       ) c  
  50. )  
  51. select roomid,to_char(dt,'yyyy-mm-dd HH24:mi:ss') dt,round(sum(dur)/60) ts,max(rn) c from (  
  52.   select roomid,trunc(starttime) dt,(endtime-starttime)*3600*24 dur,rn   
  53.   from c3 where rn>=2 and trunc(endtime)=trunc(starttime) and starttime is not null   
  54.   order by roomid,endtime  
  55. )   
  56. group by roomid,dt  
  57. order by roomid,dt; 

不像MySQL,需要考慮所有的細節.這個同樣演算法的Oracle版本,隨隨便便就能跑到213毫秒.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-2150464/，如需轉載，請註明出處，否則將追究法律責任。

相關文章