通过php中的给定间隔对测量/时间序列求和的有效方法 [英] Efficient way to sum measurements / time series by given interval in php

问题描述

我在15分钟的同一时间间隔内有一系列测量数据/时间序列.此外，我有一个给定的时间段(例如，一天，当前星期，月份，年份(...)，并且需要按小时，天，月份，(...)汇总值.

I have a series of measurement data / time series in the same interval of 15 minutes. Furthermore, I have a given period (e.g. one day, current week, month, year, (...) and I need to summarize values by hour, day, month, (...).

例如按天汇总上个月的所有值.

E.g. summarize all values of the last month, by day.

我的方法是在第一步中生成一个具有每个周期所需间隔的临时数组.例如.在PHP中(PHP并不是必需的，如果它提供了更快的方法，我会更喜欢Python或Javascript)

My approach is to generate a temporary array with the needed interval per period in the first step. E.g. here in PHP (PHP is not that necessary, I would prefer Python or Javascript if it provides a faster method)

$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对象中的一些值.在此示例中，小时和分钟与临时数组中的键匹配.

In the second step, I loop through each date/value pair (in this example 4*24*31, (96 per day)) and assign them to my temporary array. For each date, I override some values from the datetime object. In this example the hour and the minutes to match the keys in the temp array.

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

if ($interval == "d") {

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

在最后一步，我遍历temp数组并总结每个数组.结果，我收到一个包含31个新的日期/值对的数组，并按每天汇总.这很好.但是，有更快或更有效的方法吗?这种方法耗时近0.5秒，耗时一个月. (如果有人对源代码感兴趣，我将添加要点).数据存储在具有15个mio条目的mysql数据库中.

At the last step, I loop through the temp array and summarize each array. As the result, I receive an array with 31 new date/values pairs, summarized by each day. This works fine. However is there a faster way or more efficient way? It takes nearly 0.5 seconds with this approach for one month. (If someone is interested in the source code, I will add a gist). The data are stored within a mysql database with 15 mio entries.

//我认为最好的方法是将其与mysql分组.

// I think the best way is to group this with mysql.

我当前要从一年中获取数据的SQL查询:

My current SQL query to fetch data from one year:

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日期/时间函数汇总此数据很简单.让我们以一个简单的示例为例，假设这样的表结构:

If the data lies in MySQL, then that is where I would implement my solution. It is trivial to use various MySQL date/time functions to aggregate this data. Let's take a simplistic example assuming a table structure like this:

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

按天(最近的优先)进行汇总的查询将如下所示:

A query to summarize by day (most recent first) would look like this:

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

如果您希望将其限制为某个时间段(例如最近7天)，则只需添加一个where条件

If you wanted to limit it by some period of time (last 7 days for example) you can simply add a where condition

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

这是另一个示例，其中您指定了一个日期时间范围

Here is another example where you specify a range of datetimes

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日期/时间函数的引用:

Here is a reference to the MySQL Date/Time functions:

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

这篇关于通过php中的给定间隔对测量/时间序列求和的有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！