Эффективный способ суммирования измерений/временных рядов по заданному интервалу в php

У меня есть ряд данных измерений/временных рядов в том же интервале 15 минут. Кроме того, у меня есть заданный период (например, один день, текущая неделя, месяц, год, (...), и мне нужно суммировать значения по часам, дням, месяцам, (...).

Например. суммировать все значения за последний месяц по дням.

Мой подход заключается в создании временного массива с необходимым интервалом за период на первом этапе. Например. здесь, в PHP (PHP не так уж необходим, я бы предпочел Python или Javascript, если он обеспечивает более быстрый метод)

$this->tempArray = array(
'2014-10-01T00:00:00+0100' => array(),
'2014-10-02T00:00:00+0100' => array(),
'2014-10-03T00:00:00+0100' => array(),
'2014-10-04T00:00:00+0100' => array(),
(...)
'2014-10-31T00:00:00+0100' => array()
);

На втором этапе я перебираю каждую пару дата/значение (в этом примере 4*24*31, (96 в день)) и назначаю их моему временному массиву. Для каждой даты я переопределяю некоторые значения из объекта datetime. В этом примере часы и минуты соответствуют ключам в массиве temp.

$insert = array(
    'datetime' => $datetime,
    'value' => $value
);

if ($interval == "d") {

    $this->tempArray[date('Y-m-d\T00:00:sO', $datetime)][] = $insert;
}

На последнем шаге я перебираю временный массив и суммирую каждый массив. В результате я получаю массив с 31 новой парой дата/значение, суммированной по дням. Это прекрасно работает. Однако есть ли более быстрый способ или более эффективный способ? При таком подходе в течение одного месяца требуется почти 0,5 секунды. (Если кого-то заинтересует исходный код, я добавлю суть). Данные хранятся в базе данных mysql с 15 миллионами записей.

// Редактировать: я думаю, что лучше всего сгруппировать это с помощью mysql.

Мой текущий SQL-запрос для получения данных за один год:

SELECT
FROM_UNIXTIME(PointOfTime)) as `date`,
value
FROM data
WHERE EnergyMeterId="0ca64479-bddf-4b91-9e35-bf81f4bfa84c"
and PointOfTime >= unix_timestamp('2013-01-01T00:00:00')
and PointOfTime <= unix_timestamp('2013-12-31T23:45:00')
order by `date` asc;

arrays php datetime summarization
person wiesson    schedule 07.10.2014    source источник
comment
на момент вашего редактирования: Да, лучший способ - использовать компаратор mysql between и получать только то, что вам действительно нужно.   -  person dognose    schedule 07.10.2014
comment
Мне нужны все данные. Я получаю от -> до, и мне нужно сгруппировать и суммировать данные.   -  person wiesson    schedule 07.10.2014

Ответы (1)


arrow_upward
2
arrow_downward

Если данные лежат в MySQL, то я бы реализовал свое решение именно там. Тривиально использовать различные функции даты/времени MySQL для агрегирования этих данных. Давайте возьмем упрощенный пример, предполагая такую ​​структуру таблицы:

id:  autoincrement primary key
your_datetime: datetime or timestamp field
the_data: the data items you are trying to summarize

Запрос для суммирования по дням (сначала самые последние) будет выглядеть следующим образом:

SELECT
    DATE(your_datetime) as `day`,
    SUM(the_data) as `data_sum`
FROM table
GROUP BY `day`
ORDER BY `day` DESC

Если вы хотите ограничить его каким-то периодом времени (например, последние 7 дней), вы можете просто добавить условие where

SELECT
    DATE(your_datetime) as `day`,
    SUM(the_data) as `data_sum`
FROM table
WHERE your_datetime > DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
GROUP BY `day`
ORDER BY `day` DESC

Вот еще один пример, где вы указываете диапазон даты и времени

SELECT
    DATE(your_datetime) as `day`,
    SUM(the_data) as `data_sum`
FROM table
WHERE your_datetime BETWEEN '2014-08-01 00:00:00' AND '2014-08-31 23:59:59'
GROUP BY `day`
ORDER BY `day` DESC

Сумма по часам:

SELECT
    DATE(your_datetime) as `day`,
    HOUR(your_datetime) as `hour`
    SUM(the_data) as `data_sum`
FROM table
WHERE your_datetime BETWEEN '2014-08-01 00:00:00' AND '2014-08-31 23:59:59'
GROUP BY `day`, `hour`
ORDER BY `day` DESC, `hour` DESC

Сумма по месяцам:

SELECT
    YEAR(your_datetime) as `year`,
    MONTH(your_datetime) as `month`
    SUM(the_data) as `data_sum`
FROM table
GROUP BY `year`, `month`
ORDER BY `year` DESC, `month` DESC

Вот ссылка на функции MySQL Date/Time:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-sub

person Mike Brant    schedule 07.10.2014
comment
Спасибо! В настоящее время я пытаюсь понять date_sub и группу по командам. Как я писал в вопросе, моя цель - суммировать значения по заданному интервалу в диапазоне дат. Я добавил свой sql-запрос для получения данных. - person wiesson; 08.10.2014
comment
@wiesson Я добавил в свой ответ еще один пример, когда вы запрашиваете диапазон дат. Глядя на ваш запрос в обновленном вопросе, я заметил, что, похоже, вы используете значения временной метки unix в своем поле. Обычно это плохая идея в SQL. Вы должны предпочесть использование полей даты, даты и времени при сохранении значений даты/даты и времени в MySQL. Это предотвращает необходимость всегда преобразовывать в/из временных меток unix при попытке запроса данных. Это означает, что вам, как правило, будет проще использовать существующие индексы для этих полей. - person Mike Brant; 08.10.2014
comment
@wiesson Функция GROUP BY позволяет вам использовать агрегатные функции в вашем запросе. В моих примерах вы суммируете все значения данных для каждого значения даты. Это устраняет необходимость в таком суммировании на прикладном уровне. - person Mike Brant; 08.10.2014
comment
Спасибо за подсказку в поле даты и времени, я проверю это для нового проекта. Я действительно не понимаю, как подвести итог, например. часов или по месяцам. Какой-нибудь еще пример? - person wiesson; 09.10.2014
comment
@wiesson Я добавил больше примеров суммирования по часам (на самом деле день и час) и месяцу (год и месяц). - person Mike Brant; 09.10.2014
comment
Немного поздно, но как бы кто-то подвел итог получасу? - person Little Bobby Tables; 06.10.2015
comment
@josh Подведение итогов по получасу было бы немного сложнее, поскольку не существует стандартного способа агрегирования с шагом в полчаса. Вам, вероятно, потребуется сделать оператор CASE в SELECT, чтобы рассчитать время в одном из 48 получасовых интервалов. CASE используется в этом случае, чтобы определить, попадает ли дата и время в первую половину часа. Это утверждение может выглядеть как (2*HOUR(your_datetime)) + (CASE MIN(your_datetime) WHEN >= 30 THEN 1 ELSE 0 END) AS half_hour. Это даст значения от 0 до 47. Затем вы должны обратиться к столбцу half_hour для GROUPи ORDER BY. - person Mike Brant; 06.10.2015