可以使用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 上尝试它(并与之互动)。