Oracle Analytic Function RATIO_TO_REPORT
This is an example of Oracle analytic function RATIO_TO_REPORT to analyze monthly sales as a percentage (ratio) of annual sales. The monthly to annual sales ratio is computed as Jan/(Sum of 12 months), Feb/(Sum of 12 months) etc. For January it is 11000/183500 = 0.0599, February it is 12000/183500 = 0.0654 etc. as shown in the query output .

The next example shows analysis of quarterly sales as a percentage of annual sales ( quarter/annual ratio ). Based on type of analysis being performed, the PARTITION BY clause can be used to group the data ( mixed year data ). If it is blank or null, the function is computed for the complete result set of the query.

Note: The NTILE(4) splits the year into four quarters evenly since there are twelve months. If partial year data is available (current year analysis with less than 12 months), then use the syntax shown in the quarter query to get exact quarter for the month/year combination. 		   
RATIO_TO_REPORT 
SELECT
 TO_NUMBER(SUBSTR(q_dw.year_id,5)) month_No,
 NTILE(4)  OVER(ORDER BY 
            TO_NUMBER(SUBSTR(q_dw.year_id,5))) quarter,
 SUM(sales) monthly_sales,
 SUM(SUM(sales))  OVER(ORDER BY
            TO_NUMBER(SUBSTR(q_dw.year_id,5))) ytd_sales,
 SUM(SUM(sales))  OVER() yearly_total,
 ROUND(RATIO_TO_REPORT(SUM(sales))
                       OVER(),6)*100 "MONTHLY_%"
FROM
  (SELECT 201201 year_id, 11000  sales FROM DUAL UNION
   SELECT 201202 year_id, 12000  sales FROM DUAL UNION
   SELECT 201203 year_id, 14000  sales FROM DUAL UNION
   SELECT 201204 year_id, 16000  sales FROM DUAL UNION
   SELECT 201205 year_id, 15000  sales FROM DUAL UNION
   SELECT 201206 year_id, 18000  sales FROM DUAL UNION
   SELECT 201207 year_id, 19500  sales FROM DUAL UNION
   SELECT 201208 year_id, 17000  sales FROM DUAL UNION
   SELECT 201209 year_id, 16000  sales FROM DUAL UNION
   SELECT 201210 year_id, 16500  sales FROM DUAL UNION
   SELECT 201211 year_id, 13000  sales FROM DUAL UNION
   SELECT 201212 year_id, 15500  sales FROM DUAL
  ) q_dw
GROUP BY  TO_NUMBER(SUBSTR(q_dw.year_id,5));
Monthly Sales Analysis (month/year) -> Output

Oracle registered trademark of Oracle Corporation.
Last Revised on: February 23, 2012

  73950