利用Oracle内置分析函数进行高效统计汇总

利用Oracle内置分析函数进行高效统计汇总

分析函数是Oracle从8.1.6开始引入的一个新的概念，为我们分析数据提供了一种简单高效的处理方式。在分析函数出现以前，我们必须使用自联查询，子查询或者内联视图，甚至复杂的存储过程实现的语句，现在只要一条简单的SQL语句就可以实现了，而且在执行效率方面也有相当大的提高。下面我将针对分析函数做一些具体的说明。

分析函数的一般格式是函数名(参数列表) over ([partition by 字段名或表达式] [order by 字段名或表达式])，其中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 preceding 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()函数，这样使用就是聚合函数。

SQL> select department_id,sum(salary) sum_salary from employees group by department_id;

而这样使用就是分析函数。

SQL> select distinct department_id,sum(salary) over(partition by department_id) sum_salary from employees ;

它们得出的结果是相同的，都是：

DEPARTMENT_ID SUM_SALARY

------------- ----------

           10       4400

           20      19000

           30      24900

           40       6500

           50     156400

           60      28800

           70      10000

           80     304500

           90      58000

          100      51600

          110      20300

                    7000

 

已选择12行。

请注意，这里我们用到了distinct 关键字，如果不用distinct，第2个查询将返回107行数据，即employees表的每行记录都将返回一行sum_salary，因为不用distinct的含义是：针对每个雇员计算他/她所在的部门的薪金总数。

在这个例子中，聚合函数是更好的选择，但在另外一些情形下，我们更应该使用分析函数。

下面通过几个实例来介绍部分分析函数的用途。

问题1：求出每个省工业企业利润总额最多的前10名。

利用我们传统的聚合函数max可以方便地取出利润总额最多的一家，但是取出多家就无能为力了，同样，如果不分组我们可以通过排序取出任何一个省利润总额最多的前10名，但无法实现对多个省的分组。而采用rank聚合函数，可以方便地实现我们的要求。

完整的语句如下:

select * from

(select  substr(z01_04,1,2) 地区码,

DENSE_RANK() OVER (PARTITION BY substr(z01_04,1,2) order by b04_50 desc) 名次, b04_50 "利润总额"

from cj604,cj601 where b04_50>0 and cj601.uuid=cj604.uuid  ) where 名次<=10;

我们在开窗函数中使用地区码作为分组标志,并按照利润总额倒序排列。

结果如下(数据为模拟数据,以下同)

地区       名次   利润总额

---- ---------- ----------

31            1     963799

31            2     229643

...

31            9     135917

31           10     125245

32            1     349940

32            2     300587

...

注意:RANK()函数有3组,分别是rank, dense_rank, row_number,它们的区别是：

rank如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,比如：当第2名和第3名的利润相同时,rank的结果是1,2,2,4；而dense_rank则不会跳过这个排名,结果是1,2,2,3；而row_number哪怕是两个数据完全相同,排名也会不一样,结果是1,2,3,4

 

问题2:求出按登记注册类型分组的职工人数和销售额占总体的比重

分析函数ratio_to_report专门用来解决个体占总体的比重一类的问题。

语句

select d.*,round((ratio_to_report(职工人数) over())*100,1) as 人数百分比,

round((ratio_to_report(销售额) over())*100,1) as 销售额百分比

from

(select c.code 代码 , substr(b.reg_type,1,10) 登记注册类型, 职工人数, 销售额 from

(select substr(z01_08,1,1)||'00' code, sum(z01_171_01) 职工人数,sum(b03_01) 销售额

from cj603 c,cj601

j where c.uuid=j.uuid group by  substr(z01_08,1,1)

)c, djzclx b where c.code=b.reg_code

)d;

可以得出下面的结果：

代码 登记注册类型           职工人数     销售额 人数百分比 销售额百分比

---- -------------------- ---------- ---------- ---------- ------------

100  内资企业                8510509 3002627283         63      56.2

200  港、澳、台商投资企业    2066175  746728306       15.3        14

300  外商投资企业            2936984 1597046896       21.7      29.9

 

其中内层的子查询语句

select substr(z01_08,1,1)||'00' code, sum(z01_171_01) 职工人数,sum(b03_01) 销售额

from cj603 c,cj601 j where c.uuid=j.uuid group by  substr(z01_08,1,1)

获得如下的结果

CODE   职工人数     销售额

---- ---------- ----------

100     8510509 3002627283

200     2066175  746728306

300     2936984 1597046896

外层查询中ratio_to_report函数自动对结果集中的职工人数和销售额计算比重。

 

问题3 求按行业中类划分的大中小型企业个数

case语句不是分析函数，但它在统计汇总中的作用非常重要，可以用来设定复杂的分组条件。

以下是统计上工业大中小型企业划分标准。

指标名称	计量单位	大型	中型	小型
从业人员数	人	2000及以上且	300-2000以下	300以下或
销售收入	万元	30000及以上且	3000-30000以下	3000以下或
资产合计	万元	40000及以上	4000-40000以下	4000以下

 

请注意下面这个说明：大型和中型企业须同时满足所列各项条件的下限指标，否则下划一档。

比如某企业虽然从业人员数和销售收入符合大型企业的要求，但资产合计30000万元，不满足大型企业的要求，只能划归中型企业。实际上，中型企业单位数=企业单位总数-大型企业单位数-小型企业单位数。

因此，用b04_71<2000 and b04_71>=300 and b04_29>=30000 and b04_29<300000 and b04_16>=40000 and b04_16<400000的写法来表述中型标准是错误的。

正确写法应该是：

not(b04_71>=2000 and b04_29>=300000 and b04_16>=400000) and not(b04_71<300 or b04_29<30000 or b04_16<40000)，当然前提是这3个字段没有空值null。

完整的SQL语句如下：

select code 代码 , substr(INDUSTRY_NAME,1,10) 行业名称, c.*  from

(

select substr(z01_064,1,2) as code ,count(*) as TOL,

count(case when b04_71>=2000 and b04_29>=300000 and b04_16>=400000 then 1 else null end) as big,

count(case when not(b04_71>=2000 and b04_29>=300000 and b04_16>=400000)

and not(b04_71<300 or b04_29<30000 or b04_16<40000) then 1 else null end) as mid,

count(case when b04_71<300 or b04_29<30000 or b04_16<40000 then 1 else null end) as small

from cj604 a,cj601 b where a.uuid=b.uuid group by substr(z01_064,1,2)

)c, industry b where c.code=b.INDUSTRY_CODE

输出结果如下：

代码 行业名称           CODE        TOL        BIG        MID      SMALL                                                                                               

---- ------------------ ---- ---------- ---------- ---------- ----------                                                                                                

06   煤炭开采和洗选业   06            9          1          2          6                                                                                               

07   石油和天然气开采业 07            3          1          0          2                                                                                               

08   黑色金属矿采选业   08           13          1          3          8                                                                                                

13   农副食品加工业     13         1342          2         48       1269                                                                                               

14   食品制造业         14          784          3         66        691                                                                                               

15   饮料制造业         15          385          0         31        331                                                                                                

...

 

问题4 求按地区划分的3种登记注册类型的营业利润率

decode函数不是分析函数，但它在统计汇总中的作用非常重要，它的格式是：

decode(字段名或表达式,比较值1,返回值1, [比较值2,返回值2,...] 默认返回值)，它的作用是当字段或表达式的值等于比较值1时，就得出返回值1，当字段或表达式的值等于比较值2时，就得出返回值2，以此类推，如果都不符合，就返回默认返回值。其中从比较值2开始的参数对可以不提供。

语句

select substr(name,1,4) 地区名称, c.*

from(select 地区代码,

decode(注册类型码,'1',营业利润率,null) A1,

decode(注册类型码,'2',营业利润率,null) A2,

decode(注册类型码,'3',营业利润率,null) A3

from(

select

substr(z01_04,1,2) 地区代码,substr(z01_08,1,1) 注册类型码,

round(sum(b04_45)/sum(b04_29)*100,2) 营业利润率

from cj601 a,cj604 b where a.uuid=b.uuid

group by substr(z01_04,1,2),substr(z01_08,1,1)

)

)c,dq

where 地区代码=dq.code;

得出如下结果。

地区名称 地区     A1     A2     A3

-------- ---- ------ ------ ------

上海     31     6.74

上海     31            5.30

上海     31                   6.37

江苏     32     3.94

江苏     32            4.85

江苏     32                   4.32

浙江     33     4.55

浙江     33            5.25

浙江     33                   5.76

因为decode函数只针对一行内的数据进行处理，这样的结果并不符合要求，我们需要在第二层查询语句的外面再加一层按地区代码的分组汇总，完整写法如下：

select substr(name,1,4) 地区名称, c.*

from(

select 地区代码,SUM(A1) A1,SUM(A2) A2,SUM(A3) A3 from(

select 地区代码,

decode(注册类型码,'1',营业利润率,null) A1,

decode(注册类型码,'2',营业利润率,null) A2,

decode(注册类型码,'3',营业利润率,null) A3

from(

select

substr(z01_04,1,2) 地区代码,substr(z01_08,1,1) 注册类型码,

round(sum(b04_45)/sum(b04_29)*100,2) 营业利润率

from cj601 a,cj604 b where a.uuid=b.uuid

group by substr(z01_04,1,2),substr(z01_08,1,1)

)

)group by 地区代码 )c,dq

where 地区代码=dq.code;

这样就得到了正确的结果：

地区名称 地区     A1     A2     A3

------- ---- ------ ------ ------

上海     31     6.74   5.30   6.37

江苏     32     3.94   4.85   4.32

浙江     33     4.55   5.25   5.76

 

同样的问题我们也可以通过lead分析函数来完成。

select substr(name,1,4) 地区名称, 地区代码, A1,A2,A3

from(

select * from(

select 地区代码,

lead(营业利润率,    0) over(partition by 地区代码 order by 注册类型码) A1,

lead(营业利润率,    1) over(partition by 地区代码 order by 注册类型码) A2,

lead(营业利润率,    2) over(partition by 地区代码 order by 注册类型码) A3,

row_number( ) over(partition by 地区代码 order by 注册类型码) rn

from(

select

substr(z01_04,1,2) 地区代码,substr(z01_08,1,1) 注册类型码,

round(sum(b04_45)/sum(b04_29)*100,2) 营业利润率

from cj601 a,cj604 b where a.uuid=b.uuid

group by substr(z01_04,1,2),substr(z01_08,1,1)

))where rn=1

)c,dq

where 地区代码=dq.code;

lead函数的第一个参数是我们关心的值，第2个参数是偏移量n，对本例就是下n种注册类型码。

之所以要限定rn=1，还是因为分析函数对每一行都返回分组值，而我们关心的是注册类型为1的那一行。

利用lag和lead函数，我们可以在同一行中显示前n行的数据，也可以显示后n行的数据。

如果本例改用lag函数实现，代码如下：

注意过滤条件rn=3以及lag函数第2个参数的变化，我们把第3行作为当前行，取出它前面的2行。

select substr(name,1,4) 地区名称, 地区代码, A1,A2,A3

from(

select * from(

select 地区代码,

lag(营业利润率,    2) over(partition by 地区代码 order by 注册类型码) A1,

lag(营业利润率,    1) over(partition by 地区代码 order by 注册类型码) A2,

lag(营业利润率,    0) over(partition by 地区代码 order by 注册类型码) A3,

row_number( ) over(partition by 地区代码 order by 注册类型码) rn

from(

select

substr(z01_04,1,2) 地区代码,substr(z01_08,1,1) 注册类型码,

round(sum(b04_45)/sum(b04_29)*100,2) 营业利润率

from cj601 a,cj604 b where a.uuid=b.uuid

group by substr(z01_04,1,2),substr(z01_08,1,1)

))where rn=3

)c,dq

where 地区代码=dq.code;

这种方法比前一种方法利用sum分组汇总的好处是对字符类型和其他非数值类型字段都可以采用。

问题5 求按登记注册类型多个层次划分的单位个数小计和总计

例如要得出如下的结果：

代码   登记注册类型                            家数         

------ --------------------------------------- ---------

100    内资企业                                    61920

110      国有企业                                   1365

140      联营企业                                    476

141        国有联营企业                               52

...

200    港、澳、台商投资企业                         9004

210      合资经营企业(港或澳、台资)                 4454

220      合作经营企业(港或澳、台资)                  556

300    外商投资企业                                11396

310      中外合资经营企业                           5070

320      中外合作经营企业                            663

我们有3种方法，都可以完成任务。

方法1

select code 代码 , substrb('    ',1,item_level*2-2)||b.reg_type 登记注册类型, cnt 家数 from

(

(select substr(z01_08,1,1)||'00' code ,count(*) cnt

from cj601

group by substr(z01_08,1,1))

union

(select substr(z01_08,1,2)||'0' code ,count(*) cnt

from cj601

group by substr(z01_08,1,2))

union

(select substr(z01_08,1,3) code ,count(*) cnt

from cj601

group by substr(z01_08,1,3))

)

c, djzclx b where c.code=b.reg_code;

方法2

select code 代码 , substrb('    ',1,item_level*2-2)||b.reg_type 登记注册类型, cnt 家数 from

(

select

case when code3 is not null then code3

     when code2<>'0' then code2

else code1

end code,cnt from (

select substr(z01_08,1,1)||'00' code1 , substr(z01_08,1,2)||'0' code2 , substr(z01_08,1,3) code3 ,count(*) cnt

    from cj601

    group by rollup(substr(z01_08,1,1),substr(z01_08,1,2),substr(z01_08,1,3))

) where code2<>code3 or code3 is null and code1<>'00'

)

c, djzclx b where c.code=b.reg_code

order by 1

;

方法3

select code 代码 , substrb('    ',1,item_level*2-2)||b.reg_type 登记注册类型, cnt 家数 from

(

select

case when code3 is not null then code3

     when code2<>'0' then code2

else code1

end code,cnt from (

select substr(z01_08,1,1)||'00' code1 , substr(z01_08,1,2)||'0' code2 , substr(z01_08,1,3) code3 ,sum(cnt) cnt

    from (select substr(z01_08,1,3) z01_08,count(*) cnt from cj601 group by substr(z01_08,1,3))

    group by rollup(substr(z01_08,1,1),substr(z01_08,1,2),substr(z01_08,1,3))

) where code2<>code3 or code3 is null and code1<>'00'

)

c, djzclx b where c.code=b.reg_code

order by 1

;

上述3种写法都能得出正确的结果，但执行效率有巨大差别，第一种写法最简单，但是使用union要对cj601作了3遍全表扫描，执行效率最低，第2种写法对cj601做rollup分组，让数据库自动求小计和总计，第3种写法先对cj601做分组汇总，对结果集再做rollup分组，让数据库求小计和总计，在数据量中等的时候效率差不多，数据量大的时候，方法3效率更好些，因为rollup分组要处理的记录数更少，而rollup分组比普通分组开销大一些。

Oracle提供的分析函数一共有10多个，但有些专门的统计函数比如求标准差，相关系数，协方差等我们一般用不到，主要用到的是本文提到的RANK, lead, ratio_to_report等，我们如果能够将它们和decode函数,case语句配合，善加利用，就能编写出执行效率高的汇总语句，高效完成统计数据处理任务。更加详细的关于分析函数的信息，请参考资料Oracle9i Data Warehousing Guide 第19章SQL for Analysis in Data Warehouses。