[한기교] SQL 활용 필기(12) - 순위 계산

< 순위 함수 >

 

ㅁ Top 함수

: 질의 결과 튜플 수의 제한하는 함수

 

- Top(n)

--- 질의 결과는 ORDER BY 절의 이용하여 정렬할 수 있음

--- ORDER BY 정렬 기준에서 특정 등수 / 비율까지만 보고 싶은 경우

SELECT TOP(n) 속성명
…
ORDER BY 속성명

 

- WITH TIES

: 동률이 있을 때 모두 보고 싶은 경우

SELECT TOP(n) WITH TIES 속성명
…
ORDER BY 속성명

 

- Top(n) PERCENT

: 정렬 기준 특정 비율까지만 보고 싶은 경우

--- 상위 n%까지만을 출력

--- WITH TIES와도 같이 쓸 수 있음

SELECT TOP(n) PERCENT [WITH TIES] 속성명
…
ORDER BY 속성명

 

 

ㅁ RANK 함수

- TOP() 함수를 쓰면 결과 수를 제한함 & 등수를 구할 수 없음

- 이럴 때 RANK 함수를 이용해 구함

 

- RANK 함수

: 각 튜플에 등수를 표시

RANK함수 over (order by 속성명 [asc|desc] )

- RANK() 함수

SELECT 속성명, RANK () OVER (ORDER BY 속성명 [asc|desc] )

--- 동률인 경우 동일 등수 배정

--- 비연속식 등수 배정 (ex) 1, 2, 2, 4, ... (2위가 2개일 경우, 그다음 순위는 3이 아닌 4)

 

- DENSE_RANK() 함수

: RANK()함수와 유사하나 연속식 등수 배정

SELECT 속성명, DENSE_RANK () OVER (ORDER BY 속성명)

- 동률에 대하여 동일 등수 배정

- 연속식 등수 배정

 

- ROW_NUMBER() 함수

SELECT 속성명, ROW_NUMBER () OVER (ORDER BY 속성명)

- 동률에 대하여 임의 등수 배정 (ex) 1, 2, 3, 4, ...

- 연속식 등수 배정

 

- NTILE(n) 함수

- 전체 튜플을 n개로 균등 분할하여 순위 지정

(ex) 결과 튜플이 20개이고 n이 10이면 1등 2개, 2등 2개, ..., 10등 2개

- 결과 튜플수가 n으로 나누어 떨어지지 않으면 1등부터 추가적으로 배정

(ex) 결과 튜플이 22개이고 n이 10이면 1등 3개, 2등 3개, 3등 2개, ..., 10등 2개

 

 

 

< 그룹 별 순위 >

 

기존 RANK함수 문법은 전체 결과에 대한 속성값 기준으로 등수 지정

특정 그룹별로 순위를 매길 때는 아래와 같은 키워드 또는 함수 사용

 

ㅁ PARTITION BY 속성명

- 튜플들을 속성값에 따라서 그룹핑함

- 각 그룹에 대하여 순위 함수를 적용

RANK() over (PARTITION BY dno ORDER BY salary desc)
==> "DNO별로 분류하고 각 분류된 소그룹에서 salary 기준 내림차순하고 순위를 나타내시오"

 

ㅁ 그룹별 특정 등수의 정보를 보고 싶은 경우

- WHERE 절을 같이 활용

RANK() over (PARTITION BY dno ORDER BY salary desc) AS 속성명
…
WHERE 속성명 = 등수

 

ㅁ그룹 별 집단 함수

- 그룹 별 집단 함수의 적용

SELECT 집단 함수 ~ GROUP BY~

 

- PARTITION BY를 이용해서도 그룹 별 집단 함수를 적용할 수 있음

SELECT 집단함수() OVER (PARTITION BY 속성명)

 

< 행 순서 함수 >

 

ㅁ 행 순서 함수란?

: 정렬된 대상에서 특정 순위의 튜플들을 추출할 필요가 있을 때 사용되는 함수

 

- FIRST_VALUE 함수

: 정렬 대상에서 첫 번째 데이터 추출

SELECT DISTINCT dno, FIRST_VALUE(salary)
    OVER(PARTITION BY dno ORDER BY salary DESC) AS highest_sal
FROM EMPLOYEE

 

- LAG / LEAD 함수

: 지정된 순서에서 선행/후행 데이터 추출

SELECT DISTINCT ename, salary,
    LAG(salary, 1) OVER(ORDER BY salary DESC) AS LAG_VAL,
    LEAD(salary, 1) OVER(ORDER BY salary DESC) AS LEAD_VAL
FROM employee