mysql - 查找在给定时间段内发生 x 次的事件

Question

假设我有下表:

CREATE TABLE `occurences` (
  `object_id` int(10) NOT NULL,
  `seen_timestamp` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

其中包含对象的 ID(不是唯一的，它会重复)和观察到此对象 ID 时的时间戳。

观察全天候 24/7 并插入每个出现的对象 ID 和当前时间戳。

现在我想编写查询以选择在任何 10 分钟内至少出现 7 次的所有对象 ID。

它的功能应该类似于入侵检测。

在检查无效 SSH 登录的 denyhost 脚本中使用了类似的算法。如果在配置的时间段内找到配置的发生次数，它会阻止 IP。

有什么好的建议吗？

Answer 1

这应该有效:

SET @num_occurences = 7; -- how many occurences should occur in the interval
SET @max_period = 10; -- your interval in seconds

SELECT offset_start.object_id FROM 
(SELECT @rownum_start := @rownum_start+1 AS idx, object_id, seen_timestamp 
 FROM occurences, (SELECT @rownum_start:=0) r ORDER BY object_id ASC, seen_timestamp ASC) offset_start
JOIN
(SELECT @rownum_end := @rownum_end + 1 AS idx, object_id, seen_timestamp 
 FROM occurences, (SELECT @rownum_end:=0) r ORDER BY object_id ASC, seen_timestamp ASC) offset_end
   ON offset_start.object_id = offset_end.object_id 
  AND offset_start.idx + @num_occurences - 1 = offset_end.idx
  AND offset_end.seen_timestamp - offset_start.seen_timestamp <= @max_period
GROUP BY offset_start.object_id;

您可以将 @num_occurences 和 @num_occurences 移动到您的代码中，并将它们设置为语句的参数。根据您的客户，您还可以将 @rownum_start 和 @rownum_end 的初始化移动到查询前面，这可能会提高查询性能(尽管如此，您应该测试一下，看着两个版本的解释只是一种直觉)

这是它的工作原理:

它选择整个表两次，并将 offset_start 的每一行与 offset_end 中偏移量为 @num_occurences 的行连接起来。 (这是使用 @rownum_* 变量创建每一行的索引，模拟其他 rdbms 已知的 row_number() 功能完成的)。
然后它只是检查两行是否引用相同的 object_id 并满足周期要求。
由于这是针对每个出现行完成的，如果出现次数实际上大于 @max_occurences，则 object_id 将返回多次，因此最后将其分组以生成返回的 object_id独特的