Moving Averages

Aikon

I want to calculate moving averages, common in stock charts, by pulling prices from a MySQL table. Being a newbie in both php and mysql, I need some advice for an efficient solution!

To see what I want, here's an example that DOES NOT work!

$period = 50;
for ($i=0;$i<10;$i++) {
$result = mysql_query("SELECT AVG(price) AS mov_avg FROM stock LIMIT ".$i.", ".$period);
$mov_avg = mysql_fetch_row($result);
echo $mov_avg[0]."<br>";
}

As you see, I want to calculate an average of 50 prices, and do it 10 times by moving one row forward in the table for each calculation. This example obviously does not work, since LIMIT limits the result of the query (which is a single row and field, the average), not the table itself, that I want.

So how do I rewrite this to do what I want? But even if it had worked it would not have been very efficient, calling mysql_query many times.

Is there a way to do all the calculations in the SELECT query? Subquery? Temporary table?

Or is it better to get all prices with a simple SELECT price FROM stock, and then do all the averaging in php?

In the latter case, what would be the most efficient way? There are no avg functions in php, as far as I know. I will need to do several hundreds of average calculations, so it need to be as efficient as possible.

Thanks in advance.

mrhappiness

did you mean you calculate 10 average prices (1 - 50, 2 - 51, ..., 10 - 60) and calculate the average of them?

$period = 50;
$avg_prices = array();
for ($i = 0; $i < 10; $i++)
{
  $sql = 'SELECT AVG(price) as move_avg
           FROM stock
           LIMIT '.$i.', '. $period;
  $result = mysql_fetch_assoc(mysql_query($sql));
  $avg_prices[] = $result['move_avg'];
}
$avg_price = array_sum($avg_prices) / count($avg_prices);

but maybe it's more efficient to select 60 entries and work with array_slice

Aikon

Originally posted by mrhappiness
did you mean you calculate 10 average prices (1 - 50, 2 - 51, ..., 10 - 60) and calculate the average of them?

Yes, except I don't average the averages! The moving averages, your array $avg_prices(), are typically plotted on a chart together with the prices. In a real application it will also be much more than 10 calculations.

Note that my example is not correct. You can't use LIMIT like that. I need another method for the SELECT.

Thanks for the php average code. I'll have a look at array_slice.

More suggestions are welcome. I am deliberately posting this question at an early stage in the thinking process, since I need some feedback on which method is best. If I should do the average calculations in the SQL query or in php.

mrhappiness

i don't understand why LIMIT isn't correct?

if you have 60 entries: #1 to #60
you do avg(#1 - #50), avg(#2 - #51), avg(#3 - #52), avg(#4 - #53), avg(#5 - #54), avg(#6 - #55), avg(#7 - #56), avg(#8 - #57), avg(#9 - #58), avg(#10 - #60)

is that correct?

only problem coming to my mind is that if you have less than 60 entries you would calc (#1 - #50), avg(#2 - #51), avg(#3 - #52), avg(#4 - #53), avg(#5 - #54), avg(#6 - #54), avg(#7 - #54), avg(#8 - #54), avg(#9 - #54), avg(#10 - #64) for example
but you can solve that by counting the entries and store it in $total

now you can do

for ($i=0; $i < max(1, min(10, $total - $period)); $i++)

i think

Aikon

Yes, one will have to take care of the situation when the price entries end, and stop calculating averages. Given the prices are in ascending order (from earlier to later).

I have tried the query and as far as I can see LIMIT limits the output of the query, it does not limit the table used for input (unfortunately, that is what I want to achieve!).

SELECT price FROM stock LIMIT 0,50
SELECT price FROM stock LIMIT 1,50
SELECT price FROM stock LIMIT 2,50
...

will give entry 0-49, 1-50, 2-51 and so on, since the output has the same number of rows as the input. However

SELECT avg(price) FROM stock LIMIT 0,50
SELECT avg(price) FROM stock LIMIT 1,50
SELECT avg(price) FROM stock LIMIT 2,50

will give only one row as output. The first query will then give the average (row 0 is inside the limit 0-50), but the second and third query will yield NULL, since there are no rows 1 and 2 in the output.

Do you see what I mean?

mrhappiness

i'm sorry but i don't see what you mean

you want the averag price from entry #1 to entry #50 and than, which average do you need next?

Aikon

Originally posted by mrhappiness
you want the averag price from entry #1 to entry #50 and than, which average do you need next?

Then I want the average price from #2 to #51 and so on, but the problem is

SELECT avg(price) FROM stock LIMIT 1,50
and so on

don't give me that, it gives NULL.

Anyone else see what I mean, or am I missing something?

Aikon

If noone is protesting, it seems the best (and only?) way to do this is to do it all in php. 🙂