乐趣区

关于oracle:Oracle-窗口函数二

明天讲一下几个排位(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 a30
SQL> set lin 120 pages 999
SQL> SELECT deptno, ename, sal,
RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) as rank
FROM 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          6

14 行が選択されました。

再来看看 DENSE_RANK 的状况。

SQL> SELECT deptno, ename, sal,
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) as rank
FROM 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          5

14 行が選択されました。

比照一下有啥区别。

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          5

DENSE_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 rank
FROM 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          6

14 行が選択されました。

2021/04/16 @ Dalian

退出移动版