Purpose
You can call this function to return the ratio between a value and a group of values. It is an analytic function.
Syntax
RATIO_TO_REPORT(expr) OVER ([query_partition_clause])
Parameters
| Parameter | Description |
|---|---|
| expr | The name of the field to be queried. Notice Do not use a RATIO_TO_REPORT function or another analytic function as expr. |
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information about analytic functions, see Analytic functions. |
Return types
If expr is NULL, this function returns NULL. Otherwise, this function returns numeric data.
Examples
Assume that you have created the table emp_msg. Query the emp_msg table. Group the data records in the table by the deptno field. Return the ratio of each value in the mgr column.
obclient> SELECT * FROM emp_msg;
+--------+--------+------+------+
DEPTNO ENAME SAL MGR
+--------+--------+------+------+
10 CLARK 2750 7839
10 KING 5300 NULL
10 MILLER 1600 7782
20 ADAMS 1400 7788
20 FORD 3300 7566
20 JONES 3275 7839
20 SCOTT 3300 7566
20 SMITH 1100 7902
30 ALLEN 1900 7698
30 BLAKE 3150 7839
30 JAMES 1250 7698
30 MARTIN 1550 7698
30 TURNER 1800 7698
30 WARD 1550 7698
30 SCLARK 1750 7839
+--------+--------+------+------+
15 rows in set
obclient> SELECT deptno,ename,mgr,RATIO_TO_REPORT(mgr) OVER (PARTITION BY deptno)
FROM emp_msg;
+--------+--------+------+---------------------------------------------+
DEPTNO ENAME MGR RATIO_TO_REPORT(MGR)OVER(PARTITIONBYDEPTNO)
+--------+--------+------+---------------------------------------------+
10 CLARK 7839 .5018244670635682734780103706548876512387
10 KING NULL NULL
10 MILLER 7782 .4981755329364317265219896293451123487613
20 ADAMS 7788 .2014433149685729805230076821603165981221
20 FORD 7566 .1957010941258632730658803445332505625824
20 JONES 7839 .2027624738108171025064018002638317684488
20 SCOTT 7566 .1957010941258632730658803445332505625824
20 SMITH 7902 .2043920229688833708388298285093505082641
30 ALLEN 7698 .1421134249003101462117855560478511298183
30 BLAKE 7839 .1447164377492246344705361098803721754541
30 JAMES 7698 .1421134249003101462117855560478511298183
30 MARTIN 7698 .1421134249003101462117855560478511298183
30 TURNER 7698 .1421134249003101462117855560478511298183
30 WARD 7698 .1421134249003101462117855560478511298183
30 SCLARK 7839 .1447164377492246344705361098803721754541
+--------+--------+------+---------------------------------------------+
15 rows in set