求用户连续登陆的最大天数

迷南。 2024-04-03 12:58 103阅读 0赞

### 题目： ###

这张表记录了用户id为1001和1002的登陆日期，  
**求：用户各自连续登陆的天数最大值？**  
![在这里插入图片描述][256a64c92abd4276a4e03b02dabb3270.png]

思路：

1.  类似[求连续3天访问的用户id][3_id]，  
    需要增加以 user\_id开窗，计算时间的排名（如果时间有重复用`DENSE_RANK`），将结果作为新的字段 rank
2.  用访问日期减去排名rank，得到一个时间`tmp` ，如果用户是连续访问的，那么这个时间差 tmp就是一样的！
3.  对 tmp 进行 count 记数
4.  找到最大的 count 即为答案。

(1）先按照用户id（usr\_id）对访问日期（log\_date）进行排名

SELECT user_id, log_date, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY log_date) AS rank 
    FROM user_logging_format;

![在这里插入图片描述][23e28ffaff024b4d93b69e178809ae48.png]

(2) 得到排名后，用log\_date 减去 rank序号！得到差值 `tmp` ，  
tmp相同则代表是连续的！；

SELECT user_id, 
    	log_date, 
    	DATE_SUB(log_date,cast(rn AS INT) AS tmp
    FROM(
    	SELECT user_id, log_date, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY log_date) AS rank FROM user_logging_format)as q1;

(3）根据user\_id 和 tmp 联合分组并统计tmp，tmp相同的被统计，即得到连续的天数

SELECT 
    	user_id, 
    	tmp, 
    	count(*) AS count 
    FROM
    	(SELECT user_id, 
    	log_date, 
    	DATE_SUB(log_date,cast(rn AS INT)) AS tmp 
    	FROM(SELECT user_id, log_date, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY log_date) AS rank FROM     					user_logging_format)q1)q2 GROUP BY user_id, tmp;

(4) 最后求出每个user\_id 连续登录的最大天数，即对相同tmp记数后的最大值；

SELECT user_id,MAX(cc) 
    FROM
    	(SELECT 
    		user_id, 
    		tmp, 
    		count(*) AS cc
    	FROM
    		(SELECT user_id, 
    		log_date, 
    		DATE_SUB(log_date,cast(rn AS INT)) AS tmp 
    		FROM(SELECT user_id, log_date, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY log_date) AS rank FROM user_logging_format)a1)q2 GROUP BY user_id, tmp)q3
    	GROUP BY user_id;

注意：如果原始数据有重复，则使用`DENSE_RANK`而不是 `ROW_NUMBER`;

[256a64c92abd4276a4e03b02dabb3270.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/4747354eed51447da7fb26fd5230595f.png
[3_id]: https://blog.csdn.net/Swofford/article/details/126862413
[23e28ffaff024b4d93b69e178809ae48.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/55f52a91f0684d10ac25c4777f217f76.png