title: Oracle moving average
body: <p>I have a table (date, payload) which contains data for around 10 years and I want to calculate the moving average (MA) for displaying the trend of the payload based on 14 days (2 weeks) and 90 days (12 weeks) intervals </p>
  
  <p>I've written this query but it gets me wrong values</p>
  
  <pre><code>SELECT x.*,
    ABS (LTMA-STMA) DIFFERECNE
  FROM
    (SELECT SDATE,
      PAYLOAD,
      AVG(PAYLOAD) OVER (ORDER BY W ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) STMA,
      AVG(PAYLOAD) OVER (ORDER BY W ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) LTMA
    FROM
  (SELECT b.*,
    TO_NUMBER(TO_CHAR( X.SDATE, 'W')) W
  FROM
    (SELECT TO_DATE(TO_CHAR(a.SDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD') SDATE,
      SUM(a.PAYLOAD) PAYLOAD
    FROM TABLE_PAYLOAD a
    WHERE a.SDATE  &gt; sysdate - 3 * 365      
    GROUP BY TO_CHAR(a.SDATE, 'YYYY-MM-DD')
    ORDER BY TO_CHAR(a.SDATE, 'YYYY-MM-DD')
    ) b    )
   ORDER BY SDATE
   ) x;
  </code></pre>
  
  <p>Actually I know what <code>MA</code> is, but I don't understand how Oracle works!</p>
  
  <p>I can calculate <code>MA</code> in Excel but I need to do this in database level, could you please tell me how to do this?</p>