用户连续登陆sql_【sql】最长连续登陆天数

姹紫嫣红

春风拂面

今天北京的风是格外的大

吹出了我隐藏多年的发际线

让我也再次看清楚了的体重仍需多吃点

不过不管怎么

通过刷题来讲解知识点，还是要继续保持的

即使清风徐来

先养眼，再看题

❤️

今天还是小姐姐镇楼

本次我们主要讨论分组排序的问题

即row_number()函数

PS：其他的窗口函数以后再聊

0

Question

请用sql查询用户最长的登陆天数

(这道题貌似也算是经典题型，完全可以

上三年高考五年模拟那种)

原始数据

uid	login_time
1	2020/1/1
1	2020/1/2
2	2020/1/2
2	2020/1/3
3	2020/1/3
1	2020/1/4
2	2020/1/4
1	2020/1/5
2	2020/1/5
1	2020/1/6
3	2020/1/6
3	2020/1/7

期望数据

uid	最大连续登陆天数
1	3
2	4
3	2

1

Answer

嘤嘤嘤～本来打算直接写答案，但考虑再三，

感觉可以从0开始写，

让小伙伴们能跟上我的思路。

 step1

第一步：

获取每个用户的数据信息，

并按照时间进行排序 

使用row_number()窗口函数，

按uid分组，按照login_time排序 

SELECT    uid,    login_time,    row_number() OVER(PARTITION BY uid order by login_time) as rankFROM user_login

结果如下

(已经求出各个uid的以及登陆时间和连续值)

uid	login_time	rank
1	2020/1/1	1
1	2020/1/1	2
1	2020/1/1	3
1	2020/1/1	4
1	2020/1/1	5
2	2020/1/1	1
2	2020/1/1	2
2	2020/1/1	3
2	2020/1/1	4
3	2020/1/1	1
3	2020/1/1	2
3	2020/1/1	3

 step2

第二步：

开始判断是否连续

通过将login_time和rank相减法，

从而得出，是否连续

(如果不好理解的话，可通过结果找到规律) 

select  uid,  date_sub(login_time,rank) as login_sub,  min(login_time) as login_min,  max(login_time) as login_max,  count(1) as login_confrom (  -- 根据用户分组，按照时间进行排序(默认升序)    select       uid,      login_time,      row_number() OVER(PARTITION BY uid order by login_time) as rank    from user_login) agroup by uid,date_sub(login_time,rank)

结果如下

(已能够判断连续性和连续天数)

uid	login_sub	login_min	login_max	login_con
1	2019/12/31	2020/1/1	2020/1/2	2
1	2020/1/1	2020/1/4	2020/1/6	3
2	2020/1/1	2020/1/2	2020/1/5	4
3	2020/1/2	2020/1/3	2020/1/3	1
3	2020/1/4	2020/1/6	2020/1/7	2

 step3

第三步，

直接获取最大值通过uid分组，

获取最大的login_con

select uid,max(login_con) as login_max from (  select    uid,    date_sub(login_time,rank) as login_sub,    min(login_time) as login_min,    max(login_time) as login_max,    count(1) as login_con  from (    -- 根据用户分组，按照时间进行排序(默认升序)      select         uid,        login_time,        row_number() OVER(PARTITION BY uid order by login_time) as rank      from user_login  ) a  group by uid,date_sub(login_time,rank)) b group by uid

结果如下

(获取最大的连续天数)

uid	login_max
1	3
2	4
3	2

扫码关注我

郭大熊的公众号

个人博客 : www.guodaxiong.com

如果不曾见过阳光，我本可以忍受黑暗

 Hi GuoDaXiong 

我是狗子

祝你幸福