查询连续3天登录的用户

一、创建表

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则符合