[MSSQL] 윈도우 함수 RANK() OVER 다루기 / DENSE_RANK()

728x90

윈도우 함수 중 그룹 내 순위 함수로 RANK() 함수가 존재한다.

윈도우 함수란 행과 행간의 관계를 정의하기 위한 함수이고, RANK() 함수는 ANSI/ISO SQL 표준과 SQLServer Oracle에서 지원한다.

RANK()란?

RANK() 함수는 결과 집합의 파티션 내에서 각 행의 순서를 할당하는 윈도우 함수이다.

RANK()의 특징.

동일한 값을 갖는 파티션 내의 각 행은 동일한 순위를 받습니다.
파티션 내에서 첫 번째 순위는 1부터 시작한다.
같은 순위에 연결된 행의 수를 더하여 다음 행의 순위를 계산한다.

RANK() 함수 Syntax

RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

RANK() 함수의 문법을 보면 PARTITION BY와 ORDER BY가 존재한다.

위의 설명에서 파티션 내의 랭킹 순위를 할당하는 함수인만큼 ORDER BY는 필수이며, PARTITION BY는 옵션이다.

PARTITION BY 절은 함수가 적용되는 파티션을 나눈다. 무시할 경우 조회 결과의 모든 행을 한 파티션으로 취급한다.
ORDER BY 절은 함수가 적용되는 각 파티션에 있는 행의 논리적인 정렬 순서(순위)를 결정한다.

RANK() 실습

간단하게 RANK()를 실습해보려고 한다.

실습해볼 테이블의 스키마는 아래와 같다.

단순한 RANK()

아래의 쿼리는 RANK() 함수를 사용하여 정가 별로 제품에 순위를 지정한다.

SELECT product_id,
	   product_name,
	   list_price,
	   RANK () OVER ( 
		   ORDER BY list_price DESC
	   ) price_rank 
  FROM production.products;

쿼리의 결과는 아래와 같다.

위의 결과에서 주의 깊게 보아야 하는 점은 총 3가지이다.

PARTITION BY가 없기 때문에 결과 전체가 한 파티션으로 취급이 되었고, ORDER BY 절을 사용하여 list_price 값이 큰 제품부터 1순위로 할당하였다.
같은 list_price값을 가지는 제품들은 같은 순위를 할당받은 것을 볼 수 있다.
같은 순위로 할당받은 제품의 다음 순위는 같은 순위의 총합만큼 더해진 것을 알 수 있다.
( price_rank가 3인 ROW가 4개이므로 price_rank가 4가 아닌 7부터 할당되는 것.)

파티션 RANK()

이번에는 파티션을 부여하여 각 파티션 별로 순위를 할당하는 것을 해보겠다.

쿼리는 아래와 같다.

SELECT * FROM (
	             SELECT product_id,
		                product_name,
		                brand_id,
		                list_price,
		                RANK () OVER ( 
			               PARTITION BY brand_id
			               ORDER BY list_price DESC
		                ) price_rank 
	              FROM production.products
) t
WHERE price_rank <= 3;

결과는 아래와 같다.

위 결과에서 결과를 보게 되면 각 순위가 brand_id 파티션(그룹) 별로 1부터 시작하는 것을 볼 수 있다.

PARTITION BY 절은 brand_id별로 파티션을 나눈다.
ORDER BY 절은 list_price별로 각 파티션의 행을 정렬한다.
외부 쿼리는 rank가 3보다 작거나 같은 행만 반환한다.

시스템 그룹 순위 함수에는 RANK()와 비슷한 기능을 하는 함수가 하나 더 있다.

DENSE_RANK() 함수가 존재하는데 RANK()함수가 다른 점은 동일한 순서가 여러개 있어도 다음 순위는 중복 순위와 상관없이 순차적으로 할당한다

즉, 위의 예제에서 3번 순위가 아무리 여러 개가 있어도 다음 순위는 7번이 아닌 4번부터 시작한다.