SQL_连续登陆问题

方案1：使用ROW_NUMBER函数

• 1、先对数据user_id分组，根据用户的活动日期排序
• 2、用登录日期与rn求date_sub，得到的差值日期如果是相等的，则说明这两天肯定是连续的
  • 举例说，2023年1月1号、1月2号、1月3号；排名分别是1,2,3；现在用日期 - 排名 是不是都等于2022年12月31号
• 3、根据user_id和日期差sub_date分组，登录次数即为分组后的count(1)

1、针对对数据user_id分组，根据用户的活动日期排序

select
	user_id,
	activity_date,
	ROW_NUMBER() over(partition by user_id order by activity_date) as rn
from user_activity

2、用登录日期与rn求date_sub，得到的差值日期如果是相等的，则说明这两天肯定是连续的

SELECT
	user_id,
	activity_date,
	DATE_SUB(activity_date,INTERVAL rn DAY) as sub_date
from(
	select
		user_id,
		activity_date,
		ROW_NUMBER() over(partition by user_id order by activity_date) as rn
	from user_activity
)t1

3、根据user_id和日期差sub_date分组，登录次数即为分组后的count(1)

SELECT
	user_id,
	min(activity_date) as min_date,
	max(activity_date)  as max_date,
	count(1) as  login_times
from(
	SELECT
		user_id,
		activity_date,
		DATE_SUB(activity_date,INTERVAL rn DAY) as sub_date
	from(
		select
			user_id,
			activity_date,
			ROW_NUMBER() over(partition by user_id order by activity_date) as rn
		from user_activity
	)t1
)t2
group by user_id,sub_date
having login_times>=3;

从结果可以看出用户5,6,7,8存在连续登录3天及其以上的用户

方案2：使用lag和lead函数

• 1、针对每个user_id,先使用lead函数将当前日期后后一天日期求出来
• 2、针对每个用户，进行后一天的日期与当期日期相差值=1则属于连续登录。
  举例说，2023年1月1号、1月2号、1月3号；现在用日期2号 - 前后与它相差值2-1=1；3-2=1.是不是值都否为1呢。
• 3、针对用户分组，datediff函数求出最大活动时间和最小活动时间的天数，求出>=3天的用户

WITH LoginDates AS (
    SELECT 
        user_id, 
        activity_date,
        LEAD(activity_date) OVER (PARTITION BY user_id ORDER BY activity_date) AS next_login_date
    FROM 
        user_activity
)
SELECT 
    user_id
FROM 
    LoginDates
WHERE 
    DATEDIFF(activity_date, next_login_date) = 1
GROUP BY 
    user_id
HAVING 
    COUNT(*) >= 3;