hive之连续登录问题（2）

问题

登陆日志，计算每个人连续登陆的最大天数！(注意：断一天也算连续)
数据如下所示：
id dt
01 2021-02-28
01 2021-03-01
01 2021-03-02
01 2021-03-04
01 2021-03-05
01 2021-03-06
01 2021-03-08
02 2021-03-01
02 2021-03-02
02 2021-03-03
02 2021-03-06
03 2021-03-06

方法1

思路：等差数列

1、按照id分组同时按照dt排序，求rk
2、将每行日期减去rk值得到flag标志，如果之前是连续的日期，则相减后为相同的日期,flag相同
3、按照id和flag分组，计算count(*)得数连续的天数
4、按照id分组同时按照flag排序，求rk
5、将每行flag减去rk值得到new_flag标志
6、按照id和new_flag分组，计算连续的天数
7、对id进行分组

sql 实现：

select 
	id
	,max(days) as days
from 
(select 
	id 
	,new_flag
	,sum(days)+count(*)-max(1)  as days
from 
(
select 
	id
	,flag
	,days 
	,falg -rank as new_falg
from 
(select
id
,flag
,days  
,rank() over(partition by id order by flag) as rank 
from 
(select 
	id
	,flag
	,count(dt) as days 
from (
select 
	id 
	,dt
	,dt -rk as flag 
from (
select 
	id
	,dt 
	rank()over(partition by id order by dt) rk
from tablename;
)t1 
)t2 
group by 
	id
	,flag
)t3
)t4
)t5
group by 
	id 
	,new_flag
	)t6
group by 
	id;

备注：这种方法有个弊端，像俄罗斯套娃，当条件发生改变，比如断5天也算连续，你要重复5次；

方法2

思路：采用lag开窗函数

sql 实现：

select 
	id
	,flag
	,datediff(max(dt),min(dt))+1
	from 
	(
	select 
	id
	,dt
	sum(if(dtDiff >2,1,0)) over (partition by id order by dt) as flag 
	from (
	select 
	id
	,dt
	,datediff(dt,lagDt) as dtDiff
	from (
	select 
	id
	,dt 
	,lag(dt,1,'1970-01-01') over(partition by id order by dt) lagDt
	from tablename
	)t1
	)t2
	)t3
	group by 
	id
	,flag

备注：断一天也算连续，则 sum(if(dtDiff >2,1,0))处为2
断n天也算连续, 则 sum(if(dtDiff >n+1,1,0))处为n+1