實現Top-N查詢的幾種方法

Top-N查詢用於從一個有序的結果集中返回有限的記錄數。當需要取得最前面或最後面N條記錄，抑或需要對資料進行分頁查看時，該查詢尤其有用。本文將介紹幾種實現Top-N查詢的方法。
 
首先建立測試表並插入20行資料，但只有10個不同的值。
DROP TABLE rownum_order_test;
 
CREATE TABLE rownum_order_test (
 val NUMBER
);
 
INSERT ALL
 INTO rownum_order_test
 INTO rownum_order_test
SELECT level
FROM  dual
CONNECT BY level
 
COMMIT;
 
記得不要這麼做！
如果不知道偽列ROWNUM和ORDER BY子句的相互影響，下面的例子是人們通常會掉進去的一個陷阱。假設需要返回前5個最大的ID，則先按ID降序排列，然後選取前5個值。
這聽上去完全沒問題，所以我們按照這個思路得到如下查詢。
SELECT val
FROM  rownum_order_test
WHERE rownum
ORDER BY val DESC;
 
      VAL
----------
        5
        4
        3
        2
        1
 
5 rows selected.
這不是我們要的！
出現這個結果的原因是ROWNUM的分配時先於ORDER BY進行的，這導致該查詢會隨機返回5行。
 
1.使用內聯視圖和ROWNUM
最經典的Top-N查詢是通過一個內聯查詢將資料按照要求排序，然後用ROWNUM來限制返回的資料。
SELECT val
FROM  (SELECT val
       FROM  rownum_order_test
       ORDER BY val DESC)
WHERE ROWNUM
 
      VAL
----------
       10
       10
        9
        9
        8
5 rows selected.
 
由於資料在進行ROWNUM檢查前已經具有了我們希望的順序，所以返回了我們相要的結果。但是，我們要5行，也只得到了5行，雖然表中還有一個8。
 
該方法也可以用來分頁查詢資料。
SELECT val
FROM  (SELECT val, rownum AS rnum
       FROM  (SELECT val
               FROM  rownum_order_test
               ORDER BY val)
       WHERE rownum
WHERE rnum >= 4;
 
      VAL
----------
        2
        3
        3
        4
        4
 
5 rows selected.
 
2.使用RANK實現
分析函數RANK給視窗範圍內的每個不同值分配一個連續的序號。
SELECT val
FROM  (SELECT val,
              RANK() OVER (ORDER BY val DESC) AS val_rank
       FROM  rownum_order_test)
WHERE val_rank
 
      VAL
----------
       10
       10
        9
        9
        8
        8
 
6 rows selected.
返回了6行？
把RANK函數分配的序號顯示出來，結果就一目瞭然了。
SELECT val, val_rank
FROM  (SELECT val,
              RANK() OVER (ORDER BY val DESC) AS val_rank
       FROM  rownum_order_test)
WHERE val_rank
 
      VAL  VAL_RANK
---------- ----------
       10         1
       10         1
        9         3
        9         3
        8         5
        8         5
 
6 rows selected.
從上可以看出，RANK給重複行分配相同的序號且序號有跳躍，每一個新序號出現時與其實際行數保持一致。所以RANK函數並不會給出前N行資料或前N個不同的值。返回的行數依賴於表中資料的重複情況。
 
3.使用DENSE_RANK實現
分析函數DENSE_RANK和RANK函數有幾分相像，該函數也為每個不同的值分配一個序號。不同的是，該函數產生的序號不存在跳躍性。
SELECT val, val_rank
FROM  (SELECT val,
              DENSE_RANK() OVER (ORDER BY val DESC) AS val_rank
       FROM  rownum_order_test)
WHERE val_rank
 
      VAL  VAL_RANK
---------- ----------
       10         1
       10         1
        9         2
        9         2
        8         3
        8         3
        7         4
        7         4
        6         5
        6         5
 
10 rows selected.
如上所示，該函數永遠會給出前N個不同的值。
 
4.使用ROW_NUMBER函數實現
分析函數ROW_NUMBER的行為與偽列ROWNUM相似，它為返回的每一行分配不同的序號。
SELECT val
FROM  (SELECT val,
              ROW_NUMBER() OVER (ORDER BY val DESC) AS val_row_number
       FROM  rownum_order_test)
WHERE val_row_number
 
      VAL
----------
       10
       10
        9
        9
        8
 
5 rows selected.