hive_面试题 【间隔连续登入问题】

需求 : 计算每个用户最大的连续登录天数,可以间隔一天。解释:如果一个用户在 1,3,5,6 登录游戏，则视为连续 6 天登录

-- 数据准备

-- DDL
create table test2 (
`user_id` string comment '用户id',
`login_date` string comment '登入日期',
`login_status` string comment '登入状态(1:成功,2:异常)')
 comment '用户登入记录表'
row format delimited fields terminated by '\t'
lines terminated by '\n' stored as orc;

-- DML
insert overwrite table test2
select '1001' as id,'2021-12-12' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2021-12-20' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2022-02-10' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2021-12-20' as occur_date,'0' as lowcarbon union all
select '1002' as id,'2021-12-12' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2021-12-13' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2021-12-13' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2021-12-13' as occur_date,'1' as lowcarbon union all
select '1002' as id,'2021-12-14' as occur_date,'0' as lowcarbon union all
select '1001' as id,'2021-12-14' as occur_date,'1' as lowcarbon union all
select '1002' as id,'2021-12-15' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2021-12-15' as occur_date,'1' as lowcarbon union all
select '1003' as id,'2021-12-15' as occur_date,'0' as lowcarbon union all
select '1003' as id,'2021-12-16' as occur_date,'1' as lowcarbon union all
select '1003' as id,'2021-12-17' as occur_date,'1' as lowcarbon union all
select '1003' as id,'2021-12-18' as occur_date,'1' as lowcarbon union all
select '1003' as id,'2021-12-29' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2022-01-01' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2022-01-01' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2022-01-01' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2022-01-03' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2022-01-05' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2022-01-06' as occur_date,'1' as lowcarbon union all
select '1003' as id,'2021-12-19' as occur_date,'1' as lowcarbon ;

-- 数据说明
-- 某游戏公司记录的用户每日登录数据
-- 每天每天可能有多次登入记录

-- 分析思路

-- 对每条记录添加标签
    1. 当前记录是否连续(if_contine)
        思考 : 怎样判断当前记录是否为连续记录
            排序后,与前一条记录日期或后一条记录日期 差值是否为 1或者2
    2. 为同一用户多段连续记录分组
        contine_group

user_id  login_date   lead_days lag_days if_contine  contine_group
1001    2021-12-12      1       NULL    0       0
1001    2021-12-13      1       1       0       0
1001    2021-12-14      1       1       0       0
1001    2021-12-15      5       1       0       0

1001    2021-12-20      12      5       1       1

1001    2022-01-01      2       12      0       1
1001    2022-01-03      2       2       0       1
1001    2022-01-05      1       2       0       1
1001    2022-01-06      35      1       0       1

1001    2022-02-10      NULL    35      1       2
1002    2021-12-12      3       NULL    1       1
1002    2021-12-15      NULL    3       1       2

1003    2021-12-16      1       NULL    0       0
1003    2021-12-17      1       1       0       0
1003    2021-12-18      1       1       0       0
1003    2021-12-19      10      1       0       0

1003    2021-12-29      NULL    10      1       1

-- 查询结果
user_id max_contine_days
1001    6
1003    4

-- 执行sql

-- 执行sql
with t1 as (
-- 对基础数据排序,剔除登入失败记录
select
user_id
,login_date
from test2
where login_status = '1'
group by user_id
,login_date),
t2 as (
-- 对user_id分组,对login_date排序,获取当前行的 前一行和后一行
select
user_id
,login_date
,lead(login_date,1,login_date) over (partition by user_id order by login_date asc) as lead_login_date
,lag(login_date,1,login_date) over (partition by user_id order by login_date asc) as pre_login_date
,datediff(lead(login_date,1) over (partition by user_id order by login_date asc) , login_date) as lead_days
,datediff(login_date,lag(login_date,1) over (partition by user_id order by login_date asc)) as lag_days
from t1),
t3 as (
-- 根据 当前行和前一行和后一行的差值,判断当前行是否连续, 并对连续记录分组
select
user_id
,login_date
,lead_days
,lag_days
-- 是否连续 0:连续  1:不连续
,case when lead_days in (1,2) then 0
      when lag_days in (1,2) then 0
else 1 end as if_contine
-- 对每个用户,不同连续阶段分组
,sum(
case when lead_days in (1,2) then 0
      when lag_days in (1,2) then 0
else 1 end
) over (partition by user_id order by login_date asc) as contine_group
from t2),
t4 as (
-- 获取连续记录中的 最大连续天数
select
user_id
,contine_group
,datediff(max(login_date),min(login_date)) as contine_days
from t3
where if_contine = 0
group by user_id
,contine_group )
select
user_id
,max(contine_days) + 1 as max_contine_days
from t4
group by
user_id
;

-- 查询结果
user_id max_contine_days
1001    6
1003    4