草庐IT

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

coder 2023-10-02 原文

假设我有下表:

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。

有什么好的建议吗？

最佳答案

这应该有效:

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独特的

关于mysql - 查找在给定时间段内发生 x 次的事件，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/10028670/

给定mysqlcodeobject_idoccurencescountgroup-by

有关mysql - 查找在给定时间段内发生 x 次的事件的更多相关文章

  1. ruby-on-rails - Ruby 检查日期时间是否为 iso8601 并保存 - 2

    我需要检查DateTime是否采用有效的ISO8601格式。喜欢:#iso8601?我检查了ruby​​是否有特定方法，但没有找到。目前我正在使用date.iso8601==date来检查这个。有什么好的方法吗？编辑解释我的环境，并改变问题的范围。因此，我的项目将使用jsapiFullCalendar，这就是我需要iso8601字符串格式的原因。我想知道更好或正确的方法是什么，以正确的格式将日期保存在数据库中，或者让ActiveRecord完成它们的工作并在我需要时间信息时对其进行操作。 最佳答案 我不太明白你的问题。我假设您想检查

  2. ruby - 当使用::指定模块时，为什么 Ruby 不在更高范围内查找类？ - 2

    我刚刚被困在这个问题上一段时间了。以这个基地为例:moduleTopclassTestendmoduleFooendend稍后，我可以通过这样做在Foo中定义扩展Test的类:moduleTopmoduleFooclassSomeTest但是，如果我尝试通过使用::指定模块来最小化缩进:moduleTop::FooclassFailure这失败了:NameError:uninitializedconstantTop::Foo::Test这是一个错误，还是仅仅是Ruby解析变量名的方式的逻辑结果？ 最佳答案 Isthisabug,or

  3. ruby-on-rails - 将 Ruby 中的日期/时间格式化为 YYYY-MM-DD HH :MM:SS - 2

    这个问题在这里已经有了答案:Railsformattingdate(4个答案)关闭4年前。我想格式化Time.Now函数以显示YYYY-MM-DDHH:MM:SS而不是:“2018-03-0909:47:19+0000”该函数需要放在时间中.现在功能。require‘roo’require‘roo-xls’require‘byebug’file_name=ARGV.first||“Template.xlsx”excel_file=Roo::Spreadsheet.open(“./#{file_name}“,extension::xlsx)xml=Nokogiri::XML::Build

  4. ruby - 查找字符串中的内容类型(数字、日期、时间、字符串等) - 2

    我正在尝试解析一个CSV文件并使用SQL命令自动为其创建一个表。CSV中的第一行给出了列标题。但我需要推断每个列的类型。Ruby中是否有任何函数可以找到每个字段中内容的类型。例如，CSV行:"12012","Test","1233.22","12:21:22","10/10/2009"应该产生像这样的类型['integer','string','float','time','date']谢谢! 最佳答案 require'time'defto_something(str)if(num=Integer(str)rescueFloat(s

  5. ruby-on-rails - 启用 Rack::Deflater 时 ETag 发生变化 - 2

    在启用Rack::Deflater来gzip我的响应主体时偶然发现了一些奇怪的东西。也许我遗漏了一些东西，但启用此功能后，响应被压缩，但是资源的ETag在每个请求上都会发生变化。这会强制应用程序每次都响应，而不是发送304。这在没有启用Rack::Deflater的情况下有效，我已经验证页面源没有改变。我正在运行一个使用thin作为Web服务器的Rails应用程序。Gemfile.lockhttps://gist.github.com/2510816有没有什么方法可以让我从Rack中间件获得更多的输出，这样我就可以看到发生了什么？提前致谢。 最佳答案

  6. ruby-on-rails - 事件管理员日期过滤器日期格式自定义 - 2

    是否有简单的方法来更改默认ISO格式(yyyy-mm-dd)的ActiveAdmin日期过滤器显示格式？ 最佳答案 您可以像这样为日期选择器提供额外的选项，而不是覆盖js:=f.input:my_date,as::datepicker,datepicker_options:{dateFormat:"mm/dd/yy"} 关于ruby-on-rails-事件管理员日期过滤器日期格式自定义，我们在StackOverflow上找到一个类似的问题： https://s

  7. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  8. ruby-on-rails - 事件记录 : Select max of limit - 2

    我正在尝试将以下SQL查询转换为ActiveRecord，它正在融化我的大脑。deletefromtablewhereid有什么想法吗？我想做的是限制表中的行数。所以，我想删除少于最近10个条目的所有内容。编辑:通过结合以下几个答案找到了解决方案。Temperature.where('id这给我留下了最新的10个条目。 最佳答案 从您的SQL来看，您似乎想要从表中删除前10条记录。我相信到目前为止的大多数答案都会如此。这里有两个额外的选择:基于MurifoX的版本:Table.where(:id=>Table.order(:id).

  9. sql - 查询忽略时间戳日期的时间范围 - 2

    我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

  10. ruby - 当 attr_accessor 在类方法中时会发生什么？ - 2

    所以我想到了这个，想知道当下面的一些事情完成后会发生什么。classTestdefself.abcattr_accessor:Johnendendobject=Test.newputs"beforecallingclassmethodabc:#{object.class.instance_methods(false)}"Test.abcputs"aftercallingclassmethodabc:#{object.class.instance_methods(false)}"这里我检查的是，getter和setter方法是否以这种方式创建。如果是这样，是那些实例方法或类方法。首先我创

随机推荐