目錄：
===============================================
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í)時要求，或者至少說對響應(yīng)的時間多長有一定的要求；其次這些系統(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í)時響應(yīng)的要求不高或者根本不關(guān)注這方面的要求，以查詢、統(tǒng)計操作為主。
我們來看看下面的幾個典型例子：
①查找上一年度各個銷售區(qū)域排名前10的員工
②按區(qū)域查找上一年度訂單總額占區(qū)域訂單總額20%以上的客戶
③查找上一年度銷售最差的部門所在的區(qū)域
④查找上一年度銷售最好和最差的產(chǎn)品
我們看看上面的幾個例子就可以感覺到這幾個查詢和我們?nèi)粘Ｓ龅降牟樵冇行┎煌唧w有：
①需要對同樣的數(shù)據(jù)進(jìn)行不同級別的聚合操作
②需要在表內(nèi)將多條數(shù)據(jù)和同一條數(shù)據(jù)進(jìn)行多次的比較
③需要在排序完的結(jié)果集上進(jìn)行額外的過濾操作
分析函數(shù)語法:
FUNCTION_NAME(<argument>,<argument>...)
OVER
(<Partition-Clause><Order-by-Clause><Windowing Clause>)
例:
sum(sal) over (partition by deptno order by ename) new_alias
sum就是函數(shù)名
(sal)是分析函數(shù)的參數(shù),每個函數(shù)有0~3個參數(shù),參數(shù)可以是表達(dá)式,例如:sum(sal+comm)
over 是一個關(guān)鍵字,用于標(biāo)識分析函數(shù),否則查詢分析器不能區(qū)別sum()聚集函數(shù)和sum()分析函數(shù)
partition by deptno 是可選的分區(qū)子句,如果不存在任何分區(qū)子句,則全部的結(jié)果集可看作一個單一的大區(qū)
order by ename 是可選的order by 子句,有些函數(shù)需要它,有些則不需要.依靠已排序數(shù)據(jù)的那些函數(shù),如:用于訪問結(jié)果集中前一行和后一行的LAG和LEAD,必須使用,其它函數(shù),如AVG,則不需要.在使用了任何排序的開窗函數(shù)時,該子句是強(qiáng)制性的,它指定了在計算分析函數(shù)時一組內(nèi)的數(shù)據(jù)是如何排序的.
1)FUNCTION子句
ORACLE提供了26個分析函數(shù),按功能分5類
分析函數(shù)分類
等級(ranking)函數(shù):用于尋找前N種查詢
開窗(windowing)函數(shù):用于計算不同的累計,如SUM,COUNT,AVG,MIN,MAX等,作用于數(shù)據(jù)的一個窗口上
例:
sum(t.sal) over (order by t.deptno,t.ename) running_total,
sum(t.sal) over (partition by t.deptno order by t.ename) department_total
制表(reporting)函數(shù):與開窗函數(shù)同名,作用于一個分區(qū)或一組上的所有列
例:
sum(t.sal) over () running_total2,
sum(t.sal) over (partition by t.deptno ) department_total2
制表函數(shù)與開窗函數(shù)的關(guān)鍵不同之處在于OVER語句上缺少一個ORDER BY子句!
LAG,LEAD函數(shù):這類函數(shù)允許在結(jié)果集中向前或向后檢索值,為了避免數(shù)據(jù)的自連接,它們是非常用用的.
VAR_POP,VAR_SAMP,STDEV_POPE及線性的衰減函數(shù):計算任何未排序分區(qū)的統(tǒng)計值
2)PARTITION子句
按照表達(dá)式分區(qū)(就是分組),如果省略了分區(qū)子句,則全部的結(jié)果集被看作是一個單一的組
3)ORDER BY子句
分析函數(shù)中ORDER BY的存在將添加一個默認(rèn)的開窗子句,這意味著計算中所使用的行的集合是當(dāng)前分區(qū)中當(dāng)前行和前面所有行,沒有ORDER BY時,默認(rèn)的窗口是全部的分區(qū) 在Order by 子句后可以添加nulls last,如:order by comm desc nulls last 表示排序時忽略comm列為空的行.
4)WINDOWING子句
用于定義分析函數(shù)將在其上操作的行的集合
Windowing子句給出了一個定義變化或固定的數(shù)據(jù)窗口的方法,分析函數(shù)將對這些數(shù)據(jù)進(jìn)行操作
默認(rèn)的窗口是一個固定的窗口,僅僅在一組的第一行開始,一直繼續(xù)到當(dāng)前行,要使用窗口,必須使用ORDER BY子句
根據(jù)2個標(biāo)準(zhǔn)可以建立窗口:數(shù)據(jù)值的范圍(RANGES)或與當(dāng)前行的行偏移量.
5)Rang窗口
Range 5 preceding:將產(chǎn)生一個滑動窗口,他在組中擁有當(dāng)前行以前5行的集合
ANGE窗口僅對NUMBERS和DATES起作用,因為不可能從VARCHAR2中增加或減去N個單元
另外的限制是ORDER BY中只能有一列,因而范圍實(shí)際上是一維的,不能在N維空間中
例:
avg(t.sal) over(order by t.hiredate asc range 100 preceding) 統(tǒng)計前100天平均工資
6)Row窗口
利用ROW分區(qū),就沒有RANGE分區(qū)那樣的限制了,數(shù)據(jù)可以是任何類型,且ORDER BY 可以包括很多列
7)Specifying窗口
UNBOUNDED PRECEDING:這個窗口從當(dāng)前分區(qū)的每一行開始,并結(jié)束于正在處理的當(dāng)前行
CURRENT ROW:該窗口從當(dāng)前行開始(并結(jié)束)
Numeric Expression PRECEDING:對該窗口從當(dāng)前行之前的數(shù)字表達(dá)式(Numeric Expression)的行開始,對RANGE來說,從從行序值小于數(shù)字表達(dá)式的當(dāng)前行的值開始.
Numeric Expression FOLLOWING:該窗口在當(dāng)前行Numeric Expression行之后的行終止(或開始),且從行序值大于當(dāng)前行Numeric Expression行的范圍開始(或終止)
range between 100 preceding and 100 following:當(dāng)前行100前,當(dāng)前后100后
注意:分析函數(shù)允許你對一個數(shù)據(jù)集進(jìn)排序和篩選,這是SQL從來不能實(shí)現(xiàn)的.除了最后的Order by子句之外,分析函數(shù)是在查詢中執(zhí)行的最后的操作集,這樣的話,就不能直接在謂詞中使用分析函數(shù),即不能在上面使用where或having子句!!!
二、Oracle分析函數(shù)簡單實(shí)例：
下面我們通過一個實(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)計每個區(qū)域每個客戶的訂單總額，那么我們只需要group by o.region_id,o.cust_nbr就夠了。但我們還想在每一行顯示該客戶所在區(qū)域的訂單總額，這一點(diǎn)和前面的不同：需要在前面分組的基礎(chǔ)上按區(qū)域累加。很顯然group by和sum是無法做到這一點(diǎn)的(因為聚集操作的級別不一樣，前者是對一個客戶，后者是對一批客戶)。
這就是over函數(shù)的作用了！它的作用是告訴SQL引擎：按區(qū)域?qū)?shù)據(jù)進(jìn)行分區(qū)，然后累積每個區(qū)域每個客戶的訂單總額(sum(sum(o.tot_sales)))。
現(xiàn)在我們已經(jīng)知道2001年度每個客戶及其對應(yīng)區(qū)域的訂單總額，那么下面就是篩選那些個人訂單總額占到區(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)知道這些大客戶是誰了！哦，不過這還不夠，如果我們想要知道每個大客戶所占的訂單比例呢？看看下面的SQL語句，只需要一個簡單的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>
