已知登陆表中有 uid、login_time，求每个用户的最大连续登陆天数
1、模拟数据集 user_login
WITH user_login as (
select 'A' uid,
from_unixtime(
unix_timestamp('2020-08-01 10:00:00', "yyyy-MM-dd HH:mm:ss")
) as login_time
union all
select 'A' uid,
from_unixtime(
unix_timestamp('2020-08-02 10:01:00', "yyyy-MM-dd HH:mm:ss")
) as login_time
union all
select 'A' uid,
from_unixtime(
unix_timestamp('2020-08-03 10:02:03', "yyyy-MM-dd HH:mm:ss")
) as login_time
union all
select 'A' uid,
from_unixtime(
unix_timestamp('2020-08-06 10:04:03', "yyyy-MM-dd HH:mm:ss")
) as login_time
union all
select 'A' uid,
from_unixtime(
unix_timestamp('2020-08-07 10:03:10', "yyyy-MM-dd HH:mm:ss")
) as login_time
union all
select 'B' uid,
from_unixtime(
unix_timestamp('2020-08-12 10:03:11', "yyyy-MM-dd HH:mm:ss")
) as login_time
union all
select 'B' uid,
from_unixtime(
unix_timestamp('2020-08-13 10:04:00', "yyyy-MM-dd HH:mm:ss")
) as login_time
union all
select 'B' uid,
from_unixtime(
unix_timestamp('2020-08-14 12:04:06', "yyyy-MM-dd HH:mm:ss")
) as login_time
union all
select 'A' uid,
from_unixtime(
unix_timestamp('2020-08-15 12:04:10', "yyyy-MM-dd HH:mm:ss")
) as login_time
union all
select 'B' uid,
from_unixtime(
unix_timestamp('2020-08-16 13:04:12', "yyyy-MM-dd HH:mm:ss")
) as login_time
union all
select 'B' uid,
from_unixtime(
unix_timestamp('2020-08-17 15:05:10', "yyyy-MM-dd HH:mm:ss")
) as login_time
)2、将记录按照每个用户分区，对登录时间进行升序排列，得到数据集 user_log_rank
代码：
user_log_rank as
(
select uid,
login_time,
row_number() over(
partition by uid
order by login_time
) as sort
from (
select uid,
date(login_time) as login_time
from user_login
group by uid,
date(login_time)
) as rs_user_login
)结果：
文章来源地址https://www.toymoban.com/news/detail-560318.html
3、每条记录的登录时间减去排序的数字，得到一个组 date_group，对用户及date_group 做聚合，continue_days 即为连续登录天数
代码：
select uid,
date_sub(login_time, sort) as date_group,
min(login_time) as start_dt,
max(login_time) as end_dt,
count(1) as continue_days
from user_log_rank a
group by uid,
date_sub(login_time, sort)结果：
4、以 uid 分组，max（continue_days ）即为每个用户的最大连续登录天数
代码：
select uid,
max(continue_days) as max_continue_days
from (
select uid,
date_sub(login_time, sort) as date_group,
min(login_time) as start_dt,
max(login_time) as end_dt,
count(1) as continue_days
from user_log_rank a
group by uid,
date_sub(login_time, sort)
) as rs_continue_days
group by uid结果：
文章来源：https://www.toymoban.com/news/detail-560318.html
到了这里，关于SQL 求最大连续登陆天数的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
