oracle 占比函数

这个函数在oracle帮助文档的位置：SQL Reference里面，很好找的。

       

除报告详细数据外，许多报告中还包括每行总数的百分比。例如，每名客户的订单相对于总订单的百分比，或每位销售代表的销售额相对于总销售额的百分比。

传统上，Oracle计算百分比的方法是在总计报告的子查询中使用SUM函数总计报告，然后把那个结果放到细节表中相除来计算百分比。你还可以用一个子查询作为SELECT语句表达式：

RATIO_TO_REPORT解析函数使得这种类型的查询更容易编码。Oracle 8i中引入了这个函数，它的格式如下：

RATIO_TO_REPORT (expr) OVER (query_partition_clause)

       这个上面的语法我就不多说了，直接看演示的例子吧。

[root@suys1 ~]# su - oracle
[oracle@suys1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:00:03 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2253784 bytes
Variable Size            1040190504 bytes
Database Buffers          603979776 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.
SQL> conn sec/sec
Connected.
SQL>
SQL> desc emp;
ERROR:
ORA-04043: object emp does not exist


SQL>
SQL> conn / as sysdba
Connected.
SQL> create table sec.emp as select * from scott.emp;--从scott里面造测试数据

Table created.

SQL>
SQL> conn sec/sec
Connected.
SQL> select * from emp order by DEPTNO; --检查下造好的数据

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

14 rows selected.

SQL> select DEPTNO,sum(sal) from emp group by DEPTNO; --看好这些数，一些说明用

    DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        20      10875
        10       8750

SQL> select DEPTNO,sum(sal) from emp group by rollup(DEPTNO);

    DEPTNO   SUM(SAL)
---------- ----------
        10       8750
        20      10875
        30       9400
                29025

SQL>

SQL> SELECT
  2    empno,ename,ename,hiredate,sal,deptno,
  3    ratio_to_report(sal) over () as pct1l,
  4    ratio_to_report(sal) over (partition by deptno) as pct2
  5  FROM emp;

     EMPNO ENAME      ENAME      HIREDATE         SAL     DEPTNO      PCT1L       PCT2
---------- ---------- ---------- --------- ---------- ---------- ---------- ----------
      7782 CLARK      CLARK      09-JUN-81       2450         10 .084409991        .28
      7839 KING       KING       17-NOV-81       5000         10 .172265289 .571428571
      7934 MILLER     MILLER     23-JAN-82       1300         10 .044788975 .148571429
      7566 JONES      JONES      02-APR-81       2975         20 .102497847 .273563218
      7902 FORD       FORD       03-DEC-81       3000         20 .103359173 .275862069
      7876 ADAMS      ADAMS      23-MAY-87       1100         20 .037898363 .101149425
      7369 SMITH      SMITH      17-DEC-80        800         20 .027562446 .073563218
      7788 SCOTT      SCOTT      19-APR-87       3000         20 .103359173 .275862069
      7521 WARD       WARD       22-FEB-81       1250         30 .043066322 .132978723
      7844 TURNER     TURNER     08-SEP-81       1500         30 .051679587 .159574468
      7499 ALLEN      ALLEN      20-FEB-81       1600         30 .055124892 .170212766

     EMPNO ENAME      ENAME      HIREDATE         SAL     DEPTNO      PCT1L       PCT2
---------- ---------- ---------- --------- ---------- ---------- ---------- ----------
      7900 JAMES      JAMES      03-DEC-81        950         30 .032730405  .10106383
      7698 BLAKE      BLAKE      01-MAY-81       2850         30 .098191214 .303191489
      7654 MARTIN     MARTIN     28-SEP-81       1250         30 .043066322 .132978723

14 rows selected.

SQL>

SQL> SELECT
  2    empno,ename,ename,hiredate,sal,deptno,
  3    ratio_to_report(sal) over (partition by deptno order by sal ) as pct2
  4  FROM emp;
  ratio_to_report(sal) over (partition by deptno order by sal ) as pct2
                                                 *
ERROR at line 3:
ORA-30487: ORDER BY not allowed here

      从上面的例子，大家可以看出这个函数的用法了。

      结果里面的 

             PCT1L是每个SAL占所有记录的SAL的百分比。比如EMPNO=7782的这行，2450/29025=.084409991

             PCT2是每个SAL占自己所在部门的百分比，还拿EMPNO=7782的这行， 2450/8750=0.28

     还有ratio_to_report是不支持order by的。