-- Trend Analysis:
-- Compute Rolling 3-Month Average (AVG(JAN,FEB,MAR), AVG(FEB,MAR,APR), ...) 

SELECT
 ROWNUM idx,
 month_id,
 COUNT(*) OVER(ORDER BY month_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) idx_3m,
 val,
 SUM(val) OVER(ORDER BY month_id)  cumm_total,
 SUM(val) OVER(ORDER BY month_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) rolling_3m, 
 CASE 
  WHEN COUNT(*) OVER(ORDER BY month_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) < 3
   THEN NULL
   ELSE 
    ROUND(SUM(val) OVER(ORDER BY month_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)/
          COUNT(*) OVER(ORDER BY month_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),4)   
 END rolling_3m_avg
FROM
(SELECT   201401 month_id,  280 val  FROM DUAL  UNION  
 SELECT   201402 month_id,  255 val  FROM DUAL  UNION
 SELECT   201403 month_id,  265 val  FROM DUAL  UNION
 SELECT   201404 month_id,  295 val  FROM DUAL  UNION
 SELECT   201405 month_id,  275 val  FROM DUAL  UNION
 SELECT   201406 month_id,  305 val  FROM DUAL
)
;

-- If a month is missing (201406), and rolling 3-month average has skipped due to the data 
-- gap, the following change is required
-- (ORDER BY TO_DATE(month_id,'YYYYMM') 
--           RANGE BETWEEN INTERVAL '2' MONTH PRECEDING AND CURRENT ROW)


SELECT
 ROWNUM  idx,
 month_id,
 COUNT(*) OVER(ORDER BY month_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) idx_3m,
 COUNT(*) OVER(ORDER BY TO_DATE(month_id,'YYYYMM')
               RANGE BETWEEN INTERVAL '2' MONTH PRECEDING AND CURRENT ROW) idx_3m_xgap,
 val,
 SUM(val) OVER(ORDER BY month_id)  cumm_total,
 SUM(val) OVER(ORDER BY month_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) rolling_3m, 
 CASE 
  WHEN  COUNT(*) OVER(ORDER BY TO_DATE(month_id,'YYYYMM')
               RANGE BETWEEN INTERVAL '2' MONTH PRECEDING AND CURRENT ROW) < 3
   THEN NULL
   ELSE 
    ROUND(SUM(val) OVER(ORDER BY month_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)/
           COUNT(*) OVER(ORDER BY TO_DATE(month_id,'YYYYMM')
               RANGE BETWEEN INTERVAL '2' MONTH PRECEDING AND CURRENT ROW),4)   
 END rolling_3m_avg
FROM
(SELECT  201401 month_id,  280 val  FROM DUAL  UNION  
 SELECT  201402 month_id,  255 val  FROM DUAL  UNION
 SELECT  201403 month_id,  265 val  FROM DUAL  UNION
 SELECT  201404 month_id,  295 val  FROM DUAL  UNION
 SELECT  201405 month_id,  275 val  FROM DUAL  UNION     --201406 is missing 
 SELECT  201407 month_id,  305 val  FROM DUAL
)
;