var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Moving average calculation in 8.4
Hi. Struggling a bit with calculation of a moving average. Basically I have a table with closing prices for stocks and I need to calculate a 10, 50 and 200 day moving average.
So for a 10-day moving average I could get the range by running this query
select "date", "closePrice" from "SecurityData" where "ticker" = 'XYZ' and "date" <= '2011-12-16' order by "date" desc LIMIT 10
It would seem pretty logical (to me anyway) that one could get the value of 10 day moving average just by using the avg function like so:
select avg("closePrice") from "SecurityData" where "ticker" = 'XYZ' and "date" <= '2011-12-16' order by "date" desc LIMIT 10
However I get the error:
'ERROR: column "SecurityData.date" must appear in the GROUP BY clause or be used in an aggregate function
SQL state: 42803
I've tried to use some window functions, but so far haven't quite figured it out. Would appreciate any help/pointers.
Can you upgrade to 9.x?
In that case it should as simple as:
The "rows between current row and 20 preceeding" clause was introduced in 9.0 and as far as I understand your question this is exactly what you need.
avg("closePrice") over (partition by ticker order by date) rows between current row and 20 preceding
where ticker = 'XYZ'
and date <= '2011-12-16'
order by date desc
Thanks! I can upgrade to 9, although was hoping to avoid it at the moment.
That was actually very close to what I needed (had to make a slight modification though, it's actually 'rows between 9 preceding and current row' instead of 'rows between current row and 10 preceding' to get the 10 day moving average).
Wonder if there's any way to get the query to return just a single number as opposed to a result set?