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(partitionbyuser_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(partitionbyuser_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(partitionbyuser_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