ITPub博客

Session重叠问题学习(二)

原创 作者:壹頁書 时间:2018-01-17 13:05:05 0 删除 编辑
u_room_log.zip

附件是实验的初始化表.另外需要先创建数字辅助表

  1. create table nums(id int not null primary key);  
  2.   
  3. delimiter $$  
  4. create procedure pCreateNums(cnt int)  
  5. begin  
  6.     declare s int default 1;  
  7.     truncate table nums;  
  8.     while s<=cnt do  
  9.         insert into nums select s;  
  10.         set s=s+1;  
  11.     end while;  
  12. end $$  
  13. delimiter ;  
  14.   
  15. call pCreateNums(1000);  
  16. commit

数字辅助表基本上是复杂SQL的标配.

以roomid为1 的数据了解需求


求每天每个房间同时两个用户在线的时长和最高在线人数
需要解决的几个问题
1.用户数据跨天。
比如 logid=634715的记录,该用户登录房间是
2018-01-15 15:29:55 至 2018-01-16 15:30:00
那么,这条数据需要拆分为两个记录
2018-01-15 15:29:55 至 2018-01-15 23:59:59

2018-01-16 00:00:00 至
2018-01-16 15:30:00

2.同一天同一房间同一用户的时间重叠

比如某个用户有两个记录
一个是 3点-5点登录;另外一个记录是4点-7点登录.
这两个记录需要合并为一个记录 就是该用户 3点-7点在线.

3.每天每个房间同时有两个用户在线,才算房间的活跃时间.
(后续可能扩展,比如四个在线用户才算活跃时间,这就是产品随口一说的事儿。不能因为她拍脑袋就得改程序改死自己。)

最后统计每天每个房间的活跃时间和最大在线人数.

我处理这个问题分了两个步骤.

第一个步骤,先合并同一房间同一用户登录时间的重叠部分.然后拆分跨天的记录.最后输出到一个结果表中.(表名 t1)

第二个步骤,通过一个自定义函数,处理第一步的结果表(t1).
首先,把某天某个房间,任意两个用户时间范围存在交叉的记录找出来.
(因为需求说明,两个用户同时在线才算活跃时间,所以时间范围有交叉是必要条件.如果一个用户在线就算房间的在线时间的话,就更容易一点,按照步骤一的算法,再次合并相同房间的时间范围即是)
然后把这些记录的时间点生成一个排序的列(固定行数行转列),

生成每个时间点到下个时间点的范围


这个范围是算法的最核心部分,他是某天某个房间所有记录的最小范围间隔.
也就是说,在某天某个房间,任何用户的时间范围不会和最小范围有重叠的部分.

最后用最小范围关联 t1的结果表,如果用户的在线时间和最小范围重合,就将重叠的最小范围和用户ID,房间ID写入最终结果表t2.

t2这个结果表,最终包含了某个房间某个用户一个或者多个的最小范围.

最终的查询就是聚合房间和最小范围,找到超过两个用户的记录,然后对用户求max得到最大在线人数.对最小范围的间隔时间求和得到房间的活跃时间.

步骤一:
  1. drop table t1;  
  2. create table t1  
  3. select distinct  
  4. roomid,  
  5. userid,  
  6. if(date(s)!=date(e) and id>1,date(s+interval id-1 day),s) s,  
  7. if(date(s+interval id-1 day)=date(e) ,e,date_format(s+interval id-1 day,'%Y-%m-%d 23:59:59')) e  
  8. from (  
  9. SELECT DISTINCT s.roomid, s.userid, s.s, (    
  10.         SELECT MIN(e)    
  11.         FROM (SELECT DISTINCT roomid, userid, roomend AS e    
  12.             FROM u_room_log a    
  13.             WHERE NOT EXISTS (SELECT *    
  14.                 FROM u_room_log b    
  15.                 WHERE a.roomid = b.roomid    
  16.                     AND a.userid = b.userid    
  17.                     AND a.roomend >= b.roomstart    
  18.                     AND a.roomend < b.roomend)    
  19.             ) s2    
  20.         WHERE s2.e > s.s    
  21.             AND s.roomid = s2.roomid    
  22.             AND s.userid = s2.userid    
  23.         ) AS e    
  24. FROM (SELECT DISTINCT roomid, userid, roomstart AS s    
  25.     FROM u_room_log a    
  26.     WHERE NOT EXISTS (SELECT *    
  27.         FROM u_room_log b    
  28.         WHERE a.roomid = b.roomid    
  29.             AND a.userid = b.userid    
  30.             AND a.roomstart > b.roomstart    
  31.             AND a.roomstart <= b.roomend)    
  32.     ) s, (SELECT DISTINCT roomid, userid, roomend AS e    
  33.     FROM u_room_log a    
  34.     WHERE NOT EXISTS (SELECT *    
  35.         FROM u_room_log b    
  36.         WHERE a.roomid = b.roomid    
  37.             AND a.userid = b.userid    
  38.             AND a.roomend >= b.roomstart    
  39.             AND a.roomend < b.roomend)    
  40.     ) e    
  41. WHERE s.roomid = e.roomid    
  42.     AND s.userid = e.userid   
  43.   
  44. ) t1 ,  
  45. nums   
  46. where  nums.id<=datediff(e,s)+1  
  47.   
  48. ;  
  49.   
  50. alter table t1 add key(roomid,s,e),add   primary key(roomid,userid,s,e);   

步骤二:
  1. DELIMITER $$  
  2.   
  3. CREATE DEFINER=`root`@`localhost` FUNCTION `f`(pRoomId bigint,pTime timestampRETURNS int(11)  
  4. BEGIN  
  5. declare pResult bigint;  
  6. insert into t2  
  7. select v6.roomid,v6.userid,greatest(s,starttime) s,least(e,endtime) e  
  8. from (  
  9.     select roomid,starttime,endtime from (  
  10.          select @d as starttime,@d:=d,v3.roomid,v3.d endtime from (  
  11.                     select distinct roomid,   
  12.                     case      
  13.                     when nums.id=1 then v1s     
  14.                     when nums.id=2 then v1e     
  15.                     when nums.id=3 then v2s     
  16.                     when nums.id=4 then v2e     
  17.                     end d   from (  
  18.                         select   v1.roomid, v1.s v1s,v1.e v1e,v2.s v2s,v2.e v2e  
  19.                         from t1 v1  
  20.                         inner join t1 v2 on ((v1.s between v2.s and v2.e or v1.e between v2.s and v2.e )  and v1.roomid=v2.roomid)   
  21.                         where v2.roomid=pRoomId and v2.s>=pTime and v2.s<(pTime+interval '1' dayand (v2.roomid,v2.userid,v2.s,v2.e)!= (v1.roomid,v1.userid,v1.s,v1.e)   
  22.                     ) a,nums where nums.id<=4  
  23.                     order by roomid,d  
  24.         ) v3,(select @d:='') vars  
  25.     ) v4 where starttime!=''    
  26. ) v5 inner join t1 v6 on(v5.starttime between v6.s and v6.e and v5.endtime between v6.s and v6.e    and v5.roomid=v6.roomid)  
  27. ;  
  28.   
  29. select row_count() into pResult;  
  30. RETURN   pResult;  
  31. END $$  

执行函数
select f(roomid,s) from (   
    select  distinct roomid,date(s) s from t1     
) a;

最终查询得到结果
  1. select roomid,date(s) dt,round(sum(timestampdiff(second,s,e))/60) ts,max(c) c from (     
  2.     select roomid,s,e ,count(distinct userid) c from t2 where roomid=660026  group by roomid,s,e having count(distinct userid)>1     
  3. ) a group by roomid,date(s);  



上一篇: 冒泡排序
请登录后发表评论 登录
全部评论

注册时间:2013-10-19

  • 博文量
    616
  • 访问量
    5801033