SQL 고급 : 윈도우 함수(순위 함수) - RANK(), DENSE_RANK(), ROW_NUMBER()

 

오늘은 윈도우 함수 중 하나인 순위 함수를 어떻게 사용하는 지에 대해서 야구 데이터를 활용하여 함께 알아보도록 하겠습니다. 이를 위해 국내 프로야구 통산 홈런 개수가 300개가 넘는 선수들의 데이터를 가지고 홈런 순위를 계산하는 방법을 순위 함수를 통해 살펴보겠는데요.

 

 

<KBO 통산 홈런 300개 이상을 친 선수 명단>

 

이름	포지션	홈런
박경완	포수	314
이승엽	내야수	467
최정	내야수	403
이대호	내야수	351
장종훈	내야수	340
이호준	내야수	337
이범호	내야수	329
박병호	내야수	327
김태균	내야수	311
최형우	외야수	342
심정수	외야수	328
송지만	외야수	311
박재홍	외야수	300
양준혁	지명타자	351

 

1. RANK() 함수

 

첫 번째로 살펴볼 순위 함수는 RANK() 함수입니다. RANK() 함수는 순위 함수 중에서도 일반적으로 가장 많이 사용하는 함수입니다. RANK() 함수는 여러 행의 값이 같은 경우라면 같은 순위를 부여해주는 방식입니다. 흔히 많이들 얘기하는 공동 순위를 부여하고 그 다음 나오는 행에는 같은 순위가 나왔던 숫자만큼 건너 뛰고 순위를 부여해주는 것이지요. 

 

일전에 집계 함수와 윈도우 함수를 비교하면서도 언급한적이 있었는데요. 윈도우 함수를 사용할 때는 윈도우 함수 다음에 OVER() 연결되는 구조로 문법이 사용됩니다. 그리고 우리는 홈런을 많이 친 순서대로 정렬을 해줄 것이기 때문에 ORDER BY를 통해 내림차순 정렬을 해주기 위해 DESC를 함께 적어줍니다.

 

SELECT *
     , RANK() OVER(ORDER BY `홈런` DESC) AS 'RANK'
FROM kbo_hr

 

예시를 보면 더 이해하기 쉬운 것입니다. 아래를 보시면 이대호, 양준혁 선수의 홈런 개수가 같기 때문에 동일한 순위를 부여해주었고, 다음 최형우 선수에게는 4위가 아닌 5위의 순위를 부여해준 것을 보실 수 있습니다. 그 아래의 김태균, 송지만 선수도 홈런 개수가 같으므로 박재홍 선수의 순위가 13위가 아닌 14위로 부여된 것을 보실 수 있습니다.

 

🕒 실행결과

 

이름	포지션	홈런	RANK
이승엽	내야수	467	1
최정	내야수	403	2
이대호	내야수	351	3
양준혁	지명타자	351	3
최형우	외야수	342	5
장종훈	내야수	340	6
이호준	내야수	337	7
이범호	내야수	329	8
심정수	외야수	328	9
박병호	내야수	327	10
박경완	포수	314	11
김태균	내야수	311	12
송지만	외야수	311	12
박재홍	외야수	300	14

 

2. DENSE_RANK()

 

DENSE_RANK() 함수는 같은 값이 나오는 행에 동일한 순위를 부여해준다는 점에서는 RANK() 함수와 동일합니다. 하지만 차이점은 그 다음에 나오는 행에는 순위를 건너뛰지 않고 이어서 순위를 부여해주게 됩니다. 이것도 역시 아래 예시를 보면 훨씬 더 이해하기 쉽습니다.

 

SELECT *
     , DENSE_RANK() OVER(ORDER BY `홈런` DESC) AS 'RANK'
FROM kbo_hr

 

RANK() 함수의 경우 최형우 선수의 순위를 5위, 박재홍 선수의 순위를 14위로 부여했습니다만, 보시는 것처럼 DENSE_RANK() 함수에서는 순위를 건너뛰지 않고 이어서 부여해주었기 때문에 각각 4위와 11위로 나타낸 것을 확인하실 수 있습니다.

 

🕒 실행결과

 

이름	포지션	홈런	RANK
이승엽	내야수	467	1
최정	내야수	403	2
이대호	내야수	351	3
양준혁	지명타자	351	3
최형우	외야수	342	4
장종훈	내야수	340	5
이호준	내야수	337	6
이범호	내야수	329	7
심정수	외야수	328	8
박병호	내야수	327	9
박경완	포수	314	10
김태균	내야수	311	11
송지만	외야수	311	11
박재홍	외야수	300	12

 

3. ROW_NUMBER()

 

마지막으로 살펴볼 함수는 ROW_NUMBER() 함수 입니다. ROW_NUMBER() 함수는 정말 간단합니다. RANK(). DENSE_RANK() 함수와는 다르게 각 행에 순위를 부여할 때 동점인 경우가 있어도 이를 무시하고 연속적인 순위를 부여합니다. 이것도 아래 예시를 살펴보겠습니다.

 

SELECT *
     , ROW_NUMBER() OVER(ORDER BY `홈런` DESC) AS 'RANK'
FROM kbo_hr

 

🕒 실행결과

 

이름	포지션	홈런	RANK
이승엽	내야수	467	1
최정	내야수	403	2
이대호	내야수	351	3
양준혁	지명타자	351	4
최형우	외야수	342	5
장종훈	내야수	340	6
이호준	내야수	337	7
이범호	내야수	329	8
심정수	외야수	328	9
박병호	내야수	327	10
박경완	포수	314	11
김태균	내야수	311	12
송지만	외야수	311	13
박재홍	외야수	300	14

 

보시는 것처럼 홈런의 개수가 같은 행이 있음에도 불구하고 이를 무시하고 순위를 연속적으로 부여해주는 것을 보실 수 있습니다. 그럼 지금까지 본 3가지의 순위 함수를 이해하기 쉽게 한 눈에 살펴보도록 하겠습니다.

 

4. RANK() vs. DENSE_RANK() vs. ROW_NUMBER(), 한 눈에 비교하기!

 

SELECT *
    , RANK() OVER(ORDER BY `홈런` DESC) AS 'RANK'
    , DENSE_RANK() OVER(ORDER BY `홈런` DESC) AS 'DENSE_RANK'
    , ROW_NUMBER() OVER(ORDER BY `홈런` DESC) AS 'ROW_NUMBER'
FROM kbo_hr

 

🕒 실행결과

 

이름	포지션	홈런	RANK	DENSE_RANK	ROW_NUMBER
이승엽	내야수	467	1	1	1
최정	내야수	403	2	2	2
이대호	내야수	351	3	3	3
양준혁	지명타자	351	3	3	4
최형우	외야수	342	5	4	5
장종훈	내야수	340	6	5	6
이호준	내야수	337	7	6	7
이범호	내야수	329	8	7	8
심정수	외야수	328	9	8	9
박병호	내야수	327	10	9	10
박경완	포수	314	11	10	11
김태균	내야수	311	12	11	12
송지만	외야수	311	12	11	13
박재홍	외야수	300	14	12	14

 

어떤가요? 한 눈에 비교하니 어떤 차이가 있는지 아시겠죠?

 

 

응용. PARTITION BY를 활용한 포지션별 홈런 순위 계산하기

 

마지막으로 이번에는 RANK() 함수에 PARTITION BY()를 활용하여 포지션별 홈런 순위를 계산해보려고 합니다. 여기서는 대표로 RANK() 함수를 썼지만 DENSE_RANK(), ROW_NUMBER() 함수도 모두 사용할 수 있다는 점을 기억해주시기 바랍니다.

 

SELECT *
    , RANK() OVER(PARTITION BY `포지션` ORDER BY `홈런` DESC) AS 'RANK'
FROM kbo_hr

 

이것도 역시 실행결과를 살펴보는게 이해하기 가장 쉬운 방법이라고 생각합니다. 지금까지 우리는 단순위 홈런의 개수로 순위를 계산했다면 이번에는 포지션별 홈런 개수별로 순위를 부여하는 쿼리를 작성해준 것입니다. 내야수 포지션에서는 이승엽 선수, 외야수 포지션에서는 최형우 선수, 지명타자와 포수 포지션에서는 각각 양준혁, 박경완 선수가 가장 많은 홈런을 친 선수임을 확인할 수 있습니다.

 

🕒 실행결과

 

이름	포지션	홈런	RANK
이승엽	내야수	467	1
최정	내야수	403	2
이대호	내야수	351	3
장종훈	내야수	340	4
이호준	내야수	337	5
이범호	내야수	329	6
박병호	내야수	327	7
김태균	내야수	311	8
최형우	외야수	342	1
심정수	외야수	328	2
송지만	외야수	311	3
박재홍	외야수	300	4
양준혁	지명타자	351	1
박경완	포수	314	1

 

아마 실무에서 순위 함수를 활용할 경우에는 PARTITION BY()를 활용해서 쪼개서 데이터 분석을 해야 더 의미있는 인사이트를 도출할 확률이 높아질 것이니 PARTITION BY()를 활용하는 방법을 꼭 기억해주시기 바랍니다!

 

 

작성된 내용 중 잘못된 부분이나 궁금한 사항이 있다면 언제든지 피드백 부탁 드립니다.

 

감사합니다.