以连续3天为例,使用工具:MySQL。
1.创建SQL表:
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如下:
select *,row_number() over(partition by id order by date ) 'rank' from orde where orders is not NULL;
查看数据表:
3.将date日期字段减去rank排名字段,SQL如下:
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如下:
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天登录的用户情况的文章就介绍到这了,更多相关SQL 查询用户连续登录情况内容请搜索NICE源码以前的文章或继续浏览下面的相关文章希望大家以后多多支持NICE源码!