【오라클(Oracle) SQL 함수】 DENSE_RANK 함수

오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크

 

o DENSE_RANK

 

---

문 법

 

• 집계함수 문법

dense_rank_aggregate::=

 

• 분석함수 문법

dense_rank_analytic::=

 

---

목 적

DENSE_RANK 함수는 Rank 함수와 유사한 함수로 각 그룹별로 ORDER BY 절에 사용된 칼럼이나 표현식을 기준으로 정렬하고, 그룹 내의 각 열에 대한 순위를 부여한다. RANK 함수와 달리 동일 순위 다음의 순위는 동일 순위의 수와 상관없이 1을 증가하여 처리한다.

DENSE_RANK 함수는 정렬된 행 그룹 내에서 행의 순위를 계산하고, 해당 순위를 NUMBER 현태로 반환한다. 순위는 1로 시작하는 연속된 정수이다. 가장 큰 순위 값은 쿼리에서 중복을 제거한 후 반환되는 유일한(고유한) 관측치의 개수와 동일하다.

동일한 값(동점)의 경우 해당 순위를 건너뛰지 않고 동일한 순위를 부여한다. 지정한 순위 기준을 기준으로 관측치 값이 동일한 행은 동일한 순위를 부여한다. 이 함수는 상위 N (top-N) 과 하위 N (bottom-N) 개의 관측치를 구분하는데 유용하다.

예를 들어, 10명의 선수가 경주를 했는데, 1위부터 3위까지의 순위가 동점이라면, 순위 값은 1, 1, 1로 동일하게 부여한다. 1위와 2위, 3위의 순위를 구분하기 위해 동점자에 대하여 별도의 순위를 부여하는 것이 아니라, 순위 값을 동일하게 부여한다. 동점이 발생하는 경우 순위를 매기기 위해서는 순위 값을 부여해야 하는데, 이때 개별 순위를 부여하면 순위가 중복되어 혼란을 야기할 수 있기 때문에 밀도 순위(Dense Rank)를 사용한다.

 

---

이 함수는 임의의 모든 수치형 데이터 타입을 인수로 사용하여 NUMBER 형태로 반환한다.

 

집계 함수

DENSE_RANK 함수를 집계 함수로 사용하는 경우, 사용자가 지정한 정렬 기준에 따라 함수의 인수에 의해 식별되는 가상의 행의 밀도(Dense Rank) 순위를 계산한다. 함수의 인수는 각 집계 그룹 내에서 단일 행을 식별하기 때문에, 모든 인수는 각 집계 그룹 내에서 상수 표현식으로 평가되어야 한다. 집계 함수의 상수 인수 표현식과 order_by_clause 절에 지정한 표현식은 위치별로 일치해야 한다. 따라서 인수의 개수는 동일해야 하며, 타입은 호환 가능해야 한다.

Oracle Program

SELECT RANK(15000) WITHIN GROUP (ORDER BY salary DESC)
FROM employees;

여기서 15000은 상수 인수 표현식이고, ORDER BY salary DESC는 정렬 기준을 나타내는 order_by_clause 절이다. 상수 인수 표현식은 1개이고, order_by_clause 절의 정렬 기준도 1개이므로 위치별 일치가 성립한다.

 

---

분석 함수

DENSE_RANK 함수를 분석 함수로 사용하는 경우, order_by_clause 절의 value_exprs 값을 기반으로 쿼리에서 반환된 각 행의 순위를 다른 행과 비교하여 계산한다.

 

• 분석 함수 작동 방식

1. DENSE_RANK 함수는 파티션에 해당하는 쿼리의 데이트를 추출한다.
2. order_by_clause 절에서 지정한 정렬 기준에 따라 결과를 정렬한다.
3. 정렬된 결과에서 각 행의 순위를 계산한다.
4. 순위 계산 시, 동일한 값을 가진 행은 동일한 순위를 부여한다.
5. 순위는 1부터 시작하며, 데이터 값이 동일하여 동일한 순위를 부여한 경우에도 순위를 건너뛰기 없이 연속적으로 순위를 부여한다.

 

---

DENSE_RANK 함수와 RANK 함수 비교

DENSE_RANK 함수는 Rank 함수와 유사한 함수로 ORDER BY절에 사용된 칼럼이나 표현식에 대하여 순위를 부여하는데 RANK 함수와 달리 동일 순위 다음의 순위는 동일 순위의 수와 상관없이 1 을 증가한 순위를 반환한다.

Person	Amount	순위(RANK 함수)	DENSE_RANK
Adams	100	1	1
Baker	100	1	1
Connors	89	3	2
Davis	75	4	3
Edwards	75	4	3
Fitzhugh	66	6	4
Garibaldi	45	7	5

[그림] RANK()와 DENSE_RANK()의 비교

 

---

패키지 함수 비교(Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table)

• DENSE_RANK Oracle Function 프로그래밍 비교

 

---

관련 함수

함수명	함수 내용
CUME_DIST	그룹에 존재하는 관측치의 누적 분포 값을 계산한다.
DENSE_RANK	Rank()와 유사한 함수로 각 그룹별로 ORDER BY절에 사용된 칼럼이나 표현식을 기준으로 정렬하고, 그룹 내의 각 열에 대한 순위를 부여한다.
LAG	이 함수는 Self 조인 작업 수행하지 않고, 하나의 테이블을 접근하면서 여러 개의 행에 동시에 접근(Access)하여 작업한다. LAG 함수는 현재 위치를 기준으로 이전에 지정한 행에 접근하여 작업을 수행한다.
LEAD	이 함수는 Self 조인 작업을 수행하지 않고, 하나의 테이블을 접근하면서 여러 개의 행에 동시에 접근(Access)하여 작업한다. 쿼리에서 수행되는 행의 Series나 커서의 위치를 지정하면, LEAD 함수는 해당 위치 이후에 존재하는 물리적 Offset의 행에 ACCESS 한다.
NTILE	그룹 내에서 정렬된 데이터를 expr에 의해 지정된 bucket의 수로 분할하여, 각 행을 적절한 bucket의 번호를 부여하여 할당한다.
PERCENT_RANK	인수로 지정한 값의 그룹 내의 위치를 나타내는 백분위 순위(순위 퍼센트, Percent rank)를 반환한다.
RANK	값의 그룹에서 값의 순위를 계산한다. 반환되는 데이터형은 NUMBER이다.
ROW_NUMBER	이 함수는 파티션 그룹별 또는 전체 데이터에 대하여 order_by_clause에서 지정된 행의 정렬 순위대로 1로 시작하는 중복을 허용하지 않는 유일한(unique) 순서 값을 할당한다. (파티션에서 각 행 또는 쿼리에 의해서 반환되는 각 행에 대하여)

 

---

예 제

 

집계함수 예제

다음 예제는 샘플 테이블 oe.employees에서 급여가 $15,500이고, 수수료가 5% 인 가상 종업원(테이블에 존재하지 않는)의 순위를 계산한다.

Oracle Program

SELECT DENSE_RANK(15500, .05) WITHIN GROUP 
       (ORDER BY salary DESC, commission_pct) "Dense Rank" 
FROM employees;

 

Results

         Dense Rank
-------------------
                  3

 

---

분석함수 예제

다음 예제는 인사 부서 또는 구매 부서에서 근무하는 모든 종업원의 부서명, 종업원명, 급여 정보를 선택하고, 2개 부서별로 유일한 급여에 대하여 순위를 계산한다. 동일한 급여에 대하여 동일한 순위를 부여한다. RANK 함수 예제 참조.

Oracle Program

SELECT d.department_name, e.last_name, e.salary, DENSE_RANK() 
       OVER (PARTITION BY e.department_id ORDER BY e.salary) as drank
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id IN ('30', '40');

 

Results

DEPARTMENT_NAME         LAST_NAME              SALARY      DRANK
----------------------- ------------------ ---------- ----------
Purchasing              Colmenares               2500          1
Purchasing              Himuro                   2600          2
Purchasing              Tobias                   2800          3
Purchasing              Baida                    2900          4
Purchasing              Khoo                     3100          5
Purchasing              Raphaely                11000          6
Human Resources         Marvis                   6500

 

---

오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크

 

[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트 링크

2024.01 수정