[SQL] RANK() 사용법

SQL의 RANK() 함수는 데이터 분석에서 순위를 매기는 데 유용한 윈도우 함수다.
특히 RANK() OVER (PARTITION BY)를 사용하면 데이터를 그룹화하고 각 그룹 내에서 순위를 매길 수 있다.

RANK() OVER (PARTITION BY)란?

RANK()는 데이터 행에 순위를 부여하는 윈도우 함수다. OVER 절은 함수가 적용될 데이터 범위를 정의하고, PARTITION BY는 데이터를 특정 기준으로 그룹화한다. 이를 통해 각 그룹 내에서 독립적으로 순위를 계산할 수 있다.

구성 요소

• RANK(): 각 행에 순위를 부여한다. 동률인 경우 동일한 순위를 부여하고, 다음 순위를 건너뛴다.
• OVER: 윈도우 함수의 범위를 지정한다.
• PARTITION BY: 데이터를 그룹으로 나눈다.
• ORDER BY: 순위를 매길 기준을 정의한다.

예를 들어, 학급별로 학생의 성적 순위를 매기고 싶다면 PARTITION BY class와 ORDER BY score DESC를 사용해 각 학급 내에서 성적 기준으로 순위를 계산할 수 있다.

예제: 학급별 성적 순위 계산

다음은 students 테이블을 사용한 예제이다:

CREATE TABLE students (
    student_id INT,
    class VARCHAR(50),
    score DECIMAL(5, 2)
);

INSERT INTO students VALUES
(1, 'Class A', 85.50),
(2, 'Class A', 92.00),
(3, 'Class A', 78.00),
(4, 'Class B', 88.50),
(5, 'Class B', 88.50),
(6, 'Class B', 95.00);

SELECT 
    student_id,
    class,
    score,
    RANK() OVER (
        PARTITION BY class
        ORDER BY score DESC
    ) AS rank_in_class
FROM 
    students;

결과:

student_id	class	score	rank_in_class
2	Class A	92.00	1
1	Class A	85.50	2
3	Class A	78.00	3
6	Class B	95.00	1
4	Class B	88.50	2
5	Class B	88.50	2

이 쿼리는 학급별로 데이터를 나누고, 성적을 기준으로 내림차순으로 순위를 매긴다. Class A와 Class B 학급 각각에서 독립적으로 순위가 계산된다. Class B에서 학생 4와 5가 동률(88.50)로 2등을 받고, 다음 순위(3등)는 건너뛴다.

RANK() vs. DENSE_RANK() vs. ROW_NUMBER()

RANK()와 유사한 함수로 DENSE_RANK()와 ROW_NUMBER()가 있다. 이들의 차이점은 다음과 같다:

• RANK(): 동률인 경우 동일한 순위를 부여하고, 다음 순위를 건너뛴다. 예를 들어, 두 행이 1등이면 다음 순위는 3등이다.
• DENSE_RANK(): 동률인 경우 동일한 순위를 부여하지만, 다음 순위를 건너뛰지 않는다. 즉, 두 행이 1등이면 다음 순위는 2등이다.
• ROW_NUMBER(): 동률 여부와 관계없이 각 행에 고유한 번호를 부여한다.

아래 예제로 한 번에 확인해 보자:

SELECT 
    student_id,
    class,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_number
FROM 
    students;

결과:

student_id	class	score	rank	dense_rank	row_number
6	Class B	95.00	1	1	1
2	Class A	92.00	2	2	2
4	Class B	88.50	3	3	3
5	Class B	88.50	3	3	4
1	Class A	85.50	5	4	5
3	Class A	78.00	6	5	6

RANK()는 88.50의 동률로 인해 4등을 건너뛰지만, DENSE_RANK()는 4등을 부여한다. ROW_NUMBER()는 순서대로 고유 번호를 부여한다.

활용 예시!

RANK() OVER (PARTITION BY)는 다양한 데이터 분석 시나리오에서 활용된다.

1. 지역별 상위 판매자 식별

각 지역에서 최고 매출을 기록한 직원을 찾고 싶을 때 사용한다:

SELECT *
FROM (
    SELECT 
        employee_id,
        region,
        sales_total,
        RANK() OVER (PARTITION BY region ORDER BY sales_total DESC) AS rank
    FROM 
        yearly_sales
) ranked
WHERE 
    rank = 1;

이 쿼리는 각 지역에서 매출 1위인 직원을 반환한다.

2. 상위 10% 상품 분석

매출 기준 상위 10% 상품을 식별할 때 PERCENT_RANK()를 활용한다:

SELECT 
    product_id,
    product_name,
    revenue,
    percent_rank
FROM (
    SELECT 
        product_id,
        product_name,
        revenue,
        PERCENT_RANK() OVER (ORDER BY revenue DESC) AS percent_rank
    FROM 
        product_sales
) ranked
WHERE 
    percent_rank <= 0.1;

3. 시계열 분석

일별 데이터 변화를 분석하고 큰 증가폭을 가진 날을 순위로 표시할 수 있다:

SELECT 
    date,
    value,
    value - LAG(value) OVER (ORDER BY date) AS daily_change,
    RANK() OVER (ORDER BY value - LAG(value) OVER (ORDER BY date) DESC) AS rank_by_increase
FROM 
    daily_metrics;

4. 고객 세그먼테이션

고객의 구매 금액을 기준으로 등급을 나누고, 각 지역별 상위 고객을 식별할 수 있다:

SELECT 
    customer_id,
    region,
    total_spent,
    RANK() OVER (PARTITION BY region ORDER BY total_spent DESC) AS rank
FROM 
    customer_purchases
WHERE 
    purchase_date >= '2025-01-01';

이 쿼리는 2025년 이후 구매 데이터를 기반으로 각 지역에서 가장 많이 소비한 고객을 순위별로 나열한다. 이를 통해 VIP 고객을 식별하고 맞춤형 마케팅 전략을 수립할 수 있다.

주의사항

RANK() OVER (PARTITION BY)를 사용할 때 다음의 구체적인 주의사항을 고려해야 한다.

1. NULL 값 처리

RANK()는 ORDER BY에 사용된 컬럼의 NULL 값을 처리할 때 주의가 필요하다. 대부분의 SQL 엔진에서 NULL은 가장 낮은 값(ASC) 또는 가장 높은 값(DESC)으로 간주된다. 예를 들어, ORDER BY score DESC에서 NULL이 있는 경우 예상치 못하게 상위 순위로 나타날 수 있다. 이를 방지하려면 NULLS LAST 또는 COALESCE(score, 0)를 사용해 NULL 값을 명시적으로 처리한다:

SELECT 
    student_id,
    class,
    score,
    RANK() OVER (
        PARTITION BY class 
        ORDER BY score DESC NULLS LAST
    ) AS rank_in_class
FROM 
    students;

2. 동률 처리 선택

RANK()는 동률인 경우 동일한 순위를 부여하고 다음 순위를 건너뛴다(예: 1, 1, 3). 이는 비즈니스 요구사항에 따라 적절하지 않을 수 있다. 예를 들어, 상위 3명을 선정할 때 RANK()는 동률로 인해 예상보다 많은 행을 반환할 수 있다. 이 경우 DENSE_RANK()(건너뛰지 않음) 또는 ROW_NUMBER()(고유 번호 부여)를 고려한다. 비즈니스 로직에 맞는 함수를 선택해야 한다.

3. 파티션 크기 관리

PARTITION BY에 사용된 컬럼의 고유 값이 너무 많으면 각 파티션의 크기가 작아져 성능에 영향을 미칠 수 있다. 예를 들어, PARTITION BY student_id처럼 고유 값이 많은 컬럼을 사용하면 거의 모든 행이 별도의 파티션으로 처리되어 윈도우 함수의 이점이 줄어든다. 파티션 컬럼은 적절히 그룹화할 수 있는 컬럼(예: class, region)을 선택한다.

4. ORDER BY 정밀도

ORDER BY에 사용된 컬럼의 데이터 타입과 정밀도가 순위 계산에 영향을 미친다. 예를 들어, score가 DECIMAL 타입이라면 소수점 이하의 미세한 차이로 순위가 달라질 수 있다. 반면, 정수형으로 반올림하면 동률이 늘어날 수 있다. 데이터의 특성과 비즈니스 요구사항에 맞게 정렬 기준을 명확히 정의한다:

SELECT 
    student_id,
    class,
    score,
    RANK() OVER (
        PARTITION BY class 
        ORDER BY ROUND(score, 1) DESC
    ) AS rank_in_class
FROM 
    students;

5. GROUP BY와의 적절한 선택

단순히 그룹별 최대/최소 값을 찾거나 집계가 필요할 때는 RANK() 대신 GROUP BY를 사용하는 것이 더 효율적일 수 있다. 예를 들어, 학급별 최고 성적 학생을 찾을 때:

SELECT 
    s.student_id,
    s.class,
    s.score
FROM 
    students s
INNER JOIN (
    SELECT 
        class,
        MAX(score) AS max_score
    FROM 
        students
    GROUP BY 
        class
) max_scores
ON s.class = max_scores.class AND s.score = max_scores.max_score;

이 쿼리는 RANK()보다 간단하고, 인덱스를 잘 활용하면 성능이 더 나을 수 있다. 복잡한 순위 분석이 필요하지 않다면 GROUP BY를 우선 고려한다.

결론

RANK() OVER (PARTITION BY)는 데이터를 그룹화하고 순위를 매기는 데 강력한 도구다.
학급별 성적 순위, 지역별 상위 판매자, 상위 퍼센트 상품 분석, 시계열 변화 순위, 고객 세그먼테이션 등 다양한 시나리오에서 활용된다.

NULL 값 처리, 동률 처리, 파티션 크기, 정렬 정밀도, GROUP BY와의 적절한 선택을 고려해야 한다.