8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
RATIO_TO_REPORT Analytic Function
This article gives an overview of the RATIO_TO_REPORT
analytic function. If you are new to analytic functions you should probably read this introduction to analytic functions first.
Related articles.
Setup
The examples in this article require the following table.
--DROP TABLE emp PURGE; CREATE TABLE emp ( empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) ); INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20); INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30); INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10); INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20); INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10); INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20); INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30); INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20); INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10); COMMIT;
RATIO_TO_REPORT Analytic Function
The basic description for the RATIO_TO_REPORT
analytic function is shown below. The analytic clause is described in more detail here.
RATIO_TO_REPORT(expr) OVER ([ query_partition_clause ])
The RATIO_TO_REPORT
analytic function displays the ratio of the specified value to the sum of values in the set. It doesn't support the order or windowing clauses. Omitting a partitioning clause from the OVER
clause means the whole result set is treated as a single partition. In the following example we compute the ratio of the current salary compared
SELECT empno, ename, deptno, sal, RATIO_TO_REPORT(sal) OVER () AS r2r_sal FROM emp ORDER BY sal; EMPNO ENAME DEPTNO SAL R2R_SAL ---------- ---------- ---------- ---------- ---------- 7369 SMITH 20 800 .0275624462 7900 JAMES 30 950 .0327304048 7876 ADAMS 20 1100 .0378983635 7521 WARD 30 1250 .0430663221 7654 MARTIN 30 1250 .0430663221 7934 MILLER 10 1300 .044788975 7844 TURNER 30 1500 .0516795866 7499 ALLEN 30 1600 .0551248923 7782 CLARK 10 2450 .0844099914 7698 BLAKE 30 2850 .0981912145 7566 JONES 20 2975 .102497847 7788 SCOTT 20 3000 .103359173 7902 FORD 20 3000 .103359173 7839 KING 10 5000 .172265289 SQL>
Adding the partitioning clause allows us to calculate the ratio within a partition. In the following example we display the ratio of the current salary to the sum of the salaries within the department.
SELECT empno, ename, deptno, sal, RATIO_TO_REPORT(sal) OVER (PARTITION BY deptno) AS r2r_sal_per_dept FROM emp ORDER BY deptno, sal; EMPNO ENAME DEPTNO SAL R2R_SAL_PER_DEPT ---------- ---------- ---------- ---------- ---------------- 7934 MILLER 10 1300 .148571429 7782 CLARK 10 2450 .28 7839 KING 10 5000 .571428571 7369 SMITH 20 800 .0735632184 7876 ADAMS 20 1100 .101149425 7566 JONES 20 2975 .273563218 7788 SCOTT 20 3000 .275862069 7902 FORD 20 3000 .275862069 7900 JAMES 30 950 .10106383 7654 MARTIN 30 1250 .132978723 7521 WARD 30 1250 .132978723 7844 TURNER 30 1500 .159574468 7499 ALLEN 30 1600 .170212766 7698 BLAKE 30 2850 .303191489 SQL>
Quick Links
The "*" indicates the function supports the full analytic syntax, including the windowing clause.
For more information see:
Hope this helps. Regards Tim...