Oracle moving average

Question

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

I've written this query but it gets me wrong values

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  > 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;

Actually I know what MA is, but I don't understand how Oracle works!

I can calculate MA in Excel but I need to do this in database level, could you please tell me how to do this?

Answer 1

I don't understand what is the point of using TO_CHAR(SDATE, 'W') ? According to the doc this is supposed to give you the week number in the month...

Anyway I tried to simplify the query, hoping it still fit your needs:

SELECT x.*, ABS (LTMA-STMA) DIFFERENCE
FROM
(
  SELECT SDATE, PAYLOAD,
         AVG(PAYLOAD) OVER (ORDER BY SDATE rows BETWEEN 14 PRECEDING AND CURRENT ROW) STMA,
         AVG(PAYLOAD) OVER (ORDER BY SDATE rows BETWEEN 90 PRECEDING AND CURRENT ROW) LTMA
  FROM
  (
    SELECT a.SDATE, SUM(a.PAYLOAD) PAYLOAD
    FROM TABLE_PAYLOAD a
    WHERE a.SDATE  > sysdate - 3 * 365      
    GROUP BY a.SDATE
  )
) x
ORDER BY SDATE;

PS: I don't see the point either in doing TO_DATE(TO_CHAR(a.SDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD'), this is equivalent to a.SDATE...