Decode360's Blog

业精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com

  BlogJava :: 首页 :: 新随笔 :: 联系 ::  :: 管理 ::
  397 随笔 :: 33 文章 :: 29 评论 :: 0 Trackbacks
ratio_to_report函数
 
    学习一下ratio_to_report函数,这个函数今天才遇到,以前都没有注意。主要是用来进行比例的统计的,也算是一个基础的统计函数,格式也没有什么特别的,只在这里做一个简单的记录。
--------------------------------------------------
 
Syntax
 
 
 
 
RATIO_TO_REPORT is an analytic function. It computes the ratio of a value to the sum of a set of values. If expr evaluates to null, then the ratio-to-report value also evaluates to null.
 
The set of values is determined by the query_partition_clause. If you omit that clause, then the ratio-to-report is computed over all rows returned by the query.
 
You cannot use RATIO_TO_REPORT or any other analytic function for expr. That is, you cannot nest analytic functions, but you can use other built-in function expressions for expr.
 
Examples
 
The following example calculates the ratio-to-report value of each purchasing clerk's salary to the total of all purchasing clerks' salaries:
 
SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr
   FROM employees
   WHERE job_id = 'PU_CLERK';
 
LAST_NAME                     SALARY         RR
------------------------- ---------- ----------
Khoo                            3100 .223021583
Baida                           2900 .208633094
Tobias                          2800 .201438849
Himuro                          2600  .18705036
Colmenares                      2500 .179856115
 
 
--统计业务人员工资在本部门中的占比
 
SQL> select deptno,
  2         ename,
  3         sal,
  4         to_char(round(ratio_to_report(sal) over(partition by deptno) * 100,
  5                       2),
  6                 '990.00') || '%' rtr
  7    from emp
  8   order by emp.deptno, emp.sal desc;
 
    DEPTNO ENAME             SAL RTR
---------- ---------- ---------- --------
        10 KING             5000   57.14%
        10 CLARK            2450   28.00%
        10 MILLER           1300   14.86%
        20 SCOTT            3000   27.59%
        20 FORD             3000   27.59%
        20 JONES            2975   27.36%
        20 ADAMS            1100   10.11%
        20 SMITH             800    7.36%
        30 BLAKE            2850   30.32%
        30 ALLEN            1600   17.02%
        30 TURNER           1500   15.96%
        30 WARD             1250   13.30%
        30 MARTIN           1250   13.30%
        30 JAMES             950   10.11%
 
14 rows selected.
 
--注意最终格式的整理
 
 
 
 
posted on 2009-04-02 21:56 decode360 阅读(358) 评论(0)  编辑  收藏 所属分类: 05.SQL

只有注册用户登录后才能发表评论。


网站导航: