hive 用户连续签到天数及历史最大连续签到天数
需求
表temp_user_login，标记每天用户是否签到（说明：该表包含所有用户所有工作日的出勤记录） ，包含三个字段：日期 date 用户id user_id if_login 0 未签到 1 签到
问题1：统计截至当前每个用户已经连续签到的天数（输出表仅包含当天签到的所有用户，计算其连续签到天数）
问题2：统计每个用户历史以来最大的连续签到天数（输出表为用户签到表中所有出现过的用户，计算其历史最大连续签到天数）
数据准备
create table temp_user_login ( day_id string, user_id int, if_login int ) stored as orc tblproperties ('orc.compress' = 'snappy'); insert into temp_user_login values ('2022-03-01', 1, 1), ('2022-03-01', 2, 0), ('2022-03-01', 3, 0), ('2022-03-02', 1, 1), ('2022-03-02', 2, 1), ('2022-03-02', 3, 0), ('2022-03-03', 1, 1), ('2022-03-03', 2, 0), ('2022-03-03', 3, 1), ('2022-03-04', 1, 1), ('2022-03-04', 2, 0), ('2022-03-04', 3, 0), ('2022-03-05', 1, 1), ('2022-03-05', 2, 1), ('2022-03-05', 3, 1); select * from temp_user_login;
|
date
|
user_id
|
if_login
|
|
2022/3/1
|
1
|
1
|
|
2022/3/1
|
2
|
0
|
|
2022/3/1
|
3
|
0
|
|
2022/3/2
|
1
|
1
|
|
2022/3/2
|
2
|
1
|
|
2022/3/2
|
3
|
0
|
|
2022/3/3
|
1
|
1
|
|
2022/3/3
|
2
|
0
|
|
2022/3/3
|
3
|
1
|
|
2022/3/4
|
1
|
1
|
|
2022/3/4
|
2
|
0
|
|
2022/3/4
|
3
|
0
|
|
2022/3/5
|
1
|
1
|
|
2022/3/5
|
2
|
1
|
|
2022/3/5
|
3
|
1
|
解题方案-需求一
select * from (select user_id, datediff('2022-03-05', date_max) date_cz from (select t.user_id, max(case when t.if_login = 0 then t.day_id else date_sub(t1.login_min, 1) end) date_max from temp_user_login t left join (select user_id, min(day_id) login_min from temp_user_login where if_login = 1 group by user_id) t1 group by t.user_id) t) t where date_cz != 0;
|
user_id
|
days_cz
|
|
1
|
3
|
|
2
|
1
|
|
3
|
1
|
解题方案-需求二
可以将题目2 求每个用户历史以来最大的连续签到天数，问题简化如下：求用户1中签到为1的最长序列；
|
用户
|
签到
|
|
1
|
1
|
|
1
|
0
|
|
1
|
1
|
|
1
|
1
|
|
1
|
0
|
|
1
|
1
|
|
1
|
1
|
|
1
|
1
|
|
1
|
1
|
|
1
|
1
|
将签到表中0和1开始变化的数据想办法标记分开，我们将不同事件的数据进行区分并进行重新分组。具体算法如下
- 判定当前行和上一行的签到列是否相等,如果不等置为1，相等为0,作为flag列
- 累加 flag列，值作为分桶id
- 按照累加的值（分桶id）分组。
- 组内重排序
|
用户
|
签到
|
flag
|
累计值
|
|
1
|
1
|
0
|
0
|
|
1
|
0
|
1
|
1
|
|
1
|
0
|
0
|
1
|
|
1
|
1
|
1
|
2
|
|
1
|
0
|
1
|
3
|
|
1
|
1
|
1
|
4
|
|
1
|
1
|
0
|
4
|
|
1
|
1
|
0
|
4
|
|
1
|
1
|
0
|
4
|
|
1
|
1
|
0
|
4
|
|
1
|
0
|
1
|
5
|
|
1
|
1
|
1
|
6
|
上述表最后一列累计值就是需要分组的id，通过此算法可以将同一类所属的数据区分出来，很明显上表中底色标记出来的就是所求的最长的序列，连续签到最长的天数。上述标记区分的方式写成SQL如下：
1、判定当前行和上一行的签到列是否相等,如果不等置为1，相等为0,作为flag列
select *, case when lag(if_login, 1, if_login) over (partition by user_id order by day_id) != if_login then 1 else 0 end flag from temp_user_login
2、累加 flag列，值作为分桶id
select day_id, user_id, if_login, flag, sum(flag) over (partition by user_id order by day_id) tong_id from (select *, case when lag(if_login, 1, if_login) over (partition by user_id order by day_id) != if_login then 1 else 0 end flag from temp_user_login) t;
3、按照累加的值（分桶id）分组
select user_id, sum(if_login) sum_logs_days from (select day_id, user_id, if_login, flag, sum(flag) over (partition by user_id order by day_id) tong_id from (select *, case when lag(if_login, 1, if_login) over (partition by user_id order by day_id) != if_login then 1 else 0 end flag from temp_user_login) t) t group by user_id, tong_id;
4、组内重排序
select user_id, max(sum_logs_days) max_length_days from (select user_id, sum(if_login) sum_logs_days from (select day_id, user_id, if_login, flag, sum(flag) over (partition by user_id order by day_id) tong_id from (select *, case when lag(if_login, 1, if_login) over (partition by user_id order by day_id) != if_login then 1 else 0 end flag from temp_user_login) t) t group by user_id, tong_id) t group by user_id;
最终结果
|
user_id
|
max_length_days
|
|
2
|
1
|
|
3
|
1
|
|
1
|
5
|
