对于每一行，计算MySQL中最后20行的平均值 [英] For each row, compute average for last 20 rows in MySQL
问题描述
我需要使用PHP脚本计算要从Google财经导入的csv文件中的股票价格的平均和标准偏差.我可以将这些csv文件导入到mysql的不同表中.我计划将每种股票存储在单独的表中，以使其开始时变得不那么复杂.
I need to calculate average and standard deviation for stock prices that I am importing from google finance in csv files using a PHP script. I can import these csv files into mysql into different tables. I am planning to store each stock in a separate table so as to make it less complicated to start with.
在电子表格中，计算最后n行的移动平均值非常容易.但是，一旦导入到MySQL，我就对如何计算从第20行开始的每行最后20行的平均值感到惊讶(显然是了解较少).
In a spreadsheet, it would be fairly easy to calculate a running average for last n rows. However, once I import to MySQL, I am getting struck (obviously with less knowledge) on how to calculate average for last 20 rows for each row starting from 20th row.
从Google财务导入时，我的表结构如下:
My table structure when I import from google finance looks like this:
+-------------+---------+
| id | close |
+-------------+---------+
| a1447128000 | 1353.5 |
| 1 | 1356.2 |
| 2 | 1352.65 |
| 3 | 1355.65 |
| 4 | 1354.2 |
| 5 | 1356 |
| 6 | 1351 |
| 7 | 1352.5 |
| 8 | 1350 |
| 9 | 1349.3 |
| 10 | 1343.6 |
| 11 | 1342.4 |
| 12 | 1340.7 |
| 13 | 1338.5 |
| 14 | 1340.5 |
| 15 | 1338.5 |
| 16 | 1340 |
| 17 | 1335.25 |
| 18 | 1340.5 |
| 19 | 1341 |
| 20 | 1338.95 |
| 21 | 1334 |
| 22 | 1326.5 |
| 23 | 1320.1 |
| 24 | 1318 |
+-------------+---------+
我正在尝试实现这样的输出:
I am trying to achieve an output like this:
+-------------+---------+-----------+--------------------+
| id | close | average | standard deviation |
+-------------+---------+-----------+--------------------+
| a1447128000 | 1353.5 | | |
| 1 | 1356.2 | | |
| 2 | 1352.65 | | |
| 3 | 1355.65 | | |
| 4 | 1354.2 | | |
| 5 | 1356 | | |
| 6 | 1351 | | |
| 7 | 1352.5 | | |
| 8 | 1350 | | |
| 9 | 1349.3 | | |
| 10 | 1343.6 | | |
| 11 | 1342.4 | | |
| 12 | 1340.7 | | |
| 13 | 1338.5 | | |
| 14 | 1340.5 | | |
| 15 | 1338.5 | | |
| 16 | 1340 | | |
| 17 | 1335.25 | | |
| 18 | 1340.5 | | |
| 19 | 1341 | 1346.5975 | 6.885664002 |
| 20 | 1338.95 | 1345.87 | 6.886588415 |
| 21 | 1334 | 1344.76 | 6.921155973 |
| 22 | 1326.5 | 1343.4525 | 7.729917124 |
| 23 | 1320.1 | 1341.675 | 8.7418605 |
| 24 | 1318 | 1339.865 | 9.660513703 |
+-------------+---------+-----------+--------------------+
正如您正确注意到的那样，对于ID为19的行，平均值为最后20行.对于第20行ID，平均值将是最后20行...依此类推.
As you noticed rightly, for row id 19, the average will be of last 20 rows. for row id 20, the average will be of last 20 rows ... so on and so forth.
我什至想知道php的foreach循环是否可用于遍历每一行并获得最后20行的平均值，但是当我尝试时我完全迷失了.
I was even wondering if php's foreach loop can be used to loop through each row and get average for last 20 rows but I was totally lost when I tried.
我无法获得任何线索(在MySQL或PHP中).感谢您提供有关如何解决此问题的建议.
I am not able to get any leads on this (either in MySQL or PHP). Will be grateful for any advice on how to resolve this.
推荐答案
下面的代码大致显示了您可以做什么
the code bellow roughly shows what you can do
$averages = array();
while($row = $result->fetch_assoc())
{
$sum = 0;
$arraySize = array_push($averages,$row['close']);
if($arraySize > 20)
{
array_shift($averages)
}
foreach($averages as $value)
{
$sum += $value;
}
$average = $sum / $arraySize
}
从代码开头的空数组开始，在MySQL结果循环的每次迭代中，您都使用 array_shift 将所有内容向下移动1并减去第一个值
starting with an empty array at the beginning of your code, in each iteration of your loop of the MySQL Results you use array_push which also returns the size of the array after the value has be added to it. you then check this value and if it's greater than 20 you use array_shift which shifts everything down 1 dropping the first value
如果不希望它在数组中的值数小于20时不计算，只需将foreach和下面的行包装在if语句中
if you want it to not calculate when the number of values in the array is less than 20 simply wrap the the foreach and the line bellow it in an if statement
这篇关于对于每一行，计算MySQL中最后20行的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
