Monday, 28 July 2014

RATIO_TO_REPORT - Analytic Function in SQL

In Oracle PL/SQL, RATIO_TO_REPORT is an analytic function which returns the proportion of a value over the total set of values. A statement "RATIO_TO_REPORT of 2 over (1,2,3,4,5)" is (2/15) i.e. 0.133. Note that it returns NULL for NULL values of a column.


Syntax: RATIO_TO_REPORT(expr) OVER ([ query_partition_clause ])
 It computes the ratio of a value to the sum of a set of values. If expr evaluates to null, then the ratio-to-report value also evaluates to null.
The set of values is determined by the query_partition_clause. If you omit that clause, then the ratio-to-report is computed over all rows returned by the query.
You cannot use RATIO_TO_REPORT or any other analytic function for expr. That is, you cannot nest analytic functions, but you can use other built-in function expressions for expr.
The SQL query below calculates the ratio of an employee's salary over the sum of salaries in his department. SELECT DEPT, EMPNO, SAL, RATIO_TO_REPORT(SAL) OVER (PARTITION BY DEPT) RATIO FROM EMPLOYEE
      DEPT      EMPNO        SAL      RATIO
---------- ---------- ---------- ----------
        10        100       2300 .479166667
        10        110       2500 .520833333
        20        120       5400 .382978723
        20        140       3400 .241134752
        20        170       5300 .375886525
        30        180       7300 .776595745
        30        130       2100 .223404255
        40        150       6400          1
        50        160       3200          1
9 rows selected.
 
SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr
   FROM employees
   WHERE job_id = 'PU_CLERK';

LAST_NAME                     SALARY         RR
------------------------- ---------- ----------
Khoo                            3100 .223021583
Baida                           2900 .208633094
Tobias                          2800 .201438849
Himuro                          2600  .18705036
Colmenares                      2500 .179856115

 

2 comments:

  1. wonderful information, I had come to know about your blog from my friend nandu , hyderabad,i have read atleast 7 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a ton once again,
    Regards, obiee training in hyderabad

    ReplyDelete
  2. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete