分析函数使用技巧-使用over函数实现递归汇总计算.docVIP

针对目前复杂报表（比如销售结构对比，产品销售分布等）中“层次递归”量比、额比的计算，感觉目前的处理方式有点问题。推荐做如下修改，有兴趣的可以参照一下。 本来想象中，目前系统处理的方式会跟本文推荐的处理方式有较大区别，但到目前为止我并没有找到准确的证据证明这一点。所以就没动手修改目前系统中已经完成的处理。 Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。功能描述：该函数计算组中表达式的累积和。SAMPLE：下例计算同一经理下员工的薪水累积值 SELECT manager_id, last_name, salary,SUM (salary) OVER (PARTITION BY manager_id ORDER BY salaryRANGE UNBOUNDED PRECEDING) l_csumFROM employeesWHERE manager_id in (101,103,108); Subcompany Branch Region Customer Sale_qty 北京分公司 北京经营部 片区1 客户1 1 北京分公司 北京经营部 片区1 客户1 1 北京分公司 北京经营部 片区1 客户2 1 北京分公司 北京经营部 片区1 客户2 1 北京分公司 北京经营部 片区2 客户1 1 北京分公司 北京经营部 片区2 客户1 1 北京分公司 北京经营部 片区2 客户2 1 北京分公司 北京经营部 片区2 客户2 1 北京分公司 其他经营部 片区1 客户1 1 北京分公司 其他经营部 片区1 客户1 1 北京分公司 其他经营部 片区1 客户2 1 北京分公司 其他经营部 片区1 客户2 1 北京分公司 其他经营部 片区2 客户1 1 北京分公司 其他经营部 片区2 客户1 1 北京分公司 其他经营部 片区2 客户2 1 北京分公司 其他经营部 片区2 客户2 1 create table t_sales(subcompany varchar2(40),branch varchar2(40),region varchar2(40),customer varchar2(40),sale_qty numeric(18,4));comment on table t_sales is 销售表，分析函数测试;comment on column t_sales.subcompany is 分公司;comment on column t_sales.branch is 经营部;comment on column t_sales.region is 片区;comment on column t_sales.customer is 客户;comment on column t_sales.sale_qty is 销售数量; 2，问题提出 现在要求给出销售汇总报表，报表中需要提供的数据包括客户汇总，和客户在其上级机构中的销售比例。 Subcompany Branch Region Customer Sale_qty Rate 北京分公司 北京经营部 片区1 客户1 2 50％ 北京分公司 北京经营部 片区1 客户2 2 50％ 北京分公司 北京经营部 片区1 小计 4 50％ 北京分公司 北京经营部 片区2 客户1 2 50％ 北京分公司 北京经营部 片区2 客户2 2 50％ 北京分公司 北京经营部 片区2 小计 4 50％ 北京分公司 北京经营部 小计 小计 8 50％ 北京分公司 北京经营部 片区1 客户1 2 50％ 北京分公司 北京经营部 片区1 客户2 2 50％ 北京分公司 北京经营部 片区1 小计 4 50％ 北京分公司 北京经营部 片区2 客户1 2 50％ 北京分公司 北京经营部 片区2 客户2 2 50％ 北京分公司 北京经营部 片区2 小计 4 50％ 北京分公司 北京经营部 小计 小计 8 50％ 北京分公司 小计 小计 小计 16 100％ 3，解决方案（方案1） 首先我们可以使用oracle对group by 的扩展功能rollup得到如下的聚合汇总结果。 select subcompany, branch, region, customer, sum(sale_qty) sale_qty from t_sales group by rollup(subcompany,branch,region,customer); Subcompany Branch Region Customer Sale_qty