我想找出哪些用户在一小时内登录最多。我将所有活动记录在一个名为user_activity
的表中,该表有event_class
、timestamp
和user_id
列(其他列存在但不重要(。当用户登录时,将针对其user_id
编写一个LOGIN
事件类以及CURRENT_TIMESTAMP
。
服务器运行竞争激烈的大学活动注册和学生验证码求解器(Python/OpenCV2...(过去被捕获,因为我在一个小时左右的一个小时内为一个用户看到了数百个login
事件(见下文(。我做了一个脚本,暂时禁止用户在最后十分钟内登录超过5次,这很简单。
我现在想搜索历史记录以查看每个用户每一小时窗口的登录总数。用伪语来说:"select each user_id from user_activity and group by total logins per last interval 1 hour
"这样我就可以快速看到哪些用户和IP地址(未显示(快速登录。这能做到吗?
"肖恩"在我们禁止他之前的最后 30 次登录示例
+---------+-------------+---------------------+
| user_id | event_class | timestamp |
+---------+-------------+---------------------+
| 617 | LOGIN | 2018-05-01 21:46:20 |
| 617 | LOGIN | 2018-05-01 20:48:55 |
| 617 | LOGIN | 2018-05-01 20:45:01 |
| 617 | LOGIN | 2018-05-01 20:43:41 |
| 617 | LOGIN | 2018-05-01 20:42:06 |
| 617 | LOGIN | 2018-05-01 20:37:19 |
| 617 | LOGIN | 2018-05-01 20:32:31 |
| 617 | LOGIN | 2018-05-01 20:27:17 |
| 617 | LOGIN | 2018-05-01 20:26:21 |
| 617 | LOGIN | 2018-05-01 20:25:44 |
| 617 | LOGIN | 2018-05-01 20:24:08 |
| 617 | LOGIN | 2018-05-01 20:20:44 |
| 617 | LOGIN | 2018-05-01 20:16:59 |
| 617 | LOGIN | 2018-05-01 18:23:44 |
| 617 | LOGIN | 2018-05-01 13:01:14 |
| 617 | LOGIN | 2018-05-01 12:55:12 |
| 617 | LOGIN | 2018-05-01 12:47:58 |
| 617 | LOGIN | 2018-04-30 16:09:51 |
| 617 | LOGIN | 2018-04-30 15:38:22 |
| 617 | LOGIN | 2018-04-30 15:37:31 |
| 617 | LOGIN | 2018-04-30 15:36:20 |
| 617 | LOGIN | 2018-04-30 15:35:33 |
| 617 | LOGIN | 2018-04-30 15:34:08 |
| 617 | LOGIN | 2018-04-30 15:31:46 |
| 617 | LOGIN | 2018-04-30 15:24:33 |
| 617 | LOGIN | 2018-04-30 15:23:22 |
| 617 | LOGIN | 2018-04-30 15:20:27 |
| 617 | LOGIN | 2018-04-30 15:09:49 |
| 617 | LOGIN | 2018-04-30 15:05:59 |
| 617 | LOGIN | 2018-04-30 15:00:03 |
+---------+-------------+---------------------+
当然,您可以简单地选择所需的数据并按其分组,如下所示:
SELECT
user_id,
CONCAT(DATE(`timestamp`), ' ', HOUR(`timestamp`)) AS datehour,
COUNT(1) AS logins
FROM user_activity
WHERE event_class = 'LOGIN'
GROUP BY user_id, datehour
HAVING logins > 5
在这里,我们应该看到每个小时内登录超过 5 次的用户的一行。 这里唯一需要注意的是,这是每个时钟小时,因此如果他们在晚上 10:59 登录 3 次,在晚上 11:01 登录 3 次以上,他们在任何特定小时内的尝试次数都不会超过 3 次。