Oracle — window function (2)

Time:2021-4-20

Today I’ll talk about how some rank functions use window functions.

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

Example

hold scott.emp The table is partitioned according to “deptno”, and then ranked according to “Sal” in the partition.

Let’s look at rank first.

SQL> conn scott/triger;
It's going to go on.
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 lines, select the first line.

Let’s take a look at 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 lines, select the first line.

Compare the differences.

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

We can see that when rank is used for ranking, if the same ranking is met, there will be a vacancy in the next ranking, but there will be a vacancy in the next ranking_ There is no vacancy in rank.

So how to rank according to the number of records?
You can use row_ Number is used as an analysis function.

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 lines, select the first line.

2021/04/16 @ Dalian