percent_rank와 cume_dist

총 인원수 중 해당 row가 몇등인지를 0부터 1사이의 값을 통해 비율로 나타내는 두 가지 방법이 있다. SQL Server(MSSQL)에서는 지원하지 않는다.

 

 

먼저, PERCENT_RANK라는 함수가 있다.

 

이 함수는 특정 값에 대해 ORDER BY한 후 순위를 백분위로 매겨야 할 때 매우 편리하게 사용할 수 있다.

 

 

 

 

그리고 또 한가지는 CUME_DIST라는 함수가 있다.

 

 

완전 같은 조건의 테이블에, 같은 쿼리에서 PERCENT_RANK와 CUME_DIST만 바꿨을 때 결과에서 약간의 차이가 있는 것을 확인할 수 있다.

 

책에 나온 설명에 따르면 PERCENT_RANK는 특정 범위 내에서 맨 윗 행을 0, 맨 아래 행을 1로 하여 각 행의 위치를 백분율로 나타낸 값이다.

 

맨 윗 행은 0으로 시작해서 총 백분위 집계에 포함되지 않기 때문에, 총 COUNT에서 -1한 값으로 백분위가 나눠지는 것을 확인할 수 있다.

식으로 나타내면

(현재까지 ROW 누계 COUNT) - 1 / 전체범위COUNT - 1

 

 

 

반면 CUME_DIST는 특정 범위 안에서 해당 ROW의 누적 백분위를 구하는 것이기 때문에, 맨 윗 행도 집계에 포함된다.

식으로 나타내면

 

(현재까지 ROW 누계 COUNT) / 전체범위COUNT

 

와 같다.

 

특정 범위라고 표현한 것은 이 함수도 PARTITION과 함께 그룹화해서 사용할 수 있기 때문이다.

 

 

동일한 테이블에서 DEPT_NO를 기준으로 PARTITION 조건을 추가했다.

 

테스트 테이블에서 DEPT_NO가 50인 값은 하나밖에 없다. 그래서 첫번째 ROW에 있는 DEPT_NO가 50인 값을 보면, 해당 파티션의 첫 번째 ROW를 0으로 본다는 조건이 우선시되는 것을 확인할 수 있다. 

 

 

또 DEPT_NO가 60인 파티션의 경우는 ROW는 2개지만 ORDER BY 기준인 SALARY가 동일해서 유일한 값과 동일하게 표시되는 것도 재차 확인할 수 있었다.

 

 

 

 

CUME_DIST함수 결과에서는 해당 파티션의 유일한 값일 경우 1로 나타나는 걸 볼 수 있다.

파티션의 총 count에서 해당 row가 차지하는 비율이기 때문이다.