CODEWITHSUNDEEP
mysqlgroup-byaggregate
cdecker
cdecker

Reputation: 4697

Aggregating data by timespan in MySQL

Basically I want is to aggregate some values in a table according to a timespan.

What I do is, I take snapshots of a system every 15 minutes and I want to be able to draw some graph over a long period. Since the graphs get really confusing if too many points are shown (besides getting really slow to render) I want to reduce the number of points by aggregating multiple points into a single point by averaging over them.

For this I'd have to be able to group by buckets that can be defined by me (daily, weekly, monthly, yearly, ...) but so far all my experiments had no luck at all.

Is there some trick I can apply to do so?

Upvotes: 8

Views: 6065

Answers (3)

Rob
Rob

Reputation: 4987

As an addition to @cmroanirgo, I didn't need "sums" of data, but avarages (to see the avarage FPS / player count of my game servers). And, I need to view in detail per 5 minutes - or view an entire week of data (data gets stored every minute).

As an example, you can use the SQL command AVG instead of SUM to get an avarage. Also, you'd have to name your selected values to something, and it shouldn't be the actual field name (that will conflict lateron in your query). Here's the query I'm using to aggregate avarages, of 1 week, by the hour:

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

This is then used (together with PHP) to use in a Bootstrap graph;

<?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...

Upvotes: 0

cmroanirgo
cmroanirgo

Reputation: 7457

I had a similar question: collating-stats-into-time-chunks and had it answered very well. In essence, the answer was:

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( bytesOut )
FROM
    stats
WHERE
    time BETWEEN <start> AND <end>
GROUP BY
    DATE_FORMAT( time, "%H:%i" )

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

Thanks be to martin clayton for the answer he provided me.

Upvotes: 11

Rob Farley
Rob Farley

Reputation: 15849

It's easy to truncate times to the last 15 minutes (for example), by doing something like:

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');

Use similar truncation methods to group by hour, week, whatever.

You could always wrap it up in a CASE statement to handle multiple methods, using:

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

Upvotes: 2

Related Questions