一、题目

有用户登录记录，已经按照日期去重。求近三十天，用户连续登录超过3天的次数，一直连续登录算一次，有间隔然后重新计算次数, 要求不能用开窗。

数据样例如下：

二、思路

这是一个连续问题：连续问题的解决思路是找到日期和升序序号差值相同的记录，相同则表示日期连续，不同则日期不连续。题目强调不能使用开窗函数，可以借助collect_list和posexplode获取升序序号。

看一下具体实现步骤：

1）按照user_id, login_date 排序，保证同一个用户的登录日期是升序的

2）使用collect_list保存同一个userid的登录日期

3）使用lateral view posexplode膨胀数据，同时为每个日期打上升序序号

4）使用date_sub计算减去序号后的差值日期：如果连续则差值日期相同

5）计算每个userid、差值日期的数量，这里要求数量>3

6）最后统计连续登录3天以上的次数

三、解答

with tab_idx_detal as (
  -- 将每个用户的登录日期按升序打标
  select 
    user_id,
    log_dt,
    idx
  from (
    -- 将每个用户的登录日期做成list，用于后续按升序打标
    select 
      user_id,
      collect_list(login_date) log_date_lsit
    from (
      select 
        *
      from use_login_detail
      order by user_id, 
        login_date
    ) t11
    group by user_id
  ) t21
  lateral view posexplode(log_date_lsit) tab as idx, log_dt
)


select 
  user_id,
  count(res_dt) cnt
from (
  select 
    user_id,
    res_dt,
    count(*) cnt
  from (
    -- 如果res_dt相同则表示这几天连续登录
    select 
      user_id,
      log_dt,
      idx,
      date_sub(log_dt, idx) res_dt -- 减去序号后的日期
    from tab_idx_detal
  ) t11
  group by user_id,
    res_dt
) t21
where cnt > 3
group by user_id

结果：

数据

# 建表
--建表语句
create table use_login_detail
(
    user_id      string, -- 用户id
    login_date    string -- 登陆日期
) comment '用户登录记录表'
stored as parquet;

--插入数据
insert into table use_login_detail values
('001','2024-07-03'),
('001','2024-07-04'),
('001','2024-07-05'),
('001','2024-07-06'),
('001','2024-07-12'),
('001','2024-07-13'),
('001','2024-07-17'),
('001','2024-07-18'),
('001','2024-07-19'),
('001','2024-07-20'),
('001','2024-07-21')
('002','2024-07-17'),
('002','2024-07-18'),
('002','2024-07-19'),
('002','2024-07-20'),
;

注：题目获取自网络