[SQL] RANK(), DENSE_RANK(), ROW_NUMBER() 함수에 대해서

SQL 썸네일 이미지

SQL

0. 들어가며

 최근 쏘마 코테를 보면서 윈도우 함수와 랭크 관련 함수를 정리해야겠다고 마음먹었습니다. 공부하면서 해당 내용들을 같이 보면 좋을 것 같아서 이렇게 글을 작성해 봅니다! 윈도우 함수와 랭크 함수는 현실에서도 종종 사용될 수 있다고 생각합니다. 예를 들어, 각 학급 당 석차가 1등인 학생을 찾아야 되는 경우 또는 각 학년 당 각 과목 석차 Top 3인 학생들을 찾아야 하는 경우 등 다양한 상황이 있을 것입니다.

 

 이렇게 보면 윈도우 함수와 랭크 함수는 데이터 분석 관점에서 되게 유용한 정보를 뽑아낼 수 있는 함수들이라고 생각합니다. 흔히 순위라고 하면 Redis Sorted Set과 같은 자료구조를 생각할 수도 있겠지만, 관계형 데이터베이스에서는 이를 어떻게 구현할지 같이 익혀보면 좋을 것 같습니다. 그럼 한번 알아보도록 하겠습니다! (테스트를 위한 활용한 전체 쿼리는 하단에 작성해 놓도록 하겠습니다 :> )

1. 개념 정리 

윈도우 함수의 개념을 시작으로 RANK(), DENSE_RANK(), ROW_NUMBER() 함수 순서로 알아보도록 하겠습니다.

1.1 윈도우 함수

-- 기본 틀 
SELECT WINDOW_FUNCTION(arguments)
OVER(PARTITION BY 컬럼 ORDER BY windowing 절)
FROM 테이블

 윈도우 함수에는 여러 가지 함수가 존재하지만, 먼저 윈도우 함수의 의미부터 이해해 보도록 하겠습니다. "윈도우 함수"라는 말 그대로 창문을 만든다고 이해하시면 될 것 같습니다. 

SELECT
    department,
    count(*) OVER (PARTITION BY department) AS partition_count_num
FROM employees;

위 코드의 결과는 다음과 같습니다.

 얼핏 보면 그룹을 생성한다는 뜻에서 GROUP BY 절과 비슷하다고 생각할 수 있지만, GROUP BY절을 활용하면 행이 사라집니다. 즉, 가장 큰 차이는 그룹을 생성하면서 그 그룹이 하나의 행으로 압축이 되냐, 안 되냐로 GROUP BY절과 윈도우 함수를 나눌 수 있을 것 같습니다. 상황을 한번 드려보도록 하겠습니다.

각 부서마다 근무자들의 연봉을 내림차순으로 해서 표로 나타내주세요.

 "부서마다"라는 워딩 때문에 GROUP BY절을 사용하면 되겠다고 생각할 수 있습니다. 물론, GROUP BY절과 서브 쿼리를 잘 활용하면 만들 수는 있을 것 같습니다. 그러나, SELECT절에 윈도우 함수를 한 번만 사용하면 더 간단하게 구현할 수 있습니다.

-- GROUP BY 절을 서브쿼리로 활용한 쿼리 
SELECT e.department,
       e.name,
       e.salary
FROM (SELECT department
      FROM employees
      GROUP BY department) d
         JOIN employees e ON d.department = e.department
ORDER BY e.department, e.salary DESC;

-- 윈도우 함수를 활용한 쿼리
SELECT department,
       name,
       salary,
       count(*) OVER (PARTITION BY department ORDER BY department, salary DESC) AS salary_rank
FROM employees;

 이렇게 두 쿼리를 적용하면 결과는 컬럼 하나를 제외하고는 동일하게 나옵니다. GROUP BY절을 활용하면 쿼리가 복잡해지는 점과 동시에 집계 함수밖에 활용하지 못한다는 불편함도 존재합니다. 그러면 윈도우 함수로는 어떤 값들을 추가로 만들어 줄 수 있을까요? 윈도우 함수로 많이 활용되는 RANK(), DENSE_RANK(), ROW_NUMBER()는 어떤 특징들을 가지는 지 한번 순서대로 알아보도록 하겠습니다.

1.2 RANK()

 RANK() 함수는 순위를 나타내는 함수입니다. 사실 DENSE_RANK()나 ROW_NUMBER() 함수 모두 순위를 나타내는 함수이지만, 동률인 경우에 서로 나타내는 방식이 서로 다릅니다. RANK() 함수는 동률인 경우, 해당 개수만큼 건너뛰어서 다음 등수를 계산합니다. 즉, 1등 다음 공동 2등이 2명 있는 경우, 그다음 순번은 3위가 아니라 4위로 기록됩니다.

SELECT department,
       name,
       salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num
FROM employees;

RANK() 결과

위 이미지처럼 공동 2등이 2명 존재할 때, 3등이 존재하지 않고, 바로 4등으로 넘어가는 로직이 바로 RANK() 함수입니다. 

1.3 DENSE_RANK()

 그럼, DENSE_RANK()는 어떤 로직을 가질까요? DENSE_RANK() 함수는 공동 2등이 존재하더라도 그 다음 순위는 순서대로 3등으로 찍힙니다. 즉, DENSE_RANK()의 결과는 중복되는 순위가 있더라도 그 순위가 오름차순으로 빠짐없이 사용되는 것이죠.

SELECT department,
       name,
       salary,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_num
FROM employees;

DENSE_RANK() 결과

위 이미지처럼 공동 2등이 2명이 존재하더라도, 그 다음 순서는 3등으로 기록이 됩니다. 

1.4 ROW_NUMBER()

 그럼 ROW_NUMBER() 함수는 어떤 로직을 가지게 될까요? 예상이 되시나요 ㅎㅎㅎ. 그것은 바로, 공동 순위가 있더라도 무조건 순위를 다르게 뽑는 로직을 가집니다. 즉, 10명이 있다고 했을 때, ROW_NUMBER() 함수를 사용하면 1등부터 10등이 모두 존재하게 되는 것이죠.

SELECT department,
       name,
       salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

ROW_NUMBER() 결과

 위 이미지처럼 Engineering 부서에 총 5명의 직원이 있을 때, 1등부터 5등까지 모두 존재하는 로직이 바로 ROW_NUMBER() 함수의 결과입니다.

2. 정리하며

SELECT department,
       name,
       salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC)       AS rank_num,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_num,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

종합 결과

  이번 글에서는 윈도우 함수의 개념과 여러 함수 중 RANK(), DENSE_RANK(), ROW_NUMBER() 3 개의 함수에 대해서 알아보았습니다. 위 이미지처럼 3개의 함수에 대한 종합 결과를 한눈에 보면, 그 차이를 한 번에 이해하실 수 있을 거예요! GROUP BY절과 비슷한 능력을 가졌지만, GROUP BY절은 해당 행들을 하나로 종합하여 집계하는 느낌이라면 WINDOW FUNCTION은 행마다 특정 기준에 맞는 어떤 계산을 수행하고 그 값을 활용할 수 있다는 점이 큰 차이점이었습니다.

 

 사실 윈도우 함수는 이외에도 정말 다양한 함수들이 존재합니다. 비율을 나타내는 함수도 있고, 행의 순서를 다루는 함수들도 있습니다. 저는 그중에서도 특히 기준에 맞는 순위를 측정하는 함수들에 대해서 알아본 것이죠. 기회가 된다면 다른 함수들에 대해서도 알아보는 시간을 가져보도록 하겠습니다. 저도 순위를 다루는 함수들을 정리하면서 다음에 코테에 나오더라도 확실하게 활용할 수 있을 것 같아 되게 기분이 좋습니다 ㅎㅎㅎ. 이번 글이 읽으시는 분들께 조금의 도움이라도 되었으면 좋겠습니다. 화이팅 :> 

SQL 실습 쿼리.txt

0.00MB

( 실습을 원하신다면 위 txt 파일 내용을 활용해주세요 :> )

3. Reference 

• 윈도우 함수 자세하게 정리된 블로그
• 윈도우 함수 이해를 돕는 영상