如何有效地将基于时间的数据集分组以计算组平均值和中位数

Question

我有一张有100000+记录的桌子，如下所示。根据该系统，这个表可能有每天的事务，而且它将一天一天地增长。

+---------------------+-----------+
|        Date         | Value     |
+---------------------+-----------+
| 2018-12-21 11:17:00 | 85.8      |
| 2018-12-28 15:07:00 | 16.2      |
| 2019-01-28 08:05:00 | 24.8      |
| 2019-02-28 12:07:00 | 13.9      |
| 2019-05-28 10:48:00 | 8         |
| 2019-05-28 09:17:00 | 40.6      |
| 2019-08-28 10:06:00 | 71.9      |
| 2019-08-16 17:28:00 | 36        |
| 2019-08-28 10:07:00 | 1922      |
| …                   | …         |
+---------------------+-----------+

我想按季度对数据进行分组，得到季度平均数和中位数，如下图所示。

平均值图平均值 X=季度，Y=值 中样图中位数 X=季度，Y=值

我正在使用PHPLaravel5.8和一个MySQL数据库。

在我的方法中，首先我根据开始日期和结束日期[“2018 3”, “2018 4”, “2019 1”, “2019 2”, “2019 3”]创建了一个键数组

然后，我使用foreach循环读取100000+记录，并将值放在相关键下的子数组中。这将在一个拥有4GB RAM的apache核心服务器中达到100%的CPU使用率，供一个用户访问。观察到前端回路消耗了巨大的CPU功率。

// prepare all array keys        
$chunkedData = array();
while (Carbon::parse($startDate)<=Carbon::parse($endDate)) {
        $chunkedData[Carbon::parse($startDate)->isoFormat('Y Q')] = array();
            $startDate = Carbon::parse($startDate)->addMonths(3);
        }
// foreach loop to read all the records
foreach ($arrScanData as $scanData) {
    $key = Carbon::parse($scanData->qr_generated_date)->isoFormat('Y Q');
    array_push($chunkedData[$key], (float)$scanData->value);
}

如果你能给我几个解决方案(逻辑和架构)来解决我的问题，我很感激。

Answer 1

我会让MySQL完成大部分处理。

以下将给你的宿舍和他们的价值：

SELECT CONCAT(YEAR(date) , ' ', QUARTER(date)) AS quarter, value FROM yourtable

平均数字可直接用下列方法计算：

SELECT CONCAT(YEAR(date) , ' ', QUARTER(date)) AS quarter, AVG(value) AS average FROM yourtable GROUP BY quarter

中值计算将根据第一个查询进行一些处理。我没有这方面的经验。MariaDB确实有一个中间函数。

Answer 2

我建议你改变你的做法。

不要读取一次100000+记录。只需从表中读取开始日期和结束日期即可生成季度数组。然后，您可以通过提供季度开始日期和结束日期来循环$chunkedData数组并读取每个季度的数据，或者可能只读取季度的数据。