массивы — эффективный способ суммирования измерений / временных рядов по заданному интервалу в переполнении стека
У меня есть серия данных измерений / временных рядов в том же интервале 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;
Решение
Если данные лежат в 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:
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-sub
Другие решения
Других решений пока нет …