Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2011

    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 "" 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.


    Running 8.4

  2. #2
    Join Date
    Nov 2003
    Provided Answers: 23
    Can you upgrade to 9.x?

    In that case it should as simple as:
    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.

  3. #3
    Join Date
    Dec 2011
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts