mysql找出最大的天数_mysql 计算连续登录最大天数

--用户登录信息表
CREATE TABLE `user_log` (
  `user_id` varchar(10) NOT NULL,
  `log_in_date` date NOT NULL,
  PRIMARY KEY (`user_id`,`log_in_date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
--插入测试数据
INSERT INTO `user_log` VALUES ('u_01', '2020-01-02');
INSERT INTO `user_log` VALUES ('u_01', '2020-01-04');
INSERT INTO `user_log` VALUES ('u_02', '2020-01-02');
INSERT INTO `user_log` VALUES ('u_03', '2020-01-02');
INSERT INTO `user_log` VALUES ('u_03', '2020-01-04');
INSERT INTO `user_log` VALUES ('u_04', '2020-01-02');
INSERT INTO `user_log` VALUES ('u_04', '2020-01-08');
INSERT INTO `user_log` VALUES ('u_05', '2020-01-02');
INSERT INTO `user_log` VALUES ('u_05', '2020-01-08');
INSERT INTO `user_log` VALUES ('u_06', '2020-01-04');
INSERT INTO `user_log` VALUES ('u_06', '2020-01-08');
INSERT INTO `user_log` VALUES ('u_07', '2020-01-04');
INSERT INTO `user_log` VALUES ('u_08', '2020-01-02');
INSERT INTO `user_log` VALUES ('u_08', '2020-01-04');
INSERT INTO `user_log` VALUES ('u_09', '2020-01-02');
INSERT INTO `user_log` VALUES ('u_10', '2020-01-02');
INSERT INTO `user_log` VALUES ('u_10', '2020-01-04');
INSERT INTO `user_log` VALUES ('u_11', '2020-01-03');
INSERT INTO `user_log` VALUES ('u_12', '2020-01-05');
INSERT INTO `user_log` VALUES ('u_13', '2020-01-09');
INSERT INTO `user_log` VALUES ('u_14', '2020-01-03');
INSERT INTO `user_log` VALUES ('u_14', '2020-01-05');
INSERT INTO `user_log` VALUES ('u_15', '2020-01-03');
INSERT INTO `user_log` VALUES ('u_15', '2020-01-05');
INSERT INTO `user_log` VALUES ('u_15', '2020-01-09');
INSERT INTO `user_log` VALUES ('u_16', '2020-01-03');
INSERT INTO `user_log` VALUES ('u_17', '2020-01-04');
INSERT INTO `user_log` VALUES ('u_18', '2020-01-04');
INSERT INTO `user_log` VALUES ('u_18', '2020-01-10');
INSERT INTO `user_log` VALUES ('u_19', '2020-01-06');
INSERT INTO `user_log` VALUES ('u_20', '2020-01-04');
INSERT INTO `user_log` VALUES ('u_20', '2020-01-06');
INSERT INTO `user_log` VALUES ('u0001', '2019-10-10');
INSERT INTO `user_log` VALUES ('u0001', '2019-10-11');
INSERT INTO `user_log` VALUES ('u0001', '2019-10-12');
INSERT INTO `user_log` VALUES ('u0001', '2019-10-14');
INSERT INTO `user_log` VALUES ('u0001', '2019-10-15');
INSERT INTO `user_log` VALUES ('u0001', '2019-10-17');
INSERT INTO `user_log` VALUES ('u0001', '2019-10-18');
INSERT INTO `user_log` VALUES ('u0001', '2019-10-19');
INSERT INTO `user_log` VALUES ('u0001', '2019-10-20');
INSERT INTO `user_log` VALUES ('u0002', '2019-10-20');

--Step1 核心是按照访问时间进行排序，可用登录时间列减去（或加上）排序好的序列号，得到一个日期值，按该值分组计数即可。

--按时间降序排列
select 
	user_id,
	log_in_date,
	(row_number() over(partition by user_id order by log_in_date desc)) as 'rank'
from 
	user_log

--按时间升序排列
select 
	user_id,
	log_in_date,
	(row_number() over(partition by user_id order by log_in_date asc)) as 'rank'
from 
	user_log

--用登录时间相减（或相加）排序好的序列号
select
	user_id,	
	DATE_ADD(log_in_date, INTERVAL row_number() over(partition by user_id order by log_in_date DESC ) day) AS 'add_time'
FROM user_log


--连续登录次数
SELECT user_id, add_time,count(add_time) AS '连续登录天数'
FROM 
(
select
	user_id,	
	DATE_ADD(log_in_date, INTERVAL row_number() over(partition	by	user_id order by log_in_date DESC ) day) AS 'add_time'
FROM user_log
)T
GROUP BY user_id, add_time


--最大连续登录天数
SELECT user_id,MAX(连续登录天数)
FROM 
(
SELECT user_id, add_time,count(add_time) AS '连续登录天数'
FROM 
(
select
	user_id,	
	DATE_ADD(log_in_date, INTERVAL row_number() over(partition	by	user_id order by log_in_date DESC ) day) AS 'add_time'
FROM user_log
)T
GROUP BY user_id, add_time
)M


--连续登录天数为2的用户
SELECT user_id
FROM 
(
SELECT user_id, add_time,count(add_time) AS '连续登录天数'
FROM 
(
select
	user_id,	
	DATE_ADD(log_in_date, INTERVAL row_number() over(partition	by	user_id order by log_in_date DESC ) day) AS 'add_time'
FROM user_log
)T
GROUP BY user_id, add_time
)M
where M.连续登录天数 = 2