Syntax
RATIO_TO_REPORT is an analytic function. 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.
Examples
The following example calculates the ratio-to-report value of each purchasing clerk's salary to the total of all purchasing clerks' salaries:
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
--统计业务人员工资在本部门中的占比
SQL> select deptno,
2 ename,
3 sal,
4 to_char(round(ratio_to_report(sal) over(partition by deptno) * 100,
5 2),
6 '990.00') || '%' rtr
7 from emp
8 order by emp.deptno, emp.sal desc;
DEPTNO ENAME SAL RTR
---------- ---------- ---------- --------
10 KING 5000 57.14%
10 CLARK 2450 28.00%
10 MILLER 1300 14.86%
20 SCOTT 3000 27.59%
20 FORD 3000 27.59%
20 JONES 2975 27.36%
20 ADAMS 1100 10.11%
20 SMITH 800 7.36%
30 BLAKE 2850 30.32%
30 ALLEN 1600 17.02%
30 TURNER 1500 15.96%
30 WARD 1250 13.30%
30 MARTIN 1250 13.30%
30 JAMES 950 10.11%
14 rows selected.
--注意最终格式的整理
-The End-