mysql进阶篇之查询连续登陆3天以上的用户

作者：YXN-sql 阅读量：121 发布日期：2022-10-04

具体思路:

1、因为每天用户登录次数可能不止一次，所以需要先将用户每天的登录日期去重。

2、再用row_number() over(partition by _ order by _)函数将用户id分组，按照登陆时间进行排序。

3、计算登录日期减去第二步骤得到的结果值，用户连续登陆情况下，每次相减的结果都相同。

4、按照id和日期分组并求和，筛选大于等于3的即为连续3天登陆的用户。

表信息如下图:

8.0版本实现方式

1、用户登录日期去重

select DISTINCT date(into_date) as 日期,stu_name from a;

2、用row_number() over()函数计数

SELECT
	*,row_number() over ( PARTITION BY stu_name ORDER BY 日期 ) AS cum 
FROM
	( SELECT DISTINCT DATE ( into_date ) AS 日期, stu_name FROM orde ) a;

3、日期减去计数值得到结果

SELECT
  *,subdate(日期, cum ) AS 结果 
FROM
  ( SELECT
    *,row_number() over ( PARTITION BY stu_name ORDER BY 日期 ) AS cum 
  FROM
    ( SELECT DISTINCT DATE ( into_date ) AS 日期, stu_name 
      FROM orde ) a 
  ) b;

4、根据id和结果分组并计算总和，大于等于7的即为连续登陆7天的用户

-- 4、根据id和结果分组并计算总和，大于等于7的即为连续登陆7天的用户
SELECT
	stu_name,
	count(*) 
FROM
	(-- 3、日期减去计数值得到结果
	SELECT
		*,
		subdate(日期, cum ) AS 结果 
	FROM
		(-- 2、用row_number() over()函数计数
		SELECT
			*,
			row_number() over ( PARTITION BY stu_name ORDER BY 日期 ) AS cum 
		FROM
			( -- 1、用户登录日期去重
			SELECT DISTINCT DATE ( into_date ) AS 日期, stu_name FROM orde ) a 
		) b 
	) c 
GROUP BY
	stu_name,结果 
HAVING
	count(*)>= 2;

注意：来自-博客园，并修改了前月最后一天和当月第一天会被认定不连续的问题

5.7版本实现方式

-- 声明用户变量，记录行号和登录用户名
set @row_number:=0,@customer_no:='';
SELECT stu_name, COUNT(*) as 天数 FROM (
-- 3 如果连续登录，subdate(date(into_date), num ) 结果会相等
	select  stu_name,subdate(date(into_date), num ) as num from 
	(
		 -- 2 记录行号;
		select 
			@row_number:=
				case 
					when @customer_no=l1.stu_name then @row_number+1
					else 1
				end as num,
			@customer_no:= l1.stu_name as stuName
			,stu_name,DATE_FORMAT(into_date,'%Y-%m-%d') as into_date 
		from 
			(
				-- 1 去除同一天登录多次
				select DISTINCT stu_name,DATE_FORMAT(into_date,'%Y-%m-%d') as into_date from orde  ORDER BY stu_name,into_date
			) l1
	) l2
) l3 
GROUP BY stu_name,num 
HAVING 天数>=3

 

YXN-sql

2022-10-04