【sql】最长连续登陆天数

删库跑路小分队 2021-09-03

1528

姹紫嫣红

春风拂面

今天北京的风是格外的大

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

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

不过不管怎么

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

即使清风徐来

先养眼，再看题

❤️

今天还是小姐姐镇楼

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

即row_number()函数

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

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

Answer

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

感觉可以从0开始写，

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

step1

第一步：

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

并按照时间进行排序

使用row_number()窗口函数，

按uid分组，按照login_time排序

SELECT
    uid,
    login_time,
    row_number() OVER(PARTITION BY uid order by login_time) as rank
FROM 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_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)

结果如下

（已能够判断连续性和连续天数）

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

我是狗子

祝你幸福

数据库

文章转载自删库跑路小分队，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

【sql】最长连续登陆天数

评论