Question 20802 in Mysql Question posted in
The official MySQL documentation can be found here.

Find pattern based on events and number of occurences – Mysql

I have a MySQL database table that looks like below:

+----------+------------+---------------------+
| name     | event      | created             |
+----------+------------+---------------------+
| Player1  | Logged in  | 2023-02-14 10:05:00 |
| Player2  | Logged in  | 2023-02-14 10:05:30 |
| Player3  | Logged out | 2023-02-14 10:06:00 |
| Player1  | Logged out | 2023-02-14 10:10:30 |
| Player4  | Logged in  | 2023-02-14 10:10:45 |
| Player2  | Logged out | 2023-02-14 10:20:00 |
| Player4  | Logged out | 2023-02-14 10:30:00 |
| Player5  | Logged in  | 2023-02-14 10:30:05 |
| Player1  | Logged in  | 2023-02-14 10:30:10 |
| Player5  | Logged out | 2023-02-14 10:32:00 |
+----------+------------+---------------------+

What I want to do, is to figure which players might be played by the same person.
To do that, I can look at their respective "Logged in" / "Logged out" events and use that as a pattern.
If a player logs out from the game, and another player logs in within 30 seconds – and this happens a few times – then I can assume they are being played by the same person. Similarily if a player logs in and another one logs out.

In the example above, we can see that:
(row 4) **Player1** -> **Logged out**
(row 5) **Player4** -> **Logged in**
These events took place less than 30 seconds apart.

And again, the same thing happened here:
(row 7) **Player4** -> **Logged out**
(row 9) **Player1** -> **Logged in**
These events took place less than 30 seconds apart as well.

We can therefore assume that Player1 and Player4 are being played by the same person.

What I want to generate as result is a new table, that allows me to search for a specific player.
In this case, I want to search for "Player1" and it should return a list of all players that logged in/logged out within 30 seconds after Player1, and had at least 2 such occurences.

For instance, a table like below would be sufficient as results if I make a search for "Player1":

+----------+------------+
| name     | occurences |
+----------+------------+
| Player4  | 2          |
+----------+------------+

Any clue on how I can achieve this?

Tags:

2

Answers


  1. 0

    Something like:

    SELECT LEAST(t1.name, t2.name) name1, GREATEST(t1.name, t2.name) name2, COUNT(*)
FROM events t1
JOIN events t2 ON t1.event = 'out'
              AND t2.event = 'in'
              AND t2.created BETWEEN t1.created AND t1.created + INTERVAL 30 SECOND
WHERE t1.created BETWEEN @some_range_start AND @some_range_end
GROUP BY 1, 2
HAVING COUNT(*) >= @some_limit;

    where:

    @some_range_start, @some_range_end – dates range to be investigated (remove if you want to investigate the whole table);

    @some_limit – minimal amount of such incidents for definite logins pair.

    DEMO fiddle

    Remember – this query won’t be fast..

    PS. The amount of second can be parametrized too, of course.

    Login or Signup to reply.
  2. 0

    You can make a query, which uses a subquery to get all created for player 1 and then uses CROSS JOIN to compare it to all dates for other players

    SELECT name, COUNT(*) as occurances  from table1 ta1 CROSS JOIN (SELECT `created` FROM table1 WHERE name = 'Player1') t1
  WHERE ta1.`created`  BETWEEN 
 t1.`created` - INTERVAL 30 SECOND
AND t1.`created` + INTERVAL 30 SECOND aND  name <> 'Player1'
GROUP BY name
HAVING COUNT(*) > 1
    name occurances
    Player4 2

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search