Проверка MySQL для X записей в течение любого 24-часового окна
У меня есть система, которая хранит неудачные попытки входа в систему в таблице и записывает дату и время события. Мне нужен способ запроса базы данных, чтобы проверить, есть ли X неудачных попыток для любого пользователя в течение любого 24-часового периода.
Например, пусть x = 5. Мне нужно проверить для любых пользователей, было ли в любой момент времени 24-часовое окно, в котором количество записей в этой таблице составляло 5 или более. 24-часовой период может быть от текущего времени до 24-часового периода или, например, в прошлом месяце между любыми двумя временными точками, которые находятся на расстоянии 24 часа.
Можно ли это сделать с помощью простого запроса MySQL? Большинство потоков, которые я видел, касаются подсчета записей за последние 24 часа, что не совсем то, что мне нужно.
Решение
SELECT
yt1.userId
FROM
your_table yt1
INNER JOIN your_table yt2 ON yt1.userId = yt2.userId
AND yt2.fail_date <= yt1.fail_date
AND yt2.fail_date >= yt1.fail_date - INTERVAL 24 HOUR
GROUP BY yt1.userId
HAVING COUNT(*) >= 5
Другие решения
Вот этот простой запрос:
SELECT
login1.user_id,
login1.login_time,
COUNT(*) AS linked_login_count,
MAX(login2.login_time) AS linked_login_max_time
FROM login_history AS login1
INNER JOIN login_history AS login2 ON
login2.user_id = login1.user_id AND
login2.login_time > login1.login_time AND
login2.login_time <= login1.login_time + INTERVAL 1 DAY
GROUP BY login1.user_id, login1.login_time
HAVING COUNT(*) >= 4
Он работает, соединяя таблицу с самим собой, сопоставляя строки, в которых идентификатор пользователя совпадает, а время входа в систему составляет от 24 часов. Результаты сгруппированы по идентификатору пользователя и времени входа в систему.
Вероятно, ваш сервер MySQL отправится на орбиту в зависимости от количества строк, которые у вас есть. Индекс с умом.
detector