一、创建表
CREATE TABLE `log` (
`user_id` int(11) NOT NULL,
`create_time` datetime(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Compact;
INSERT INTO `log` VALUES (101, '2021-11-10 20:12:42');
INSERT INTO `log` VALUES (102, '2021-11-26 20:13:04');
INSERT INTO `log` VALUES (101, '2021-11-08 20:12:42');
INSERT INTO `log` VALUES (103, '2021-11-18 20:14:11');
INSERT INTO `log` VALUES (102, '2021-11-26 20:14:20');
INSERT INTO `log` VALUES (101, '2021-11-09 20:12:42');
INSERT INTO `log` VALUES (103, '2021-11-20 20:14:11');
INSERT INTO `log` VALUES (103, '2021-11-19 20:14:11');
INSERT INTO `log` VALUES (102, '2021-11-24 21:02:37');
解决方案分析:
通过对用户id进行分组,并按时间排序。 排序后生成一个临时字段rank存储排序结果,然后通过date_sub()函数用登录时间减去排序rank,如果是连续登录的话得到的时间相等。
方案一:
select *, count(1) ct from (
select user_id, create_time, row_number() over(partition by user_id order by create_time) as rank
from log
) m
group by m.userId, DATE_SUB(m.createTime,INTERVAL m.rank DAY) having ct > 2
Oracle 通过 row_number() over partition by 函数实现了分组内排序功能,通过它进行分组排序,然后登录时间减去排序字段,通过data_sub()函数计算后的日期分组,并统计它的个数。
方案二:
mysql是没有row_number() over partition by函数的,所有如何想要解决这个问题,可以通过伪列
格式:
SELECT
(@r :=@r + 1) AS rank
FROM (SELECT @r := 0) r
;
实现:
select *, count(1) ct from (
select createTime, if(@p = userId, @r :=@r + 1,@r :=1) AS rank, @p := userId as userId
from (
select user_id as userId, create_time as createTime
from log , (SELECT @r := 0, @p := null) d group by userId, createTime order by createTime
) t
) m
group by m.userId, DATE_SUB(m.createTime,INTERVAL m.rank DAY) having ct > 2
定义两个变量@r := 0, @p := null,通过用户id、时间分组,并按时间排序。
if(@p = userId, @r :=@r + 1,@r :=1) AS rank 把用户id赋给变量@p ,判断如果用户id相等则排序加一,否则排序从一开始排。这样是为了实现组内排序
使用DATE_SUB(m.createTime,INTERVAL m.rank DAY) 函数减去 排序 后进行分组,如果个数大于2则符合