[SQLD 15화] ROW_NUMBER 순위 함수와 QUALIFY 절을 활용한 중복 데이터 제거 실무 치트키 (SQLD, 직장인 SQL)

작성자:

1. 정밀한 데이터 랭킹의 시작: ROW_NUMBER 함수의 본질

14화에서 우리는 단 한 번의 조회만으로 대그룹, 중그룹의 중간 소계와 전사 최종 총합계를 입체적으로 나열하여 보고서의 서식을 완성하는 GROUP BY ROLLUP 확장 문법의 특성을 살펴보았습니다. 계층별 요약 통계를 산출하는 집계 기법을 마스터하고 나면, 분석가는 데이터의 전체 행수나 구조를 파괴하지 않은 채 ‘개별 행 옆에 정밀한 순위를 부여하고 특정 순위만 골라내야 하는’ 고난도 비즈니스 요구사항과 마주하게 됩니다.

현업 실무나 데이터 자격증(SQLD 등) 시험에서 단골로 등장하는 요청은 대개 “고객별로 가장 최근에 발생한 주문 내역 1건만 뽑아달라”거나 “상품 카테고리별로 매출액 기준 1등부터 5등까지의 우수 상품 라인업을 나열해 달라”는 식의 순위 기반 필터링입니다.

일반적인 GROUP BY 절은 데이터를 그룹 단위로 집계하기 때문에 상세 내역을 유지한 채 순위를 매길 수 없습니다. 이때 데이터의 각 행에 중복 없는 고유한 일련번호를 순서대로 부여해 주는 강력한 분석용 무기가 바로 ROW_NUMBER() 함수입니다. 본 연작 시리즈는 대용량 처리에 특화된 테라데이터 SQL 환경을 기준으로 순위 함수의 문법 규칙과 중복 정제 가이드를 설명하겠습니다.

2. ROW_NUMBER Window 함수의 기본 문법 구조

ROW_NUMBER() 함수는 SQL 문법 분류상 ‘윈도우 함수(Window Function)’ 또는 ‘OLAP 함수’에 속하며, 일반적인 함수와 달리 뒤에 OVER 절을 필수적으로 동반하는 독특한 문법 구조를 취합니다.

-- ROW_NUMBER 윈도우 함수의 가장 표준적인 문법 구조
SELECT 
    CUST_NO
  , ORDER_DT
  , SALE_AMT
  , ROW_NUMBER() OVER (
        PARTITION BY CUST_NO     -- 순위를 나누어 매길 그룹 기준 (생략 가능)
        ORDER BY ORDER_DT DESC   -- 그룹 내에서 줄을 세울 정렬 기준
    ) AS RNK
FROM 
    TABLE_NAME;

💡 윈도우 구성 요소별 핵심 문법 규칙

  • PARTITION BY (그룹화 기준): 전체 데이터를 특정 칼럼 값을 기점으로 쪼개어 가상의 독립된 방(Partition)을 만드는 역할을 합니다. 위 예시에서는 회원번호(CUST_NO)별로 방을 나누었기 때문에, 회원별로 각각 1등부터 순위가 새롭게 시작되는 구조를 형성합니다. 만약 전체 데이터셋 기준 통째로 순위를 매기고 싶다면 이 구문은 생략해도 무방합니다.
  • ORDER BY (정렬 기준): 쪼개진 방 안에서 어떤 칼럼을 기준으로 줄을 세워 1등을 부여할지 결정하는 핵심 장치입니다. 내림차순(DESC)을 지정하면 가장 큰 값이나 가장 최신 날짜가 1등(RNK = 1)을 차지하게 됩니다. 실무에서는 반드시 ORDER BY를 지정하는 것이 좋습니다.

3. 현업 비즈니스 사례로 보는 이력 중복 데이터 제거의 당면 과제

ROW_NUMBER() 함수가 실무에서 가장 압도적인 가치를 발휘하는 순간은 단연 ‘누적 이력 데이터에서 최신 건만 남기고 중복을 솎아낼 때’입니다. 이해를 돕기 위해 실제 금융권이나 이커머스 분석 부서에서 매일 같이 겪는 가상의 시나리오를 살펴보겠습니다.

📊 [실무 사례 예시: 마케팅 팀 최 대리의 최신 계약 정보 매칭]

카드사 데이터 분석 담당자인 최 대리는 마케팅 부서로부터 “현재 당사 회원들의 카드 상품 계약 테이블에서 고객별로 가장 최근에 가입한 ‘최신 계약 내역 1건’만 남기고 과거 가입 이력은 전부 제외한 정제된 마스터 명단을 추출해 달라”는 요청을 받았습니다.

해당 테이블(CUSTOMER_CONTRACT_HIST)은 고객이 카드를 해지하거나 재발급할 때마다 행이 무제한 누적되는 대용량 이력 테이블이었습니다. 최 대리는 고객별(CUST_NO)로 그룹을 논리적으로 구분하여 계약일자(CONTRACT_DT)가 가장 최신인 행에 1등을 매기면 되겠다고 판단했습니다.

만약 이 상황을 타 DBMS(Oracle, MySQL 등) 환경이나 일반적인 SQL 문법 규칙으로 해결하려고 했다면, 다음과 같이 11화에서 배운 인라인 뷰(Inline View) 서브쿼리를 바깥에 한 번 더 감싸서 1등만 필터링하는 복잡한 구조의 코드를 작성해야만 합니다.

-- [타 DBMS 일반 방식] 순위를 매긴 후 바깥에서 서브쿼리로 한 번 더 감싸 필터링하는 쿼리
SELECT 
    V.CUST_NO, V.CONTRACT_NO, V.CONTRACT_DT
FROM (
    SELECT 
        CUST_NO, CONTRACT_NO, CONTRACT_DT
      , ROW_NUMBER() OVER (PARTITION BY CUST_NO ORDER BY CONTRACT_DT DESC) AS RN
    FROM 
        DW_MART.CUSTOMER_CONTRACT_HIST
) V
WHERE 
    V.RN = 1; -- 표준 SQL 규칙상 윈도우 함수는 WHERE 절에 직접 쓸 수 없어 서브쿼리 강제됨

표준 SQL 문법 규칙상 WHERE ROW_NUMBER() OVER(...) = 1 과 같은 문장은 실행할 수 없습니다. SQL의 논리적 처리 순서상 WHERE 절은 윈도우 함수가 계산되기 전에 수행되므로, WHERE 절에서는 ROW_NUMBER()와 같은 윈도우 함수 결과를 직접 참조할 수 없습니다.

4. 테라데이터만의 독보적인 실무 치트키: QUALIFY 절의 등장

본 시리즈의 기준인 테라데이터(Teradata) SQL 환경에서는 이러한 서브쿼리의 번거로움을 단번에 해결해 주는 독자적이고 강력한 전용 조건절인 QUALIFY 구문을 제공합니다.

QUALIFY 절은 쉽게 말해 “윈도우 함수 전용 WHERE 절”이라고 이해하시면 직관적입니다. 이 구문을 활용하면 바깥쪽에 인라인 뷰 서브쿼리를 무겁게 선언할 필요 없이, 단 하나의 평탄한 쿼리문 안에서 순위 매기기와 1등 필터링을 동시에 종결할 수 있는 탁월한 문법적 대안이 됩니다.

-- [테라데이터 치트키 예시] QUALIFY 절을 활용해 서브쿼리 없이 최신 1건만 솎아내는 최적화 쿼리
SELECT 
    CUST_NO
  , CONTRACT_NO
  , CONTRACT_DT
FROM 
    DW_MART.CUSTOMER_CONTRACT_HIST
WHERE 
    BASE_YYMM = '202607' -- 대용량 테이블 모수를 날짜 필터로 선제 압축
QUALIFY 
    ROW_NUMBER() OVER (PARTITION BY CUST_NO ORDER BY CONTRACT_DT DESC) = 1; -- 즉시 1등만 필터링

💡 QUALIFY 구문 적용의 비즈니스적 가치 분석

최 대리가 테라데이터의 QUALIFY 치트키를 적용한 결과, 코드가 수 줄 이상 단축되어 쿼리의 시각적 가독성이 극대화되었습니다.

QUALIFY를 사용하면 인라인 뷰를 작성하지 않아도 되므로 SQL이 훨씬 간결해지고 유지보수가 쉬워집니다. 또한 테라데이터 옵티마이저가 윈도우 함수 결과를 효율적으로 처리할 수 있어 복잡한 중첩 SQL을 줄이는 데 도움이 됩니다.

최종 결과 화면에는 ORDER BY 기준으로 가장 우선순위가 높은 1행만 남게 됩니다.

5. 팩트 체크 및 데이터 분석가의 관점 점검 (RANK 함수와의 차이)

기술 콘텐츠의 신뢰성과 정밀도를 보장하기 위해, 실무에서 순위 함수를 구사할 때 가장 많이 혼동하거나 실수하는 사실 관계들을 명확히 점검해 보겠습니다.

  • ROW_NUMBER 함수와 RANK 함수는 완전히 동일하게 작동하는가?
    • 팩트 체크: 그렇지 않습니다. 동일한 정렬 기준 값을 가진 ‘공동 순위(동점자)’가 발생했을 때 두 함수의 메커니즘은 확연히 갈라집니다.
    • 예를 들어 두 고객의 구매 금액이 10,000원으로 완전히 똑같은 동점 상황일 때, ROW_NUMBER() 함수는 동일한 정렬값(동점)이 존재하더라도 각 행에 서로 다른 순번을 부여합니다. 반면 RANK() 함수는 공동 순위를 인정하여 1등, 1등을 부여한 뒤 다음 순위를 3등으로 건너뛰는 정량적 속성을 지닙니다.
    • 따라서 중복 데이터를 완벽히 제거하고 무조건 ‘단 1행’만 깔끔하게 남겨야 하는 실무 정제 마트 구축 목적이라면, 등수가 겹칠 위험이 없는 ROW_NUMBER()를 매칭하는 것이 안전한 대안으로 권장됩니다.
  • QUALIFY 절을 사용할 때 SELECT 절에 순위 칼럼을 반드시 명시해야 하는가?
    • 팩트 체크: 명시하지 않아도 완전히 무방합니다. QUALIFY 절의 독특한 장점 중 하나는, 순위를 매기는 연산은 내부적으로 수행하여 필터링하되 굳이 최종 SELECT 화면에 순위 번호 칼럼(1이라는 숫자들)을 노출하고 싶지 않다면 SELECT 절에서 해당 구문을 과감히 생략해도 정상 작동한다는 점입니다. 이 덕분에 보고서 서식에 불필요한 등수 칼럼을 숨겨서 깔끔한 규격의 원천 데이터를 뽑아낼 수 있습니다.

6. 15화 요약 및 실무 순위 데이터 정제 체크리스트

쿼리를 빌드하고 전사 데이터 파이프라인 스크립트에 상정하기 전, 로직의 결점이 없는지 스스로 진단할 수 있는 요약 가이드를 제공합니다.

체크포인트발생 가능한 실무 리스크현직 분석가의 권장 대안 및 조치
동점자 발생 시 행 누락 리스크RANK() 사용 시 공동 1등이 발생하여 최종 결과가 1건 이상으로 뻥튀기됨무조건 1건만 추출하는 이력 정제 목적이라면 ROW_NUMBER()로 함수 교체
QUALIFY 내 ORDER BY 누락ORDER BY가 없으면 오류가 발생하거나 원하는 순위를 보장할 수 없음순위의 선후 관계를 규정하는 ORDER BY 정렬 기준을 윈도우 내에 필수 기재
과도하게 넓은 PARTITION 지정대용량 분산 환경에서 불필요한 자원 정렬 부하 유발순위를 나누어 매길 필요가 없는 전사 순위라면 PARTITION BY 과감히 생략 유도

7. 결론 및 다음 화 예고

이번 15화에서는 데이터의 행 구조를 파괴하지 않은 채 정밀한 순위를 매겨주는 ROW_NUMBER 함수의 문법적 속성을 살펴보고, 서브쿼리의 번거로움을 획기적으로 걷어내어 최신 1건의 데이터만 신속하게 정제해 내는 테라데이터 전용 QUALIFY 절의 실무 리팩토링 치트키를 다각도로 점검해 보았습니다. 중복된 이력 데이터의 파편 속에서 명확한 기준에 부합하는 유일한 유효 행을 정교하게 솎아내는 역량은 데이터 마트의 신뢰성을 지켜내는 훌륭한 밑거름이 됩니다.

순위를 매겨 데이터를 제어하는 분석형 윈도우 함수의 기본 뼈대를 이해한 분석가가 다음 단계로 확장하게 되는 영역은, 행 간의 경계를 넘어 유연한 집계를 수행하는 일입니다.

이어지는 16화에서는 ROW_NUMBER에 이어 윈도우 함수의 진정한 정점이자 확장판이라 불리는 OVER 절 기반의 집계 윈도우 함수 기초를 집중 분석합니다. 일반적인 GROUP BY처럼 행을 압축해 버리지 않고, 상세 거래 내역 행들을 그대로 유지한 상태에서 우측 가상 칼럼에 ‘실시간 누적 합계(Cumulative Sum)’를 구하거나 ‘이동 평균(Moving Average)’을 자유롭게 산출해 내는 현직자들의 고급 데이터 가공 분석 테크닉을 비즈니스 사례와 함께 상세히 파악해 보겠습니다.

댓글 남기기