使用over函数实现递归汇总

Oracle从 8.1.6 开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。

一、over函数

  over函数指定了分析函数工作的数据窗口的大小，这个数据窗口大小可能会随着行的变化而变化，例如：

over(order by salary)按照salary排序进行累计，order by是个默认的开窗函数

over(partition by deptno) 按照部门分区

over(order by salary range between 50 preceding and 150 following)每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150的数据记录

over(order by salary rows between 50 perceding and 150 following)前50行，后150行

over（order by salary rows between unbounded preceding and unbounded following）所有行

over（order by salary range between unbounded preceding and unbounded following）所有行

二、sum函数

  功能描述：该函数计算组中表达式的累积和。
SAMPLE：下例计算同一经理下员工的薪水累积值

SELECT manager_id, last_name, salary,
SUM (salary) OVER (PARTITION BY manager_id ORDER BY salary
RANGE UNBOUNDED PRECEDING) l_csum
FROM employees
WHERE manager_id in (101,103,108);

三、应用实例

1，  测试环境设置

设有销售表t_sales (subcompany,branch,region,customer,sale_qty); 存储客户的销售明细，记录如下所示。

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
北京分公司	北京经营部	片区1	客户1	2
北京分公司	北京经营部	片区1	客户2	2
北京分公司	北京经营部	片区1		4
北京分公司	北京经营部	片区2	客户1	2
北京分公司	北京经营部	片区2	客户2	2
北京分公司	北京经营部	片区2		4
北京分公司	北京经营部			8
北京分公司	其他经营部	片区1	客户1	2
北京分公司	其他经营部	片区1	客户2	2
北京分公司	其他经营部	片区1		4
北京分公司	其他经营部	片区2	客户1	2
北京分公司	其他经营部	片区2	客户2	2
北京分公司	其他经营部	片区2		4
北京分公司	其他经营部			8
北京分公司				16
				16

分析上面的临时结果，我们看到：

明细到客户的汇总信息，其除数为当前的sum(sale_qty),被除数应该是到片区的小计信息。

明细到片区的汇总信息，其除数为片区的sum(sale_qty),被除数为聚合到经营部的汇总数据。

。。。

考虑到上述因素，我们可以使用oracle的开窗函数over，将数据定位到我们需要定位的记录。如下代码中，我们利用开窗函数over直接将数据定位到其上次的小计位置。

over(partition by decode(f_branch, 1, null, subcompany), decode(f_branch, 1, null, decode(f_region, 1, null, branch)), decode(f_branch, 1, null, decode(f_region, 1, null, decode(f_customer, 1, null, region))), null)

经整理后的查询语句如下。
select subcompany,
       decode(f_branch, 1,subcompany||'(С¼Æ)', branch),
       decode(f_region,1,branch||'(С¼Æ)',region),
       decode(f_customer,1,region||'(С¼Æ)', customer),
       sale_qty,
       trim(to_char(round(sale_qty/
       sum(sale_qty) over(partition by decode(f_branch, 1, null, subcompany), decode(f_branch, 1, null, decode(f_region, 1, null, branch)), decode(f_branch, 1, null, decode(f_region, 1, null, decode(f_customer, 1, null, region))), null),2) *100,99990.99))
  from (select grouping(branch) f_branch,
               grouping(region) f_region,
               grouping(customer) f_customer,
               subcompany,
               branch,
               region,
               customer,
               sum(sale_qty) sale_qty
          from t_sales
         group by subcompany, rollup(branch, region, customer))

Subcompany	Branch	Region	Customer	Sale_qty	Rate
北京分公司	北京经营部	片区1	客户1	2	50.00
北京分公司	北京经营部	片区1	客户2	2	50.00
北京分公司	北京经营部	片区2	客户1	2	50.00
北京分公司	北京经营部	片区2	客户2	2	50.00
北京分公司	北京经营部	片区1	片区1(小计)	4	50.00
北京分公司	北京经营部	片区2	片区2(小计)	4	50.00
北京分公司	其他经营部	片区1	客户1	2	50.00
北京分公司	其他经营部	片区1	客户2	2	50.00
北京分公司	其他经营部	片区2	客户1	2	50.00
北京分公司	其他经营部	片区2	客户2	2	50.00
北京分公司	其他经营部	片区1	片区1(小计)	4	50.00
北京分公司	其他经营部	片区2	片区2(小计)	4	50.00
北京分公司	北京经营部	北京经营部(小计)	(小计)	8	50.00
北京分公司	其他经营部	其他经营部(小计)	(小计)	8	50.00
北京分公司	北京分公司(小计)	(小计)	(小计)	16	100.00
北京分公司	北京经营部	片区1	客户1	2	50.00

4，可能的另外一种解决方式（方案2）

select subcompany,
       decode(f_branch, 1,subcompany||'(С¼Æ)', branch),
       decode(f_region,1,branch||'(С¼Æ)',region),
       decode(f_customer,1,region||'(С¼Æ)', customer),
       sale_qty,
      /* trim(to_char(round(sale_qty/*/
       decode(f_branch+f_region+f_customer,
       0,
       (sum(sale_qty) over(partition by subcompany,branch,region))/2,
       1,
       (sum(sale_qty) over(partition by subcompany,branch))/3,
       2,
       (sum(sale_qty) over(partition by subcompany))/4 ,
       sum(sale_qty) over()/4  
        )/*
       ,2) *100,99990.99))*/
  from (select grouping(branch) f_branch,
               grouping(region) f_region,
               grouping(customer) f_customer,
               subcompany,
               branch,
               region,
               customer,
               sum(sale_qty) sale_qty
          from t_sales
         group by subcompany, rollup(branch, region, customer))

在上面的解决方式中，最大的问题在于开窗函数过大。导致每次计算涉及到的行数过多，影响到执行的速度和效率。并且需要额外的计算处理清除多余叠加进去的数值 。