利用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。