ratio_to_report函數
?
??? 學習一下ratio_to_report函數，這個函數今天才遇到，以前都沒有注意。主要是用來進行比例的統計的，也算是一個基礎的統計函數，格式也沒有什么特別的，只在這里做一個簡單的記錄。
--------------------------------------------------
?
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.
?
--注意最終格式的整理
?
?
?
?