Purpose
RATIO_TO_REPORT() is an analytic function. It returns the ratio between a value and a group of values.
Syntax
RATIO_TO_REPORT(expr) OVER ([query_partition_clause])
Parameters
| Parameter | Description |
|---|---|
| expr | The name of the field to be queried. Notice Do not nest expr in a RATIO_TO_REPORT function or another analytic function. |
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information, see Analytic functions. |
Return type
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