一、帶空值的排列：
在前面《Oracle開發(fā)之分析函數(shù)(Rank、Dense_rank、row_number)》一文中，我們已經(jīng)知道了如何為一批記錄進(jìn)行全排列、分組排列。假如被排列的數(shù)據(jù)中含有空值呢？
復(fù)制代碼 代碼如下:
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
---------- ----------- ---------- ---------- ----------
10 31 6238901 1
10 26 1808949 6238901 2
10 27 1322747 6238901 3
10 30 1216858 6238901 4
10 28 986964 6238901 5
10 29 903383 6238901 6
我們看到這里有一條記錄的CUST_TOTAL字段值為NULL，但居然排在第一名了！顯然這不符合情理。所以我們重新調(diào)整完善一下我們的排名策略，看看下面的語句：
復(fù)制代碼 代碼如下:
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
---------- ----------- ---------- ---------- ----------
10 26 1808949 6238901 1
10 27 1322747 6238901 2
10 30 1216858 6238901 3
10 28 986964 6238901 4
10 29 903383 6238901 5
10 31 6238901 6
綠色高亮處，NULLS LAST/FIRST告訴Oracle讓空值排名最后后第一。
注意是NULLS，不是NULL。
二、Top/Bottom N查詢：
在日常的工作生產(chǎn)中，我們經(jīng)常碰到這樣的查詢：找出排名前5位的訂單客戶、找出排名前10位的銷售人員等等?，F(xiàn)在這個(gè)對(duì)我們來說已經(jīng)是很簡(jiǎn)單的問題了。下面我們用一個(gè)實(shí)際的例子來演示：
【1】找出所有訂單總額排名前3的大客戶：
復(fù)制代碼 代碼如下:
SQL> select *
from (select region_id,
customer_id,
sum(customer_sales) cust_total,
rank() over(order by sum(customer_sales) desc NULLS LAST) rank
from user_order
group by region_id, customer_id)
where rank = 3;
REGION_ID CUSTOMER_ID CUST_TOTAL RANK
---------- ----------- ---------- ----------
9 25 2232703 1
8 17 1944281 2
7 14 1929774 3
SQL>
【2】找出每個(gè)區(qū)域訂單總額排名前3的大客戶：
復(fù)制代碼 代碼如下:
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 = 3;
REGION_ID CUSTOMER_ID CUST_TOTAL REG_TOTAL RANK
---------- ----------- ---------- ---------- ----------
5 4 1878275 5585641 1
5 2 1224992 5585641 2
5 5 1169926 5585641 3
6 6 1788836 6307766 1
6 9 1208959 6307766 2
6 10 1196748 6307766 3
7 14 1929774 6868495 1
7 13 1310434 6868495 2
7 15 1255591 6868495 3
8 17 1944281 6854731 1
8 20 1413722 6854731 2
8 18 1253840 6854731 3
9 25 2232703 6739374 1
9 23 1224992 6739374 2
9 24 1224992 6739374 2
10 26 1808949 6238901 1
10 27 1322747 6238901 2
10 30 1216858 6238901 3
18 rows selected.
三、First/Last排名查詢：
想象一下下面的情形：找出訂單總額最多、最少的客戶。按照前面我們學(xué)到的知識(shí)，這個(gè)至少需要2個(gè)查詢。第一個(gè)查詢按照訂單總額降序排列以期拿到第一名，第二個(gè)查詢按照訂單總額升序排列以期拿到最后一名。是不是很煩？因?yàn)镽ank函數(shù)只告訴我們排名的結(jié)果，卻無法自動(dòng)替我們從中篩選結(jié)果。
幸好Oracle為我們?cè)谂帕泻瘮?shù)之外提供了兩個(gè)額外的函數(shù)：first、last函數(shù)，專門用來解決這種問題。還是用實(shí)例說話：
復(fù)制代碼 代碼如下:
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
---------- ----------
31 1
這里有幾個(gè)看起來比較疑惑的地方：
①為什么這里要用min函數(shù)
②Keep這個(gè)東西是干什么的
③fist/last是干什么的
④dense_rank和dense_rank()有什么不同，能換成rank嗎？
首先解答一下第一個(gè)問題：min函數(shù)的作用是用于當(dāng)存在多個(gè)First/Last情況下保證返回唯一的記錄。假如我們?nèi)サ魰?huì)有什么樣的后果呢？
復(fù)制代碼 代碼如下:
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 1:
ORA-00907: missing right parenthesis
接下來看看第2個(gè)問題：keep是干什么用的？從上面的結(jié)果我們已經(jīng)知道Oracle對(duì)排名的結(jié)果只“保留”2條數(shù)據(jù)，這就是keep的作用。告訴Oracle只保留符合keep條件的記錄。
那么什么才是符合條件的記錄呢？這就是第3個(gè)問題了。dense_rank是告訴Oracle排列的策略，first/last則告訴最終篩選的條件。
第4個(gè)問題：如果我們把dense_rank換成rank呢？
復(fù)制代碼 代碼如下:
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 1:
ORA-02000: missing DENSE_RANK
四、按層次查詢：
現(xiàn)在我們已經(jīng)見識(shí)了如何通過Oracle的分析函數(shù)來獲取Top/Bottom N，第一個(gè)，最后一個(gè)記錄。有時(shí)我們會(huì)收到類似下面這樣的需求：找出訂單總額排名前1/5的客戶。
很熟悉是不？我們馬上會(huì)想到第二點(diǎn)中提到的方法，可是rank函數(shù)只為我們做好了排名，并不知道每個(gè)排名在總排名中的相對(duì)位置，這時(shí)候就引入了另外一個(gè)分析函數(shù)NTile，下面我們就以上面的需求為例來講解一下：
復(fù)制代碼 代碼如下:
SQL> select region_id,
customer_id,
ntile(5) over(order by sum(customer_sales) desc) til
from user_order
group by region_id, customer_id;
REGION_ID CUSTOMER_ID TILE
---------- ----------- ----------
10 31 1
9 25 1
10 26 1
6 6 1
8 18 2
5 2 2
9 23 3
6 9 3
7 11 3
5 3 4
6 8 4
8 16 4
6 7 5
10 29 5
5 1 5
Ntil函數(shù)為各個(gè)記錄在記錄集中的排名計(jì)算比例，我們看到所有的記錄被分成5個(gè)等級(jí)，那么假如我們只需要前1/5的記錄則只需要截取TILE的值為1的記錄就可以了。假如我們需要排名前25%的記錄(也就是1/4)那么我們只需要設(shè)置ntile(4)就可以了。
以上就是Oracle中前幾名、后幾名、最多、最少以及按層次查詢的全部?jī)?nèi)容，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
您可能感興趣的文章:- Oracle開發(fā)之分析函數(shù)總結(jié)
- Oracle開發(fā)之分析函數(shù)(Rank, Dense_rank, row_number)
- Oracle開發(fā)之分析函數(shù)簡(jiǎn)介Over用法
- 深入探討:oracle中row_number() over()分析函數(shù)用法
- Oracle 分析函數(shù)RANK(),ROW_NUMBER(),LAG()等的使用方法
- 常用Oracle分析函數(shù)大全
