Oracle analytic function-ratio_to_report

分析函数RATIO_TO_REPORT 用来计算当前记录的指标expr占开窗函数over中包含记录的所有同一指标的百分比. 这里如果开窗函数的统计结果为null或者为0,就是说占用比率的被除数为0或者为null, 则得到的结果也为0.
开窗条件query_partition_clause决定被除数的值, 如果用户忽略了这个条件, 则计算查询结果中所有记录的汇总值.
用户不能使用其他分析函数或者ratio_to_report作为分析函数ratio_to_report的参数expr, 也就是说这个函数不能循环使用. 但我们可以使用其他普通函数作为这个分析函数的查询结果.

计算每个员工的工资在部门中的所占的比例

SQL> col employee_name for a35
SQL>
SQL> select department_id,
  2         first_name || ' ' || last_name employee_name,
  3         hire_date,
  4         salary,
  5         ratio_to_report(salary) over(partition by department_id) as ratio_salary   --over()每个员工占全体员工的百分比
  6    from employees
  7   where department_id <= 40;

DEPARTMENT_ID EMPLOYEE_NAME                       HIRE_DATE               SALARY RATIO_SALARY
------------- ----------------------------------- ------------------- ---------- ------------
           10 Jennifer Whalen                     2003-09-17 00:00:00       4400            1
           20 Michael Hartstein                   2004-02-17 00:00:00      13000   .684210526
           20 Pat Fay                             2005-08-17 00:00:00       6000   .315789474
           30 Den Raphaely                        2002-12-07 00:00:00      11000   .454545455
           30 Alexander Khoo                      2003-05-18 00:00:00       2600   .107438017
           30 Shelli Baida                        2005-12-24 00:00:00       2900   .119834711
           30 Sigal Tobias                        2005-07-24 00:00:00       2600   .107438017
           30 Guy Himuro                          2006-11-15 00:00:00       2600   .107438017
           30 Karen Colmenares                    2007-08-10 00:00:00       2500   .103305785
           40 Susan Mavris                        2002-06-07 00:00:00       6500            1

已选择10行。
此条目发表在analytic function, SQL分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s