Jaka jest różnica między funkcjami RANK () i DENSE_RANK () w oracle?

Jaka jest różnica między funkcjami RANK()i DENSE_RANK()? Jak sprawdzić n-tą pensję w poniższej emptbltabeli?

DEPTNO  EMPNAME    SAL
------------------------------
10       rrr    10000.00
11       nnn    20000.00
11       mmm    5000.00
12       kkk    30000.00
10       fff    40000.00
10       ddd    40000.00
10       bbb    50000.00
10       ccc    50000.00

Jeśli w tabeli mamy dane nulls, co się stanie, jeśli będę chciał poznać nthwynagrodzenie?

RANK podaje ranking w ramach uporządkowanej partycji. Remisy mają tę samą rangę, z pomijaniem kolejnych rang. Tak więc, jeśli masz 3 przedmioty na randze 2, następna wymieniona pozycja będzie miała miejsce 5.

DENSE_RANK ponownie podaje ranking w uporządkowanej partycji, ale rangi następują po sobie. Żadne rangi nie są pomijane, jeśli istnieją rangi z wieloma przedmiotami.

Jeśli chodzi o wartości null, zależy to od klauzuli ORDER BY. Oto prosty skrypt testowy, dzięki któremu możesz sprawdzić, co się stanie:

with q as (
select 10 deptno, 'rrr' empname, 10000.00 sal from dual union all
select 11, 'nnn', 20000.00 from dual union all
select 11, 'mmm', 5000.00 from dual union all
select 12, 'kkk', 30000 from dual union all
select 10, 'fff', 40000 from dual union all
select 10, 'ddd', 40000 from dual union all
select 10, 'bbb', 50000 from dual union all
select 10, 'xxx', null from dual union all
select 10, 'ccc', 50000 from dual)
select empname, deptno, sal
     , rank() over (partition by deptno order by sal nulls first) r
     , dense_rank() over (partition by deptno order by sal nulls first) dr1
     , dense_rank() over (partition by deptno order by sal nulls last) dr2
 from q; 

EMP     DEPTNO        SAL          R        DR1        DR2
--- ---------- ---------- ---------- ---------- ----------
xxx         10                     1          1          4
rrr         10      10000          2          2          1
fff         10      40000          3          3          2
ddd         10      40000          3          3          2
ccc         10      50000          5          4          3
bbb         10      50000          5          4          3
mmm         11       5000          1          1          1
nnn         11      20000          2          2          2
kkk         12      30000          1          1          1

9 rows selected.

Oto link do dobrego wyjaśnienia i kilku przykładów.

Ten artykuł ładnie to wyjaśnia. Zasadniczo możesz spojrzeć na to jako na takie:

CREATE TABLE t AS
SELECT 'a' v FROM dual UNION ALL
SELECT 'a'   FROM dual UNION ALL
SELECT 'a'   FROM dual UNION ALL
SELECT 'b'   FROM dual UNION ALL
SELECT 'c'   FROM dual UNION ALL
SELECT 'c'   FROM dual UNION ALL
SELECT 'd'   FROM dual UNION ALL
SELECT 'e'   FROM dual;

SELECT
  v,
  ROW_NUMBER() OVER (ORDER BY v) row_number,
  RANK()       OVER (ORDER BY v) rank,
  DENSE_RANK() OVER (ORDER BY v) dense_rank
FROM t
ORDER BY v;

Powyższe przyniesie:

+---+------------+------+------------+
| V | ROW_NUMBER | RANK | DENSE_RANK |
+---+------------+------+------------+
| a |          1 |    1 |          1 |
| a |          2 |    1 |          1 |
| a |          3 |    1 |          1 |
| b |          4 |    4 |          2 |
| c |          5 |    5 |          3 |
| c |          6 |    5 |          3 |
| d |          7 |    7 |          4 |
| e |          8 |    8 |          5 |
+---+------------+------+------------+

W słowach

• ROW_NUMBER() przypisuje unikalną wartość do każdego wiersza
• RANK() przypisuje ten sam numer wiersza tej samej wartości, pozostawiając „dziury”
• DENSE_RANK() przypisuje ten sam numer wiersza tej samej wartości, nie pozostawiając żadnych „dziur”

SELECT empno,
       deptno,
       sal,
       RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM   emp;

     EMPNO     DEPTNO        SAL       rank
---------- ---------- ---------- ----------
      7934         10       1300          1
      7782         10       2450          2
      7839         10       5000          3
      7369         20        800          1
      7876         20       1100          2
      7566         20       2975          3
      7788         20       3000          4
      7902         20       3000          4
      7900         30        950          1
      7654         30       1250          2
      7521         30       1250          2
      7844         30       1500          4
      7499         30       1600          5
      7698         30       2850          6


SELECT empno,
       deptno,
       sal,
       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM   emp;

     EMPNO     DEPTNO        SAL       rank
---------- ---------- ---------- ----------
      7934         10       1300          1
      7782         10       2450          2
      7839         10       5000          3
      7369         20        800          1
      7876         20       1100          2
      7566         20       2975          3
      7788         20       3000          4
      7902         20       3000          4
      7900         30        950          1
      7654         30       1250          2
      7521         30       1250          2
      7844         30       1500          3
      7499         30       1600          4
      7698         30       2850          5

rank () : Służy do uszeregowania rekordu w grupie wierszy.

dense_rank () : Funkcja DENSE_RANK działa jak funkcja RANK, z tym wyjątkiem, że przypisuje kolejne stopnie.

Zapytanie -

select 
    ENAME,SAL,RANK() over (order by SAL) RANK
from 
    EMP;

Wynik -

+--------+------+------+
| ENAME  | SAL  | RANK |
+--------+------+------+
| SMITH  |  800 |    1 |
| JAMES  |  950 |    2 |
| ADAMS  | 1100 |    3 |
| MARTIN | 1250 |    4 |
| WARD   | 1250 |    4 |
| TURNER | 1500 |    6 |
+--------+------+------+

Zapytanie -

select 
    ENAME,SAL,dense_rank() over (order by SAL) DEN_RANK
from 
    EMP;

Wynik -

+--------+------+-----------+
| ENAME  | SAL  |  DEN_RANK |
+--------+------+-----------+
| SMITH  |  800 |         1 |
| JAMES  |  950 |         2 |
| ADAMS  | 1100 |         3 |
| MARTIN | 1250 |         4 |
| WARD   | 1250 |         4 |
| TURNER | 1500 |         5 |
+--------+------+-----------+

select empno
       ,salary
       ,row_number() over(order by salary desc) as Serial
       ,Rank() over(order by salary desc) as rank
       ,dense_rank() over(order by salary desc) as denseRank
from emp ;

Row_number() -> Służy do generowania numeru seryjnego

Dense_rank() poda stałą rangę, ale ranga pominie rangę w przypadku kolizji rang.

Jedyna różnica między funkcjami RANK () i DENSE_RANK () występuje w przypadkach, gdy występuje „remis”; tj. w przypadkach, gdy wiele wartości w zestawie ma ten sam ranking. W takich przypadkach RANK () przypisze niekolejne „rangi” do wartości w zestawie (powodując przerwy między całkowitymi wartościami rankingu w przypadku remisu), podczas gdy DENSE_RANK () przypisze kolejne rangi do wartości w set (więc w przypadku remisu nie będzie żadnych luk między wartościami rankingu liczb całkowitych).

Rozważmy na przykład zbiór {25, 25, 50, 75, 75, 100}. Dla takiego zestawu RANK () zwróci {1, 1, 3, 4, 4, 6} (zwróć uwagę, że wartości 2 i 5 są pomijane), podczas gdy DENSE_RANK () zwróci {1,1,2,3, 3,4}.

Funkcja Rank () SQL generuje ranking danych w uporządkowanym zbiorze wartości, ale następna pozycja po poprzedniej rangi to numer_wiersza tego konkretnego wiersza. Z drugiej strony funkcja SQL Dense_Rank () generuje następną liczbę zamiast generować numer_wiersza. Poniżej znajduje się przykład SQL, który wyjaśni pojęcie:

Select ROW_NUMBER() over (order by Salary) as RowNum, Salary, 
RANK() over (order by Salary) as Rnk, 
DENSE_RANK() over (order by Salary) as DenseRnk from (
Select 1000 as Salary union all
Select 1000 as Salary union all
Select 1000 as Salary union all
Select 2000 as Salary union all
Select 3000 as Salary union all
Select 3000 as Salary union all
Select 8000 as Salary union all
Select 9000 as Salary) A

Wygeneruje następujące dane wyjściowe:

----------------------------
RowNum  Salary  Rnk DenseRnk
----------------------------
1       1000    1   1
2       1000    1   1
3       1000    1   1
4       2000    4   2
5       3000    5   3
6       3000    5   3
7       8000    7   4
8       9000    8   5

Ranga i ranga zagęszczona określają pozycję w podzielonym na partycje zbiorze danych.

Rank (): Nie daje ci kolejnych liczb całkowitych.

Dense_rank (): Daje ci kolejne liczby całkowite.

Na powyższym obrazku pozycja 10008 zip wynosi 2 według funkcji dense_rank () i 24 według funkcji rank (), biorąc pod uwagę parametr row_number.

Rank(), Dense_rank(), row_number() Wszystkie te funkcje są funkcjami okna, co oznacza, że ​​działają one jako okno nad pewnym uporządkowanym zestawem wejściowym. Te okna mają różne funkcje dołączone do niego w zależności od wymagań. Oto powyższe 3:

row_number()

Począwszy od row_number()tego, że stanowi to podstawę tych powiązanych funkcji okna. row_number()jak sama nazwa wskazuje, nadaje unikalny numer zestawowi wierszy, do których został zastosowany. Podobnie jak nadanie numeru seryjnego każdemu wierszowi.

Rank()

Podwersję row_number()można powiedzieć jako rank(). Rank () służy do nadawania tego samego numeru seryjnego tym uporządkowanym wierszom zestawu, które są duplikatami, ale nadal utrzymuje liczbę zachowaną jako podobną do a row_number()dla wszystkich po duplikatach rank () znaczenie jak od dołu np. Dla danych 2 row_number () = rank (), co oznacza, że ​​oba różnią się tylko w postaci duplikatów.

Data row_number() rank() dense_rank() 
    1         1                    1       1
    1         2                    1       1
    1         3                    1       1
    2         4                    4       2

Wreszcie,

Dense_rank () jest rozszerzoną wersją rank (), jak nazwa sugeruje, że jest gęsty, ponieważ jak widać z powyższego przykładu rank () = dense_rank () dla wszystkich danych 1, ale tylko dla danych 2 różni się formą utrzymuje kolejność rangi () od poprzedniej rangi (), a nie rzeczywiste dane

Jedyna różnica między funkcjami RANK () i DENSE_RANK () występuje w przypadkach, gdy występuje „remis”; tj. w przypadkach, gdy wiele wartości w zestawie ma ten sam ranking. W takich przypadkach RANK () przypisze niekolejne „rangi” do wartości w zestawie (powodując przerwy między całkowitymi wartościami rankingu w przypadku remisu), podczas gdy DENSE_RANK () przypisze kolejne rangi do wartości w set (więc w przypadku remisu nie będzie żadnych luk między wartościami rankingu liczb całkowitych).

Rozważmy na przykład zbiór {30, 30, 50, 75, 75, 100}. Dla takiego zestawu RANK () zwróci {1, 1, 3, 4, 4, 6} (zwróć uwagę, że wartości 2 i 5 są pomijane), podczas gdy DENSE_RANK () zwróci {1,1,2,3, 3,4}.