将统计信息整理成时间块 [英] collating stats into time chunks

问题描述

我正在尝试为MySQL制定一条SQL语句.我有一系列半小时报告的一系列服务器的统计信息.我有一个统计表，其中的列与此类似:

I'm trying to work out an SQL statement for MySQL. I have a series of stats for a range of servers that are reported on a half hourly basis. I have a stats table with columns similar to this:

server varchar(64), 
time datetime,
bytesIn int,
bytesOut int

现在，我想生成一系列报告，以在1小时或1天的时间内在所有服务器上整理所有bytesIn和bytesOut. IE.我知道我需要SUM(bytesIn)作为bytesIn，SUM(bytesOut)作为bytesOut，但是不知道如何将所有时间聚集"到每小时/每天的存储桶中，并且还返回聚集"的时间. 也就是说，返回的数据应如下所示:

Now, I want to generate a series of reports that collate all of the bytesIn and bytesOut on a either 1 hour or 1 day period across all servers. ie. I know I need SUM(bytesIn) as bytesIn, SUM(bytesOut) as bytesOut, but don't know how to 'clump' all of the times into hourly/daily buckets and also return that 'clumped' time. That is, the returned data should look something like this:

Time,bytesIn,bytesOut
12:00,1024,2048
13:00,4096,8192
etc

我知道这应该很简单，但是我迷路了.有人可以帮忙吗?

I know this should be simple, but I'm lost. Can anyone help?

推荐答案

也许您可以使用

Perhaps you can use the DATE_FORMAT() function, and grouping. Here's an example, hopefully you can adapt to your precise needs.

SELECT
    DATE_FORMAT( time, "%H:%i" ),
    SUM( bytesIn ),
    SUM( butesOut )
FROM
    stats
WHERE
    time BETWEEN <start> AND <end>
GROUP BY
    DATE_FORMAT( time, "%H:%i" )

如果您的时间范围超过一天，并且您使用示例格式，则来自不同日期的数据将汇总到一天中的时段"存储桶中.如果原始数据并非完全按小时计，则可以使用"%H:00"进行平滑处理.

If your time window covers more than one day, and you use the example format, data from different days will be aggregated into the 'hour-of-day' buckets. If the raw data don't fall exactly on the hour, you can smooth that out by using "%H:00".

这篇关于将统计信息整理成时间块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！