創(chuàng)建表格
|
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 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 ) |
思路
將表按照id分組根據(jù)時(shí)間排名，時(shí)間減去排名rnk,如果時(shí)間是連續(xù)的則相減的結(jié)果相等：
|
1
|
select *,date-排名 rnk from (select *,row_number() over(partition by id order by date) 排名 from orde) a; |
然后按照id，rnk分組，計(jì)數(shù)
|
1
2
|
select id,rnk,count(*) ok from(select *,date-排名 rnk from (select *,row_number() over(partition by id order by date) 排名 from orde) a) b group by id,rnk; |
得到下表：
最后篩選計(jì)數(shù)項(xiàng)ok>=7的。
還可以直接進(jìn)行篩選：
|
1
2
|
select id,rnk from(select *,date-排名 rnk from (select *,row_number() over(partition by id order by date) 排名 from orde) a) b group by id,rnk having count(*)>=7; |
以上所述是小編給大家介紹的SQL查詢至少連續(xù)七天下單的用戶，希望對(duì)大家有所幫助。在此也非常感謝大家對(duì)服務(wù)器之家網(wǎng)站的支持！
原文鏈接：https://blog.csdn.net/qq_43807789/article/details/99091753
