hive高频面试题1.2每个用户连续登陆最大天数

数据:
login表
uid,date1
1,2019-08-01
1,2019-08-02
1,2019-08-03
2,2019-08-01
2,2019-08-02
3,2019-08-01
3,2019-08-03
4,2019-07-28
4,2019-07-29
4,2019-08-01
4,2019-08-02
4,2019-08-03

结果如下：
uid cnt_days
1 3
2 2
3 1
4 3

1.先对数据按照uid分组，date1升序进行排序，然后观察

select
uid,
date1,
row_number() over(partition by uid order by date1) date2
from login

 

2.将date1-date2得到的日期如果是连续的，那么值就是相等的

select
uid,
date1,
date_sub(date1,date2) dates
from
(
select
uid,
date1,
row_number() over(partition by uid order by date1) date2
from login
) login1

 3.观察dates这列，需要对uid和dates分组就sum（dates）

select
uid,
count(dates)
from
(
select
uid,
date1,
date_sub(date1,date2) dates
from
(
select
uid,
date1,
row_number() over(partition by uid order by date1) date2
from login
) login1
) login2
group by uid,dates

 4.分组就max就可以得到结果了

select
uid,
max(cnt_days) cnt_days
from
(
select
uid,
count(dates) cnt_days
from
(
select
uid,
date1,
date_sub(date1,date2) dates
from
(
select
uid,
date1,
row_number() over(partition by uid order by date1) date2
from login
) login1
) login2
group by uid,dates
) login3
group by uid