Shulou(Shulou.com)06/01 Report--

Follow the previous article

Session overlapping problem learning (2), which is a description of problems and requirements, execution time is 90 seconds

Http://blog.itpub.net/29254281/viewspace-2150229/

Session overlap problem Learning (3)-- Optimization. After one optimization, the execution time is 25 seconds.

Http://blog.itpub.net/29254281/viewspace-2150259/

Session overlap problem learning (4)-- re-optimization, after the second optimization, the execution time is 10 seconds

Http://blog.itpub.net/29254281/viewspace-2150297/

Session overlap problem Learning (5)-- Optimization, after three optimizations, the execution time is 1.6s

Http://blog.itpub.net/29254281/viewspace-2150339/

Session overlap problem learning (6)-- extreme optimization, execution time 1250-1300 milliseconds after four optimization

Http://blog.itpub.net/29254281/viewspace-2150364/

This is the algorithm summary and the last optimization for this problem.

After this optimization, on my computer (SSD hard disk, mechanical hard disk is still not so fast), the running time is about 980ms. Seconds out in the real sense. And I really think it's excellent.

The reason why it can be optimized from the 10-second version to the 1.6s version of 1.3s. Because of the use of the small beaver Session merging algorithm.

If there is no repetition of the first and last time periods between users, the following rules are satisfied

But this rule only exists in the case that the user's first and last time periods do not coincide.

For example, user A's launch time is from 10:00 to 11:00, while user B's is from 11:00 to 12:00.

Because at exactly 11:00, users An and B coincide, the algorithm doesn't work.

So at that time, it was a trick to add or subtract a very small amount of time if it coincided.

S+interval startnum/1000000 second s

E-interval endnum/1000000 second e

Insert into T2 (roomid,s,e) selectroomid,s + interval startnum/1000000 second SMagne etalk interval endnum/1000000 second efrom (selectroomid,s,e,startnum,when @ eflag=eflag then @ rn:=@rn+1 when @ eflag:=eflag then @ rn else @ rn end endnumfrom (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) sflag (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

But this introduces a problem, that is, there is an error. The error can only be reduced but not eliminated.

This is why version 1.3s and 1.6s use timestamp (6) to reduce the error.

But after thinking over and over again, the law found before is actually a special case.

The general situation of the merger of Little Beaver Session is actually shown in the following figure

Label each dot first.

Each point, if it is a start point, is + 1, and the end point is-1.

Take the second point on the left in the figure as an example, which is the coincidence of four points, including one end point (- 1) and three start points (+ 3), so the dot label is 2.

Then calculate from left to right

The leftmost point is 0 plus the dot label as the maximum number of people online on the right side.

The maximum number of people online at other points is the maximum number of people online at the left side, plus the label.

Finally, you can query the time period when the maximum number of online users is greater than or equal to 2.

The process of improvement is as follows:

DELIMITER $$CREATE DEFINER= `root` @ `localhost`localhost` PROCEDURE `p` () BEGINdrop table if exists T1 drop table if exists t2ash 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 '0mm, `userid` bigint (20) NOT NULL DEFAULT' 0mm, `s` timestamp, `e`localhost` (roomid,userid,s,e) ENGINE=memory;CREATE temporary TABLE `t2` (`roomid`int (11) NOT NULL DEFAULT '0mm, `timepoint` timestamp,c int,key (roomid,timepoint)) ENGINE=memory CREATE temporary TABLE `tmp_min_ Range` (`roomid` int (11) NOT NULL DEFAULT '0questions, `s` timestamp, `e` timestamp,primary key (roomid,s,e), key (roomid,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;SET @ AbiliseSet @ Bath0 Insert into tmp_sSELECT x.SELECT DISTINCT roomid,userid, roomstart AS sFROM u_room_log aWHERE NOT EXISTS (SELECT * FROM u_room_log bWHERE a.roomid = b.roomidAND a.userid = b.useridAND a.roomstart > b.roomstartAND a.roomstart = b.roomstartAND 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)selectroomid, userid,if(date(s)!=date(e) and id>

1Magnum date (s+interval id-1 date (s+interval id-1 date (e), e Magazine datebook format (s+interval id-1'% Y-%m-%d 23 Y-%m-%d 59V 59)) efrom tmp_s T1 STRAIGHT_JOINnums on (nums.id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.