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

一、Oracle分析函數(shù)簡介：

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

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

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

我們看看上面的幾個(gè)例子就可以感覺到這幾個(gè)查詢和我們?nèi)粘Ｓ龅降牟樵冇行┎煌唧w有：

①需要對同樣的數(shù)據(jù)進(jìn)行不同級別的聚合操作
②需要在表內(nèi)將多條數(shù)據(jù)和同一條數(shù)據(jù)進(jìn)行多次的比較
③需要在排序完的結(jié)果集上進(jìn)行額外的過濾操作

二、Oracle分析函數(shù)簡單實(shí)例：

下面我們通過一個(gè)實(shí)際的例子：按區(qū)域查找上一年度訂單總額占區(qū)域訂單總額20%以上的客戶，來看看分析函數(shù)的應(yīng)用。

【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,
  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

三、分析函數(shù)OVER解析：

請注意上面的綠色高亮部分，group by的意圖很明顯：將數(shù)據(jù)按區(qū)域ID，客戶進(jìn)行分組，那么Over這一部分有什么用呢？假如我們只需要統(tǒng)計(jì)每個(gè)區(qū)域每個(gè)客戶的訂單總額，那么我們只需要group by o.region_id,o.cust_nbr就夠了。但我們還想在每一行顯示該客戶所在區(qū)域的訂單總額，這一點(diǎn)和前面的不同：需要在前面分組的基礎(chǔ)上按區(qū)域累加。很顯然group by和sum是無法做到這一點(diǎn)的(因?yàn)榫奂僮鞯募墑e不一樣，前者是對一個(gè)客戶，后者是對一批客戶)。

這就是over函數(shù)的作用了！它的作用是告訴SQL引擎：按區(qū)域?qū)?shù)據(jù)進(jìn)行分區(qū)，然后累積每個(gè)區(qū)域每個(gè)客戶的訂單總額(sum(sum(o.tot_sales)))。

現(xiàn)在我們已經(jīng)知道2001年度每個(gè)客戶及其對應(yīng)區(qū)域的訂單總額，那么下面就是篩選那些個(gè)人訂單總額占到區(qū)域訂單總額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> 

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

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> 

總結(jié)：

①Over函數(shù)指明在那些字段上做分析，其內(nèi)跟Partition by表示對數(shù)據(jù)進(jìn)行分組。注意Partition by可以有多個(gè)字段。

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

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