MySQL中按时间跨度聚合数据

Question

基本上我想要的是根据时间跨度聚合表中的一些值。

我所做的是，我每 15 分钟拍摄一次系统快照，并且希望能够在很长一段时间内绘制一些图表。由于如果显示太多点，图表会变得非常混乱（除了渲染速度非常慢之外），我想通过对多个点进行平均，将多个点聚合成一个点来减少点的数量。

为此，我必须能够按我可以定义的存储桶进行分组（每日、每周、每月、每年……），但到目前为止，我所有的实验都没有运气。

我可以应用一些技巧来做到这一点吗？

Answer 1

我有一个类似的问题： colating-stats-into-time-chunks 并得到了它回答得很好。本质上，答案是：

也许您可以使用 DATE_FORMAT() 函数和分组。这是一个示例，希望您可以适应您的具体需求。

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

如果您的时间窗口涵盖超过一天并且您使用示例格式，则不同日期的数据将聚合到“一天中的一小时”存储桶中。如果原始数据不完全符合小时，您可以使用“%H:00”进行平滑处理。

感谢马丁·克莱顿为我提供的答案。

Answer 2

通过执行以下操作，可以轻松地将时间截断到最后 15 分钟（例如）：

SELECT dateadd(minute, datediff(minute, '20000101', yourDateTimeField) / 15 * 15, '20000101') AS the15minuteBlock, COUNT(*) as Cnt
FROM yourTable
GROUP BY dateadd(minute, datediff(minute, '20000101', yourDateTimeField) / 15 * 15, '20000101');

使用类似的截断方法按小时、周等进行分组。

您始终可以将其包装在 CASE 语句中以处理多种方法，使用：

GROUP BY CASE @option WHEN 'week' THEN dateadd(week, .....

Answer 3

作为 @cmroanirgo 的补充，我不需要数据的“总和”，而是平均值（以查看我的游戏服务器的平均 FPS/玩家数量）。而且，我需要每 5 分钟详细查看一次 - 或查看一整周的数据（每分钟都会存储数据）。

例如，您可以使用 SQL 命令 AVG 而不是 SUM 来获取平均值。另外，您必须将所选值命名为某个名称，并且它不应该是实际的字段名称（这将在稍后的查询中发生冲突）。这是我用来按小时聚合 1 周平均值的查询：

SELECT
    DATE_FORMAT( moment, "%Y-%m-%d %H:00" ) as _moment,
    AVG( maxplayers ) as _maxplayers,
    AVG( players ) as _players,
    AVG( servers ) as _servers,
    AVG( avarage_fps ) as _avarage_fps,
    AVG( avarage_realfps ) as _avarage_realfps,
    AVG( avarage_maxfps ) as _avarage_maxfps
FROM
    playercount
WHERE
    moment BETWEEN "<date minus 1 week>" AND "<now>"
GROUP BY
    _moment
ORDER BY moment ASC

然后将其（与 PHP 一起）用于 Bootstrap 图表中；

<?php
//Do the query here

foreach ($result->fetch_all(MYSQLI_ASSOC) as $item) {
    $labels[] = $item['_moment'];
    $maxplayers[] = $item['_maxplayers'];
    $players[] = $item['_players'];
    $servers[] = $item['_servers'];
    $fps[] = $item['_avarage_fps'];
    $fpsreal[] = $item['_avarage_realfps']/10;
    $fpsmax[] = $item['_avarage_maxfps'];
}
?>

var playerChartId = document.getElementById("playerChartId");
var playerChart = new Chart(playerChartId, {
    type: 'line',
    data: {
        labels: ["<?= implode('","', $labels); ?>"],
        datasets: [
            {
                data: [<?= implode(',', $servers); ?>],
                borderColor: '#007bff',
                pointRadius: 0
            },
            //etc...