面试题 Hive-SQL查询连续活跃登录用户思路详解_sql查询连续登录两天的用户

age int)
ROW format delimited fields terminated BY ‘,’;

INSERT INTO TABLE test5active VALUES
(‘2019-02-11’,‘user_1’,23),(‘2019-02-12’,‘user_1’,23),(‘2019-02-11’,‘user_2’,19),
(‘2019-02-11’,‘user_3’,39),(‘2019-02-11’,‘user_1’,23),
(‘2019-02-11’,‘user_3’,39),(‘2019-02-11’,‘user_1’,23),
(‘2019-02-12’,‘user_2’,19),(‘2019-02-13’,‘user_1’,23),
(‘2019-02-15’,‘user_2’,19),(‘2019-02-16’,‘user_2’,19);

### 登录日志去重


因为每天用户登录次数可能不止一次，所以需要先将用户每天的登录日期去重。

select distinct user_id,dt from test5active

![在这里插入图片描述](https://img-blog.csdnimg.cn/6a0820b1b49b43e19fd31cc0c102af5a.png)


## 一、思路一


1、再用row\_number() over(partition by \_ order by \_)函数将用户id分组，按照登陆时间进行排序。


2、计算登录日期减去第二步骤得到的结果值，用户连续登陆情况下，每次相减的结果都相同。


3、按照id和日期分组并求和，筛选大于等于2的即为连续活跃登陆的用户。


### 1.1、分组排序

SELECT
t1.user_id,
t1.dt,
ROW_NUMBER() OVER(PARTITION BY t1.user_id ORDER BY t1.dt) day_rank
FROM (
SELECT DISTINCT dt,user_id FROM test5active
)t1;

![在这里插入图片描述](https://img-blog.csdnimg.cn/201033bfa6a742dbaabf5f060d551539.png)


### 1.2、日期减去计数值得到结果, 用户连续登陆情况下，每次相减的结果都相同

SELECT
t2.user_id,
t2.dt,
date_sub(t2.dt, t2.day_rank) AS dis
FROM (
SELECT
t1.user_id,
t1.dt,
ROW_NUMBER() OVER(PARTITION BY t1.user_id ORDER BY t1.dt) day_rank
FROM
(SELECT DISTINCT dt, user_id FROM test5active) t1
)t2;

![在这里插入图片描述](https://img-blog.csdnimg.cn/8b3e46cf277b474abc3172770e80f78b.png)


### 1.3、根据 user\_id 和 dis 分组，得到用户的 开始、结束时间、连续登录天数

SELECT
t3.user_id,
MIN(t3.dt),
MAX(t3.dt),
COUNT(1)
FROM
(SELECT
t2.user_id,
t2.dt,
DATE_SUB(t2.dt, t2.day_rank) AS dis
FROM
(SELECT
t1.user_id,
t1.dt,
ROW_NUMBER() OVER(PARTITION BY t1.user_id ORDER BY t1.dt) day_rank
FROM
(SELECT DISTINCT dt, user_id FROM test5active) t1
) t2
) t3
GROUP BY t3.user_id,t3.dis

![在这里插入图片描述](https://img-blog.csdnimg.cn/2b4a693647f54cdb8bd878e3cd9031bb.png)


### 1.4、连续登录超过两天用户

– 1.3基础上
HAVING COUNT(1) > 1;

![在这里插入图片描述](https://img-blog.csdnimg.cn/039b9be9d76a4de3823cdcf4c6c3a085.png)  
 接下来就是用户去重


## 二、思路二： 使用 LAG(向后)或者 LEAD(向前)


![img](https://img-blog.csdnimg.cn/img_convert/e848eb07be99f1f00b518949547020e5.png)
![img](https://img-blog.csdnimg.cn/img_convert/ac3b9cc8664b503da17c1dc7d3780de2.png)

**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/topics/618545628)**


**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**

csdn.net/topics/618545628)**


**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**