关于大数据:Hive计算最大连续登陆天数

一、背景
在网站平台类业务需要中用户的「最大登陆天数」，需要比拟广泛。

原始数据：

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)

二、算法
外围是按拜访工夫排序，登陆工夫列减去排序后的序列号，失去一个日期值，按这个值分组计数即可。大数据培训

1. 第一步：排序
   依照 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 天，应用前面计算方法计算后能够验证。

2. 第二步：第二列与第三列做日期差值
   能够看出法则，日期小的，行号也小；如果将日期跟行号做差值，间断登录的差值应该是一样的。

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 次。

3. 第三步: 按第二列分组求和
   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

4. 第四步：求最大次数
   曾经算出了，每个用户间断登录天数序列，接下取每个用户最大登录天数最大值即可：

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，显然是间断的；
如果第三列的日期，减去第二列的日期，差值等于 3，然而第三列日期是星期一，那么也算是间断了；
以上两种条件综合，就能计算出股票的最大间断涨停天数了，你学废了吗。