转载

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

实验结构和数据:

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

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

需求:
统计每天的,三天内登录过两次的用户数量.

select logintime,sum(c)
from (
select
logintime,pid,
(
select if(count(*)>=2,1,0) from loginlog t1
where
t1.pid=log.pid and
t1.logintime between log.logintime-interval 2 day and log.logintime + interval 1 day
) c
from
(
select date_format(logintime,'%Y-%m-%d') logintime,pid
from loginlog group by date_format(logintime,'%Y-%m-%d'),pid
) log
)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次)

问题变种:
统计每天的,三天内登录过两天的用户数量.

select logintime,sum(c)
from (
select
logintime,pid,
(
select if(count(distinct date_format(t1.logintime,'%Y-%m-%d'))>=2,1,0) from loginlog t1
where
t1.pid=log.pid and
t1.logintime between log.logintime-interval 2 day and log.logintime + interval 1 day
) c
from
(
select date_format(logintime,'%Y-%m-%d') logintime,pid
from loginlog group by date_format(logintime,'%Y-%m-%d'),pid
) log
)t4 group by logintime;