ORACLE 分析函数ratio_to_report的使用

Ratio_to_report函数

Syntax

分析函数RATIO_TO_REPORT 用来计算当前记录的指标expr占开窗函数over中包含记录的所有同一指标的百分比.这里如果开窗函数的统计结果为null或者为0,就是说占用比率的被除数为0或者为null, 则得到的结果也为0.

开窗条件query_partition_clause决定被除数的值, 如果用户忽略了这个条件, 则计算查询结果中所有记录的汇总值.

用户不能使用其他分析函数或者ratio_to_report作为分析函数ratio_to_report的参数expr, 也就是说这个函数不能循环使用. 但我们可以使用其他普通函数作为这个分析函数的查询结果.

 Examples 1

下面的示例演示了如何计算每一个员工的工资占部门全部工资的比例.

创建表

create table T_salary(
F_depart varchar2(20),
F_EMP varchar2(20),
F_salary integer 
);

truncate table t_salary;
-- 插入测试数据
insert into t_salary(f_depart, f_emp, f_salary)
select '信息管理部','张三',10000 from dual
union all
select '信息管理部','李四',2000 from dual
union all
select '人力资源部','王五',3000 from dual
union all
select '人力资源部','赵六',10000 from dual;

commit;

查询每个员工占所在部门的工资比例

select f_depart,f_emp,f_salary,sum(f_salary) over(partition by f_depart) sum_salary,
  ratio_to_report(f_salary) over(partition by f_depart) ratio_salary
from t_salary;

递归查询员工占所在部门的百分比, 以及部门所占公司的工资比例.

select f_depart,f_emp,f_salary,g1,
   sum(f_salary) over(partition by decode(g1, 0, f_depart,  null), g1) sum_salary,
   ratio_to_report(f_salary) over(partition by decode(g1, 0, f_depart, null), g1) r_salary
from (      
select f_depart,
       f_emp,
       sum(f_salary) f_salary, grouping(f_depart) + grouping(F_emp) g1
  from t_salary
 group by rollup(f_depart, f_emp)
 ) t

 

 

由于分析函数可以使用普通函数的结果作为expr参数, 所以上面的代码又可以整合为下述方式.

select f_depart,
       f_emp,
       sum(f_salary) f_salary,
       sum(sum(f_salary)) over(partition by decode(grouping(f_depart) + grouping(F_emp), 0, f_depart, null),grouping(f_depart) + grouping(F_emp)) sum_salary,
       ratio_to_report(sum(f_salary)) over(partition by decode(grouping(f_depart) + grouping(F_emp), 0, f_depart, null), grouping(f_depart) + grouping(F_emp)) r_salary,
       grouping(f_depart) + grouping(F_emp) g1
  from t_salary
 group by rollup(f_depart, f_emp)