MySQL ROW_NUMBER() 설명 (MySQL 8.0): 순위 매기기, Top‑N 쿼리 및 중복 제거

1. 소개

MySQL 8.0 버전은 많은 새로운 기능을 도입했으며, 그 중 가장 눈에 띄는 것은 윈도우 함수 지원입니다. 이 글에서는 가장 많이 사용되는 함수 중 하나인 ROW_NUMBER()에 초점을 맞춥니다.

ROW_NUMBER() 함수는 데이터 분석 및 보고에 강력한 기능을 제공하며, 특정 조건에 따라 데이터를 정렬하고 순위를 매기는 작업을 쉽게 해줍니다. 이 글에서는 기본 사용법과 실용적인 예제부터 오래된 MySQL 버전에서 사용할 수 있는 대체 방법까지 모두 설명합니다.

대상 독자

• 기본 SQL 지식이 있는 초급~중급 사용자
• MySQL을 사용해 데이터를 처리하고 분석하는 엔지니어 및 데이터 분석가
• 최신 MySQL 버전으로 마이그레이션을 고려 중인 모든 사람

ROW_NUMBER()의 장점

이 함수는 특정 조건에 따라 각 행에 고유한 번호를 부여할 수 있게 해줍니다. 예를 들어 “판매량을 내림차순으로 순위 매기기” 혹은 “중복 데이터를 추출하고 정리하기”와 같은 쿼리를 간결하게 작성할 수 있습니다.

이전 버전에서는 사용자 정의 변수를 이용해 복잡한 쿼리를 작성해야 했지만, ROW_NUMBER()를 사용하면 SQL이 더 간단하고 가독성이 높아집니다.

이 글에서는 구체적인 쿼리 예제를 사용해 초보자도 이해하기 쉽게 설명합니다. 다음 섹션에서는 이 함수의 기본 구문과 동작 방식을 자세히 살펴보겠습니다.

2. ROW_NUMBER() 함수란?

MySQL 8.0에 새롭게 추가된 ROW_NUMBER() 함수는 행에 순차적인 번호를 부여하는 윈도우 함수의 일종입니다. 특정 순서에 따라 혹은 각 그룹 내에서 행에 번호를 매길 수 있어 데이터 분석 및 보고에 매우 유용합니다. 여기서는 실용적인 예제와 함께 기본 구문을 자세히 설명합니다.

ROW_NUMBER()의 기본 구문

먼저 ROW_NUMBER()의 기본 형식은 다음과 같습니다.

SELECT
    column_name,
    ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column) AS row_num
FROM
    table_name;

각 요소의 의미

• ROW_NUMBER() : 각 행에 순차적인 번호를 부여합니다.
• OVER : 윈도우 함수를 위한 윈도우 영역을 정의하는 키워드입니다.
• PARTITION BY : 지정된 열을 기준으로 데이터를 그룹화합니다. 선택 사항이며, 생략하면 모든 행을 대상으로 번호가 매겨집니다.
• ORDER BY : 번호를 부여할 때 사용되는 정렬 기준을 정의합니다.

기본 예제

예를 들어, 아래와 같은 데이터가 들어 있는 “sales” 테이블이 있다고 가정해 보겠습니다.

각 부서별로 판매량을 내림차순 정렬하여 순차적인 번호를 부여하려면 다음 쿼리를 사용합니다.

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS row_num
FROM
    sales;

결과

이 결과를 통해 각 부서 내에서 판매량에 따른 순위가 표시된 것을 확인할 수 있습니다.

PARTITION BY 사용 방법

위 예제에서는 “department” 열을 기준으로 데이터를 그룹화했습니다. 이렇게 하면 각 부서마다 별도의 번호 시퀀스가 생성됩니다.

PARTITION BY를 생략하면 모든 행을 하나의 시퀀스로 번호가 매겨집니다.

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

결과

ROW_NUMBER()의 특성 및 주의사항

• 고유 번호 부여 : 값이 동일하더라도 부여되는 번호는 고유합니다.
• NULL 처리 : ORDER BY에 NULL이 포함되면, 오름차순에서는 먼저, 내림차순에서는 마지막에 배치됩니다.
• 성능 영향 : 대용량 데이터셋에서는 ORDER BY가 비용이 많이 들 수 있으므로 적절한 인덱스 설계가 중요합니다.

3. 실제 사용 사례

다음은 MySQL의 ROW_NUMBER() 함수를 활용한 실용적인 시나리오들입니다. 이 함수는 데이터 순위 매기기, 중복 데이터 처리 등 다양한 실제 상황에서 유용하게 사용됩니다.

3-1. 그룹별 순위 매기기

예를 들어, 판매 데이터를 사용하여 “부서별로 직원의 매출 순위를 매기고 싶다”는 경우를 생각해 보세요. 다음 데이터 세트를 예시로 사용합니다.

쿼리 예시: 부서별 매출 순위

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

결과:

이렇게 하면 각 부서는 매출을 내림차순으로 정렬한 자체 순서를 갖게 되어 순위 작성을 쉽게 할 수 있습니다.

3-2. 상위 N 행 추출

다음으로, “부서별로 매출 기준 상위 3명의 직원을 추출하고 싶다”는 경우를 살펴보겠습니다.

쿼리 예시: 상위 N 행 추출

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT
    employee,
    department,
    sale
FROM
    RankedSales
WHERE
    rank <= 3;

결과:

이 예시는 각 부서별 매출 기준 상위 3행만을 가져옵니다. 보시다시피 ROW_NUMBER()는 순위 매기기에만 국한되지 않고 상위 결과를 필터링하는 데도 적합합니다.

3-3. 중복 데이터 찾기 및 제거

데이터베이스에는 때때로 중복 레코드가 존재합니다. 이러한 경우 ROW_NUMBER()를 사용하면 쉽게 처리할 수 있습니다.

쿼리 예시: 중복 감지

SELECT *
FROM (
    SELECT
        employee,
        sale,
        ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
    FROM
        sales
) tmp
WHERE rank > 1;

이 쿼리는 동일한 직원 이름에 대해 여러 레코드가 존재할 때 중복을 감지합니다.

쿼리 예시: 중복 삭제

DELETE FROM sales
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
        FROM
            sales
    ) tmp
    WHERE rank > 1
);

요약

ROW_NUMBER()는 다음과 같은 다양한 시나리오에서 유용합니다:

1. 각 그룹 내 순위 매기기
2. 상위 N 행 추출
3. 중복 감지 및 삭제

이를 통해 복잡한 데이터 처리와 분석이 보다 간단하고 효율적으로 이루어집니다.

4. 다른 윈도우 함수와의 비교

MySQL 8.0에서는 ROW_NUMBER() 외에도 순위 및 위치 계산에 사용할 수 있는 RANK(), DENSE_RANK()와 같은 윈도우 함수가 있습니다. 이들 함수는 역할이 비슷하지만 동작 방식과 결과가 다릅니다. 여기서는 각 함수를 비교하고 언제 사용해야 하는지 설명합니다.

4-1. RANK() 함수

RANK() 함수는 순위를 부여하며, 동일한 값에 동일한 순위를 주고 다음 순위 번호를 건너뜁니다.

기본 구문

SELECT
    column_name,
    RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS rank
FROM
    table_name;

예시

다음 데이터를 사용하여 매출 순위를 계산합니다.

쿼리 예시: RANK() 사용

SELECT
    employee,
    sale,
    RANK() OVER (ORDER BY sale DESC) AS rank
FROM
    sales;

결과:

핵심 포인트:

• 매출액이 동일한 A와 B(800)는 모두 순위 “1”로 처리됩니다.
• 다음 순위 “2”가 건너뛰어져 C는 순위 “3”이 됩니다.

4-2. DENSE_RANK() 함수

DENSE_RANK() 함수도 동일한 값에 동일한 순위를 부여하지만, 다음 순위 번호를 건너뛰지 않습니다.

기본 구문

SELECT
    column_name,
    DENSE_RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS dense_rank
FROM
    table_name;

예시

위와 동일한 데이터를 사용하여 DENSE_RANK() 함수를 적용해 보세요.

쿼리 예시: DENSE_RANK() 사용

SELECT
    employee,
    sale,
    DENSE_RANK() OVER (ORDER BY sale DESC) AS dense_rank
FROM
    sales;

결과:

핵심 포인트:

• 판매 금액이 동일한(800) A와 B는 둘 다 순위 “1”로 처리됩니다.
• RANK()와 달리 다음 순위는 “2”부터 시작하므로 순위 연속성이 유지됩니다.

4-3. ROW_NUMBER()의 차이점

ROW_NUMBER() 함수는 값이 동일하더라도 고유한 번호를 할당한다는 점에서 다른 두 함수와 다릅니다.

예제

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

결과:

주요 포인트:

• 값이 동일하더라도 각 행은 고유한 번호를 받으므로 중복 순위가 없습니다.
• 엄격한 순서 제어가 필요하거나 행별 고유성이 필요한 경우에 유용합니다.

4-4. 사용 사례 요약

요약

시나리오에 따라 ROW_NUMBER(), RANK(), DENSE_RANK()를 적절히 사용해야 합니다.

1. ROW_NUMBER()는 행별 고유 번호가 필요한 경우에 가장 적합합니다.
2. RANK()는 동점 순위를 공유하고 순위 간격을 강조하고 싶을 때 유용합니다.
3. DENSE_RANK()는 간격 없이 연속된 순위를 원할 때 적합합니다.

5. MySQL 8.0 미만 버전의 대안

MySQL 8.0 이전 버전에서는 ROW_NUMBER() 및 기타 윈도우 함수가 지원되지 않습니다. 그러나 사용자 정의 변수를 사용하여 유사한 동작을 달성할 수 있습니다. 이 섹션에서는 MySQL 8.0 미만 버전에 대한 실용적인 대안을 설명합니다.

5-1. 사용자 정의 변수 사용한 순차 번호 매기기

MySQL 5.7 및 이전 버전에서는 사용자 정의 변수를 사용하여 행별 순차 번호를 할당할 수 있습니다. 다음 예제를 살펴보겠습니다.

예제: 부서별 판매 순위

샘플 데이터:

쿼리:

SET @row_num = 0;
SET @dept = '';

SELECT
    employee,
    department,
    sale,
    @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
    @dept := department
FROM
    (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales;

결과:

5-2. 상위 N 행 추출

상위 N 행을 검색하려면 사용자 정의 변수를 유사한 방식으로 사용할 수 있습니다.

쿼리:

SET @row_num = 0;
SET @dept = '';

SELECT *
FROM (
    SELECT
        employee,
        department,
        sale,
        @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
        @dept := department
    FROM
        (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales
) AS ranked_sales
WHERE rank <= 3;

결과:

이 쿼리는 부서별 순위를 할당한 후 상위 3위 이내의 행만 추출합니다.

5-3. 중복 감지 및 삭제

사용자 정의 변수를 사용하여 중복 데이터도 처리할 수 있습니다.

쿼리 예제: 중복 감지

SET @row_num = 0;
SET @id_check = '';

SELECT *
FROM (
    SELECT
        id,
        name,
        @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
        @id_check := name
    FROM
        (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
) AS tmp
WHERE rank > 1;

쿼리 예제: 중복 삭제

DELETE FROM customers
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
            @id_check := name
        FROM
            (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
    ) AS tmp
    WHERE rank > 1
);

5-4. 사용자 정의 변수 사용 시 주의사항

1. 세션 종속성

• 사용자 정의 변수는 현재 세션 내에서만 유효합니다. 다른 쿼리나 세션 간에 재사용할 수 없습니다.

1. 처리 순서 종속성

• 사용자 정의 변수는 실행 순서에 의존하므로 ORDER BY를 적절히 설정하는 것이 중요합니다.

1. SQL 가독성 및 유지보수성

• 쿼리가 복잡해질 수 있으므로 MySQL 8.0 이상에서는 윈도우 함수 사용을 권장합니다.

요약

MySQL 8.0 이하 버전에서는 사용자 정의 변수를 사용하여 윈도우 함수 대신 순차 번호 매기기와 순위 매기기를 구현할 수 있습니다. 하지만 쿼리가 복잡해지는 경향이 있기 때문에 가능한 경우 최신 버전으로 마이그레이션하는 것이 좋습니다.

6. 주의사항 및 모범 사례

MySQL의 ROW_NUMBER() 함수와 변수 기반 대안은 매우 편리하지만, 정확하고 효율적으로 사용하기 위해 기억해야 할 중요한 포인트가 있습니다. 이 섹션에서는 성능 최적화를 위한 실용적인 주의사항과 모범 사례를 설명합니다.

6-1. 성능 고려 사항

1. ORDER BY 비용

ROW_NUMBER()는 항상 ORDER BY와 함께 사용됩니다. 정렬이 필요하기 때문에 대용량 데이터셋에서는 처리 시간이 크게 증가할 수 있습니다.

완화 방안:

• 인덱스 사용: ORDER BY에 사용되는 컬럼에 인덱스를 추가하여 정렬 속도를 높입니다.
• LIMIT 사용: 실제로 필요한 행 수만 가져와 처리되는 데이터 양을 줄입니다.

예시:

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales
LIMIT 1000;

2. 메모리 사용량 및 디스크 I/O 증가

윈도우 함수는 임시 테이블과 메모리를 사용해 처리됩니다. 데이터 양이 늘어날수록 메모리 소비와 디스크 I/O가 증가할 수 있습니다.

완화 방안:

• 쿼리 분할: 처리를 작은 쿼리로 나누고 데이터를 단계별로 추출하여 부하를 감소시킵니다.
• 임시 테이블 사용: 추출한 데이터를 임시 테이블에 저장하고 그곳에서 집계를 수행해 작업 부하를 분산합니다.

6-2. 쿼리 튜닝 팁

1. 실행 계획 확인

MySQL에서는 EXPLAIN을 사용해 쿼리 실행 계획을 확인할 수 있습니다. 이를 통해 인덱스가 올바르게 사용되고 있는지 검증할 수 있습니다.

예시:

EXPLAIN
SELECT
    employee,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

예시 출력:

Using index가 표시되면 인덱스가 적절히 사용되고 있음을 의미합니다.

2. 인덱스 최적화

ORDER BY와 WHERE에 사용되는 컬럼에 인덱스를 반드시 추가하세요. 특히 다음 사항에 주의합니다.

• 단일 컬럼 인덱스: 간단한 정렬 조건에 적합
• 복합 인덱스: 여러 컬럼이 조건에 포함될 때 효과적

예시:

CREATE INDEX idx_department_sale ON sales(department, sale DESC);

3. 배치 처리 사용

한 번에 거대한 데이터셋을 처리하는 대신, 데이터를 배치로 나누어 처리하면 부하를 줄일 수 있습니다.

예시:

SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 0;
SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 1000;

6-3. 데이터 일관성 유지

1. 업데이트 및 재계산

행이 삽입되거나 삭제될 때 번호가 변경될 수 있습니다. 필요에 따라 번호를 재계산하는 메커니즘을 구축하세요.

예시:

CREATE VIEW ranked_sales AS
SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

뷰를 사용하면 최신 데이터를 기반으로 순위를 최신 상태로 유지할 수 있습니다.

6-4. 모범 사례 쿼리 예시

아래는 성능과 유지 보수를 고려한 모범 사례 예시입니다.

예시: Top N 행 추출

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT *
FROM RankedSales
WHERE rank <= 3;

이 구조는 공통 테이블 식(CTE)을 사용해 가독성과 재사용성을 높입니다.

요약

ROW_NUMBER() 또는 그 대안을 사용할 때는 위의 사항들을 기억하세요.

1. 인덱스 최적화를 통해 속도를 향상시킵니다.
2. 실행 계획을 확인하여 병목 현상을 파악합니다.
3. 데이터 업데이트를 계획하고 일관성을 유지합니다.
4. 배치 처리와 CTE를 사용하여 부하를 분산합니다.

이러한 모범 사례를 적용하면 대규모 데이터 분석 및 보고에 효율적인 처리를 할 수 있습니다.

7. 결론

이 글에서는 MySQL의 ROW_NUMBER() 함수에 초점을 맞추어 기본 사용법과 실용 예제부터 구버전 대안, 주의사항 및 모범 사례까지 모두 설명했습니다. 이번 섹션에서는 핵심 포인트를 정리하고 실용적인 요점을 요약합니다.

7-1. ROW_NUMBER()가 유용한 이유

ROW_NUMBER() 함수는 다음과 같은 방식으로 데이터 분석 및 보고에 특히 편리합니다:

1. 그룹 내 순차 번호 부여: 부서별 또는 카테고리별 매출 순위를 손쉽게 생성합니다.
2. Top N 행 추출: 특정 조건에 따라 데이터를 효율적으로 필터링하고 추출합니다.
3. 중복 탐지 및 삭제: 데이터 정리와 조직에 유용합니다.

복잡한 쿼리를 단순화해 주기 때문에 SQL의 가독성과 유지보수성을 크게 향상시킵니다.

7-2. 다른 윈도우 함수와의 비교

RANK(), DENSE_RANK()와 같은 윈도우 함수와 비교했을 때, ROW_NUMBER()는 동일한 값이 있더라도 고유한 번호를 부여한다는 점에서 차이가 있습니다.

올바른 함수를 선택하기:
목적에 맞는 최적의 함수를 선택하면 효율적인 데이터 처리가 가능합니다.

7-3. 구버전 MySQL 처리

MySQL 8.0 이하 환경에서는 사용자 정의 변수를 활용한 방법도 소개했습니다. 하지만 다음과 같은 주의사항을 고려해야 합니다:

• 복잡한 SQL로 인해 가독성이 낮아짐
• 경우에 따라 쿼리 최적화가 더 어려워짐
• 데이터 일관성을 유지하기 위해 추가적인 처리가 필요할 수 있음

가능하다면 MySQL 8.0 이상으로 마이그레이션하고 윈도우 함수를 사용하는 것을 강력히 권장합니다.

7-4. 성능 최적화를 위한 핵심 포인트

1. 인덱스 사용: ORDER BY에 사용되는 컬럼에 인덱스를 추가하여 속도를 개선합니다.
2. 실행 계획 확인: EXPLAIN을 통해 사전에 성능을 검증합니다.
3. 배치 처리 도입: 대용량 데이터를 작은 청크로 나누어 부하를 분산합니다.
4. 뷰와 CTE 활용: 재사용성을 높이고 복잡한 쿼리를 단순화합니다.

이러한 기법을 적용하면 효율적이고 안정적인 데이터 처리를 달성할 수 있습니다.

7-5. 최종 메모

ROW_NUMBER()는 데이터 분석 효율성을 크게 높일 수 있는 강력한 도구입니다.
이 글에서는 기본 구문과 실용 예제부터 주의사항 및 대안까지 모두 다루었습니다.

이 글을 따라가며 직접 쿼리를 실행해 보시길 권장합니다. SQL 실력을 향상시키면 보다 복잡한 데이터 분석 및 보고 작업을 자신 있게 수행할 수 있습니다.

부록: 참고 자료

• 공식 문서: MySQL Window Functions
• 온라인 SQL 환경: SQL Fiddle (온라인에서 SQL을 실행하고 테스트할 수 있는 도구)