找出连续登录五天的用户

题目描述

 找出每个用户连续五天登录的记录范围。

结果输出格式如下：

数据准备

with tmp as (
          select '小白鼠' as uid, '2023-3-3' as `login_date` union all
          select '小白鼠' as uid, '2023-3-4' as `login_date` union all
          select '小白鼠' as uid, '2023-3-5' as `login_date` union all
          select '小白鼠' as uid, '2023-3-7' as `login_date` union all
          select '小白鼠' as uid, '2023-3-8' as `login_date` union all
          select '小白鼠' as uid, '2023-3-9' as `login_date` union all
          select '小白鼠' as uid, '2023-3-10' as `login_date` union all
          select '小白鼠' as uid, '2023-3-11' as `login_date` union all
          select '小白鼠' as uid, '2023-3-12' as `login_date` union all
          select '小黑鼠' as uid, '2023-3-10' as `login_date` union all
          select '小黑鼠' as uid, '2023-3-10' as `login_date` union all
          select '小黑鼠' as uid, '2023-3-11' as `login_date` union all
          select '小黑鼠' as uid, '2023-3-12' as `login_date` union all
          select '小黑鼠' as uid, '2023-3-13' as `login_date` union all
          select '小黑鼠' as uid, '2023-3-14' as `login_date` union all
          select '小黑鼠' as uid, '2023-3-15' as `login_date`
)
select * from tmp
;

解题思路

1、这就是一道连续登录问题，因此通过登录日期去重后与 row_number() 的差值就可以得出连续登录的范围。数据中日期格式不是标准日期格式，因此需要通过 date() 将日期转换为标准日期格式。

不理解连续登录问题的可以查看这篇博客。：最大连续登录天数-CSDN博客文章浏览阅读86次，点赞5次，收藏4次。最大连续登录天数问题是SQL面试题目中的一个经典题目。https://blog.csdn.net/weixin_55015548/article/details/134063687?spm=1001.2014.3001.5502

2、上图中根据diff分组后每一组都是连续登录的日期。通过lead()进行开窗分组取下面第四个值，因为数据是连续的，因此能取到值说明当前日期往后有五天登录的记录。否则返回null值。

lead(dt, 4, null) over (partition by uid, diff order by dt) 5_day

3、最后过滤掉 5_day 字段为null的数据，再排个序即可。

完整HQL代码

with tmp as (
          select '小白鼠' as uid, '2023-3-3' as `login_date` union all
          select '小白鼠' as uid, '2023-3-4' as `login_date` union all
          select '小白鼠' as uid, '2023-3-5' as `login_date` union all
          select '小白鼠' as uid, '2023-3-7' as `login_date` union all
          select '小白鼠' as uid, '2023-3-8' as `login_date` union all
          select '小白鼠' as uid, '2023-3-9' as `login_date` union all
          select '小白鼠' as uid, '2023-3-10' as `login_date` union all
          select '小白鼠' as uid, '2023-3-11' as `login_date` union all
          select '小白鼠' as uid, '2023-3-12' as `login_date` union all
          select '小黑鼠' as uid, '2023-3-10' as `login_date` union all
          select '小黑鼠' as uid, '2023-3-10' as `login_date` union all
          select '小黑鼠' as uid, '2023-3-11' as `login_date` union all
          select '小黑鼠' as uid, '2023-3-12' as `login_date` union all
          select '小黑鼠' as uid, '2023-3-13' as `login_date` union all
          select '小黑鼠' as uid, '2023-3-14' as `login_date` union all
          select '小黑鼠' as uid, '2023-3-15' as `login_date`
), t1 as (
    select uid, date(login_date) dt
    from tmp group by uid, login_date
), t2 as (
    select uid, dt,
        date_sub(dt, row_number() over (partition by uid order by dt)) diff
    from t1
), t3 as (
    select
        uid, dt as curr, diff,
        lead(dt, 4, null) over (partition by uid, diff order by dt) 5_day
    from t2
)
select *
from t3
where 5_day is not null
order by uid, curr
;