mysql查询最后20行数据,对于每一行，计算MySQL中最后20行的平均值

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.

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.

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 |

+-------------+---------+-----------+--------------------+

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.

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.

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

}

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

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