我有一个包含数十万行的数据表,这些行表示对服务器获取数据的请求。每个记录都有一个时间戳、服务器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(这是针对记录服务器的第一个事件以及offline
事件之后的事件的每一行执行的)。
现在,您只需将上述查询产生的结果集SUM()seconds_online
值进行分组,然后将其除以60即可获得分钟数(如果您对秒数不满意),如下所示:
SELECT
server,
SUM(seconds_online) DIV 60 AS minutes
FROM (
the query above
) 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上尝试(也可以使用它)。
这是我创建的示例表结构:
-- 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.