草庐IT

php - 在mysql中用where子句添加时间差

coder 2023-10-19 原文

我有一个包含数十万行的数据表，这些行代表对服务器获取数据的请求。每条记录都有时间戳、服务器 ID 和服务器是否正确响应的二进制值 (tinyint)。查询时间不是常数。

我试图通过将服务器在线的查询之间的时间相加(非常优选 mysql 查询)来获得服务器被视为“在线”的总时间。 例如。

server | time | status
1 | 1/1/2012 11:00 online
1 | 1/1/2012 11:02 online
1 | 1/1/2012 11:05 offline
2 | 1/1/2012 11:10 online
1 | 1/1/2012 11:30 online
Time now: 11:40
Server 1 Online Time = 2+3+10 = 15 minutes

在 mysql 中可以这样做吗？我更喜欢它，而不是将所有行都获取到 php 并计算它或取任何平均值。

最佳答案

这可以通过对正确排序的行集使用 UNIX 时间戳转换和变量赋值来完成。 “正确排序”是指行必须按 server 排序，然后按 time 排序。以下是如何使用变量获取表中每一行自上次事件以来的在线时间(间隔)(以秒为单位)(出于此答案的目的，称为 server_status):

SELECT
  *,
  @currenttime := UNIX_TIMESTAMP(`time`),
  @lasttime := CASE
    WHEN server <> @lastserver OR @laststatus = 'offline'
    THEN @currenttime
    ELSE @lasttime
  END,
  @currenttime - @lasttime AS seconds_online,
  @lasttime := @currenttime,
  @lastserver := server,
  @laststatus := status
FROM
  server_satus s,
  (SELECT @lastserver := 0) x
ORDER BY
  s.server,
  s.`time`

如您所见，一个临时变量 (@currenttime) 被初始化为等同于 time 的 UNIX 时间戳，另一个用于保存之前的时间戳，以便可以计算出两者之间的差异。其他变量用于记住先前的服务器 ID 和先前的状态，以便在必要时将差异返回为 0(对记录服务器的第一个事件以及 之后的每一行都这样做)线下事件)。

您现在可以对上述查询生成的结果集进行分组，SUM() seconds_online 值并将它们除以 60 得到分钟(如果您对秒不满意)，例如这个:

SELECT
  server,
  SUM(seconds_online) DIV 60 AS minutes
FROM (
  <i>the query above</i>
) s

但是请注意，第一个查询并没有真正计算服务器自其各自的最后 事件以来在线花费的秒数。也就是说，当前时间可能与任何最新事件记录中的时间有很大不同，并且不会将其考虑在内，因为查询计算自上一行以来每行的秒数。

解决此问题的一种方法是为每个服务器添加一行，其中包含当前时间戳和与上一条记录中相同的状态。因此，除了 server_status 之外，您还可以将以下内容作为源表:

SELECT
  server,
  `time`,
  status
FROM server_status

UNION ALL

SELECT
  s.server,
  NOW() AS `time`,
  s.status
FROM server_status s
  INNER JOIN (
    SELECT
      server,
      MAX(`time`) AS last_time
    FROM server_status
    GROUP BY
      server
  ) t
    ON s.server = t.server AND s.`time` = t.last_time

UNION ALL 的左侧部分仅返回 server_status 中的所有行。右边部分首先获取每个服务器的最后一个time，然后将结果集加入到server_status中获取相应的状态，将time替换为NOW() 一路走来。

现在表已完成并包含反射(reflect)当前时间的“假”事件行，您可以应用第一个查询中使用的方法。这是最终查询的样子:

SELECT
  server,
  SUM(seconds_online) DIV 60 AS minutes_online
FROM (
  SELECT
    *,
    @currenttime := UNIX_TIMESTAMP(`time`),
    @lasttime := CASE
      WHEN server <> @lastserver OR @laststatus = 'offline'
      THEN @currenttime
      ELSE @lasttime
    END,
    @currenttime - @lasttime AS seconds_online,
    @lasttime := @currenttime,
    @lastserver := server,
    @laststatus := status
  FROM
    (
      SELECT
        server,
        `time`,
        status
      FROM server_status

      UNION ALL

      SELECT
        s.server,
        NOW() AS `time`,
        s.status
      FROM server_status s
        INNER JOIN (
          SELECT
            server,
            MAX(`time`) AS last_time
          FROM server_status
          GROUP BY
            server
        ) t
          ON s.server = t.server AND s.`time` = t.last_time
    ) s,
    (SELECT @lastserver := 0) x
  ORDER BY
    s.server,
    s.`time`
) s
GROUP BY
  server
;

您可以尝试(也可以玩)at SQL Fiddle也是。

关于php - 在mysql中用where子句添加时间差，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/12336748/

中用mysqlservercodestatusphpsqltimestamp

有关php - 在mysql中用where子句添加时间差的更多相关文章

  1. ruby - 我需要将 Bundler 本身添加到 Gemfile 中吗？ - 2

    当我使用Bundler时，是否需要在我的Gemfile中将其列为依赖项？毕竟，我的代码中有些地方需要它。例如，当我进行Bundler设置时:require"bundler/setup" 最佳答案 没有。您可以尝试，但首先您必须用鞋带将自己抬离地面。 关于ruby-我需要将Bundler本身添加到Gemfile中吗？，我们在StackOverflow上找到一个类似的问题： https://stackoverflow.com/questions/4758609/

  2. ruby - 将 Bootstrap Less 添加到 Sinatra - 2

    我有一个ModularSinatra应用程序，我正在尝试将Bootstrap添加到应用程序中。get'/bootstrap/application.css'doless:"bootstrap/bootstrap"end我在views/bootstrap中有所有less文件，包括bootstrap.less。我收到这个错误:Less::ParseErrorat/bootstrap/application.css'reset.less'wasn'tfound.Bootstrap.less的第一行是://CSSReset@import"reset.less";我尝试了所有不同的路径格式，但它

  3. ruby - 续集在添加关联时访问many_to_many连接表 - 2

    我正在使用Sequel构建一个愿望list系统。我有一个wishlists和itemstable和一个items_wishlists连接表(该名称是续集选择的名称)。items_wishlists表还有一个用于facebookid的额外列(因此我可以存储opengraph操作)，这是一个NOTNULL列。我还有Wishlist和Item具有续集many_to_many关联的模型已建立。Wishlist类也有:selectmany_to_many关联的选项设置为select:[:items.*,:items_wishlists__facebook_action_id].有没有一种方法可以

  4. ruby - 在 Ruby 中用键盘诅咒数组浏览 - 2

    我正在尝试在Ruby中制作一个cli应用程序，它接受一个给定的数组，然后将其显示为一个列表，我可以使用箭头键浏览它。我觉得我已经在Ruby中看到一个库已经这样做了，但我记不起它的名字了。我正在尝试对soundcloud2000中的代码进行逆向工程做类似的事情，但他的代码与SoundcloudAPI的使用紧密耦合。我知道cursesgem，我正在考虑更抽象的东西。广告有没有人见过可以做到这一点的库或一些概念证明的Ruby代码可以做到这一点？ 最佳答案 我不知道这是否是您正在寻找的，但也许您可以使用我的想法。由于我没有关于您要完成的工作

  5. ruby-on-rails - 相关表上的范围为 "WHERE ... LIKE" - 2

    我正在尝试从Postgresql表(table1)中获取数据，该表由另一个相关表(property)的字段(table2)过滤。在纯SQL中，我会这样编写查询:SELECT*FROMtable1JOINtable2USING(table2_id)WHEREtable2.propertyLIKE'query%'这工作正常:scope:my_scope,->(query){includes(:table2).where("table2.property":query)}但我真正需要的是使用LIKE运算符进行过滤，而不是严格相等。然而，这是行不通的:scope:my_scope,->(que

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

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

  7. 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

  8. 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

  9. ruby - 可以通过多少种方法将方法添加到 ruby​​ 对象？ - 2

    当谈到运行时自省(introspection)和动态代码生成时，我认为ruby​​没有任何竞争对手，可能除了一些lisp方言。前几天，我正在做一些代码练习来探索ruby​​的动态功能，我开始想知道如何向现有对象添加方法。以下是我能想到的3种方法:obj=Object.new#addamethoddirectlydefobj.new_method...end#addamethodindirectlywiththesingletonclassclass这只是冰山一角，因为我还没有探索instance_eval、module_eval和define_method的各种组合。是否有在线/离线资

  10. ruby - 如何在 Ruby 中向现有方法定义添加语句 - 2

    我注意到类定义，如果我打开classMyClass，并在不覆盖的情况下添加一些东西我仍然得到了之前定义的原始方法。添加的新语句扩充了现有语句。但是对于方法定义，我仍然想要与类定义相同的行为，但是当我打开defmy_method时似乎,def中的现有语句和end被覆盖了，我需要重写一遍。那么有什么方法可以使方法定义的行为与定义相同，类似于super，但不一定是子类？ 最佳答案 我想您正在寻找alias_method:classAalias_method:old_func,:funcdeffuncold_func#similartoca

随机推荐