[SQL] ROW_NUMBER() 함수

 

구글 클라우드(Google Cloud, GCP)에서 제공하는 완전 관리형 데이터 분석 플랫폼, 데이터웨어하우스인 빅쿼리(BigQuery)를 통신하여 자동화 프로그램을 개발하던 도중, SQL 쿼리를 최적화하고자 하는 목표를 가지고 ROW_NUMBER() 을 정리하고자 작성하는 글

---

 

목표는 불필요한 중복 제거, 쿼리 간소화 등을 통한 성능 향상을 위한 글이다.

 

ROW_NUMBER() 

- 결과 집합의 PARTITION 내 ORDER BY절에 의해 각 행에 고유값을 할당하는 윈도우 함수

- 분석 함수

 

- 적용되는 각 행(파티션의 행 또는 쿼리에 의해 반환되는 각 행)에 1부터 시작하는 order_by_clause에서 지정된 행의 순서로 고유 번호를 할당

- 지정된 범위의 ROW_NUMBER 값을 검색하는 쿼리에서 ROW_NUMBER 를 사용하여 서브 쿼리를 중첩하여 내부 쿼리 결과에서 정확한 행의 일부를 찾을 수 있다.

 

문법

PARTITION BY는 선택, ORDER BY는 필수

ROW_NUMBER() OVER(PARTITION BY [그룹핑할 컬럼] ORDER BY [정렬할 컬럼])

 

 

나누고 싶은 곳을 구역을 나누고(PARTITION BY) 정렬해서 순서(ORDER BY)를 정하거나

ROW_NUMBER() OVER (PARTITION BY ~ ORDER BY ~)

 

원하는 컬럼에 대해 정렬만 해서 순서(ORDER BY)를 정하는 방법이 있다.

ROW_NUMBER() OVER(ORDER BY ~)

 

 

예제

SELECT department_id, first_name, last_name, salary
FROM
  (
    SELECT
      department_id, first_name, last_name, salary,
      ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary desc) rn
    FROM employees
  )
WHERE rn <= 3
ORDER BY department_id, salary DESC, last_name;

 

- 서브쿼리로 ROW_NUMBER 를 이용하여 department_id 별로 파티션을 나누고(PARTITION BY), 나눠진 상태에서 급여를 내림차순으로 정렬(ORDER BY salary desc)

- 서브쿼리 바깥인 메인 쿼리에서 WHERE 절에 rn <= 3 을 이용해 상위 3명의 정보 확인

- ORDER BY로 WHERE 절로 뽑은 상위 3명에 대해 오름차순 정렬

(ORDER BY 정렬 기준 default 는 오름차순인 ASC)