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