For each row, calculate the average of the last 20 rows in MySQL

Question

I need to calculate the mean and standard deviation for stock prices that I import from google finance in CSV files using a PHP script. I can import these csv files into mysql into different tables. I plan on storing each stock in a separate table to make it less difficult to start with.

In a spreadsheet, it would be pretty simple to calculate the current average for the last n rows. However, as soon as I import into MySQL, it amazes me (obviously with less knowledge) about how to calculate the average value for the last 20 rows for each row, starting from the 20th row.

My table structure, when I import from google finance, is as follows:

+-------------+---------+
|     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 conclude as follows:

+-------------+---------+-----------+--------------------+
|     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 . id 20 20 ... ..

, php foreach 20 , , .

( MySQL PHP). , .

Answer 1

,

$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_push, , . , 20, array_shift, ,

, , 20, foreach if

Answer 2

    $variableLoop=0;
    while($row = $result->fetch_assoc())
    {
        $array[]=$row['close'];
        if($variableLoop >= 19){
            $result=0;
            for($i=$variableLoop-19;$i<=$variableLoop;$i++){
                $result+=$array[$i];
            }
            $average=$result/20;
            //print or whatever you need to do with average and result
        }
        $variableLoop++;
    }

Answer 3

, , .

$values = array();
$sum = 0;
$i = 0;
while($row = $result->fetch_assoc())
{
    $values[] = $row['close'];
    $sum += $row['close'];
    if($i > 19)
    {
        $sum -= $values[$i - 20];
        $ row ['average'] = $ sum / 20;
    }
}

Answer 4

Here is the complete code that worked for me. Thanks to @ Memor-X, @origenes and @Terminus, as this code was developed using their answers. I even calculated the standard deviation using the same logic.

$averages = array();

while($row = $result->fetch_assoc())
{
      $sum = 0;
    $arraySize = array_push($averages,$row['close']);

    if($arraySize > 20)
    {
        array_shift($averages);
    }

if($arraySize > 19) {

    $sum = array_sum($averages);

    $average = $sum /20;
    echo "average: " .$average ."<br/>";
    }
    $sumOfDiff =0;
if($arraySize > 19) {
    foreach($averages as $valueitem)
    {
        $sumOfDiff += ($valueitem - $average)*($valueitem - $average);
    }
    $variance = $sumOfDiff/20;
    $stddev = sqrt($variance);
    $upperband = $average + ($stddev *2);
    $lowerband = $average - ($stddev *2);
    echo "variance: " .$variance."<br/>";
    echo "standard deviation: " .$stddev ."<br/>";

    }

}