oracle 占比函数

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

       

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

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

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

RATIO_TO_REPORT (expr) OVER (query_partition_clause)

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

 

[sql] view plain copy
 
  1. [root@suys1 ~]# su - oracle  
  2. [oracle@suys1 ~]$ sqlplus / as sysdba  
  3.   
  4. SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:00:03 2014  
  5.   
  6. Copyright (c) 1982, 2013, Oracle.  All rights reserved.  
  7.   
  8. Connected to an idle instance.  
  9.   
  10. SQL> startup  
  11. ORACLE instance started.  
  12.   
  13. Total System Global Area 1653518336 bytes  
  14. Fixed Size                  2253784 bytes  
  15. Variable Size            1040190504 bytes  
  16. Database Buffers          603979776 bytes  
  17. Redo Buffers                7094272 bytes  
  18. Database mounted.  
  19. Database opened.  
  20. SQL> conn sec/sec  
  21. Connected.  
  22. SQL>   
  23. SQL> desc emp;  
  24. ERROR:  
  25. ORA-04043: object emp does not exist  
  26.   
  27.   
  28. SQL>   
  29. SQL> conn / as sysdba  
  30. Connected.  
  31. SQL> create table sec.emp as select * from scott.emp;--从scott里面造测试数据  
  32.   
  33. Table created.  
  34.   
  35. SQL>   
  36. SQL> conn sec/sec  
  37. Connected.  
  38. SQL> select * from emp order by DEPTNO; --检查下造好的数据  
  39.   
  40.      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO  
  41. ---------- ---------- --------- ---------- --------- ---------- ---------- ----------  
  42.       7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10  
  43.       7839 KING       PRESIDENT            17-NOV-81       5000                    10  
  44.       7934 MILLER     CLERK           7782 23-JAN-82       1300                    10  
  45.       7566 JONES      MANAGER         7839 02-APR-81       2975                    20  
  46.       7902 FORD       ANALYST         7566 03-DEC-81       3000                    20  
  47.       7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20  
  48.       7369 SMITH      CLERK           7902 17-DEC-80        800                    20  
  49.       7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20  
  50.       7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30  
  51.       7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30  
  52.       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30  
  53.   
  54.      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO  
  55. ---------- ---------- --------- ---------- --------- ---------- ---------- ----------  
  56.       7900 JAMES      CLERK           7698 03-DEC-81        950                    30  
  57.       7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30  
  58.       7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30  
  59.   
  60. 14 rows selected.  
  61.   
  62. SQL> select DEPTNO,sum(sal) from emp group by DEPTNO; --看好这些数，一些说明用  
  63.   
  64.     DEPTNO   SUM(SAL)  
  65. ---------- ----------  
  66.         30       9400  
  67.         20      10875  
  68.         10       8750  
  69.   
  70. SQL> select DEPTNO,sum(sal) from emp group by rollup(DEPTNO);  
  71.   
  72.     DEPTNO   SUM(SAL)  
  73. ---------- ----------  
  74.         10       8750  
  75.         20      10875  
  76.         30       9400  
  77.                 29025  
  78.   
  79. SQL>   
  80.   
  81. SQL> SELECT  
  82.   2    empno,ename,ename,hiredate,sal,deptno,  
  83.   3    ratio_to_report(sal) over () as pct1l,    
  84.   4    ratio_to_report(sal) over (partition by deptno) as pct2  
  85.   5  FROM emp;  
  86.   
  87.      EMPNO ENAME      ENAME      HIREDATE         SAL     DEPTNO      PCT1L       PCT2  
  88. ---------- ---------- ---------- --------- ---------- ---------- ---------- ----------  
  89.       7782 CLARK      CLARK      09-JUN-81       2450         10 .084409991        .28  
  90.       7839 KING       KING       17-NOV-81       5000         10 .172265289 .571428571  
  91.       7934 MILLER     MILLER     23-JAN-82       1300         10 .044788975 .148571429  
  92.       7566 JONES      JONES      02-APR-81       2975         20 .102497847 .273563218  
  93.       7902 FORD       FORD       03-DEC-81       3000         20 .103359173 .275862069  
  94.       7876 ADAMS      ADAMS      23-MAY-87       1100         20 .037898363 .101149425  
  95.       7369 SMITH      SMITH      17-DEC-80        800         20 .027562446 .073563218  
  96.       7788 SCOTT      SCOTT      19-APR-87       3000         20 .103359173 .275862069  
  97.       7521 WARD       WARD       22-FEB-81       1250         30 .043066322 .132978723  
  98.       7844 TURNER     TURNER     08-SEP-81       1500         30 .051679587 .159574468  
  99.       7499 ALLEN      ALLEN      20-FEB-81       1600         30 .055124892 .170212766  
  100.   
  101.      EMPNO ENAME      ENAME      HIREDATE         SAL     DEPTNO      PCT1L       PCT2  
  102. ---------- ---------- ---------- --------- ---------- ---------- ---------- ----------  
  103.       7900 JAMES      JAMES      03-DEC-81        950         30 .032730405  .10106383  
  104.       7698 BLAKE      BLAKE      01-MAY-81       2850         30 .098191214 .303191489  
  105.       7654 MARTIN     MARTIN     28-SEP-81       1250         30 .043066322 .132978723  
  106.   
  107. 14 rows selected.  
  108.   
  109. SQL>   
  110.   
  111. SQL> SELECT  
  112.   2    empno,ename,ename,hiredate,sal,deptno,  
  113.   3    ratio_to_report(sal) over (partition by deptno order by sal ) as pct2  
  114.   4  FROM emp;  
  115.   ratio_to_report(sal) over (partition by deptno order by sal ) as pct2  
  116.                                                  *  
  117. ERROR at line 3:  
  118. 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的。

posted @ 2017-12-13 12:30  潇湘灬隐者  阅读(2924)  评论(0)  编辑  收藏  举报