The RATIO_TO_REPORT function calculates the ratio of a value to the sum of a group of values.
Syntax
RATIO_TO_REPORT(expr) OVER ([query_partition_clause])
Parameter
| Parameter | Description |
|---|---|
| expr | The value can be only a positive constant. |
| OVER | Uses the OVER clause to define a window for calculation. |
Return type
The numeric data is returned.
Examples
The following example shows the ratio of the output of employees to the total output of the department. To create the product table and insert data into the data, execute the following statements:
CREATE TABLE product (name VARCHAR(8), deptno NUMBER, output NUMBER);
INSERT INTO product VALUES('Linda',100,5050);
INSERT INTO product VALUES('Tan',1001,8500);
INSERT INTO product VALUES('Tom',1001,3900);
INSERT INTO product VALUES('John',100,29500);
INSERT INTO product VALUES('Mery',1001,1500);
INSERT INTO product VALUES('Peter',100,1060);
COMMIT;
Execute the following statement:
SELECT name, OUTPUT, deptno, RATIO_TO_REPORT(output) OVER (partition BY deptno) FROM product;
The following query result is returned:
+-------+--------+--------+------------------------------------------------+
| NAME | OUTPUT | DEPTNO | RATIO_TO_REPORT(OUTPUT)OVER(PARTITIONBYDEPTNO) |
+-------+--------+--------+------------------------------------------------+
| Linda | 5050 | 100 | .1418140971637180567256388654872226902555 |
| John | 29500 | 100 | .8284189834316203313675933726481325470373 |
| Peter | 1060 | 100 | .0297669194046616119067677618646447627071 |
| Tan | 8500 | 1001 | .6115107913669064748201438848920863309353 |
| Tom | 3900 | 1001 | .2805755395683453237410071942446043165468 |
| Mery | 1500 | 1001 | .107913669064748201438848920863309352518 |
+-------+--------+--------+------------------------------------------------+