Oracle開發(fā)之分析函數(shù)(Top/Bottom N、First/Last、NTile)

一、帶空值的排列：

在前面《Oracle開發(fā)之分析函數(shù)(Rank、Dense_rank、row_number)》一文中，我們已經(jīng)知道了如何為一批記錄進行全排列、分組排列。假如被排列的數(shù)據(jù)中含有空值呢？

 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)整完善一下我們的排名策略，看看下面的語句：

 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)在這個對我們來說已經(jīng)是很簡單的問題了。下面我們用一個實際的例子來演示：

【1】找出所有訂單總額排名前3的大客戶：

 REGION_ID CUSTOMER_ID CUST_TOTAL       RANK
---------- ----------- ---------- ----------
         9          25    2232703          1
         8          17    1944281          2
         7          14    1929774          3

SQL>

【2】找出每個區(qū)域訂單總額排名前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排名查詢：

想象一下下面的情形：找出訂單總額最多、最少的客戶。按照前面我們學到的知識，這個至少需要2個查詢。第一個查詢按照訂單總額降序排列以期拿到第一名，第二個查詢按照訂單總額升序排列以期拿到最后一名。是不是很煩？因為Rank函數(shù)只告訴我們排名的結果，卻無法自動替我們從中篩選結果。

幸好Oracle為我們在排列函數(shù)之外提供了兩個額外的函數(shù)：first、last函數(shù)，專門用來解決這種問題。還是用實例說話：

     FIRST       LAST
---------- ----------
        31          1

這里有幾個看起來比較疑惑的地方：

①為什么這里要用min函數(shù)
②Keep這個東西是干什么的
③fist/last是干什么的
④dense_rank和dense_rank()有什么不同，能換成rank嗎？

首先解答一下第一個問題：min函數(shù)的作用是用于當存在多個First/Last情況下保證返回唯一的記錄。假如我們?nèi)サ魰惺裁礃拥暮蠊兀?br />

ERROR at line 1:
ORA-00907: missing right parenthesis

接下來看看第2個問題：keep是干什么用的？從上面的結果我們已經(jīng)知道Oracle對排名的結果只“保留”2條數(shù)據(jù)，這就是keep的作用。告訴Oracle只保留符合keep條件的記錄。

那么什么才是符合條件的記錄呢？這就是第3個問題了。dense_rank是告訴Oracle排列的策略，first/last則告訴最終篩選的條件。

第4個問題：如果我們把dense_rank換成rank呢？

ERROR at line 1:
ORA-02000: missing DENSE_RANK

四、按層次查詢：

現(xiàn)在我們已經(jīng)見識了如何通過Oracle的分析函數(shù)來獲取Top/Bottom N，第一個，最后一個記錄。有時我們會收到類似下面這樣的需求：找出訂單總額排名前1/5的客戶。

很熟悉是不？我們馬上會想到第二點中提到的方法，可是rank函數(shù)只為我們做好了排名，并不知道每個排名在總排名中的相對位置，這時候就引入了另外一個分析函數(shù)NTile，下面我們就以上面的需求為例來講解一下：

 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ù)為各個記錄在記錄集中的排名計算比例，我們看到所有的記錄被分成5個等級，那么假如我們只需要前1/5的記錄則只需要截取TILE的值為1的記錄就可以了。假如我們需要排名前25%的記錄(也就是1/4)那么我們只需要設置ntile(4)就可以了。

以上就是Oracle中前幾名、后幾名、最多、最少以及按層次查詢的全部內(nèi)容，希望能給大家一個參考，也希望大家多多支持腳本之家。