A data table, about 100 w records, field: phone number, product ID, date 13:22:42 (2018-01-01), the other
Now given the two input:
1. The time range, such as:
60 seconds2. A record number of X times, such as: five

Requirement description, please provide 60 seconds, the same user record data appear 5 times or more,

Pray god to save!!!!!!

CodePudding user response:

Don't know if this meaning, you give it a try,
The SET @ d:=60; - 60 seconds
The SET @ c:=5; Five -
SELECT
T. a phone number,
T. product ID,
T. date
The FROM
(SELECT
T. *,
(SELECT
COUNT (0)
The FROM
Table_name t1
. WHERE the t1 date BETWEEN t. date
AND DATE_ADD (t. date, INTERVAL + @ d SECOND)
AND t1. Phone number
AND t cell number
AND t1. The product ID=t. The product ID - if a phone number, can be on behalf of a user, please remove the this line,
C)
The FROM
Table_name t) tc & gt;=@ c;

CodePudding user response:

reference 1st floor AHUA1001 response:

don't know if this meaning, you give it a try,
The SET @ d:=60; - 60 seconds
The SET @ c:=5; Five -
SELECT
T. a phone number,
T. product ID,
T. date
The FROM
(SELECT
T. *,
(SELECT
COUNT (0)
The FROM
Table_name t1
. WHERE the t1 date BETWEEN t. date
AND DATE_ADD (t. date, INTERVAL + @ d SECOND)
AND t1. Phone number
AND t cell number
AND t1. The product ID=t. The product ID - if a phone number, can be on behalf of a user, please remove the this line,
C)
The FROM
Table_name t) tc & gt;=@ c;

Behind this can only take you to the first article, article 4 no out,
Is 60 seconds to 5 records are extracted

CodePudding user response:

Try this watch
The SET @ d:=60; - 60 seconds
The SET @ c:=5; Five -
SELECT
T. a phone number,
T. product ID,
T. date
The FROM
(SELECT
T. *,
(SELECT
COUNT (0)
The FROM
Table_name t1
. WHERE the t1 date BETWEEN DATE_ADD (t. date, INTERVAL - @ d SECOND)
AND DATE_ADD (t. date, INTERVAL + @ d SECOND)
AND t1. Phone number
AND t cell number
AND t1. The product ID=t. The product ID - if a phone number, can be on behalf of a user, please remove the this line,
C)
The FROM
Table_name t) tc & gt;=@ c;

CodePudding user response:

The

reference 3 floor AHUA1001 response:

try this see
The SET @ d:=60; - 60 seconds
The SET @ c:=5; Five -
SELECT
T. a phone number,
T. product ID,
T. date
The FROM
(SELECT
T. *,
(SELECT
COUNT (0)
The FROM
Table_name t1
. WHERE the t1 date BETWEEN DATE_ADD (t. date, INTERVAL - @ d SECOND)
AND DATE_ADD (t. date, INTERVAL + @ d SECOND)
AND t1. Phone number
AND t cell number
AND t1. The product ID=t. The product ID - if a phone number, can be on behalf of a user, please remove the this line,
C)
The FROM
Table_name t) tc & gt;=@ c;

You this is 120 seconds, please have a look at the questions carefully

CodePudding user response:

I'm here without you database, to finish the also can't test,
Probably thinking is like this, you can try to change the condition,