Агрегирование данных по промежутку времени в MySQL

В основном я хочу агрегировать некоторые значения в таблице в соответствии с промежутком времени.

Что я делаю, так это делаю снимки системы каждые 15 минут и хочу иметь возможность нарисовать график за длительный период. Поскольку графики становятся действительно запутанными, если отображается слишком много точек (помимо того, что они очень медленно отображаются), я хочу уменьшить количество точек, объединив несколько точек в одну точку, усредняя их.

Для этого мне нужно было бы иметь возможность группировать по ведрам, которые я могу определить (ежедневно, еженедельно, ежемесячно, ежегодно, ...), но до сих пор все мои эксперименты не увенчались успехом.

Есть ли какой-нибудь трюк, который я могу применить для этого?


mysql group-by aggregate
person cdecker    schedule 10.12.2009    source источник

Ответы (3)


arrow_upward
10
arrow_downward

У меня был похожий вопрос: collating-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».

Спасибо Мартину Клейтону за ответ, который он мне дал.

person cmroanirgo    schedule 10.12.2009
comment
Это масштабируется? Моя проблема в том, что через год это создаст несколько миллионов записей. - person cdecker; 11.12.2009
comment
Не понимаю, почему бы и нет. Очевидно, что выполнение любых преобразований времени происходит медленно, но большая часть времени будет потрачена на агрегирование самих данных, что неизбежно в вашем случае. - person cmroanirgo; 17.12.2009

arrow_upward
2
arrow_downward

Легко сократить время до последних 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, .....
person Rob Farley    schedule 10.12.2009

arrow_upward
0
arrow_downward

В дополнение к @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...
person Rob    schedule 25.12.2018