题目描述
求用户最长登录天数
思路: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