ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > 统计每天的三天内两次登录问题

统计每天的三天内两次登录问题

原创 MySQL 作者：壹頁書 时间：2015-12-10 17:07:09 1 删除 编辑
实验结构和数据:
  1. CREATE TABLE `loginlog` (  
  2.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',  
  3.   `pid` int(11) NOT NULL COMMENT '玩家ID',  
  4.   `logintime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '登录时间',  
  5.   PRIMARY KEY (`id`),  
  6.   KEY `pid` (`pid`,`logintime`),  
  7.   KEY `logintime` (`logintime`)  
  8. ) ENGINE=Innodb  COMMENT='登录日志表';  
  9.   
  10.   
  11. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (1,1,'2015-12-09 16:40:50');  
  12. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (2,2,'2015-12-08 16:40:50');  
  13. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (3,3,'2015-12-07 16:40:50');  
  14. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (4,4,'2015-12-06 16:40:50');  
  15. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (5,5,'2015-12-05 16:40:50');  
  16. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (6,6,'2015-12-04 16:40:50');  
  17. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (7,7,'2015-12-03 16:40:50');  
  18. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (8,8,'2015-12-02 16:40:50');  
  19. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (9,9,'2015-12-01 16:40:50');  
  20. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (10,1,'2015-12-09 16:41:55');  
  21. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (11,2,'2015-12-08 16:41:55');  
  22. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (12,3,'2015-12-07 16:41:55');  
  23. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (13,4,'2015-12-07 16:41:55');  
  24. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (14,5,'2015-12-09 16:41:55');  
  25. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (15,6,'2015-12-09 16:41:55');  
  26. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (16,7,'2015-12-05 16:41:55');  
  27. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (17,8,'2015-12-03 16:41:55');  
  28. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (18,9,'2015-12-04 16:41:55');  
  29. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (19,1,'2015-12-09 16:41:56');  
  30. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (20,2,'2015-12-08 16:41:56');  
  31. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (21,3,'2015-12-08 16:41:56');  
  32. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (22,4,'2015-12-08 16:41:56');  
  33. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (23,5,'2015-12-06 16:41:56');  
  34. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (24,6,'2015-12-06 16:41:56');  
  35. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (25,7,'2015-12-07 16:41:56');  
  36. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (26,8,'2015-12-05 16:41:56');  
  37. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (27,9,'2015-12-04 16:41:56');  
  38. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (28,1,'2015-12-09 16:41:56');  
  39. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (29,2,'2015-12-09 16:41:56');  
  40. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (30,3,'2015-12-09 16:41:56');  
  41. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (31,4,'2015-12-09 16:41:56');  
  42. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (32,5,'2015-12-05 16:41:56');  
  43. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (33,6,'2015-12-09 16:41:56');  
  44. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (34,7,'2015-12-08 16:41:56');  
  45. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (35,8,'2015-12-08 16:41:56');  
  46. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (36,9,'2015-12-06 16:41:56');  
  47. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (37,1,'2015-12-09 16:41:56');  
  48. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (38,2,'2015-12-08 16:41:56');  
  49. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (39,3,'2015-12-08 16:41:56');  
  50. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (40,4,'2015-12-07 16:41:56');  
  51. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (41,5,'2015-12-08 16:41:56');  
  52. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (42,6,'2015-12-04 16:41:56');  
  53. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (43,7,'2015-12-08 16:41:56');  
  54. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (44,8,'2015-12-07 16:41:56');  
  55. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (45,9,'2015-12-02 16:41:56');  
  56. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (46,1,'2015-12-09 16:41:56');  
  57. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (47,2,'2015-12-08 16:41:56');  
  58. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (48,3,'2015-12-07 16:41:56');  
  59. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (49,4,'2015-12-06 16:41:56');  
  60. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (50,5,'2015-12-09 16:41:56');  
  61. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (51,6,'2015-12-05 16:41:56');  
  62. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (52,7,'2015-12-07 16:41:56');  
  63. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (53,8,'2015-12-04 16:41:56');  
  64. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (54,9,'2015-12-05 16:41:56');  
  65. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (55,1,'2015-12-09 16:41:56');  
  66. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (56,2,'2015-12-09 16:41:56');  
  67. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (57,3,'2015-12-08 16:41:56');  
  68. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (58,4,'2015-12-08 16:41:56');  
  69. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (59,5,'2015-12-08 16:41:56');  
  70. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (60,6,'2015-12-08 16:41:56');  
  71. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (61,7,'2015-12-06 16:41:56');  
  72. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (62,8,'2015-12-03 16:41:56');  
  73. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (63,9,'2015-12-06 16:41:56');  
  74. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (64,1,'2015-12-09 16:41:56');  
  75. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (65,2,'2015-12-09 16:41:56');  
  76. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (66,3,'2015-12-07 16:41:56');  
  77. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (67,4,'2015-12-08 16:41:56');  
  78. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (68,5,'2015-12-08 16:41:56');  
  79. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (69,6,'2015-12-05 16:41:56');  
  80. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (70,7,'2015-12-03 16:41:56');  
  81. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (71,8,'2015-12-06 16:41:56');  
  82. INSERT INTO `loginlog` (`id`,`pid`,`logintime`) VALUES (72,9,'2015-12-07 16:41:56');  

这个需求我其实是见过的..但是时间长了,反应就慢了.
把这个问题再抽象出来看看吧.

需求:
统计每天的,三天内登录过两次的用户数量.
  1. select logintime,sum(c)  
  2. from (  
  3.     select   
  4.     logintime,pid,  
  5.     (
  6.         select if(count(*)>=2,1,0) from loginlog t1 
  7.         where 
  8.         t1.pid=log.pid and 
  9.         t1.logintime between log.logintime-interval 2 day and log.logintime + interval 1 day 
  10.     ) c  
  11.     from   
  12.     (  
  13.         select date_format(logintime,'%Y-%m-%d') logintime,pid  
  14.         from loginlog  group by date_format(logintime,'%Y-%m-%d'),pid   
  15.     ) log  
  16. )t4 group by logintime;  

首先聚合,去除一个用户在一天内多次登录的情况,获得log别名的表.
然后使用相关子查询,将log表的每个用户和登录时间推入内层.在内层计算三天内的登录次数,如果大于等于2,则返回1,否则返回0.
最后,在最外层聚合,得到每天有多少用户在三天内两次登录.


验证:

以2015-12-04日的数据进行分析,
当天登录的用户有 6,8,9
这三个用户在 2015-12-02 至 2015-12-04 之间,都有超过2次登录。
所以2015-12-04有三个用户在三天内登录两次(虽然用户6是在当天登录2次)



问题变种:
统计每天的,三天内登录过两天的用户数量.
  1. select logintime,sum(c)    
  2. from (    
  3.     select     
  4.     logintime,pid,    
  5.     (  
  6.         select if(count(distinct date_format(t1.logintime,'%Y-%m-%d'))>=2,1,0) from loginlog t1 
  7.         where 
  8.         t1.pid=log.pid and 
  9.         t1.logintime between log.logintime-interval 2 day and log.logintime + interval 1 day 
  10.     ) c    
  11.     from     
  12.     (    
  13.         select date_format(logintime,'%Y-%m-%d') logintime,pid    
  14.         from loginlog  group by date_format(logintime,'%Y-%m-%d'),pid     
  15.     ) log    
  16. )t4 group by logintime;  





来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29254281/viewspace-1869889/ ，如需转载，请注明出处，否则将追究法律责任。

1
0
上一篇： Session重叠问题学习(一)
下一篇： 查看每个月举办的活动总数
请登录后发表评论 登录
全部评论
壹頁書

注册时间：2013-10-19

  • 博文量
    620
  • 访问量
    6339978

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593