MySQL如何计算连续登录天数
作者:BurningSilence 时间:2024-01-17 18:05:00
建表、insert数据
create table tmp_login (
user_id int(11) ,
login_date datetime
);
insert into tmp_login values(2,'2020-05-29 11:12:12');
insert into tmp_login values(2,'2020-05-29 15:12:12');
insert into tmp_login values(2,'2020-05-30 11:12:12');
insert into tmp_login values(2,'2020-05-31 11:12:12');
insert into tmp_login values(2,'2020-06-01 11:12:12');
insert into tmp_login values(2,'2020-06-02 11:12:12');
insert into tmp_login values(2,'2020-06-03 11:12:12');
insert into tmp_login values(2,'2020-06-04 11:12:12');
insert into tmp_login values(2,'2020-06-05 11:12:12');
insert into tmp_login values(2,'2020-06-06 11:12:12');
insert into tmp_login values(2,'2020-06-07 11:12:12');
insert into tmp_login values(7,'2020-06-01 11:12:12');
insert into tmp_login values(7,'2020-06-02 11:12:12');
insert into tmp_login values(7,'2020-06-03 11:12:12');
insert into tmp_login values(7,'2020-06-05 11:12:12');
insert into tmp_login values(7,'2020-06-06 11:12:12');
insert into tmp_login values(7,'2020-06-07 11:12:12');
insert into tmp_login values(7,'2020-06-08 11:12:12');
方法一 row_number()
1.查询所有用户的每日登录记录
select distinct user_id, date(login_date) as days
from tmp_login;
2.row_number()计算登录时间排序
select user_id, days, row_number() over(partition by user_id order by days) as rn
from (
select distinct user_id, date(login_date) as days from tmp_login) t1;
3.用登录时间 - row_number(),如果得到的日期相同,则认为是连续登录日期
select *, date_sub(days, interval rn day) as results
from(
select user_id, days, row_number() over(partition by user_id order by days) as rn
from (
select distinct user_id, date(login_date) as days from tmp_login) t1
) t2;
4. 按user_id、results分组就可得出连续登录天数
select user_id, count(*) as num_days
from (
select *, date_sub(days, interval rn day) as results
from(
select user_id, days, row_number() over(partition by user_id order by days) as rn
from (
select distinct user_id, date(login_date) as days from tmp_login) t1
) t2) t3
group by user_id , results;
直接用日期减去row_number(),不用date_sub的话,遇到登录日期跨月时会计算错误,
方法二lead() 或 lag()
这种情况适合的场景是,需要查找连续登录超过n天的用户,n为确定值
如果n为4,即计算连续登录超过4天的用户
-- lead计算连续登录
select distinct user_id
from(
select user_id, days, datediff(lead(days, 3, '1970-01-01') over(partition by user_id order by days), days)as results
from (
select distinct user_id, date(login_date) as days from tmp_login) t1) t2
where results = 3;
连续登录4天,则日期差应该为3。
来源:https://blog.csdn.net/BurningSilence/article/details/121558764
标签:MySQL,计算,连续登录,天数
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
selenium WebDriverWait类等待机制的实现
2022-12-18 13:07:21
CentOS6.5设置Django开发环境
2022-09-29 22:55:30
PHP程序员最常犯的11个MySQL错误
2012-01-05 19:13:02
python3.7将代码打包成exe程序并添加图标的方法
2021-01-17 08:29:32
![](https://img.aspxhome.com/file/2023/6/67286_0s.png)
Python生成8位随机字符串的方法分析
2023-06-15 20:53:08
np.ones的使用小结
2021-05-20 06:55:34
![](https://img.aspxhome.com/file/2023/7/101327_0s.png)
python3使用SMTP发送简单文本邮件
2023-09-28 10:07:24
![](https://img.aspxhome.com/file/2023/0/109430_0s.jpg)
python自带缓存lru_cache用法及扩展的使用
2022-06-16 08:48:15
hta应用—代码统计工具
2024-01-31 14:40:44
python之从文件读取数据到list的实例讲解
2021-11-11 08:04:26
为什么Python中没有"a++"这种写法
2023-12-04 09:40:57
vue-quill-editor插入图片路径太长问题解决方法
2024-05-29 22:46:29
![](https://img.aspxhome.com/file/2023/5/123005_0s.png)
Vue中的Object.defineProperty全面理解
2024-05-29 22:44:02
SQL SERVER中各类触发器的完整语法及参数说明
2024-01-15 20:54:17
Python列表切片操作实例总结
2023-01-30 16:06:57
javascript验证form表单数据的案例详解
2024-04-10 10:39:05
![](https://img.aspxhome.com/file/2023/9/137059_0s.png)
MySQL数据库事务与锁深入分析
2024-01-28 19:03:12
一篇文章带你入门Python正则表达式
2021-11-29 03:00:56
![](https://img.aspxhome.com/file/2023/6/70126_0s.jpg)
Asp测试网速代码(带进度条)
2007-11-12 13:09:00
![](https://img.aspxhome.com/file/UploadPic/200711/12/20071112131916460s.jpg)
Python3实现带附件的定时发送邮件功能
2023-07-08 23:33:57