http://blog.csdn.net/suyishuai/article/details/28856069
今天给大家介绍一个函数ratio_to_report占比函数,这个函数有多少作用呢,简单来说就是算百分比的。
这个函数在Oracle帮助文档的位置:SQL Reference里面,很好找的。
这个上面的语法我就不多说了,直接看演示的例子吧。
[sql] view plain copy
[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的。
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!