一分析函數(OVER)
目錄
===============================================
Oracle分析函數簡介
Oracle分析函數簡單實例
分析函數OVER解析
一Oracle分析函數簡介
在日常的生產環境中我們接觸得比較多的是OLTP系統(即Online Transaction Process)這些系統的特點是具備實時要求或者至少說對響應的時間多長有一定的要求其次這些系統的業務邏輯一般比較復雜可能需要經過多次的運算比如我們經常接觸到的電子商城
在這些系統之外還有一種稱之為OLAP的系統(即Online Aanalyse Process)這些系統一般用於系統決策使用通常和數據倉庫數據分析數據挖掘等概念聯系在一起這些系統的特點是數據量大對實時響應的要求不高或者根本不關注這方面的要求以查詢統計操作為主
我們來看看下面的幾個典型例子
①查找上一年度各個銷售區域排名前的員工
②按區域查找上一年度訂單總額占區域訂單總額%以上的客戶
③查找上一年度銷售最差的部門所在的區域
④查找上一年度銷售最好和最差的產品
我們看看上面的幾個例子就可以感覺到這幾個查詢和我們日常遇到的查詢有些不同具體有
①需要對同樣的數據進行不同級別的聚合操作
②需要在表內將多條數據和同一條數據進行多次的比較
③需要在排序完的結果集上進行額外的過濾操作
二Oracle分析函數簡單實例
下面我們通過一個實際的例子按區域查找上一年度訂單總額占區域訂單總額%以上的客戶來看看分析函數的應用
【】測試環境
SQL> desc orders_tmp;
Name Null? Type
CUST_NBR NOT NULL NUMBER()
REGION_ID NOT NULL NUMBER()
SALESPERSON_ID NOT NULL NUMBER()
YEAR NOT NULL NUMBER()
MONTH NOT NULL NUMBER()
TOT_ORDERS NOT NULL NUMBER()
TOT_SALES NOT NULL NUMBER()
【】測試數據
SQL> select * from orders_tmp;
CUST_NBR REGION_ID SALESPERSON_ID YEAR MONTH TOT_ORDERS TOT_SALES
rows selected
【】測試語句
SQL> select ocust_nbr customer
oregion_id region
sum(otot_sales) cust_sales
sum(sum(otot_sales)) over(partition by oregion_id) region_sales
from orders_tmp o
where oyear =
group by oregion_id ocust_nbr;
CUSTOMER REGION CUST_SALES REGION_SALES
三分析函數OVER解析
請注意上面的綠色高亮部分group by的意圖很明顯將數據按區域ID客戶進行分組那麼Over這一部分有什麼用呢？假如我們只需要統計每個區域每個客戶的訂單總額那麼我們只需要 group by oregion_idocust_nbr就夠了但我們還想在每一行顯示該客戶所在區域的訂單總額這一點和前面的不同需要在前面分組的基礎上按區域累加很顯然group by和sum是無法做到這一點的(因為聚集操作的級別不一樣前者是對一個客戶後者是對一批客戶)
這就是over函數的作用了！它的作用是告訴SQL引擎按區域對數據進行分區然後累積每個區域每個客戶的訂單總額(sum(sum(otot_sales)))
現在我們已經知道年度每個客戶及其對應區域的訂單總額那麼下面就是篩選那些個人訂單總額占到區域訂單總額%以上的大客戶了
SQL> select *
from (select ocust_nbr customer
oregion_id region
sum(otot_sales) cust_sales
sum(sum(otot_sales)) over(partition by oregion_id) region_sales
from orders_tmp o
where oyear =
group by oregion_id ocust_nbr) all_sales
where all_salescust_sales > all_salesregion_sales * ;
CUSTOMER REGION CUST_SALES REGION_SALES
SQL>
現在我們已經知道這些大客戶是誰了！哦不過這還不夠如果我們想要知道每個大客戶所占的訂單比例呢？看看下面的SQL語句只需要一個簡單的Round函數就搞定了 SQL> select all_sales*
* round(cust_sales / region_sales ) || % Percent
from (select ocust_nbr customer
oregion_id region
sum(otot_sales) cust_sales
sum(sum(otot_sales)) over(partition by oregion_id) region_sales
from orders_tmp o
where oyear =
group by oregion_id ocust_nbr) all_sales
where all_salescust_sales > all_salesregion_sales * ;
CUSTOMER REGION CUST_SALES REGION_SALES PERCENT
%
%
%
SQL>
總結
①Over函數指明在那些字段上做分析其內跟Partition by表示對數據進行分組注意Partition by可以有多個字段
②Over函數可以和其它聚集函數分析函數搭配起到不同的作用例如這裡的SUM還有諸如RankDense_rank等
一分析函數(rank\dense_rank\row_number)
目錄
===============================================
使用rownum為記錄排名
使用分析函數來為記錄排名
使用分析函數為記錄進行分組排名
一使用rownum為記錄排名
在前面一篇《Oracle開發專題之分析函數》我們認識了分析函數的基本應用現在我們再來考慮下面幾個問題
①對所有客戶按訂單總額進行排名
②按區域和客戶訂單總額進行排名
③找出訂單總額排名前位的客戶
④找出訂單總額最高最低的客戶
⑤找出訂單總額排名前%的客戶
按照前面第一篇文章的思路我們只能做到對各個分組的數據進行統計如果需要排名的話那麼只需要簡單地加上rownum不就行了嗎？事實情況是否如此想象般簡單我們來實踐一下
【】測試環境
SQL> desc user_order;
Name Null? Type
REGION_ID NUMBER()
CUSTOMER_ID NUMBER()
CUSTOMER_SALES NUMBER
【】測試數據
SQL> select * from user_order order by customer_sales;
REGION_ID CUSTOMER_ID CUSTOMER_SALES
rows selected
注意這裡有條記錄的訂單總額是一樣的假如我們現在需要篩選排名前位的客戶如果使用rownum會有什麼樣的後果呢？ SQL> select rownum t*
from (select *
from user_order
order by customer_sales desc) t
where rownum <=
order by customer_sales desc;
ROWNUM REGION_ID CUSTOMER_ID CUSTOMER_SALES
rows selected
很明顯假如只是簡單地按rownum進行排序的話我們漏掉了另外兩條記錄(參考上面的結果)
二使用分析函數來為記錄排名
針對上面的情況Oracle從i開始就提供了個分析函數randdense_rankrow_number來解決諸如此類的問題下面我們來看看這個分析函數的作用以及彼此之間的區別
RankDense_rankRow_number函數為每條記錄產生一個從開始至N的自然數N的值可能小於等於記錄的總數這個函數的唯一區別在於當碰到相同數據時的排名策略
①ROW_NUMBER
Row_number函數返回一個唯一的值當碰到相同數據時排名按照記錄集中記錄的順序依次遞增
②DENSE_RANK
Dense_rank函數返回一個唯一的值除非當碰到相同數據時此時所有相同數據的排名都是一樣的
③RANK
Rank函數返回一個唯一的值除非遇到相同的數據時此時所有相同數據的排名是一樣的同時會在最後一條相同記錄和下一條不同記錄的排名之間空出排名
這樣的介紹有點難懂我們還是通過實例來說明吧下面的例子演示了個不同函數在遇到相同數據時不同排名策略
SQL> select region_id customer_id sum(customer_sales) total
rank() over(order by sum(customer_sales) desc) rank
dense_rank() over(order by sum(customer_sales) desc) dense_rank
row_number() over(order by sum(customer_sales) desc) row_number
from user_order
group by region_id customer_id;
REGION_ID CUSTOMER_ID TOTAL RANK DENSE_RANK ROW_NUMBER
rows selected
請注意上面的綠色高亮部分這裡生動的演示了種不同的排名策略
①對於第一條相同的記錄種函數的排名都是一樣的
②當出現第二條相同的記錄時Rank和Dense_rank依然給出同樣的排名而row_number則順延遞增為依次類推至第三條相同的記錄
③當排名進行到下一條不同的記錄時可以看到Rank函數在和之間空出了的排名因為這個排名實際上已經被第二三條相同的記錄占了而Dense_rank則順序遞增row_number函數也是順序遞增
比較上面種不同的策略我們在選擇的時候就要根據客戶的需求來定奪了
①假如客戶就只需要指定數目的記錄那麼采用row_number是最簡單的但有漏掉的記錄的危險
②假如客戶需要所有達到排名水平的記錄那麼采用rank或dense_rank是不錯的選擇至於選擇哪一種則看客戶的需要選擇dense_rank或得到最大的記錄
三使用分析函數為記錄進行分組排名
上面的排名是按訂單總額來進行排列的現在跟進一步假如是為各個地區的訂單總額進行排名呢？這意味著又多了一次分組操作對記錄按地區分組然後進行排名幸虧Oracle也提供了這樣的支持我們所要做的僅僅是在over函數中order by的前面增加一個分組子句partition by region_id
SQL> select region_id customer_id
sum(customer_sales) total
rank() over(partition by region_id
order by sum(customer_sales) desc) rank
dense_rank() over(partition by region_id
order by sum(customer_sales) desc) dense_rank
row_number() over(partition by region_id
order by sum(customer_sales) desc) row_number
from user_order
group by region_id customer_id;
REGION_ID CUSTOMER_ID TOTAL RANK DENSE_RANK ROW_NUMBER
rows selected
現在我們看到的排名將是基於各個地區的而非所有區域的了！Partition by 子句在排列函數中的作用是將一個結果集劃分成幾個部分這樣排列函數就能夠應用於這各個子集
三分析函數（top\bottom nfirst\lastntile）
目錄
===============================================
帶空值的排列
Top/Bottom N查詢
First/Last排名查詢
按層次查詢
一帶空值的排列
在前面《Oracle開發專題之分析函數(RankDense_rankrow_number)》一文中我們已經知道了如何為一批記錄進行全排列分組排列假如被排列的數據中含有空值呢？
SQL> select region_id customer_id
sum(customer_sales) cust_sales
sum(sum(customer_sales)) over(partition by region_id) ran_total
rank() over(partition by region_id
order by sum(customer_sales) desc) rank
from user_order
group by region_id customer_id;
REGION_ID CUSTOMER_ID CUST_SALES RAN_TOTAL RANK
我們看到這裡有一條記錄的CUST_TOTAL字段值為NULL但居然排在第一名了！顯然這不符合情理所以我們重新調整完善一下我們的排名策略看看下面的語句
SQL> select region_id customer_id
sum(customer_sales) cust_total
sum(sum(customer_sales)) over(partition by region_id) reg_total
rank() over(partition by region_id
order by sum(customer_sales) desc NULLS LAST) rank
from user_order
group by region_id customer_id;
REGION_ID CUSTOMER_ID CUST_TOTAL REG_TOTAL RANK
綠色高亮處NULLS LAST/FIRST告訴Oracle讓空值排名最後後第一
注意是NULLS不是NULL
二Top/Bottom N查詢
在日常的工作生產中我們經常碰到這樣的查詢找出排名前位的訂單客戶找出排名前位的銷售人員等等現在這個對我們來說已經是很簡單的問題了下面我們用一個實際的例子來演示
【】找出所有訂單總額排名前的大客戶
SQL> select *
SQL> from (select region_id
SQL> customer_id
SQL> sum(customer_sales) cust_total
SQL> rank() over(order by sum(customer_sales) desc NULLS LAST) rank
SQL> from user_order
SQL> group by region_id customer_id)
SQL> where rank <= ;
REGION_ID CUSTOMER_ID CUST_TOTAL RANK
SQL>
【】找出每個區域訂單總額排名前的大客戶
SQL> select *
from (select region_id
customer_id
sum(customer_sales) cust_total
sum(sum(customer_sales)) over(partition by region_id) reg_total
rank() over(partition by region_id
order by sum(customer_sales) desc NULLS LAST) rank
from user_order
group by region_id customer_id)
where rank <= ;
REGION_ID CUSTOMER_ID CUST_TOTAL REG_TOTAL RANK
rows selected
三First/Last排名查詢
想象一下下面的情形找出訂單總額最多最少的客戶按照前面我們學到的知識這個至少需要個查詢第一個查詢按照訂單總額降序排列以期拿到第一名第二個查詢按照訂單總額升序排列以期拿到最後一名是不是很煩？因為Rank函數只告訴我們排名的結果卻無法自動替我們從中篩選結果
幸好Oracle為我們在排列函數之外提供了兩個額外的函數firstlast函數專門用來解決這種問題還是用實例說話 SQL> select min(customer_id)
keep (dense_rank first order by sum(customer_sales) desc) first
min(customer_id)
keep (dense_rank last order by sum(customer_sales) desc) last
from user_order
group by customer_id;
FIRST LAST
這裡有幾個看起來比較疑惑的地方
①為什麼這裡要用min函數
②Keep這個東西是干什麼的
③fist/last是干什麼的
④dense_rank和dense_rank()有什麼不同能換成rank嗎？
首先解答一下第一個問題min函數的作用是用於當存在多個First/Last情況下保證返回唯一的記錄假如我們去掉會有什麼樣的後果呢？ SQL> select keep (dense_rank first order by sum(customer_sales) desc) first
keep (dense_rank last order by sum(customer_sales) desc) last
from user_order
group by customer_id;
select keep (dense_rank first order by sum(customer_sales) desc) first
*
ERROR at line :
ORA: missing right parenthesis
接下來看看第個問題keep是干什麼用的？從上面的結果我們已經知道Oracle對排名的結果只保留條數據這就是keep的作用告訴Oracle只保留符合keep條件的記錄
那麼什麼才是符合條件的記錄呢？這就是第個問題了dense_rank是告訴Oracle排列的策略first/last則告訴最終篩選的條件
第個問題如果我們把dense_rank換成rank呢？ SQL> select min(region_id)
keep(rank first order by sum(customer_sales) desc) first
min(region_id)
keep(rank last order by sum(customer_sales) desc) last
from user_order
group by region_id;
select min(region_id)
*
ERROR at line :
ORA: missing DENSE_RANK
四按層次查詢
現在我們已經見識了如何通過Oracle的分析函數來獲取Top/Bottom N第一個最後一個記錄有時我們會收到類似下面這樣的需求找出訂單總額排名前/的客戶
很熟悉是不？我們馬上會想到第二點中提到的方法可是rank函數只為我們做好了排名並不知道每個排名在總排名中的相對位置這時候就引入了另外一個分析函數NTile下面我們就以上面的需求為例來講解一下
SQL> select region_id
customer_id
ntile() over(order by sum(customer_sales) desc) til
from user_order
group by region_id customer_id;
REGION_ID CUSTOMER_ID TILE
Ntil函數為各個記錄在記錄集中的排名計算比例我們看到所有的記錄被分成個等級那麼假如我們只需要前/的記錄則只需要截取TILE的值為的記錄就可以了假如我們需要排名前%的記錄(也就是/)那麼我們只需要設置ntile()就可以了
From:http://tw.wingwit.com/Article/program/Oracle/201311/18316.html
