43. 间断连续登录用户问题

题目需求

现有各用户的登录记录表（login_events）如下，表中每行数据为：一个用户何时登录了平台。

现要求统计各用户最长的连续登录天数，间断一天也算作连续，例如：一个用户在1,3,5,6登录，则视为连续6天登录。

期望结果如下（截取部分）：

user_id (用户id)	max_day_count (最大连续天数)
100	3
101	6
102	3
104	3
105	1

需要用到的表：

用户的登录记录表：login_events

user_id	login_datetime
100	2021-12-01 19:00:00
100	2021-12-01 19:30:00
100	2021-12-02 21:01:00

思路一

实现一

-- 3） 计算日期差
select user_id,-- 统计每个用户每次连续登录的日期聚合-- collect_set(login_datetime)datediff(collect_set(login_datetime)[size(collect_set(login_datetime)) - 1], collect_set(login_datetime)[0]) +1 as max_day_count
from (-- 2） 求 sumselect user_id,login_datetime,sum(flag) over (partition by user_id order by login_datetime) as sumfrom (select user_id,-- 本地登录时间date_format(login_datetime, 'yyyy-MM-dd')                                       as login_datetime,-- 1.1） 获取该用户上次登录时间-- 1.2）计算该用户 (本次登录时间 - 上次登录时间), if(本次登录时间 - 上次登录时间) > 2, 标记为1(不连续登录标记)，否则为0if(datediff(date_format(login_datetime, 'yyyy-MM-dd'),-- 注意: 不可以使用不存在的日期，例如 '0000-00-00'lag(date_format(login_datetime, 'yyyy-MM-dd'), 1, '0001-01-01')over (partition by user_id order by login_datetime)) > 2, 1, 0) as flagfrom login_events) t1) t2
group by user_id, sum;

题目来源

http://practice.atguigu.cn/#/question/43/desc?qType=SQL