526互联

HIVE-最大连续登陆天数

发布时间 2023-08-30 13:53:17作者: 打代码的彭于晏

一、背景

在网站平台类业务需求中用户的「最大登陆天数」，需求比较普遍。
原始数据：

u0001 2019-10-10
u0001 2019-10-11
u0001 2019-10-12
u0001 2019-10-14
u0001 2019-10-15
u0001 2019-10-17
u0001 2019-10-18
u0001 2019-10-19
u0001 2019-10-20
u0002 2019-10-20

说明：数据是简化版，两列分别是user_id,log_in_date。现实情况需要从采集数据经过去重，转换得到以上形式数据。
我们先建表并且将数据导入Hive：

create table test.user_log_1 (
  user_id string,
  log_in_date string) 
row format delimited fields terminated 
by ' ';

load data local inpath '/var/lib/hadoop-hdfs/data/user_log.txt' into table test.user_log_1 ;

查看一下数据：

hive> select * from test.user_log_1 ;
OK
u0001   2019-10-10
u0001   2019-10-11
u0001   2019-10-12
u0001   2019-10-14
u0001   2019-10-15
u0001   2019-10-17
u0001   2019-10-18
u0001   2019-10-19
u0001   2019-10-20
u0002   2019-10-20
Time taken: 0.076 seconds, Fetched: 10 row(s)

二、算法

核心是按访问时间排序，登陆时间列减去排序后的序列号，得到一个日期值，按这个值分组计数即可。

第一步：排序

按照user_id分组，并且按照日期log_in_date排序：

select 
	user_id, 
	log_in_date,
	row_number() over(partition by user_id order by log_in_date) as rank 
from test.user_log_1;

结果：

u0001   2019-10-10  1
u0001   2019-10-11  2
u0001   2019-10-12  3
u0001   2019-10-14  4
u0001   2019-10-15  5
u0001   2019-10-17  6
u0001   2019-10-18  7
u0001   2019-10-19  8
u0001   2019-10-20  9

u0002   2019-10-20  1

这里可以看出，u0001这个用户最大连续登录天数是4天，使用后面计算方法计算后可以验证。

第二步：第二列与第三列做日期差值

可以看出规律，日期小的，行号也小；如果将日期跟行号做差值，连续登录的差值应该是一样的。

select 
	user_id,
	date_sub(log_in_date, rank) dts 
from
(
  select 
  	user_id, 
  	log_in_date, 
  	row_number() over(partition by user_id order by log_in_date) as rank 
  from test.user_log_1
)m;

结果：

u0001   2019-10-09
u0001   2019-10-09
u0001   2019-10-09
u0001   2019-10-10
u0001   2019-10-10
u0001   2019-10-11
u0001   2019-10-11
u0001   2019-10-11
u0001   2019-10-11
u0002   2019-10-19

显然可以看出，最大连续连续登录是4次。

第三步:按第二列分组求和

select 
	user_id,
	dts, 
	count(1) num 
from 
(
  select 
  	user_id, 
  	date_sub(log_in_date, rank) dts 
  from 
  	(
    	select 
      	user_id, 
      	log_in_date, 
      	row_number() over(partition by user_id order by log_in_date) as rank 
      from 
      	test.user_log_1
    )m
)m2 
group by 
	user_id, dts;

结果：

u0001   2019-10-09  3
u0001   2019-10-10  2
u0001   2019-10-11  4
u0002   2019-10-19  1

第四步：求最大次数

已经算出了，每个用户连续登录天数序列，接下取每个用户最大登录天数最大值即可：

select 
	user_id, 
	max(num) 
from 
	(
    select 
    	user_id, 
    	dts, 
    	count(1) num 
    from 
    	(
        select 
        	user_id, 
        	date_sub(log_in_date, rank) dts 
        from 
        	(
            select 
            	user_id, 
            	log_in_date,
            	row_number() over(partition by user_id order by log_in_date) as rank 
            from 
            	test.user_log_1
          )m
      )m2 
    group by 
    	user_id, dts
  )m3 
group by 
	user_id;

结果跟我们的预期是一致的，用户u0001最大登录天数是4。

u0001   4
u0002   1

三、扩展（股票最大涨停天数）

我们知道股票市场，比如咱们的A股，周末是不开盘的，那么一只股票如果上周五涨停，本周一接着涨停，这算是连续2天涨停，使用上面这种方法是不行的，使用lead函数试试：

select 
	user_id, 
	log_in_date, 
	lead(log_in_date) over(partition by user_id order by log_in_date) end_date 
from 
	test.user_log_1;

u0001   2019-10-10  2019-10-11
u0001   2019-10-11  2019-10-12
u0001   2019-10-12  2019-10-14
u0001   2019-10-14  2019-10-15
u0001   2019-10-15  2019-10-17
u0001   2019-10-17  2019-10-18
u0001   2019-10-18  2019-10-19
u0001   2019-10-19  2019-10-20
u0001   2019-10-20  NULL
u0002   2019-10-20  NULL

思路：
上面结果一共有3列，第一列是uid，通过lead函数，后面两列都是日期，那么两列日期都取值周一到周五之间，也就是说数据里面只有工作日日期，没有周末的数据，可以提前过滤使得数据满足，既然要连续，那么：

  1. 如果第三列的日期，减去第二列的日期，差值等于1，显然是连续的；
  2. 如果第三列的日期，减去第二列的日期，差值等于3，但是第三列日期是星期一，那么也算是连续了；