在MySQL中使用where子句添加时间差值

Question

我有一个包含数十万行的数据表，代表向服务器获取数据的请求。每条记录都有一个时间戳、服务器 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并计算或平均任何内容，我更喜欢使用MySQL。

- Gricey

---

列的类型是什么？ - shail

不确定这被认为是“在线”的，你打算用这个指标做什么？对我来说，使用SHOW STATUS LIKE 'Uptime';更有意义。 - user557846

希望它不是一个僵尸网络！ - user557846

@Dagon 哈哈，听了你的话我笑了。我可以向你保证，如果你帮助我，那只是为了好而不是为了恶。 - Gricey

这可以通过使用类似于 rationalboss 下面的答案的 MySQL 存储过程来完成。 - Barmar

Answer 1

可以使用UNIX时间戳转换和变量分配在正确排序的行集上完成。所谓的“正确排序”，是指行必须按服务器排序，然后按时间排序。以下是如何使用变量获取在线时间（间隔），以秒为单位，对于表中的每一行（此答案的目的是称为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（对于记录服务器第一个事件以及那些在offline事件之后出现的每一行都会这样做）。

现在，您只需分组上述查询产生的结果集，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 表连接以获得相应的状态，并在此过程中使用 NOW() 替换 time。

现在，表格已经用反映当前时间的“假”事件行完成，您可以应用第一个查询中使用的方法。以下是最终查询的样子：

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
;

你也可以在 SQL Fiddle 上尝试它（并与之互动）。

- Andriy M

---

哇，谢谢。这一定是我在这个网站上读过的最长、最详细的答案，纯粹的高质量。 - Gricey

Answer 2

这是我创建的示例表结构：

-- SQL EXAMPLE
CREATE TABLE IF NOT EXISTS `stack_test` (
  `server` int(11) NOT NULL,
  `rtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` tinyint(4) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `stack_test` (`server`, `rtime`, `status`) VALUES
    (1, '2012-01-01 11:00:24', 1),
    (1, '2012-01-01 11:02:24', 1),
    (1, '2012-01-01 11:05:24', 0),
    (2, '2012-01-01 11:10:24', 1),
    (1, '2012-01-01 11:30:24', 1);
-- SQL EXAMPLE END

以下是PHP代码：

<?php
$query = 'SELECT DISTINCT(`server`) `server` FROM stack_test';
$res = sql::exec($query); // replace with your function/method to execute SQL
while ($row = mysql_fetch_assoc($res)) {
    $server = $row['server'];
    $uptimes = sql::exec('SELECT * FROM stack_test WHERE server=? ORDER BY rtime DESC',$server);
    $online = 0;
    $prev = time();
    $prev = strtotime('2012-01-01 11:40:00'); // just to show that it works given the example
    while ($uptime = mysql_fetch_assoc($uptimes)) {
        if ($uptime['status'] == 1) {
            echo date('g:ia',$prev) . ' to ' . date('g:ia',strtotime($uptime['rtime'])) . ' = '.(($prev-strtotime($uptime['rtime']))/60).' mins<br />';
            $online += $prev-strtotime($uptime['rtime']);
        }
        $prev = strtotime($uptime['rtime']);
    }
    echo 'Server '.$server.' is up for '.($online/60).' mins.<br />';
}
?>

这是我得到的输出：

11:40am to 11:30am = 10 mins
11:05am to 11:02am = 3 mins
11:02am to 11:00am = 2 mins
Server 1 is up for 15 mins.

11:40am to 11:10am = 30 mins
Server 2 is up for 30 mins.

- rationalboss

---

这是一个绝对棒的答案，但我尽可能想要更多的SQL而少用PHP。问题在于有成千上万行数据，将每一行数据传输到PHP中证明速度太慢了。 - Gricey