SQL 查询连续登录的用户情况
这篇文章主要介绍了SQL 查询连续n天登录的用户情况,本文以3天为例,通过使用mysql工具sql语句给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下
以连续3天为例,使用工具:MySQL。
1.创建SQL表:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | create table if not exists orde(id varchar (10), date datetime,orders varchar (10)); insert into orde values ( '1' , '2019/1/1' ,10 ); insert into orde values ( '1' , '2019/1/2' ,109 ); insert into orde values ( '1' , '2019/1/3' ,150 ); insert into orde values ( '1' , '2019/1/4' ,99); insert into orde values ( '1' , '2019/1/5' ,145); insert into orde values ( '1' , '2019/1/6' ,1455); insert into orde values ( '1' , '2019/1/7' ,199); insert into orde values ( '1' , '2019/1/8' ,188 ); insert into orde values ( '4' , '2019/1/1' ,10 ); insert into orde values ( '2' , '2019/1/2' ,109 ); insert into orde values ( '3' , '2019/1/3' ,150 ); insert into orde values ( '4' , '2019/1/4' ,99); insert into orde values ( '5' , '2019/1/5' ,145); insert into orde values ( '6' , '2019/1/6' ,1455); insert into orde values ( '7' , '2019/1/7' ,199); insert into orde values ( '8' , '2019/1/8' ,188 ); insert into orde values ( '9' , '2019/1/1' ,10 ); insert into orde values ( '9' , '2019/1/2' ,109 ); insert into orde values ( '9' , '2019/1/3' ,150 ); insert into orde values ( '9' , '2019/1/4' ,99); insert into orde values ( '9' , '2019/1/6' ,145); insert into orde values ( '9' , '2019/1/9' ,1455); insert into orde values ( '9' , '2019/1/10' ,199); insert into orde values ( '9' , '2019/1/13' ,188 ); |
查看数据表:
2.使用row_number() over() 排序函数计算每个id的排名,SQL如下:
1 2 3 | select *,row_number() over(partition by id order by date ) 'rank' from orde where orders is not NULL ; |
查看数据表:
3.将date日期字段减去rank排名字段,SQL如下:
1 2 3 4 5 6 | select *,DATE_SUB(a. date ,interval a.rank day ) 'date_sub' from ( select *,row_number() over(partition by id order by date ) 'rank' from orde where orders is not NULL ) a; |
查看数据:
4.根据id和date分组并计算分组后的数量(count)、计算最早登录和最晚登录的时间,SQL如下:
1 2 3 4 5 6 7 8 9 10 11 12 | select b.id, min ( date ) 'start_time' , max ( date ) 'end_time' , count (*) 'date_count' from ( select *,DATE_SUB(a. date ,interval a.rank day ) 'date_sub' from ( select *,row_number() over(partition by id order by date ) 'rank' from orde where orders is not NULL ) a ) b group by b.date_sub,id having count (*) >= 3 ; |
查看数据:
参考资料:
SQL查询连续七天以上下单的用户
https://blog.csdn.net/qq_43807789/article/details/99091753?spm=1001.2101.3001.6661.1&utm_medium=distribute.pc_relevant_t0.none-task-blog-2~default~CTRLIST~default-1.highlightwordscore&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-2~default~CTRLIST~default-1.highlightwordscore
到此这篇关于SQL 查询连续n天登录的用户情况的文章就介绍到这了
原文链接:https://blog.csdn.net/weixin_45456209/article/details/122218453