一、题目

现有各用户的登录记录表zbj_login_events如下，表中每行数据表达的信息是一个用户何时登录了平台。 现要求统计各用户最长的连续登录天数，间断一天也算作连续，例如：一个用户在1,3,5,6登录，则视为连续4天登录。

二、思路

这是一个连续性问题，但是又一些新变化，间隔一天也算连续，所以只使用日期差值计算的方法就不能实现目标了，下面看一下具体实现步骤：

第一步：计算登录日期与登录次序的差值日期。

可以发现一个规律：如果连续登录则date_diff相同，如果登录日期相隔一天则date_diff也相隔一天，比如user_id：101的date_diff有2021-11-30和2021-12-01，那么这两个时间段相隔一天。

第二步：基于第1步的结果，如果相隔一天则date_diff是连续的，那么又变成了一个连续问题。首先对userid和date_diff，然后计算date_diff与次序的差值日期

第三步：计算用户连续登录总天数，包含间隔一天的情况

第四步：取每个用户连续登录最长的天数

三、解答

with tab_user_login_diff as (
  -- 第一步：计算登录日期与登录次序的差值日期。
  select 
    user_id,
    log_date,
    date_sub(log_date, rn) date_diff,
    rn
  from (
      select 
        user_id,
        log_date,
        row_number() over(partition by user_id order by log_date) rn
      from (
        select 
          user_id,
          date_format(login_datetime, 'yyyy-MM-dd') log_date
        from zbj_login_events
        group by user_id,
          date_format(login_datetime, 'yyyy-MM-dd')
      ) t11
  ) t21
),

tab_res_detail as (
  -- 第二步：基于第1步的结果，如果相隔一天则date_diff是连续的，那么又变成了一个连续问题。首先对userid和date_diff，然后计算date_diff与次序的差值日期
  select  
    user_id,
    date_diff,
    date_sub(date_diff, rn) date_diff2,
    day_cnt
  from (
    select 
      user_id,
      date_diff,
      day_cnt,
      row_number() over(partition by user_id order by date_diff) rn
    from (
      select 
        user_id,
        date_diff,
        count(*) day_cnt
      from tab_user_login_diff
      group by user_id, 
        date_diff
    ) t11
  ) t21
) 

select 
  -- 第四步：取每个用户连续登录最长的天数
  user_id,
  max(day_cnt) day_cnt
from (
  -- 第三步：计算连续登录总天数，包含间隔一天的情况
  select 
    user_id,
    date_diff2,
    sum(day_cnt) day_cnt
  from tab_res_detail
  group by user_id,
    date_diff2
) t11
group by user_id

结果：

数据

# 建表
--建表语句
create table if not exists zbj_login_events(
    user_id        int comment '用户id',
    login_datetime string comment '登录时间'
)comment '直播间访问记录'
stored as parquet;
--数据插入
INSERT overwrite table zbj_login_events
VALUES (100, '2021-12-01 19:00:00'),
       (100, '2021-12-01 19:30:00'),
       (100, '2021-12-02 21:01:00'),
       (100, '2021-12-03 11:01:00'),
       (101, '2021-12-01 19:05:00'),
       (101, '2021-12-01 21:05:00'),
       (101, '2021-12-02 11:05:00'),
       (101, '2021-12-03 21:05:00'),
       (101, '2021-12-05 15:05:00'),
       (101, '2021-12-06 19:05:00'),
       (101, '2021-12-10 10:05:00'),
       (102, '2021-12-01 19:55:00'),
       (102, '2021-12-01 21:05:00'),
       (102, '2021-12-02 21:57:00'),
       (102, '2021-12-03 19:10:00'),
       (104, '2021-12-04 21:57:00'),
       (104, '2021-12-02 22:57:00'),
       (105, '2021-12-01 10:01:00');

注：题目获取自网络