[SQL 입문] 순위 함수

05 - 5 순위 함수

순위 함수는 결과에 순위를 부여하는 함수를 말합니다. 순위 함수의 종류는 다양하며 각 함수마다 순위를 처리하는 방식이 조금씩 다릅니다. MSSQL은 순위 함수로 ROW_NUMBER 함수, RANK 함수, DENSE_RANK 함수, NTILE 함수를 제공합니다. 순위 함수는 전체 데이터에 순위를 부여할 수도 있고, PARTITION 옵션을 함께 사용해 사용자가 지정한 그룹에 따라 그룹 내 순위를 부여할 수도 있습니다.

 

유일값으로 순위 부여하기 : ROW_NUMBER 함수

ROW_NUMBER 함수는 모든 행에 유일값으로 순위를 부여합니다. 다시 말해 함수 실행 결과에는 같은 순위가 없을 것입니다. 같은 순위의 경우 정렬 순서에 따라 순위를 부여합니다.

-- ROW_NUMBER 함수의 기본 형식
ROW_NUMBER() OVER([PARTITION BY 열, ··· [n]] ORDER BY 열)

-- 유일값으로 순위 부여 : ROUND 함수로 소수부 삭제
SELECT
symbol, sector,
ROUND(close_price, 0),
ROW_NUMBER() OVER(ORDER BY ROUND(close_price, 0) DESC)
FROM nasdaq_company

-- 유일값으로 순위 부여 : ORDER BY 문에 정렬 조건 추가 1
SELECT
symbol, sector,
ROUND(close_price, 0),
ROW_NUMBER() OVER(ORDER BY ROUND(close_price, 0) DESC, symbol ASC)
FROM nasdaq_company

-- 유일값으로 순위 부여 : ORDER BY 문에 정렬 조건 추가 2
SELECT
symbol, sector,
ROUND(close_price, 0),
ROW_NUMBER() OVER(PARTITION BY sector ORDER BY ROUND(close_price, 0) DESC)
FROM nasdaq_company

 

같은 순위 개수 고려해 순위 부여하기 : RANK 함수

RANK 함수는 ROW_NUMBER 함수와 비슷하지만 같은 순위를 처리하는 방법은 다릅니다. RANK 함수는 같은 순위일 때 같은 값을 부여합니다. 또한 같은 순위를 처리한 다음 순위의 값은 같은 순위를 처리한 개수를 이용해 매깁니다. 예를 들어 1 순위가 3개라면 다음 순위는 2가 아닌 4가 됩니다.

-- RANK 함수의 기본 형식
RANK() OVER([partition_by_clause] order_by_clause)

-- RANK 함수로 순위 부여 : 같은 순위 개수 고려
SELECT
symbol, sector,
ROUND(close_price, 0),
RANK() OVER(ORDER BY ROUND(closse_price, 0) DESC)
FROM nasdaq_company

 

같은 순위 개수 무시하고 순위 부여하기 : DENSE_RANK 함수

DENSE_RANK 함수는 RANK 함수와 거의 같지만 같은 순위 개수를 무시한다는 점이 다릅니다. 예를 들어 1위가 3개이면 그다음은 2위가 됩니다.

-- DENSE_RANK 함수의 기본 형식
DENSE_RANK() OVER([partition_by_clause] order_by_clause)

-- DENSE_RANK 함수로 순위 부여 : 같은 순위 개수 무시
SELECT
symbol, sector,
ROUND(close_price, 0),
DENSE_RANK() OVER(ORDER BY ROUND(closse_price, 0) DESC)
FROM nasdaq_company

 

그룹화해 순위 부여하기 : NTILE 함수

NTILE 함수는 인자로 지정한 값만큼 데이터 행을 그룹화해 그룹별 순위를 부여합니다. 각 그룹은 1부터 순위가 매겨지며 순위는 각 행의 순위가 아닌 행이 속한 그룹의 순위입니다.

-- NTILE 함수의 기본 형식
NTILE(integer_expression) OVER([partition_by_clause] order_by_clause)

-- NTILE 함수로 그룹별 순위 부여 : 1,000개 행 기준 그루핑
SELECT
symbol, sector,
ROUND(close_price, 0),
NTILE(1000) OVER(ORDER BY ROUND(close_price, 0) DESC)
FROM nasdaq_company

NTILE 함수는 전체 행을 균등하게 나누어서 어떠한 그룹을 생성해야 할 때 사용하기 좋습니다. 만약 그룹화할 전체 행 수가 행의 개수로 정확하게 나누어 떨어지지 않으면 나머지 행은 마지막 그룹에 할당됩니다.

 

실전 SQL

퀴즈 4. nasdaq_company 테이블에서 industry별로 최고가 symbol을 검색해 해당 데이터의 symbol, company_name, industry, close_price를 검색하세요

SELECT
a.symbol,
a.company_name,
a.industry,
a.close_price
FROM nasdaq_company AS a
	INNER JOIN (
		SELECT
        	symbol,
            ROW_NUMBER() OVER(PARTITION BY industry ORDER BY close_price DESC) AS
[RANK]
	FROM nasdaq_company
    ) AS b ON a.symbol = b.symbol
WHERE b.[RANK] = 1