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. 判定当前行和上一行的签到列是否相等,如果不等置为1，相等为0,作为flag列
2. 累加 flag列，值作为分桶id
3. 按照累加的值（分桶id）分组。
4. 组内重排序

用户	签到	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