求年度最大连续登陆天数

求年度最大连续登陆天数?

pk,20210801
pk,20210802
pk,20210803
pk,20210804
pk,20210806
pk,20210807
pk,20210808
pk,20210811
pk,20210812
ruoze,20210730
ruoze,20210731
ruoze,20210801
ruoze,20210804
ruoze,20210806

思路:

提取关键词年度、连续登陆、最大

解决:

在Spark SQL中有DayOfYear()函数来确定当前传入的时间是一年中的哪一天。
对day使用row_number() 开窗，按照用户分区、时间排序。
对每一行天数减去排名，得到一个常数值diff。
对上面的结果按照用户和diff分区，并对diff求count()，得到succession_day。
对上面的结果按照用户分区，并对succession_day求max()，得到用户的最大连续登陆天数。

代码:

select 
	name,max(succession_day) max_succession_day
from(
	select
		name,count(1) succession_day
	from(
		select 
			name,
			date,
			dayofyear(from_unixtime(unix_timestamp(date,'yyyyMMdd'),'yyyy-MM-dd')) - row_number() over(partition by name order by date) diff 
		from 
			succession_login)
	group by name,diff)
group by name

结果:

pk	4
ruoze	3