8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

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.

AVG * CLUSTER_DETAILS CLUSTER_DISTANCE CLUSTER_ID CLUSTER_PROBABILITY
CLUSTER_SET CORR * COUNT * COVAR_POP * COVAR_SAMP *
CUME_DIST DENSE_RANK FEATURE_DETAILS FEATURE_ID FEATURE_SET
FEATURE_VALUE FIRST FIRST_VALUE * LAG LAST
LAST_VALUE * LEAD LISTAGG MATCH_RECOGNIZE MAX *
MEDIAN MIN * NTH_VALUE * NTILE PERCENT_RANK
PERCENTILE_CONT PERCENTILE_DISC PREDICTION PREDICTION_COST PREDICTION_DETAILS
PREDICTION_PROBABILITY PREDICTION_SET RANK RATIO_TO_REPORT REGR_ (Linear Regression) Functions *
ROW_NUMBER STDDEV * STDDEV_POP * STDDEV_SAMP * SUM *
VAR_POP * VAR_SAMP * VARIANCE * String Aggregation Top-N Queries

For more information see:

Hope this helps. Regards Tim...

Back to the Top.