Identify sequential records between a short period of time

Question

Identify sequential records between a short period of time

Asked 3 years, 10 months ago

Viewed 79 times

0

I need your help to identify sequential records in my database in a short period of time

Example:

$qdt_acao_suspeitas = 5; //5 ações consecutivas
$tempo = 10; //10 minuntos

If user X performs the action more than $qdt_acao_suspeitas between time $tempo displays an echo informing...

Sequential records in my bank:

Description:

mat3us made 5 equal shares in less than 10 minutes

guilherme21 made 5 equal shares in less than 10 minutes

I’ve tried something like

$flood = $pdo->querySELECT usuario,action,data,count(action) FROM eventos GROUP BY usuario,data having count(action) > 5");

in a while I make the following request

if($flood > 0){
...
}

would like a return of all users matching my request

but I’m sure my SELECT not much to do with what I want to do and I’m already out of ideas

And how much is "short term"? 10 minutes? this will be difficult to do in the query, if it was for 1 minute or 1 hour, just group the date per minute or hour and make a Count, will have to do this in the code

– Ricardo Pontual

2020/08/24 at 13:43
Which bank is it? Assuming Mysql, search by lead / lag de window functions , which allow you to see the previous records of an sql , https://www.geeksforgeeks.org/mysql-lead-and-lag-function/ , also timediff for the difference calculation https://www.w3schools.com/sql/func_mysql_timediff.asp , others SGBD has similar solutions

– Motta

2020/08/24 at 13:59
@Ricardopunctual supposing it is 1 hour, as would be in practice?

– javinha

2020/08/24 at 15:17
@Motta I’ve tried too, gives the error: Fatal error: Uncaught Error: Call to a Member Function fetch() on bool in ...

– javinha

2020/08/24 at 15:18
gives a look at this other answer to get an idea: https://answall.com/a/460199/57220

– Ricardo Pontual

2020/08/24 at 15:34
@javinha lead and lag works , but I believe it depends on the environment , version etc

– Motta

2020/08/24 at 15:50
@Ricardopunctual I did in the same reasoning and always returns the Fatal Errol =@

– javinha

2020/08/24 at 16:24
good without seeing the code it is difficult to help... edit and ask the question

– Ricardo Pontual

2020/08/24 at 16:44
This would also be the case if thinking the case of a Function, very complicated rules can be solved by Function.

– Motta

2020/08/24 at 23:39

Show 4 more comments

1 answer

Browser other questions tagged php sql

You are not signed in. Login or sign up in order to post.

by Developer • **121** points · Answer 1 · 2020-08-25T12:23:44+00:00

Try to modify your select. Considering that each access can be considered Flood or not depending on the previous accesses, you can add a column to count these accesses like this:

SELECT usuario,action,data,
(
SELECT count(*)>=5 FROM eventos
where eventos.usuario = x.usuario and 
data between DATE_ADD(x.data, INTERVAL -10 MINUTE) and x.data
) as is_flood
FROM eventos as x

The is_flood column will have the information if the event is followed by 5 previous events within 10 minutes.