目錄：
===============================================
1.Oracle分析函數簡介
2. Oracle分析函數簡單實例
3.分析函數OVER解析

一、Oracle分析函數簡介：

在日常的生產環境中，我們接觸得比較多的是OLTP系統(即Online Transaction Process)，這些系統的特點是具備實時要求，或者至少說對響應的時間多長有一定的要求；其次這些系統的業務邏輯一般比較復雜，可能需要經過多次的運算。比如我們經常接觸到的電子商城。

在這些系統之外，還有一種稱之為OLAP的系統(即Online Aanalyse Process)，這些系統一般用于系統決策使用。通常和數據倉庫、數據分析、數據挖掘等概念聯系在一起。這些系統的特點是數據量大，對實時響應的要求不高或者根本不關注這方面的要求，以查詢、統計操作為主。

我們來看看下面的幾個典型例子：
①查找上一年度各個銷售區域排名前10的員工
②按區域查找上一年度訂單總額占區域訂單總額20%以上的客戶
③查找上一年度銷售最差的部門所在的區域
④查找上一年度銷售最好和最差的產品

我們看看上面的幾個例子就可以感覺到這幾個查詢和我們日常遇到的查詢有些不同，具體有：

①需要對同樣的數據進行不同級別的聚合操作
②需要在表內將多條數據和同一條數據進行多次的比較
③需要在排序完的結果集上進行額外的過濾操作

二、Oracle分析函數簡單實例：

下面我們通過一個實際的例子：按區域查找上一年度訂單總額占區域訂單總額20%以上的客戶，來看看分析函數的應用。

【1】測試環境：

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】測試數據：

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,

2 o.region_id region,

3 sum(o.tot_sales) cust_sales,

4 sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales

5 from orders_tmp o

6 where o.year = 2001

7 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

三、分析函數OVER解析：

請注意上面的綠色高亮部分，group by的意圖很明顯：將數據按區域ID，客戶進行分組，那么Over這一部分有什么用呢？假如我們只需要統計每個區域每個客戶的訂單總額，那么我們只需要group by o.region_id,o.cust_nbr就夠了。但我們還想在每一行顯示該客戶所在區域的訂單總額，這一點和前面的不同：需要在前面分組的基礎上按區域累加。很顯然group by和sum是無法做到這一點的(因為聚集操作的級別不一樣，前者是對一個客戶，后者是對一批客戶)。

這就是over函數的作用了！它的作用是告訴SQL引擎：按區域對數據進行分區，然后累積每個區域每個客戶的訂單總額(sum(sum(o.tot_sales)))。

現在我們已經知道2001年度每個客戶及其對應區域的訂單總額，那么下面就是篩選那些個人訂單總額占到區域訂單總額20%以上的大客戶了

SQL> select *

2 from (select o.cust_nbr customer,

3 o.region_id region,

4 sum(o.tot_sales) cust_sales,

5 sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales

6 from orders_tmp o

7 where o.year = 2001

8 group by o.region_id, o.cust_nbr) all_sales

9 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>

現在我們已經知道這些大客戶是誰了！哦，不過這還不夠，如果我們想要知道每個大客戶所占的訂單比例呢？看看下面的SQL語句，只需要一個簡單的Round函數就搞定了。

SQL> select all_sales.*,

2 100 * round(cust_sales / region_sales, 2) || '%' Percent

3 from (select o.cust_nbr customer,

4 o.region_id region,

5 sum(o.tot_sales) cust_sales,

6 sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales

7 from orders_tmp o

8 where o.year = 2001

9 group by o.region_id, o.cust_nbr) all_sales

10 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函數指明在那些字段上做分析，其內跟Partition by表示對數據進行分組。注意Partition by可以有多個字段。

②Over函數可以和其它聚集函數、分析函數搭配，起到不同的作用。例如這里的SUM，還有諸如Rank，Dense_rank等。

參考資料：《Mastering Oracle SQL》(By Alan Beaulieu, Sanjay Mishra O'Reilly June 2004 0-596-00632-2)

-------------------------------------------------------------
生活就像打牌，不是要抓一手好牌，而是要盡力打好一手爛牌。

posted on 2008-06-25 14:02 Paul Lin 閱讀(15291) 評論(8) 編輯收藏所屬分類: Oracle 開發

FeedBack: