oracle 統計/分析函數

標籤：

Oracle從8.1.6開始提供分析函數，分析函數用於計算基於組的某種彙總值，它和彙總函式的不同之處是對於每個組返回多行，而彙總函式對於每個組只返回一行。
文法：

Sql代碼

1. <analytic-function>(<argument>,<argument>,...)   
2. over(   
3. <query-partition-clause>   
4. <order-by-clause>   
5. <windowing-clause>   
6. )  

說明： 
<1> over是關鍵字，用於標識分析函數。 
<2> <analytic-function>是指定的分析函數的名字。 
<3> <argument>為參數，分析函數可以選取0-3個參數。 
<4> 分區子句<query-partition-clause>的格式為： 
     partition by<value_exp>[,value_expr]... 
    關鍵字partition by子句根據由分區運算式的條件邏輯地將單個結果集分成N組。這裡的"分區partition"和"組group" 都是同義字。 
<5> 排序子句order-by-clause指定資料是如何存在分區內的。其格式為： 
order[siblings]by{expr|position|c_alias}[asc|desc][nulls first|nulls last] 
其中： 
     A.asc|desc:指定了排列順序。 
     B.nulls first|nulls last:指定了包含空值的返回行應出現在有序序列中的第一個或最後一個位置。 
<6>視窗子句windowing-clause 
    給出一個固定的或變化的資料視窗方法，分析函數將對這些資料進行操作。在一組基於任意變化或固定的視窗中， 
可用該子句讓分析Function Compute出它的值。 
格式： 
{rows|range} 
{between 
{unbounded preceding|current row |<value_expr>{preceding|following} 
}and 
{unbounded preceding|current row |<value_expr>{preceding|following} 
}|{unbounded preceding|current row |<value_expr>{preceding|following 
}} 
    A.rows|range:此關鍵字定義了一個window。 
     B.between...and...:為窗品指一個起點和終點。 
     C.unbounded preceding：指明視窗是從分區(partition)的第一行開始。 
     D.current row:指明視窗是從當前行開始。 

開窗函數： 
       開窗函數指定了分析函數工作的資料視窗大小，這個資料視窗大小可能會隨著行的變化而變化，舉例如下：

• 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）

1、Oracle ROLLUP和CUBE 用法
      Oracle的GROUP BY語句除了最基本的文法外，還支援ROLLUP和CUBE語句。如果是Group by  ROLLUP(A, B, C)的話，首先會對(A、B、C)進行GROUP BY，然後對(A、B)進行GROUP BY，然後是(A)進行GROUP BY，最後對全表進行GROUP BY操作。
      如果是GROUP BY CUBE(A, B, C)，則首先會對(A、B、C)進行GROUP BY，然後依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最後對全表進行GROUP BY操作。也可以 Group by Rollup(A,(B,C)) ，Group by A Rollup(B,C)，……
      如何來區分到底是根據那個欄位做的匯總呢,這時候,oracle的grouping函數就粉墨登場了.如果當前的匯總記錄是利用該欄位得出的,grouping函數就會返回1,否則返回0。
樣本：

Sql代碼

1. select nvl(area_code,‘合計‘) area_code,sum(local_fare) local_fare  
2. from t  
3. group by rollup(nvl(area_code,‘合計‘)); 

Sql代碼

1. select area_code,bill_month,sum(local_fare) local_fare  
2. from t  
3. group by cube(area_code,bill_month)  
4. order by area_code,bill_month nulls last; 

Sql代碼

1. select decode(grouping(area_code),1,‘all area‘,to_char(area_code)) area_code,  
2. decode(grouping(bill_month),1,‘all month‘,bill_month) bill_month,  
3. sum(local_fare) local_fare  
4. from t  
5. group by cube(area_code,bill_month)  
6. order by area_code,bill_month nulls last; 

    簡單點說：為了產生資料統計以及橫向小計統計，可以在GROUP BY子句中使用ROLLUP操作符。為了產生資料統計、橫向小計、縱向小計結果，可以使用CUBE操作符。
2、Rank的用法
       功能描述：根據ORDER BY子句中運算式的值，從查詢返回的每一行，計算它們與其它行的相對位置。組內的資料按ORDER BY子句排序，然後給每一行賦一個號，從而形成一個序列，該序列從1開始，往後累加。每次ORDER BY運算式的值發生變化時，該序列也隨之增加。有同樣值的行得到同樣的數字序號（認為null時相等的）。然而，如果兩行的確得到同樣的排序，則序數將隨後跳躍。若兩行序數為1，則沒有序數2，序列將給組中的下一行分配值3，DENSE_RANK則沒有任何跳躍。
       rank()是跳躍排序，比如有兩個第二名時接下來就是第四名（同樣是在各個分組內）
       dense_rank()是連續排序，比如有兩個第二名時仍然跟著第三名。
樣本：

Sql代碼

1. select area_code,sum(local_fare) local_fare,  
2. rank() over (order by sum(local_fare) desc) fare_rank  
3. from t  
4. group by area_code; 

結果：
AREA_CODE LOCAL_FARE FARE_RANK
---------- -------------- ----------
5765      104548.72   1
5761      54225.41    2
5763      54225.41    2
5764      53156.77    4
5762      52039.62    5

Sql代碼

1. select area_code,sum(local_fare) local_fare,  
2. dense_rank() over (order by sum(local_fare) desc ) fare_rank  
3. from t  
4. group by area_code; 

結果：
AREA_CODE LOCAL_FARE FARE_RANK
---------- -------------- ----------
5765      104548.72   1
5761      54225.41    2
5763      54225.41    2
5764      53156.77    3 這是這裡出現了第三名
5762      52039.62    4
ROW_NUMBER
功能描述：返回有序組中一行的位移量，從而可用於按特定標準排序的行號。

Sql代碼

1. select area_code,sum(local_fare) local_fare,  
2. row_number() over (order by sum(local_fare) desc ) fare_rank  
3. from t  
4. group by area_code; 

結果：
AREA_CODE LOCAL_FARE FARE_RANK
---------- -------------- ----------
5765     104548.72    1
5761     54225.41     2
5763     54225.41     3
5764     53156.77     4
rank()樣本：
a. 取出資料庫中最後入網的n個使用者

Sql代碼

1. select user_id,tele_num,user_name,user_status,create_date   
2. from (  
3. select user_id,tele_num,user_name,user_status,create_date,  
4. rank() over (order by create_date desc) add_rank  
5. from user_info  
6. )  
7. where add_rank <= :n; 

b.根據object_name刪除資料庫中的重複記錄
create table t as select obj#,name from sys.obj$;
再insert into t1 select * from t1 數次.

Sql代碼

1. delete from t1 where rowid in (  
2. select row_id from (  
3. select rowid row_id,row_number() over (partition by obj# order by rowid ) rn  
4. ) where rn <> 1  
5. ); 

c. 取出各地區的話費收入在各個月份排名.

Sql代碼

1. select bill_month,area_code,sum(local_fare) local_fare,  
2. rank() over (partition by bill_month order by sum(local_fare) desc) area_rank  
3. from t  
4. group by bill_month,area_code 

結果：
BILL_MONTH AREA_CODE LOCAL_FARE AREA_RANK
--------------- --------------- -------------- ----------
200405        5765    25057.74      1
200405        5761    13060.43      2
200405        5763    13060.43      2
200405        5762    12643.79      4
200405        5764    12487.79      5
200406        5765    26058.46      1
200406        5761    13318.93      2
200406        5763    13318.93      2
200406        5764    13295.19      4
200406        5762    12795.06      5
200407        5765    26301.88      1
200407        5761    13710.27      2
200407        5763    13710.27      2
200407        5764    13444.09      4
200407        5762    13224.30      5
3、First/Last的用法
    First功能描述：從DENSE_RANK返回的集合中取出排在最前面的一個值的行（可能多行，因為值可能相等），因此完整的文法需要在開始處加上一個集合函數以從中取出記錄。
     Last功能描述：從DENSE_RANK返回的集合中取出排在最後面的一個值的行（可能多行，因為值可能相等），因此完整的文法需要在開始處加上一個集合函數以從中取出記錄。
樣本：下面例子中DENSE_RANK按部門分區，再按傭金commission_pct排序，FIRST取出傭金最低的對應的所有行，然後前面的MIN函數從這個集合中取出薪水最低的值；LAST取出傭金最高的對應的所有行，然後前面的MAX函數從這個集合中取出薪水最高的值。

Sql代碼

1. SELECT last_name, department_id, salary,  
2. MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)  
3. OVER (PARTITION BY department_id) "Worst",  
4. MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)  
5. OVER (PARTITION BY department_id) "Best"
6. FROM employees  
7. WHERE department_id in (20,80)  
8. ORDER BY department_id, salary; 

結果：
LAST_NAME DEPARTMENT_ID SALARY Worst Best
------------------------- ------------- ---------- ---------- ----------
Fay 20 6000 6000 13000
Hartstein 20 13000 6000 13000
Kumar 80 6100 6100 14000
Banda 80 6200 6100 14000
Johnson 80 6200 6100 14000
4、FIRST_VALUE/LAST_VALUE的用法
      FIRST_VALUE、LAST_VALUE是兩個分析函數。返回結果集中排在第一位和最後一位的值。文法是：
FIRST_VALUE (expr) OVER ( analytic_clause)
樣本：
計算按部門分區按薪水排序的資料視窗的第一個值對應的名字，如果薪水的第一個值有多個，則從多個對應的名字中取預設排序的第一個名字。

Sql代碼

1. SELECT department_id, last_name, salary, FIRST_VALUE(last_name)  
2. OVER (PARTITION BY department_id ORDER BY salary ASC ) AS lowest_sal  
3. FROM employees  
4. WHERE department_id in(20,30); 

結果：
DEPARTMENT_ID LAST_NAME SALARY LOWEST_SAL
------------- ------------------------- ---------- --------------
20 Fay 6000 Fay
20 Hartstein 13000 Fay
30 Colmenares 2500 Colmenares
30 Himuro 2600 Colmenares
30 Tobias 2800 Colmenares
30 Baida 2900 Colmenares
30 Khoo 3100 Colmenares
30 Raphaely 11000 Colmenares
5、Lag/Lead的用法
       功能描述：可以訪問結果集中的其它行而不用進行自串連。它允許去處理遊標，就好像遊標是一個數組一樣。在給定組中可參考當前行之前的行，這樣就可以從組中與當前行一起選擇以前的行。Offset是一個正整數，其預設值為1，若索引超出視窗的範圍，就返回預設值（預設返回的是組中第一行），其相反的函數是LEAD。
樣本：lag和lead函數介紹取出每個月的上個月和下個月的話費總額

Sql代碼

1. select area_code,bill_month, local_fare cur_local_fare,  
2. lag(local_fare,2,0) over (partition by area_code order by bill_month ) pre_local_fare,  
3. lag(local_fare,1,0) over (partition by area_code order by bill_month ) last_local_fare,  
4. lead(local_fare,1,0) over (partition by area_code order by bill_month ) next_local_fare,  
5. lead(local_fare,2,0) over (partition by area_code order by bill_month ) post_local_fare  
6. from (  
7. select area_code,bill_month,sum(local_fare) local_fare  
8. from t  
9. group by area_code,bill_month  
10. ) 

結果：
AREA_CODE BILL_MONTH CUR_LOCAL_FARE PRE_LOCAL_FARE LAST_LOCAL_FARE NEXT_LOCAL_FARE POST_LOCAL_FARE
--------- ---------- -------------- -------------- --------------- --------------- ---------------
5761 200405 13060.433 0 0 13318.93 13710.265
5761 200406 13318.93 0 13060.433 13710.265 14135.781
5761 200407 13710.265 13060.433 13318.93 14135.781 0
5761 200408 14135.781 13318.93 13710.265 0 0
5762 200405 12643.791 0 0 12795.06 13224.297
5762 200406 12795.06 0 12643.791 13224.297 13376.468
5762 200407 13224.297 12643.791 12795.06 13376.468 0
5762 200408 13376.468 12795.06 13224.297 0 0
6.RATIO_TO_REPORT用法
功能描述：該Function Computeexpression/(sum(expression))的值，它給出相對於總數的百分比，即當前行對sum(expression)的貢獻。
格式：RATIO_TO_REPORT (expr) OVER (query_partition_clause)
樣本：計算每個員工的工資占該類員工總工資的百分比

Sql代碼

1. SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr  
2. FROM employees  
3. WHERE job_id = ‘PU_CLERK‘; 

7.GROUPING SETS用法
     從Oracle9i開始，使用GROUPING SETS操作符可以合并多個分組的結果。並且可以用GROUP BY GROUPING SETS來代替GROUP BY CUBE。你可以應用來指定你感興趣的總數組合。因為它不必計算它不需要集合（也不會產生太多結果），所以對SQL引擎來說更為高效。
其格式為：
GROUP BY GROUPING SETS ((list), (list) ... )
      這裡（list）是圓括弧中的一個列序列，這個組合產生一個總數。要增加一個總和，必須增加一個(NUlL)分組集。
樣本：如果只要產生每項產品（包括所有顧客和通道）和每個顧客/通道組合（包括所有產品）的總數。

Sql代碼

1. SELECT prod_id, cust_id, channel_id, SUM(quantity_sold)  
2. FROM sales  
3. WHERE cust_id < 3  
4. GROUP BY GROUPING SETS (  
5. (prod_id), (cust_id, channel_id) ,NULL
6. ); 

樣本：統計人員的獲獎數

Sql代碼

1. SELECT
2.       (CASE WHEN grouping(a.c_xm)=1 AND grouping(a.n_jxdm)=1 AND grouping(a.c_rydm)=1  
3. THEN ‘合計‘
4. WHEN grouping(a.n_jxdm)=1 AND grouping(a.c_rydm)=1  
5. THEN ‘小計‘
6. ELSE a.c_xm  
7. END) c_xm  
8.       ,a.n_jxdm,a.c_rydm,COUNT(*)  
9. FROM T_SK_JPGL_HJMD a  
10. GROUP BY grouping sets (a.c_xm,(a.c_xm,a.n_jxdm,a.c_rydm),NULL) 

摘錄自：http://log-cd.javaeye.com/blog/405187

oracle 統計/分析函數