Oracle SQL 基础：窗口函数（二）RANK函数如何使用窗口函数

Oracle数据库技术 2021-04-24

1533

编者按：

本文作者系Scott（中文名陈晓辉），现任大连华信资深分析师，ORACLE数据库专家，曾就职于甲骨文中国。个人主页：segmentfault.com/u/db_perf ，经其本人授权发布。

【免责声明】本公众号文章仅代表个人观点，与任何公司无关。

Oracle SQL 基础：窗口函数（一）over()函数

今天讲一下几个排位（RANK）函数如何使用窗口函数。

RANK() OVER ( query_partition_clause ORDER_BY clause)DENSE_RANK( ) OVER ( query_partition_clause ORDER_BY clause )

Example

把scott.emp表按照“deptno”分区，分区内再按“sal”排位。

先看看RANK的情况。

SQL> conn scott/triger;接続されました。SQL> col ENAME for a30SQL> set lin 120 pages 999SQL> SELECT deptno, ename, sal,RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) as rankFROM emp;  2    3    DEPTNO ENAME                                 SAL       RANK---------- ------------------------------ ---------- ----------        10 KING                                 5000          1        10 CLARK                                2450          2        10 MILLER                               1300          3        20 SCOTT                                3000          1        20 FORD                                 3000          1        20 JONES                                2975          3        20 ADAMS                                1100          4        20 SMITH                                 800          5        30 BLAKE                                2850          1        30 ALLEN                                1600          2        30 TURNER                               1500          3        30 MARTIN                               1250          4        30 WARD                                 1250          4        30 JAMES                                 950          614行が選択されました。

再来看看DENSE_RANK的情况。

SQL> SELECT deptno, ename, sal,DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) as rankFROM emp;  2    3    DEPTNO ENAME                                 SAL       RANK---------- ------------------------------ ---------- ----------        10 KING                                 5000          1        10 CLARK                                2450          2        10 MILLER                               1300          3        20 SCOTT                                3000          1        20 FORD                                 3000          1        20 JONES                                2975          2        20 ADAMS                                1100          3        20 SMITH                                 800          4        30 BLAKE                                2850          1        30 ALLEN                                1600          2        30 TURNER                               1500          3        30 MARTIN                               1250          4        30 WARD                                 1250          4        30 JAMES                                 950          514行が選択されました。

对比一下有啥区别。

RANK：    DEPTNO ENAME                                 SAL       RANK---------- ------------------------------ ---------- ----------        20 SCOTT                                3000          1        20 FORD                                 3000          1    <<<1        20 JONES                                2975          3    <<<3        20 ADAMS                                1100          4        20 SMITH                                 800          5DENSE_RANK：    DEPTNO ENAME                                 SAL       RANK---------- ------------------------------ ---------- ----------        20 SCOTT                                3000          1        20 FORD                                 3000          1    <<<1        20 JONES                                2975          2    <<<2        20 ADAMS                                1100          3        20 SMITH                                 800          4

我们可以看到使用RANK排位时遇到相同的排位的话，下个排位是有空位的，而DENSE_RANK是没有空位的。

那么如何按照记录数排位呢？可以使用ROW_NUMBER当作分析函数。

SQL> SELECT deptno, ename, sal,ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) as rankFROM emp;  2    3    DEPTNO ENAME                                 SAL       RANK---------- ------------------------------ ---------- ----------        10 KING                                 5000          1        10 CLARK                                2450          2        10 MILLER                               1300          3        20 SCOTT                                3000          1        20 FORD                                 3000          2        20 JONES                                2975          3        20 ADAMS                                1100          4        20 SMITH                                 800          5        30 BLAKE                                2850          1        30 ALLEN                                1600          2        30 TURNER                               1500          3        30 MARTIN                               1250          4        30 WARD                                 1250          5        30 JAMES                                 950          614行が選択されました。