SQL——求用户最长连续登录时间

题目描述
求用户最长登录天数

思路：1.如果同一天登录有多条数据的话，首先先对id和日期进行去重

# 先对id和日期进行去重
select id,visit_date
from user_visit_log
group by id,visit_date

2.通过窗口函数对每个人的登录时间进行排序

# 通过窗口函数进行排序
select id,visit_date,row_number() over(partition by id order by visit_date) r
from user_visit_log

3.如果是连续登录的话，当天日期减去排名得到的日期应该是相同的

# 计算减去名次
select id,visit_date,r,date_sub(visit_date,interval r day) as day
from (
    select id,visit_date,row_number() over(partition by id order by visit_date) r
    from user_visit_log) as t

4.计算得到日期相同的天数，按照id和减去得到的日期进行分组

# # 计算连续天数
select id,day,count(day) as active_day
from (
    select id,visit_date,r,date_sub(visit_date,interval r day) as day
    from (
        select id,visit_date,row_number() over(partition by id order by visit_date) r
        from user_visit_log) as t
) as t1
group by id,day

5.最后找到最大的连续天数

select id,max(active_day) as max_Continuous_login_days
from (
    select id,day,count(day) as active_day
    from (
        select id,visit_date,r,date_sub(visit_date,interval r day) as day
        from (
            select id,visit_date,row_number() over(partition by id order by visit_date) r
            from user_visit_log) as t
    ) as t1
group by id,day
) as t2
group by id

完整代码

# 先对id和日期进行去重
# select id,visit_date
# from user_visit_log
# group by id,visit_date

# 通过窗口函数进行排序
# select id,visit_date,row_number() over(partition by id order by visit_date) r
# from user_visit_log

# 计算减去名次
# select id,visit_date,r,date_sub(visit_date,interval r day) as day
# from (
#     select id,visit_date,row_number() over(partition by id order by visit_date) r
#     from user_visit_log) as t

# # 计算连续天数
# select id,day,count(day) as active_day
# from (
#     select id,visit_date,r,date_sub(visit_date,interval r day) as day
#     from (
#         select id,visit_date,row_number() over(partition by id order by visit_date) r
#         from user_visit_log) as t
# ) as t1
# group by id,day

# # 计算最大天数
select id,max(active_day) as max_Continuous_login_days
from (
    select id,day,count(day) as active_day
    from (
        select id,visit_date,r,date_sub(visit_date,interval r day) as day
        from (
            select id,visit_date,row_number() over(partition by id order by visit_date) r
            from user_visit_log) as t
    ) as t1
group by id,day
) as t2
group by id