hive表中连续N天问题的实现

1、连续登录用户
用户登录数据表中有两个字段，分别是userid和logintime

userId表示唯一的用户ID，唯一标识一个用户，loginTime表示用户的登录日期，例如第一条数据就表示A在2021年3月22日登录了。
现在需要对用户的登录次数进行统计，得到连续登陆N（N>=2）天的用户。
例如统计连续两天的登录的用户，需要返回A和C，因为A在22/23/24都登录了，所以肯定是连续两天登录，C在22和23号登录了，所以也是连续两天登录的。
例如统计连续三天的登录的用户，只能返回A，因为只有A是连续三天登录的。

2.窗口函数实现
窗口函数lead
功能：用于从当前数据中基于当前行的数据向后偏移取值
语法：lead(colName，N，defautValue)
colName：取哪一列的值
N：向后偏移N行
defaultValue：如果取不到返回的默认值

分析
当前数据中记录了每个用户每一次登陆的日期，一个用户在一天只有1条信息，我们可以基于用户的登陆信息，找到如下规律：
连续两天登陆 ： 用户下次登陆时间 = 本次登陆以后的第二天
连续三天登陆 ： 用户下下次登陆时间 = 本次登陆以后的第三天
……依次类推。
我们可以对用户ID进行分区，按照登陆时间进行排序，通过lead函数计算出用户下次登陆时间，通过日期函数计算出登陆以后第二天的日期，如果相等即为连续两天登录。

建表

--切换数据库
use db_function;

--建表
create table tb_login(
  userid string,
  logintime string
) row format delimited fields terminated by '\t';
1
2
3
4
5
6
7
8

创建数据：vim /export/data/login.log

A       2021-03-22
B       2021-03-22
C       2021-03-22
A       2021-03-23
C       2021-03-23
A       2021-03-24
B       2021-03-24
1
2
3
4
5
6
7

加载数据

load data local inpath '/export/data/login.log' into table tb_login;
1

查询数据

select * from tb_login;
1

统计连续2天登录

select
  userid,
  logintime,
  --本次登陆日期的第二天
  date_add(logintime,1) as nextday,
  --按照用户id分区，按照登陆日期排序，取下一次登陆时间，取不到就为0
  lead(logintime,1,0) over (partition by userid order by logintime) as nextlogin
from tb_login;
1
2
3
4
5
6
7
8

with t1 as (
  select
    userid,
    logintime,
    --本次登陆日期的第二天
      date_add(logintime,1) as nextday,
    --按照用户id分区，按照登陆日期排序，取下一次登陆时间，取不到就为0
     lead(logintime,1,0) over (partition by userid order by logintime) as nextlogin
from tb_login )
select distinct userid from t1 where nextday = nextlogin;
1
2
3
4
5
6
7
8
9
10

统计连续3天登录

select
  userid,
  logintime,
  --本次登陆日期的第三天
  date_add(logintime,2) as nextday,
  --按照用户id分区，按照登陆日期排序，取下下一次登陆时间，取不到就为0
  lead(logintime,2,0) over (partition by userid order by logintime) as nextlogin
from tb_login;
1
2
3
4
5
6
7
8

with t1 as (
select
  userid,
  logintime,
  --本次登陆日期的第三天
  date_add(logintime,2) as nextday,
  --按照用户id分区，按照登陆日期排序，取下下一次登陆时间，取不到就为0
  lead(logintime,2,0) over (partition by userid order by logintime) as nextlogin
from tb_login )
select distinct userid from t1 where nextday = nextlogin;
1
2
3
4
5
6
7
8
9
10

统计连续N天登录

select
  userid,
  logintime,
  --本次登陆日期的第N天
  date_add(logintime,N-1) as nextday,
  --按照用户id分区，按照登陆日期排序，取下下一次登陆时间，取不到就为0
  lead(logintime,N-1,0) over (partition by userid order by logintime) as nextlogin
from tb_login;
1
2
3
4
5
6
7
8

在这张表中，只要nextlogin和nextday相等则表示符合连续N天登录，再进行用户id去重即可得到连续N天登录的用户id，
所以将上面的表用with封装成临时表t1，再对临时表进行筛选即可得出答案。

with t1 as (
select
  userid,
  logintime,
  --本次登陆日期的第三天
  date_add(logintime,2) as nextday,
  --按照用户id分区，按照登陆日期排序，取下下一次登陆时间，取不到就为0
  lead(logintime,2,0) over (partition by userid order by logintime) as nextlogin
from tb_login )
select distinct userid from t1 where nextday = nextlogin;
1
2
3
4
5
6
7
8
9
10