Moving average calculation in 8.4

HeyNow · #1 (**permalink**) 12-17-11, 01:25

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
Character: 121'

I've tried to use some window functions, but so far haven't quite figured it out. Would appreciate any help/pointers.

TIA.

Running 8.4

shammat · #2 (**permalink**) 12-17-11, 03:16

Can you upgrade to 9.x?

In that case it should as simple as:

Code:

select date, 
       avg("closePrice") over (partition by ticker order by date) rows between current row and 20 preceding
from "SecurityData" 
where ticker = 'XYZ' 
and date <= '2011-12-16' 
order by date desc

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.

HeyNow · #3 (**permalink**) 12-18-11, 22:56

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech