[Oracle] OVER (PARTITION BY / ORDER BY) 문법과 예제 - 순위/집계 윈도우 함수 활용하기

 

목차

1. OVER  란?
    1-1. 문법 구조
    1-2. 기본 예제

2. OVER 확장 옵션
    2-1. PARTITION BY
    2-2. ORDER BY

3. 활용 포인트
    3-1. 순위 및 누적 계산
    3-2. 그룹 내 비율 분석
    3-3. 데이터 검증 및 이상치 탐지

4. 정리

 

 

1. OVER 란?

OVER 절은 윈도우 함수에서 사용되며 그룹별로 집계나 순위를 계산할 때 매우 유용합니다.

 

일반적인 집계 함수(SUM, AVG, COUNT 등)는 GROUP BY로 데이터를 묶어서 결과를 한 줄로 반환하지만,

OVER를 사용하면 그룹화하지 않고도 각 행별로 집계 결과를 함께 확인할 수 있습니다.

 

    1-1. 문법 구조

함수명() OVER (PARTITION BY 컬럼명 ORDER BY 컬럼명)

 

OVER() 만 단독으로 사용하면 전체 데이터를 하나의 그룹으로 간주합니다.

즉, 행은 그대로 유지되면서 모든 행에 동일한 집계값이 들어가게 됩니다.

 

실제 데이터와 쿼리를 통해 더 자세히 알아보겠습니다.

 

    1-2. 기본 예제

 

[데이터]

 

NAME	SAL
KING	5000
SCOTT	3000
SAM	2000
JONE	3000

 

[쿼리]

 

SELECT NAME, SAL, SUM(SAL) OVER() AS TOTAL_SAL
FROM EMP;

 

[결과 데이터]

 

NAME	VAL	TOTAL_SAL
KING	5000	13000
SCOTT	3000	13000
SAM	2000	13000
JONE	3000	13000

 

이렇게 OVER의 결과로 SAL 값이 전부 합쳐진 TOTAL_SAL 이라는 컬럼이 추가되었음을 확인할 수 있습니다.

2. OVER 확장 옵션

OVER의 문법 구조를 보면, 괄호 안에 두 가지의 추가 옵션이 있는 것을 알 수 있습니다.

이는 특정 그룹이나 순서를 기준으로 더 정교한 분석을 수행하기 위한 옵션입니다.

 

    2-1. PARTITION BY

PARTITION BY는 데이터를 그룹별로 나누어 계산하는 옵션입니다. 

전체 데이터가 아니라 특정 기준으로 구분된 그룹 단위로 결과를 계산하고 싶을 때 사용합니다.

 

예를 들어, 아래와 같은 데이터가 있다고 가정해봅시다.

 

DEPT_NO	NAME	SAL
10	KING	5000
10	SCOTT	3000
20	SAM	2000
20	JONE	3000

 

PARTITION BY를 사용하면 각 부서별(DEPT_NO)로 평균 판매량을 구할 수 있습니다.

 

SELECT 
    DEPT_NO,
    NAME,
    SAL,
    AVG(SAL) OVER (PARTITION BY DEPT_NO) AS DEPT_AVG
FROM EMP;

 

DEPT_NO	NAME	SAL	DEPT_AVG
10	KING	5000	4000
10	SCOPP	3000	4000
20	SAM	2000	2500
20	JONE	3000	2500

 

결과 데이터를 보면 부서 번호(DEPT_NO)별로 평균이 구해진 것을 볼 수 있습니다.

이처럼 PARTITION BY는 그룹화된 데이터 내에서 계산하되, 원본 행을 유지해야할 때 매우 유용한 기능입니다.

 

    2-2. ORDER BY

ORDER BY는 그룹 내에서 순서를 지정할 때 사용하는 옵션입니다.

 

즉, PARTITION BY로 그룹을 나누고 그 그룹 안에서 정렬 기준을 부여해 계산 순서를 정의합니다.

 

아래는 각 부서 내에서 급여가 높은 순서대로 누적합을 계산하는 예제입니다.

 

DEPT_NO	NAME	SAL
10	KING	5000
10	SCOPP	3000
20	SAM	2000
20	JONE	3000

 

SELECT
    DEPT_NO,
    NAME,
    SAL,
    SUM(SAL) OVER (PARTITION BY DEPT_NO ORDER BY SAL DESC) AS CUM_SAL
FROM EMP;

 

쿼리 실행 결과는 다음과 같습니다.

 

DEPT_NO	NAME	SAL	CUM_SAL
10	KING	5000	5000
10	SCOPP	3000	8000
20	SAM	2000	3000
20	JONE	3000	5000

 

3. 활용 포인트

OVER 함수는 단순히 순위를 매기는 것뿐만 아니라, 집계 결과를 행 단위로 함께 조회할 수 있어 유용합니다.

 

    3-1. 순위 및 누적 계산

SUM(), AVG(), RANK() 등과 함께 사용하면 다양한 통계 계산을 구현할 수 있습니다.

 

    3-2. 데이터 비교 및 분석

PARTITION BY를 활용해 그룹별로 구간 통계나 전후 비교가 가능합니다.

 

    3-3. 서브쿼리 최소화

집계 결과를 별도의 서브쿼리 없이 바로 조회할 수 있에 SQL의 가독성과 성능이 개선됩니다.

 

5. 정리

OVER (PARTITION BY 컬럼명 ORDER BY 컬럼명) 함수는 리포트 조회, 대시보드 통계, 월별 변화율 등 분석용 쿼리 작성시에 자주 사용되니 숙지해두길 바랍니다.

 

여기까지 수고하셨습니다.

감사합니다.