Take 3 consecutive days as an example, using the tool: MySQL. 1. Create SQL table:create table if not exists order(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 ); View the datasheet: 2. Use the row_number() over() sorting function to calculate the ranking of each id. The SQL is as follows:select *,row_number() over(partition by id order by date ) 'rank' from orde where orders is not NULL; View datasheet: 3. Subtract the rank field from the date field. The SQL is as follows: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; View the data: 4. Group by id and date and calculate the number of grouped items (count), and calculate the earliest and latest login times. The SQL is as follows: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 ) ) b group by b.date_sub,id having count(*) >= 3 ; View the data: References: SQL query for users who have placed orders for at least seven consecutive days The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM. You may also be interested in:
|
<<: Summary of common tool functions necessary for front-end development
>>: Neon light effects implemented with pure CSS3
Example: tip: This component is based on vue-crop...
Preface A Docker image consists of a Dockerfile a...
Table of contents 502 bad gateway error formation...
How to uninstall Mysql perfectly? Follow the step...
Table of contents 1. Deconstruction Tips 2. Digit...
Sometimes you need to use links, but you don't...
Preface Previously, I talked about the problem of...
Repetition: Repeat certain page design styles thr...
Table of contents jQuery's $.ajax The beginni...
In order to handle a large number of concurrent v...
systemd: The service systemctl script of CentOS 7...
Table of contents Overview 1. Menu and routing pr...
Table of contents 1. Basic usage and logic 2. Fea...
In daily work, we often need to view logs. For ex...
Table of contents Problem Description Cause Analy...