티스토리 뷰
목차
관계형 데이터베이스(RDBMS) 환경에서 복잡한 데이터 분석과 정교한 리포팅 쿼리를 작성할 때, 개발자와 DBA(데이터베이스 관리자)의 생산성을 비약적으로 끌어올려 주는 가장 혁신적인 기능은 단연 '윈도우 함수(Window Function)'입니다. 그중에서도 실무 현장에서 가장 빈번하게 사용되며 핵심적인 역할을 담당하는 것이 바로 데이터에 순위를 부여하는 순위 함수(Ranking Functions) 3대장인 ROW_NUMBER, RANK, 그리고 DENSE_RANK입니다. 이 세 가지 함수는 '순위를 매긴다'는 기본적인 목적과 OVER() 절을 동반한다는 문법적 구조는 완전히 동일하지만, 중복된 값(동점자)이 발생했을 때 순위를 처리하는 내부적인 논리 구조와 결과값이 확연하게 다릅니다. 이 미세한 차이를 정확하게 이해하지 못하고 쿼리를 작성하면, 쇼핑몰의 베스트셀러 상품 추출이나 사내 인사 평가 시스템의 등급 산정 등 매우 중요한 비즈니스 로직에서 치명적인 데이터 누락이나 중복 오류를 발생시킬 수 있습니다. 오늘 포스팅에서는 이 3가지 윈도우 함수의 정확한 개념 정의부터 시작하여, 동점자를 처리하는 구체적인 작동 원리의 차이, PARTITION BY 절과의 강력한 응용 기법, 그리고 대용량 데이터 처리 시 반드시 고려해야 할 인덱스 최적화 및 실행 계획(Execution Plan) 분석까지 현업 데이터 엔지니어의 관점에서 아주 깊고 완벽하게 비교 분석해 드리겠습니다.
1. ROW_NUMBER(): 중복을 허용하지 않는 고유한 순차 번호 부여
가장 직관적이고 실무에서 압도적인 사용 빈도를 자랑하는 윈도우 함수는 바로 ROW_NUMBER()입니다. 이 함수는 이름이 내포하고 있듯이, OVER 절 내부에 명시된 ORDER BY 정렬 기준에 따라서 결과 집합의 각 행(Row)에 1부터 시작하는 연속적이고 고유한 정수 번호를 무조건적으로 부여합니다. 여기서 가장 중요한 핵심이자 다른 두 함수와의 결정적인 차이점은, 정렬 기준이 되는 컬럼의 값이 완전히 동일한 '동점자' 데이터가 여러 건 존재하더라도 절대 공동 순위를 허용하지 않는다는 것입니다. 동점 데이터들 사이에서는 데이터베이스 엔진이 내부적으로 데이터를 읽어들인 물리적인 순서(RowID 등)나 임의의 기준에 따라 무작위로 줄을 세워 1, 2, 3, 4, 5와 같이 중복 없는 일련번호를 강제로 할당해 버립니다. 이러한 특성 때문에 동점자 처리 시 매번 쿼리를 실행할 때마다 순위가 뒤바뀔 수 있는 비결정적(Non-deterministic) 결과를 초래할 위험이 있으므로, 데이터의 정합성을 보장하기 위해서는 ORDER BY 절에 고유 키(Primary Key) 컬럼을 서브 정렬 기준으로 추가하여 명확한 순서를 지정해 주는 것이 바람직한 코딩 습관입니다.
ROW_NUMBER()가 실무에서 가장 눈부시게 활약하는 대표적인 비즈니스 유스케이스는 크게 두 가지입니다. 첫 번째는 웹 애플리케이션의 게시판이나 상품 목록에서 흔히 볼 수 있는 '페이징(Paging) 처리'입니다. ROWNUM이나 LIMIT, OFFSET 절을 사용하는 대신 ROW_NUMBER를 활용하여 인라인 뷰(Inline View)를 구성하면 정렬과 추출 범위를 매우 정교하게 제어할 수 있습니다. 두 번째는 데이터 웨어하우스(DW) 환경의 ETL(추출, 변환, 적재) 과정에서 가장 골치 아픈 '중복 데이터 제거(Deduplication)' 작업입니다. 예를 들어 한 명의 고객(User ID)이 여러 번 회원 정보를 수정하여 이력 테이블에 데이터가 여러 줄 쌓였을 때, 고객별로 가장 최근에 수정된 최신 데이터 단 한 건씩만 깔끔하게 추출하고 싶다면 어떻게 해야 할까요? 바로 ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY update_date DESC) 쿼리를 작성한 뒤, 이 값이 '1'인 행만 필터링하는 방식을 사용합니다. 이는 다중 서브쿼리나 복잡한 조인을 사용하는 것보다 디스크 I/O 비용을 획기적으로 줄여주며, 쿼리의 가독성을 극대화하는 DBA들의 최고급 필수 스킬입니다.
2. RANK(): 올림픽 메달 집계 방식, 동점자 공동 순위와 건너뛰기
우리가 일상생활에서 가장 흔하게 접하는 전통적인 순위 산정 방식을 데이터베이스로 그대로 옮겨 놓은 것이 바로 RANK() 함수입니다. RANK 함수는 OVER 절의 ORDER BY 기준 컬럼 값이 완전히 동일한 데이터가 존재할 경우, ROW_NUMBER처럼 억지로 순위를 가르지 않고 동일한 순위(공동 순위)를 정직하게 부여합니다. 하지만 그다음 이어지는 순위를 부여할 때 매우 독특하고 중요한 규칙이 적용되는데, 바로 '앞에서 발생한 동점자의 명수만큼 숫자를 건너뛴다(Skip)'는 점입니다. 이 방식은 올림픽이나 스포츠 경기의 순위 매기기를 떠올리면 이해가 매우 쉽습니다. 만약 100m 달리기 경기에서 1등이 한 명 있고, 간발의 차이 없이 완전히 똑같은 기록으로 결승선을 통과한 선수가 두 명이라면 이 두 명은 모두 공동 2등이 됩니다. 그리고 그들 바로 다음으로 들어온 선수는 3등이 아니라 앞의 세 명을 제외한 '4등'이 되는 것입니다. 즉, RANK 함수의 결과값은 1, 2, 2, 4, 5, 5, 5, 8 과 같이 중간중간 숫자가 이빨 빠진 것처럼 건너뛰는 형태의 데이터 시퀀스를 만들어냅니다.
이러한 RANK 함수의 갭(Gap) 발생 특성은 데이터 분석 보고서 작성 시 전체 모집단 내에서 특정 레코드가 차지하는 '절대적인 위치나 누적 등수'를 파악해야 할 때 매우 유용하게 쓰입니다. 예를 들어 사내 영업 사원들의 분기별 실적을 기반으로 상위 10명을 선발하여 포상금을 지급하는 비즈니스 로직을 구현한다고 가정해 보겠습니다. 만약 공동 9등이 3명이나 발생했다면 어떻게 해야 할까요? RANK 함수를 사용하여 순위가 10 이하인 사람을 추출하게 되면, 1등부터 공동 9등 3명까지 총 11명이 추출되며 그다음 실적자는 12등으로 밀려나 자연스럽게 커트라인 밖으로 제외됩니다. 이는 기업의 예산 집행이나 장학금 지급, 입시 커트라인 산정 등 "내 앞에 나보다 성적이 좋거나 같은 사람이 정확히 몇 명 존재하는가?"라는 팩트가 가장 중요할 때 오류 없는 정확한 지표를 제공해 주는 핵심 함수로 작용합니다.
3. DENSE_RANK(): 촘촘하게 채우는 순위, 건너뛰기 없는 등급제 연산
세 번째로 살펴볼 DENSE_RANK() 함수는 이름에 포함된 'Dense(밀집한, 촘촘한)'라는 영단어의 뜻에서 유추할 수 있듯이, 순위와 순위 사이의 틈(Gap)을 허용하지 않고 숫자를 아주 촘촘하게 채워 넣는 특성을 지니고 있습니다. 이 함수는 RANK 함수와 마찬가지로 정렬 기준 값이 같은 레코드들에 대해서는 동일한 공동 순위를 쿨하게 인정하고 부여합니다. 하지만 앞서 살펴본 RANK와의 치명적인 차이점은 공동 순위가 몇 명이 나오든 전혀 상관하지 않고, 그다음 이어지는 레코드에는 무조건 순차적인 다음 숫자를 부여하여 결코 순위를 건너뛰지 않는다는 점입니다. 즉, 100점 만점자가 3명이 나와 공동 1등을 차지하더라도, 99점을 맞은 그다음 사람은 4등으로 밀려나는 것이 아니라 바로 다음 순위인 '2등'의 지위를 획득하게 됩니다. DENSE_RANK의 산출 결과는 1, 1, 1, 2, 3, 3, 4 와 같이 빠진 숫자 없이 1씩 꼬박꼬박 증가하는 매끄러운 수열을 보여줍니다.
DENSE_RANK 함수가 실무에서 가장 강력한 힘을 발휘하는 영역은 내 앞에 사람이 몇 명인지 세는 것이 아니라, 내가 몇 번째 '그룹(Group) 또는 등급(Tier)'에 속해 있는지를 판별해야 하는 비즈니스 로직입니다. 가장 흔한 예로 쇼핑몰의 고객 VIP 등급 산정 시스템을 들 수 있습니다. "올해 가장 돈을 많이 쓴 결제 금액 상위 1위부터 3위까지의 '금액 단위'에 도달한 고객들에게 다이아몬드 등급을 부여하라"는 요건이 떨어졌다고 가정해 봅시다. 만약 1천만 원을 결제한 공동 1위 고객이 5명이라면 RANK를 쓰면 벌써 5명이 채워져 그다음 결제액인 9백만 원 고객은 6등이 되어 다이아몬드 등급을 받지 못합니다. 하지만 DENSE_RANK를 사용하면 1천만 원 결제 그룹 전원이 1위, 9백만 원 결제 그룹 전원이 2위, 8백만 원 결제 그룹 전원이 3위가 되어, 앞선 동점자의 명수와 관계없이 특정 목표치(등수)에 도달한 '모든 티어'의 고객들을 아주 공평하고 정확하게 추출해 낼 수 있습니다. 이처럼 점수의 계층 구간 자체를 서열화할 때 없어서는 안 될 필수 함수입니다.
4. PARTITION BY의 마법: 그룹별 순위 산정과 Top-N 쿼리의 진화
순위 함수 3대장의 강력함은 단순히 전체 테이블을 한 줄로 세우는 것을 넘어서, OVER 절 내부의 PARTITION BY 구문과 결합되었을 때 그 진가를 1000% 발휘합니다. 일반적인 GROUP BY 절이 데이터를 그룹화한 뒤 합계나 평균 같은 한 줄짜리 단일 요약 정보로 데이터를 납작하게 압축해 버린다면, 윈도우 함수의 PARTITION BY는 원본 데이터의 행(Row) 개수와 세부 정보를 전혀 훼손하거나 잃어버리지 않은 상태에서, 오직 순위를 매기기 위한 '가상의 논리적 그룹 창문(Window)'만을 생성합니다. 데이터베이스 엔진은 이 PARTITION BY에 명시된 컬럼 값을 기준으로 데이터를 파티션 단위로 쪼갠 뒤, 각각의 파티션 내부에서 독립적으로 순위 번호를 1번부터 새롭게 매기기 시작하고 파티션이 바뀌면 다시 1번으로 순위를 초기화(Reset)하는 환상적인 작업을 내부적으로 수행합니다.
이 메커니즘은 실무의 복잡한 '그룹별 Top-N 추출' 요구사항을 단 한 번의 깔끔한 스캔으로 해결해 줍니다. 과거 윈도우 함수가 표준화되기 전에는 "각 부서별로 가장 연봉을 많이 받는 상위 3명의 직원을 모두 추출하라"는 다소 까다로운 미션을 수행하기 위해 엄청나게 무거운 상호 연관 서브쿼리(Correlated Subquery)를 돌리거나 비효율적인 셀프 조인(Self-Join)을 남발하여 시스템 장애를 유발하곤 했습니다. 하지만 이제는 인라인 뷰(또는 WITH 절인 CTE) 내부에 RANK() OVER(PARTITION BY 부서코드 ORDER BY 연봉 DESC) 구문을 단 한 줄만 작성해 둔 다음, 바깥쪽 메인 쿼리에서 해당 순위 값이 3 이하(<= 3)인 데이터만 WHERE 절로 툭 필터링해 주면 그 어떤 대용량 데이터베이스에서도 마법처럼 빠르고 우아하게 그룹별 최상위권 데이터만을 발라낼 수 있습니다. 이 기법은 부서별 급여 탑 랭커 추출뿐만 아니라, 월별 최고 매출을 달성한 영업 지점 추출, 학생들의 과목별 최고 득점자 조회, 심지어 IoT 센서의 일자별 최고 온도 기록 추출에 이르기까지 산업군을 가리지 않고 매일같이 사용되는 SQL의 황금 패턴입니다.
5. 옵티마이저의 윈도우 소트(Window Sort)와 인덱스 튜닝 전략
순위 함수가 개발자에게 제공하는 압도적인 편리함 뒤에는 데이터베이스 엔진이 감당해야 하는 보이지 않는 무거운 비용이 숨어 있습니다. ROW_NUMBER, RANK, DENSE_RANK를 불문하고 윈도우 함수 내부의 ORDER BY 절은 필연적으로 윈도우 소트(Window Sort)라는 매우 값비싼 물리적 정렬 연산을 유발합니다. 수백만 건에서 수천만 건에 달하는 대용량 테이블을 대상으로 순위 함수를 실행할 때 별도의 최적화가 되어 있지 않다면, 데이터베이스는 이 거대한 데이터를 모두 메모리(PGA 혹은 워크 메모리)로 퍼올려 무식하게 정렬을 시도합니다. 만약 데이터의 크기가 할당된 가용 메모리 용량을 초과해 버리면, 디스크의 임시 테이블스페이스(Temp Tablespace)로 데이터를 내렸다 올렸다를 반복하는 끔찍한 페이징(Paging) I/O가 발생하며 쿼리의 수행 속도는 기하급수적으로 느려지게 됩니다. 현업에서 윈도우 함수를 사용한 리포팅 쿼리가 타임아웃(Timeout) 에러를 뿜으며 죽어버리는 가장 큰 원인이 바로 이 악명 높은 소트 부하입니다.
이러한 치명적인 성능 저하를 방지하고 윈도우 함수의 퍼포먼스를 극한으로 끌어올리기 위해서는 DBA의 섬세한 인덱스(Index) 설계와 튜닝 전략이 필수적입니다. 가장 교과서적이고 확실한 해결책은 OVER 절 내부에 명시된 컬럼들을 조합하여 결합 인덱스(Composite Index)를 미리 생성해 두는 것입니다. 즉, PARTITION BY A ORDER BY B 구문을 사용한다면 데이터베이스에 (A, B) 순서로 구성된 결합 인덱스를 걸어주는 것입니다. 이렇게 인덱스를 태우게 되면 옵티마이저는 이미 B-Tree 구조로 예쁘게 정렬되어 있는 인덱스 리프 노드를 순서대로 스캔(Index Full Scan 혹은 Range Scan)하기만 하면 되므로, 무거운 윈도우 소트 연산 자체를 아예 생략(Sort Omission)하고 실시간으로 순위 번호를 발급해 낼 수 있습니다. 또한 최근 오라클(Oracle)이나 SQL Server 등의 최신 CBO(비용 기반 옵티마이저)는 순위 함수를 래핑한 인라인 뷰 바깥쪽에 WHERE row_number <= 10 과 같은 조건이 명시되어 있으면, 전체 데이터를 끝까지 읽어 순위를 매기지 않고 상위 10개만 찾는 즉시 연산을 칼같이 멈춰버리는 Top-N 정지(Stopkey) 최적화 알고리즘을 스스로 작동시킵니다. 따라서 실무에서 순위 함수를 작성할 때는 반드시 인덱스의 구성 상태를 확인하고 실행 계획(EXPLAIN PLAN) 상에 'WINDOW NOSORT'나 'STOPKEY' 오퍼레이션이 정상적으로 잘 타고 있는지를 꼼꼼히 점검하는 습관을 들여야 완벽한 고성능 쿼리를 완성할 수 있습니다.
