实现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.

相关内容