连续登录天数统计

1.数据准备(建表sql在最后)

2.SQL分析

1、首先我们先对user表中的数据使用 DENSE_RANK() 函数，根据user_id进行分组，并通过login_date进行排序，以此来获取到登陆时间 rk 的排名，SQL如下

SELECT
	user_id,
	login_date,
	DENSE_RANK() over ( PARTITION BY user_id ORDER BY login_date ) rk 
FROM USER

2、根据第一步查询出来的结果，我们可以通过DATE_SUB() 函数计算出来login_date减去步骤一计算出来的排名 rk，如果减出来的时间结果相同，那就说明登录时间是连续的(比较难理解，大家可以根据结果去分析一下)，将减出来的时间记为diff SQL如下：

SELECT
	user_id,
	login_date,
	rk,
	DATE_SUB( login_date, INTERVAL rk DAY ) AS diff 
FROM
	( SELECT user_id, login_date, DENSE_RANK() over ( PARTITION BY user_id ORDER BY login_date ) rk FROM USER ) t1

3、然后我们可以根据user_id和第二步得出来的diff时间来进行分组，查出来所有用户所有的连续登录时间
SQL如下：

SELECT
	user_id,
	COUNT( DISTINCT login_date ) num 
FROM
	(
	SELECT
		user_id,
		login_date,
		rk,
		DATE_SUB( login_date, INTERVAL rk DAY ) AS diff 
	FROM
		( SELECT user_id, login_date, DENSE_RANK() over ( PARTITION BY user_id ORDER BY login_date ) rk FROM USER ) t1 
	) t2 
GROUP BY
	user_id,
	diff

4、最后使用max函数求出最大的连续登录时间

SELECT
	user_id,
	max( num ) maxNum 
FROM
	(
	SELECT
		user_id,
		COUNT( DISTINCT login_date ) num 
	FROM
		(
		SELECT
			user_id,
			login_date,
			rk,
			DATE_SUB( login_date, INTERVAL rk DAY ) AS diff 
		FROM
			( SELECT user_id, login_date, DENSE_RANK() over ( PARTITION BY user_id ORDER BY login_date ) rk FROM USER ) t1 
		) t2 
	GROUP BY
		user_id,
		diff 
	) t3 
GROUP BY
	user_id

---

建表数据SQL：

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_id` int(0) NOT NULL,
  `login_date` date NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '2020-04-20');
INSERT INTO `user` VALUES (1, '2020-04-21');
INSERT INTO `user` VALUES (1, '2020-04-25');
INSERT INTO `user` VALUES (1, '2020-04-26');
INSERT INTO `user` VALUES (1, '2020-05-10');
INSERT INTO `user` VALUES (1, '2020-05-11');
INSERT INTO `user` VALUES (1, '2020-05-12');
INSERT INTO `user` VALUES (1, '2020-05-30');
INSERT INTO `user` VALUES (1, '2020-06-17');
INSERT INTO `user` VALUES (1, '2020-06-25');
INSERT INTO `user` VALUES (1, '2020-05-10');
INSERT INTO `user` VALUES (1, '2020-05-11');
INSERT INTO `user` VALUES (2, '2020-10-20');
INSERT INTO `user` VALUES (2, '2020-10-21');
INSERT INTO `user` VALUES (2, '2020-10-22');
INSERT INTO `user` VALUES (2, '2020-10-23');
INSERT INTO `user` VALUES (2, '2020-11-10');
INSERT INTO `user` VALUES (2, '2020-11-11');
INSERT INTO `user` VALUES (2, '2020-11-17');
INSERT INTO `user` VALUES (2, '2020-12-25');
INSERT INTO `user` VALUES (2, '2020-04-20');
INSERT INTO `user` VALUES (2, '2020-12-17');
INSERT INTO `user` VALUES (2, '2020-10-21');
INSERT INTO `user` VALUES (2, '2020-11-30');
INSERT INTO `user` VALUES (3, '2020-10-01');
INSERT INTO `user` VALUES (3, '2020-10-02');
INSERT INTO `user` VALUES (3, '2020-10-11');
INSERT INTO `user` VALUES (3, '2020-10-31');
INSERT INTO `user` VALUES (3, '2020-11-01');
INSERT INTO `user` VALUES (3, '2020-11-02');
INSERT INTO `user` VALUES (3, '2020-11-03');
INSERT INTO `user` VALUES (3, '2020-11-04');
INSERT INTO `user` VALUES (3, '2020-12-17');
INSERT INTO `user` VALUES (3, '2020-12-18');