思路分析:
本题特点在于间断一天登录也认为是连续登录
首先需要对数据做格式化和去重操作user_id,login_date
(1)同会话划分问题一样,先求上一次登录日期
(2)对时间差打标签flag
(3)给每一个会话的建立一个唯一标识(时间差不超过1天的标识相同)
(4)user_id,flag分组下最大最小时间差就是连续登录时间
注:核心点依然是为会话打标签
代码实现:
WITH tmp as(
--数据格式化、去重
SELECT
user_id,
date_format(login_datetime, "yyyy-MM-dd") login_date
from login_events
group by
user_id,
date_format(login_datetime, "yyyy-MM-dd")
)
select
user_id,
--4.user_id,flag分组下最大最小时间差就是连续登录时间
datediff(max(login_date), min(login_date)) + 1 max_day_count
from(
select
user_id,
login_date,
--3.给每一个会话建立一个唯一标识
concat(
user_id,
sum(flag) over(partition by user_id order by login_date)
) as flag
from(
SELECT
user_id,
login_date,
--2.为时间差打标签
if(login_date - last_login > 1, 1, 0) as flag
from(
SELECT
user_id,
login_date,
--1.取用户上一次登录的日期
lag(login_date, 1, '1970-01-01') over(PARTITION by user_id order by login_date) last_login
from tmp
) t1
) t2
) t3
group by
user_id,
flag;