一、Oracle分析函數(shù)簡介：
在日常的生產(chǎn)環(huán)境中，我們接觸得比較多的是OLTP系統(tǒng)(即Online Transaction Process)，這些系統(tǒng)的特點是具備實時要求，或者至少說對響應的時間多長有一定的要求；其次這些系統(tǒng)的業(yè)務邏輯一般比較復雜，可能需要經(jīng)過多次的運算。比如我們經(jīng)常接觸到的電子商城。
在這些系統(tǒng)之外，還有一種稱之為OLAP的系統(tǒng)(即Online Aanalyse Process)，這些系統(tǒng)一般用于系統(tǒng)決策使用。通常和數(shù)據(jù)倉庫、數(shù)據(jù)分析、數(shù)據(jù)挖掘等概念聯(lián)系在一起。這些系統(tǒng)的特點是數(shù)據(jù)量大，對實時響應的要求不高或者根本不關注這方面的要求，以查詢、統(tǒng)計操作為主。
我們來看看下面的幾個典型例子：
①查找上一年度各個銷售區(qū)域排名前10的員工
②按區(qū)域查找上一年度訂單總額占區(qū)域訂單總額20%以上的客戶
③查找上一年度銷售最差的部門所在的區(qū)域
④查找上一年度銷售最好和最差的產(chǎn)品
我們看看上面的幾個例子就可以感覺到這幾個查詢和我們?nèi)粘Ｓ龅降牟樵冇行┎煌?，具體有：
①需要對同樣的數(shù)據(jù)進行不同級別的聚合操作
②需要在表內(nèi)將多條數(shù)據(jù)和同一條數(shù)據(jù)進行多次的比較
③需要在排序完的結果集上進行額外的過濾操作
二、Oracle分析函數(shù)簡單實例：
下面我們通過一個實際的例子：按區(qū)域查找上一年度訂單總額占區(qū)域訂單總額20%以上的客戶，來看看分析函數(shù)的應用。
【1】測試環(huán)境：
復制代碼 代碼如下:
SQL> desc orders_tmp;
Name Null? Type
----------------------- -------- ----------------
CUST_NBR NOT NULL NUMBER(5)
REGION_ID NOT NULL NUMBER(5)
SALESPERSON_ID NOT NULL NUMBER(5)
YEAR NOT NULL NUMBER(4)
MONTH NOT NULL NUMBER(2)
TOT_ORDERS NOT NULL NUMBER(7)
TOT_SALES NOT NULL NUMBER(11,2)
【2】測試數(shù)據(jù)：
復制代碼 代碼如下:
SQL> select * from orders_tmp;
CUST_NBR REGION_ID SALESPERSON_ID YEAR MONTH TOT_ORDERS TOT_SALES
---------- ---------- -------------- ---------- ---------- ---------- ----------
11 7 11 2001 7 2 12204
4 5 4 2001 10 2 37802
7 6 7 2001 2 3 3750
10 6 8 2001 1 2 21691
10 6 7 2001 2 3 42624
15 7 12 2000 5 6 24
12 7 9 2000 6 2 50658
1 5 2 2000 3 2 44494
1 5 1 2000 9 2 74864
2 5 4 2000 3 2 35060
2 5 4 2000 4 4 6454
2 5 1 2000 10 4 35580
4 5 4 2000 12 2 39190
13 rows selected.
【3】測試語句：
復制代碼 代碼如下:
SQL> select o.cust_nbr customer,
o.region_id region,
sum(o.tot_sales) cust_sales,
sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
from orders_tmp o
where o.year = 2001
group by o.region_id, o.cust_nbr;
CUSTOMER REGION CUST_SALES REGION_SALES
---------- ---------- ---------- ------------
4 5 37802 37802
7 6 3750 68065
10 6 64315 68065
11 7 12204 12204
三、分析函數(shù)OVER解析：
請注意上面的綠色高亮部分，group by的意圖很明顯：將數(shù)據(jù)按區(qū)域ID，客戶進行分組，那么Over這一部分有什么用呢？假如我們只需要統(tǒng)計每個區(qū)域每個客戶的訂單總額，那么我們只需要group by o.region_id,o.cust_nbr就夠了。但我們還想在每一行顯示該客戶所在區(qū)域的訂單總額，這一點和前面的不同：需要在前面分組的基礎上按區(qū)域累加。很顯然group by和sum是無法做到這一點的(因為聚集操作的級別不一樣，前者是對一個客戶，后者是對一批客戶)。
這就是over函數(shù)的作用了！它的作用是告訴SQL引擎：按區(qū)域?qū)?shù)據(jù)進行分區(qū)，然后累積每個區(qū)域每個客戶的訂單總額(sum(sum(o.tot_sales)))。
現(xiàn)在我們已經(jīng)知道2001年度每個客戶及其對應區(qū)域的訂單總額，那么下面就是篩選那些個人訂單總額占到區(qū)域訂單總額20%以上的大客戶了
復制代碼 代碼如下:
SQL> select *
from (select o.cust_nbr customer,
o.region_id region,
sum(o.tot_sales) cust_sales,
sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
from orders_tmp o
where o.year = 2001
group by o.region_id, o.cust_nbr) all_sales
where all_sales.cust_sales > all_sales.region_sales * 0.2;
CUSTOMER REGION CUST_SALES REGION_SALES
---------- ---------- ---------- ------------
4 5 37802 37802
10 6 64315 68065
11 7 12204 12204
SQL>
現(xiàn)在我們已經(jīng)知道這些大客戶是誰了！哦，不過這還不夠，如果我們想要知道每個大客戶所占的訂單比例呢？看看下面的SQL語句，只需要一個簡單的Round函數(shù)就搞定了。
復制代碼 代碼如下:
SQL> select all_sales.*,
100 * round(cust_sales / region_sales, 2) || '%' Percent
from (select o.cust_nbr customer,
o.region_id region,
sum(o.tot_sales) cust_sales,
sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
from orders_tmp o
where o.year = 2001
group by o.region_id, o.cust_nbr) all_sales
where all_sales.cust_sales > all_sales.region_sales * 0.2;
CUSTOMER REGION CUST_SALES REGION_SALES PERCENT
---------- ---------- ---------- ------------ ----------------------------------------
4 5 37802 37802 100%
10 6 64315 68065 94%
11 7 12204 12204 100%
SQL>
總結：
①Over函數(shù)指明在那些字段上做分析，其內(nèi)跟Partition by表示對數(shù)據(jù)進行分組。注意Partition by可以有多個字段。
②Over函數(shù)可以和其它聚集函數(shù)、分析函數(shù)搭配，起到不同的作用。例如這里的SUM，還有諸如Rank，Dense_rank等。
您可能感興趣的文章:- oracle數(shù)據(jù)庫中sql%notfound的用法詳解
- Oracle中的INSTR,NVL和SUBSTR函數(shù)的用法詳解
- Oracle minus用法詳解及應用實例
- oracle數(shù)據(jù)庫定時任務dbms_job的用法詳解
- Oracle存儲過程游標用法分析
- 講解Oracle數(shù)據(jù)庫中的數(shù)據(jù)字典及相關SQL查詢用法
- Oracle中游標Cursor基本用法詳解
