오라클에서 RANK ()와 DENSE_RANK () 함수의 차이점은 무엇입니까?

오라클에서 RANK ()와 DENSE_RANK () 함수의 차이점은 무엇입니까?

---

차이 무엇 RANK()과 DENSE_RANK()기능은? 다음 emptbl표 에서 n 번째 급여를 찾는 방법은 무엇입니까?

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

표 데이터에 nulls가있는 경우 nth급여 를 찾으려면 어떻게됩니까?

---

RANK는 주문한 파티션 내 순위를 제공합니다. 동점에는 동일한 순위가 할당되며 다음 순위는 건너 뜁니다. 따라서 순위 2에 3 개의 항목이 있으면 다음 순위가 5가됩니다.

DENSE_RANK는 다시 주문 파티션 내의 순위를 제공하지만 순위는 연속적입니다. 여러 항목이있는 순위가 있으면 순위를 건너 뛰지 않습니다.

널은 ORDER BY 절에 따라 다릅니다. 다음은 간단한 테스트 스크립트로 어떤 일이 발생하는지 확인할 수 있습니다.

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.

다음 은 좋은 설명과 몇 가지 예에 대한 링크 입니다.

---

이 기사는 여기에 잘 설명되어 있습니다. 기본적으로 다음과 같이 볼 수 있습니다.

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;

위의 결과는 다음과 같습니다.

+---+------------+------+------------+
| 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 |
+---+------------+------+------------+

말로

• ROW_NUMBER() 각 행에 고유 한 값을 부여
• RANK() "구멍"을 남기고 동일한 행 번호를 동일한 값으로 속성
• DENSE_RANK() "구멍"을 남기지 않고 동일한 행 번호를 동일한 값으로 속성 지정

---

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 () : 행 그룹 내에서 레코드 순위를 지정하는 데 사용됩니다.

dense_rank () : DENSE_RANK 함수는 연속 순위를 할당한다는 점을 제외하고는 RANK 함수처럼 작동합니다.

검색어-

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

출력-

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

검색어-

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

출력-

+--------+------+-----------+
| 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() -> 일련 번호 생성에 사용

Dense_rank() 연속 순위를 제공하지만 순위 충돌의 경우 순위를 건너 뜁니다.

---

The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a “tie”; i.e., in cases where multiple values in a set have the same ranking. In such cases, RANK() will assign non-consecutive “ranks” to the values in the set (resulting in gaps between the integer ranking values when there is a tie), whereas DENSE_RANK() will assign consecutive ranks to the values in the set (so there will be no gaps between the integer ranking values in the case of a tie).

For example, consider the set {25, 25, 50, 75, 75, 100}. For such a set, RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped), whereas DENSE_RANK() will return {1,1,2,3,3,4}.

---

Rank() SQL function generates rank of the data within ordered set of values but next rank after previous rank is row_number of that particular row. On the other hand, Dense_Rank() SQL function generates next number instead of generating row_number. Below is the SQL example which will clarify the concept:

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

It will generate following output:

----------------------------
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

---

Rank and Dense rank gives the rank in the partitioned dataset.

Rank() : It doesn't give you consecutive integer numbers.

Dense_rank() : It gives you consecutive integer numbers.

In above picture , the rank of 10008 zip is 2 by dense_rank() function and 24 by rank() function as it considers the row_number.

참고URL : https://stackoverflow.com/questions/11183572/whats-the-difference-between-rank-and-dense-rank-functions-in-oracle