Purpose
This function is an analytic function that calculates the ratio of a value to the sum of a group of values.
Syntax
RATIO_TO_REPORT(expr) OVER ([query_partition_clause])
Parameters
Parameter |
Description |
|---|---|
| expr | The field to query. Notice You cannot nest expr in the RATIO_TO_REPORT function or other analytic functions. |
| OVER | Use the OVER clause to define the window for calculation. For more information, see Analytic Function Description. |
Return type
If expr is NULL, the result is NULL. Otherwise, the result is a numeric data type.
Examples
Assume that the emp_msg table has been created. Query the emp_msg table to calculate the ratio of each value in the mgr column to the total of the mgr column, grouped by the deptno field.
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