SQL查询连续登陆用户数据笔记

题一：SQLW11 查询连续登陆的用户

描述：某产品在2022年2月8日各端口用户注册信息及后几日登录信息如下，

①用户注册信息表register_tb (user_id-用户id, reg_time-注册时间, reg_port-注册端口)

②用户登录信息表login_tb(log_id-登录动作id,user_id-用户id, log_time-登录时间,  log_port-登录端口)

问题：请查询连续登陆不少于3天的新注册用户？

注：登录表为单日随机一次登录数据，该题忽略单日多次登录情况。

要求：输出user_id并升序排序；

 方法一：

查询用户信息表中各用户登录时间及以用户ID进行分组以登录时间进行排序的编号。

 row_number()函数

【是一种常用的窗口函数，类型rank】

语法格式：row_number() over(partition by 分组列 order by 排序列 desc)

SELECT
	user_id,
	DATE(log_time) AS d,
	ROW_NUMBER () over ( PARTITION BY user_id ORDER BY DATE(log_time)) AS rn
FROM
	login_tb

 结果：

 查询各用户的用户登录时间，以及排序编号，以及登录时间减去指定的时间间隔日期。

DATE_SUB()函数

【从DATE或DATETIME值中减去时间值(或间隔)】

SELECT user_id,d,rn,
	DATE_SUB(d,INTERVAL rn DAY) AS sub_date
FROM
	(SELECT
			user_id,
			DATE(log_time) AS d,
			ROW_NUMBER () over (PARTITION BY user_id ORDER BY DATE(log_time)) AS rn
		FROM
			login_tb) as t1;

结果

SELECT user_id,COUNT(*) as num
FROM
(SELECT user_id,d,rn,
	DATE_SUB(d,INTERVAL rn DAY) AS sub_date
FROM
	(SELECT
			user_id,
			DATE(log_time) AS d,
			ROW_NUMBER () over (PARTITION BY user_id ORDER BY DATE(log_time)) AS rn
		FROM
			login_tb) as t1)as t2
where user_id in (select user_id from register_tb)
GROUP BY user_id
HAVING >=3;

MySQL窗口函数

MySQL中的分析函数，也称为窗口函数：

1. **CUME_DIST**：计算一组值中某个值的累积分布。
2. **DENSE_RANK**：基于ORDER BY子句为每个分区内的行分配排名，相等的值会分配相同的排名，并且排名之间不会有间隔。
3. **FIRST_VALUE**：返回窗口框架中指定表达式的第一行的值。
4. **LAG**：返回当前行在分区中前N行的值，如果不存在前N行，则返回NULL。
5. **LAST_VALUE**：返回窗口框架中指定表达式的最后一行的值。
6. **LEAD**：返回当前行在分区中后N行的值，如果不存在后N行，则返回NULL。
7. **NTH_VALUE**：返回窗口框架中第N行的值。
8. **NTILE**：将每个窗口分区的行分配到指定数量的排名组中。
9. **PERCENT_RANK**：计算分区或结果集中某行的百分位排名。
10. **RANK**：与DENSE_RANK类似，但是当两行或多行具有相同的排名时，排名之间会有间隔。
11. **ROW_NUMBER**：为每个分区内的行分配一个连续的整数。

这些是MySQL中主要的窗口函数，它们提供了强大的数据分析和查询能力。

方法二：

lead()函数 

【用于统计窗口内往下第n行值】

SELECT user_id
FROM
(SELECT user_id
       ,DATE(log_time) AS log_date #登录日期
       ,LEAD(DATE(log_time), 1) OVER(PARTITION BY user_id ORDER BY log_time) AS y1
       ,LEAD(DATE(log_time), 2) OVER(PARTITION BY user_id ORDER BY log_time) AS y2
FROM login_tb) as t
WHERE DATEDIFF(y1,log_date) = 1
  AND DATEDIFF(y2,y1) = 1
AND user_id in (select user_id from register_tb)
ORDER BY user_id;

解题思路：至少连续三天的条件为判断准则，只要lead一次和lead两次时间间隔为1天即符合。

lead 类似excel加辅助列，y1列是DATE(log_time)登录日期+1，y2列是DATE(log_time)登录日期+2；

题二：SQLW11 查询连续登陆的用户

描述：

你正在搭建一个用户活跃度的画像，其中一个与活跃度相关的特征是“最长连续登录天数”， 请用SQL实现“2023年1月1日-2023年1月31日用户最长的连续登录天数”。

tb_dau表(user_id-用户id, fdate-登录时间)

 解题思路：①使用ROW_NUMBER()函数进行分组，用用户登录日期做排序，生成排序编号数。

select
		user_id,#用户ID
		fdate,#用户登录日期
		ROW_NUMBER() over(partition by user_id order by fdate) as rn #排序
from tb_dau
where fdate between '2023-01-01'and '2023-01-31'

 解题思路：②DATE_SUB函数用于确定从登录日期减去排序编号数，生成日期与排序的差值日期。(其目的在于通过这样的计算，如果日期是连续的，那么同一用户的 sub_date 值应该是相等的，后续可以据此来判断连续的日期段。)

SELECT
		user_id,fdate,rn,
		DATE_SUB(fdate,INTERVAL rn DAY) as sub_date#日期与排序的差值日期
	from(
		select
			user_id,fdate,
			ROW_NUMBER() over(partition by user_id order by fdate) as rn #排序
		from tb_dau
where fdate between '2023-01-01'and '2023-01-31'
	)t1;

 结果

  解题思路：③在t1表的基础上，按照 user_id 和 sub_date 进行分组,count(*) as login_times查询连续登录天数。

SELECT
	user_id,count(*) as login_times
from(
	SELECT
		user_id,fdate,DATE_SUB(fdate,INTERVAL rn DAY) as sub_date#日期与排序的差值日期
	from(
		SELECT
			user_id,fdate,ROW_NUMBER() over(partition by user_id order by fdate) as rn
		from tb_dau
where fdate between '2023-01-01'and '2023-01-31')t1
)t2
group by user_id,sub_date;

 增加min(fdate)和max(fdate)增强理解。

  解题思路：④在t2表的基础上，按照 user_id 进行分组,并查询最大天数，即为最长连续登录天数

SELECT user_id,max(login_times) as max_consec_days
FROM
(SELECT
	user_id,count(*) as login_times
from(
	SELECT
		user_id,fdate,DATE_SUB(fdate,INTERVAL rn DAY) as sub_date#日期与排序的差值日期
	from(
		SELECT
			user_id,fdate,ROW_NUMBER() over(partition by user_id order by fdate) as rn
		from tb_dau
where fdate between '2023-01-01'and '2023-01-31')t1
)t2
group by user_id,sub_date
) as t3
GROUP BY user_id;

SQL练习题解题思路梳理，仅做练习草稿。

参考 ：拿捏SQL：以“统计连续登录天数超过3天的用户“为例拿捏同类型SQL需求_sql连续登录3天用户数-优快云博客

 MySQL date_sub()函数_mysql datesub函数-优快云博客