Moving Averages using Limits

PHP
1

Hi,

Im trying to get some moving averages out using limits.

$query = mysql_query("SELECT AVG(close) FROM (
SELECT `close` FROM `shares` 
WHERE `name` = '". $name ."' AND `date` < '". $date ."' LIMIT $period) 
AS subquery");

I use the limit to attempt an moving average. That is, it finds the average of the last $period rows to the $date.

What will happen instead is that it will return the average of ALL rows until the limit row. So if I have 100 rows and the $period = 12, it will find the average of the first 12 rows.

How do I approach this without finding $period days before the current date (which is tricky because it data excludes weekend, so I can’t use something like $date-$period)?

2

Have you tried ordering your rows?

$query = mysql_query("
SELECT
    AVG(close)
FROM
    (
        SELECT
            close
        FROM
            shares
        WHERE
                name = '{$name}'
            AND
                date < '{$date}'
        ORDER BY
            date DESC
        LIMIT $period
    ) AS subquery
");

That way it will select the LAST $period rows rather than the first, before averaging.

3

!

I could of sworn I had tried this. Thankyou.

Is there anyway to include the current date in the average? So rather than date < $date, have date <= $date (which does not work, because it returns 1 row, where date = $date)