홈 › SQL 고급 › 02 / 10

집계 분석 함수와 윈도 프레임

SUM OVER, 누적합, 이동 평균, 비율

섹션 6진행 0 / 10

2. 핵심 원리

2.1 OVER 절의 세 부분

text

집계함수(컬럼) OVER (
    PARTITION BY 그룹 컬럼           -- 생략하면 전체가 한 그룹
    ORDER BY 정렬 컬럼               -- 생략하면 순서 없음
    ROWS|RANGE BETWEEN 시작 AND 끝   -- 프레임, 생략하면 기본값
)

부분	하는 일	생략하면
`PARTITION BY`	행을 그룹으로 나눈다	전체가 한 그룹
`ORDER BY`	그룹 안의 행 순서를 정한다	그룹 전체가 프레임
프레임	각 행이 집계에 쓰는 범위	아래 2.2절의 기본값

OVER () 처럼 괄호를 비워 두면 전체 행이 하나의 그룹이 되어, 모든 행에 같은 전체 합계가 붙습니다. 이 값으로 비율(amt * 100.0 / SUM(amt) OVER ())을 구합니다.

2.2 프레임과 기본값

프레임은 "현재 행을 기준으로 집계에 넣을 행의 범위"입니다. 범위의 양 끝은 다음 다섯 가지 중에서 고릅니다.

경계	뜻
`UNBOUNDED PRECEDING`	그룹의 첫 행
`n PRECEDING`	현재 행보다 n행 앞
`CURRENT ROW`	현재 행
`n FOLLOWING`	현재 행보다 n행 뒤
`UNBOUNDED FOLLOWING`	그룹의 마지막 행

OVER 에 ORDER BY 만 쓰고 프레임을 생략하면, 기본 프레임은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 입니다. 표준이며 Oracle·Tibero·MySQL·MSSQL 이 모두 같습니다. 그래서 SUM(amt) OVER (ORDER BY ord_date) 는 첫 행부터 현재 행까지의 누적합이 됩니다.

2.3 ROWS 와 RANGE 의 차이

ROWS 는 정렬된 행의 위치를 세고, RANGE 는 ORDER BY 값이 같은 행(동점 행)을 한 덩어리로 봅니다.

구분	기준	같은 정렬 값의 행
`ROWS`	물리적 행 위치	하나씩 따로 더한다
`RANGE`	정렬 값의 범위	함께 더한다

핵심
기본 프레임이 RANGE 라서, 정렬 기준이 같은 행이 2건이면 두 행 모두 "그 값까지 전부 더한 합계"를 받습니다. 행 단위 누적합이 필요하면 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 를 명시하고, ORDER BY 에 유일한 컬럼(id)을 덧붙입니다.

2.4 DB 별 지원 범위

기능	Oracle·Tibero	MySQL	MSSQL
윈도 함수	Oracle 8i 부터, Tibero 지원	8.0 부터	2005 부터
`OVER (PARTITION BY)` 집계	지원	8.0 부터	2005 부터
집계의 `OVER (ORDER BY)`·프레임	지원	8.0 부터	2012 부터
`RANGE` 숫자·`INTERVAL` 오프셋	가능	8.0 부터 가능	불가
`RATIO_TO_REPORT`	지원	없음	없음

MSSQL 은 2005 에 OVER (PARTITION BY) 집계만 되고, 집계 함수의 누적합(OVER (ORDER BY))과 ROWS·RANGE 프레임은 2012 부터 됩니다. MSSQL 의 RANGE 는 UNBOUNDED·CURRENT ROW 만 허용하고 RANGE 3 PRECEDING 같은 숫자 오프셋은 쓸 수 없습니다.